PostgreSQL笔记60: 权限与角色管理——从层级体系到最佳实践

PostgreSQL(七)权限管理 3、对超级用户 postgres不做权限检查,其它用户走ACL(Access Control list);2、schema:PG的一个用户可以有多个schema,但一个schema只能属于一个用户;(可用此预防表被误删除)1、实例权限:控制部分用户、主机是否允许登录、访问数据库(白名单&黑名单);1、每个数据库对象都有一个所有者,默认情况下,所有者拥有该对象的所有权限;4、对于数据库对象,开始只有所有者和超级用户可以做任何操作,其它走ACL;3、object:表、视图、索引等对象的增删改查的权限。 阅读详情

概述

PostgreSQL 的权限管理体系设计精细且层次分明。与某些数据库系统相对简化的权限模型不同,PostgreSQL 要求用户从实例连接层开始,逐级通过数据库、Schema、表、列乃至行级安全(Row Level Security)的层层校验,才能最终访问到目标数据。这种层次化的设计虽然在初次接触时略显复杂,但为数据安全提供了极为细粒度的控制能力。

本文将从权限层级体系出发,深入解析 PostgreSQL 中 Role 与 User 的本质区别、预定义角色的使用场景、角色删除的标准流程,以及权限审计与巡检的最佳实践。

权限层级体系

PostgreSQL 的权限控制遵循一个清晰的层次结构。用户要访问最终数据,必须依次获得以下各层的授权:

客户端连接

pg_hba.conf 认证

实例级别
连接权限

数据库级别
CONNECT 权限

Schema 级别
USAGE 权限

表/视图级别
SELECT/INSERT/UPDATE/DELETE 等

列级别
列级权限

行级别
Row Level Security 策略

访问最终数据

各层级对应的核心权限类型如下:

层级对象核心权限说明
实例级数据库集群pg_hba.conf白名单控制,决定能否建立网络连接
数据库级DATABASECONNECT, CREATE, TEMP控制能否连接到特定数据库
Schema 级SCHEMAUSAGE, CREATE控制能否使用或在该 Schema 下创建对象
表级TABLESELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER控制对表数据的操作
列级COLUMNSELECT(column), UPDATE(column)控制对特定列的访问
行级ROWRLS 策略通过行级安全策略控制行级访问

值得注意的是,表级权限不会从 Schema 级别的授权中自动继承。即使用户拥有某个 Schema 的 USAGE 权限,若未被显式授予该 Schema 下某张表的 SELECT 权限,仍然无法查询该表的数据。

以下示例演示了一个完整的权限授予流程:

-- 1. 创建数据库
CREATE DATABASE mydb;

-- 2. 创建 Schema
CREATE SCHEMA app_schema;

-- 3. 创建角色(无登录权限,作为权限模板)
CREATE ROLE app_readonly;

-- 4. 授予 Schema 使用权
GRANT USAGE ON SCHEMA app_schema TO app_readonly;

-- 5. 授予表查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_readonly;

-- 6. 创建用户(具有登录权限)
CREATE USER app_user WITH PASSWORD 'secure_password';

-- 7. 将角色授予用户
GRANT app_readonly TO app_user;

-- 8. 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO app_user;

若在上述任意一层缺少相应权限,访问将被拒绝。例如,仅回收数据库的连接权限即可阻止用户访问:

-- 回收数据库连接权限
REVOKE CONNECT ON DATABASE mydb FROM app_user;

-- 注意:由于 PUBLIC 是特殊角色,代表所有用户,可能还需要额外回收
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

Role 与 User 的本质区别

在 PostgreSQL 中,Role(角色) 和 User(用户) 在本质上并无区别——二者都是 Role。唯一的差异在于默认属性:

  • CREATE ROLE 创建的角色默认 不具有 LOGIN 属性(即 NOLOGIN)
  • CREATE USER 创建的角色默认 具有 LOGIN 属性

因此,可以这样理解:

CREATE USER name 等价于 CREATE ROLE name LOGIN。

Role 的核心设计理念是作为权限的集合(权限模板)。在实际生产环境中,推荐的最佳实践是:

  1. 根据业务需求创建多个 不带 LOGIN 属性的 Role,每个 Role 代表一组特定的权限集合(如只读、读写、管理员等)
  2. 将相应的 Role 授予给实际的 User(带 LOGIN 属性的 Role)
  3. 当权限需求发生变化时,只需修改 Role 的权限,所有授予了该 Role 的 User 将自动继承变更

这种设计模式类似于班级中的“课代表”“劳动委员”等角色——角色定义了职责和权力范围,具体同学被授予某个角色后即获得相应的权力。

-- 创建组角色(无登录权限)
CREATE ROLE read_only_role;
CREATE ROLE read_write_role;

-- 授予组角色权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write_role;

-- 创建用户并授予组角色
CREATE USER alice WITH PASSWORD 'xxx';
CREATE USER bob WITH PASSWORD 'xxx';

GRANT read_only_role TO alice;   -- alice 只有只读权限
GRANT read_write_role TO bob;    -- bob 拥有读写权限

权限继承机制

PostgreSQL 支持 Role 之间的继承关系。当一个 Role 被授予给另一个 Role 时,成员 Role 会自动获得父 Role 的所有权限。

-- 创建基础角色
CREATE ROLE viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewer;

-- 创建高级角色,继承 viewer
CREATE ROLE editor;
GRANT viewer TO editor;          -- editor 自动拥有 viewer 的所有权限
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor;

-- 创建用户并授予 editor
CREATE USER charlie WITH PASSWORD 'xxx';
GRANT editor TO charlie;         -- charlie 拥有 viewer + editor 的全部权限

继承关系是传递性的:若 A 继承 B,B 继承 C,则 A 自动拥有 B 和 C 的全部权限。但需要注意,继承链会在遇到 NOINHERIT 属性的 Role 时终止。

对于拥有高权限的角色(如 SUPERUSER、CREATEDB、CREATEROLE 等),建议设置 NOINHERIT 属性以减少权限提升的风险。

预定义角色

PostgreSQL 提供了一组预定义角色(Predefined Roles),用于简化常见权限管理场景。这些角色在 PostgreSQL 14 及更高版本中得到了显著增强。

以下是常用预定义角色的汇总:

预定义角色权限说明引入版本
pg_read_all_data读取所有表、视图、序列的数据,如同拥有 SELECT 权限;对所有 Schema 拥有 USAGE 权限PostgreSQL 14
pg_write_all_data写入所有表、视图、序列的数据,如同拥有 INSERT、UPDATE、DELETE 权限PostgreSQL 14
pg_monitor读取/执行各种监控视图和函数。是 pg_read_all_settings、pg_read_all_stats、pg_stat_scan_tables 的成员角色PostgreSQL 10
pg_signal_backend向其他后端进程发送信号,以取消查询或终止会话PostgreSQL 9.6
pg_read_all_settings读取所有配置变量,包括通常仅对超级用户可见的变量PostgreSQL 10
pg_read_all_stats读取所有 pg_stat_* 视图及统计相关信息PostgreSQL 10
pg_stat_scan_tables执行可能对表持有 ACCESS SHARE 锁的监控函数PostgreSQL 10
pg_database_owner隐式成员关系,成员为当前数据库的 OwnerPostgreSQL 14
pg_read_server_files通过 COPY 及其他文件访问函数读取服务器文件PostgreSQL 10
pg_write_server_files通过 COPY 及其他文件访问函数写入服务器文件PostgreSQL 10
pg_execute_server_program以数据库运行用户身份执行服务器端程序PostgreSQL 10

使用示例:

-- 授予只读权限(PostgreSQL 14+)
GRANT pg_read_all_data TO app_readonly_user;

-- 授予读写权限(PostgreSQL 14+)
GRANT pg_write_all_data TO app_readwrite_user;

-- 授予监控权限
GRANT pg_monitor TO monitoring_user;

-- 授予终止会话权限
GRANT pg_signal_backend TO admin_user;

需要注意的是,pg_read_all_data 和 pg_write_all_data 不包含 BYPASSRLS 属性。如果数据库中启用了行级安全(RLS),管理员可能需要为被授予这些角色的用户额外设置 BYPASSRLS。

在 PostgreSQL 14 之前,要实现只读用户需要逐表授予 SELECT 权限并回收默认权限,操作较为繁琐。PostgreSQL 14 引入的 pg_read_all_data 和 pg_write_all_data 极大地简化了这一流程。

PUBLIC 伪角色

PUBLIC 是一个特殊的伪角色,代表 所有角色(包括当前已存在和将来可能创建的角色)。每个新创建的用户都会自动成为 PUBLIC 的成员。

关键注意事项:

  • 某些对象类型在创建时会默认授予 PUBLIC 一定的权限
  • 例如,public Schema 默认授予 PUBLIC 的 CREATE 权限
  • 回收单个用户的权限时,必须同时从 PUBLIC 回收,否则该用户仍可能通过 PUBLIC 保留权限
-- 创建测试用户
CREATE USER test_user WITH PASSWORD 'xxx';

-- 仅回收 test_user 的权限是不够的
REVOKE CONNECT ON DATABASE mydb FROM test_user;

-- 还必须从 PUBLIC 回收
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

-- 更安全的做法:在 template1 中预先回收,使所有新建数据库默认安全
\c template1
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

删除角色

删除一个曾拥有对象或权限的角色,并非简单的 DROP ROLE 操作。PostgreSQL 要求先处理该角色拥有的所有对象及其被授予的权限。

标准删除流程:

REASSIGN OWNED

DROP OWNED

重复执行
每个数据库

DROP ROLE

步骤说明:

  1. REASSIGN OWNED BY old_role TO new_role:将 old_role 拥有的所有对象的所有权转移给 new_role
  2. DROP OWNED BY old_role:删除 old_role 拥有的、尚未转移所有权的对象,并移除授予 old_role 的所有权限
  3. 在每个包含 old_role 所拥有对象的数据库中重复执行上述两步
  4. DROP ROLE old_role:最后删除角色本身

重要提示:REASSIGN OWNED 无法处理跨数据库的对象,且不会移除授予给目标角色的权限(这些权限不属于该角色拥有)。因此,必须先执行 REASSIGN OWNED,再执行 DROP OWNED,顺序不可颠倒。

错误示例(直接 DROP ROLE):

-- 直接删除会报错
DROP ROLE u1;
-- ERROR:  role "u1" cannot be dropped because some objects depend on it
-- DETAIL:  privileges for table t1
--          privileges for table t2
--          ...

正确示例:

-- 1. 在每个相关数据库中转移所有权
\c db1
REASSIGN OWNED BY u1 TO u2;

\c db2
REASSIGN OWNED BY u1 TO u2;

-- 2. 在每个相关数据库中移除权限和残留对象
\c db1
DROP OWNED BY u1;

\c db2
DROP OWNED BY u1;

-- 3. 最后删除角色
DROP ROLE u1;

DROP OWNED 会删除目标角色拥有的对象,因此执行前请确保所有需要保留的对象已通过 REASSIGN OWNED 转移给其他角色。

权限审计与巡检

PostgreSQL 本身不提供内置的权限差异对比功能。但可以通过社区扩展或自定义查询来实现权限审计。

pg_permissions 扩展

pg_permissions 是一个第三方扩展,用于审查和对比数据库对象权限。

安装与使用:

-- 安装扩展
CREATE EXTENSION pg_permissions SCHEMA public;

-- 定义期望的权限配置
INSERT INTO public.permission_target (role_name, permissions, object_type, schema_name)
VALUES ('appuser', '{SELECT,INSERT,UPDATE,DELETE}', 'TABLE', 'appschema');

-- 查看权限差异(期望权限与实际权限的对比)
SELECT * FROM public.permission_diffs();
-- 返回缺失的权限和多余的权限

-- 查看实际权限
SELECT * FROM schema_permissions 
WHERE role_name = 'appuser' AND schema_name = 'appschema';
-- 返回该角色在指定 Schema 上的实际权限

该扩展提供的视图包括:

  • database_permissions:数据库级权限
  • schema_permissions:Schema 级权限
  • table_permissions:表级权限
  • view_permissions:视图级权限
  • column_permissions:列级权限
  • function_permissions:函数权限
  • sequence_permissions:序列权限
  • all_permissions:所有对象的权限合集

自定义权限查询

若不安装扩展,也可以通过查询系统目录来审查权限:

-- 查看角色的所有权限(表级)
SELECT 
    grantor,
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'appuser';

-- 查看角色的角色成员关系
SELECT 
    r.rolname AS role_name,
    m.rolname AS member_name,
    r.rolinherit
FROM pg_roles r
JOIN pg_auth_members am ON am.roleid = r.oid
JOIN pg_roles m ON m.oid = am.member;

-- 查看角色的登录权限等属性
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
WHERE rolcanlogin = true;

API 速览

CREATE ROLE

创建新的数据库角色。

语法:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

常用选项:

选项说明
LOGIN / NOLOGIN是否允许登录。CREATE USER 默认 LOGIN,CREATE ROLE 默认 NOLOGIN
SUPERUSER / NOSUPERUSER是否拥有超级用户权限
CREATEDB / NOCREATEDB是否允许创建数据库
CREATEROLE / NOCREATEROLE是否允许创建其他角色
INHERIT / NOINHERIT是否继承所属角色的权限
BYPASSRLS / NOBYPASSRLS是否绕过行级安全策略
CONNECTION LIMIT并发连接数限制
PASSWORD设置密码

示例:

-- 创建无登录权限的组角色
CREATE ROLE app_readonly NOLOGIN;

-- 创建可登录的用户(等效于 CREATE USER)
CREATE ROLE app_user LOGIN PASSWORD 'secure_password';

-- 创建具有创建数据库权限的角色
CREATE ROLE db_admin CREATEDB;

CREATE USER

CREATE USER 是 CREATE ROLE 的别名,区别在于默认包含 LOGIN 属性。

语法:

CREATE USER name [ [ WITH ] option [ ... ] ]

示例:

-- 等效于 CREATE ROLE app_user LOGIN
CREATE USER app_user WITH PASSWORD 'secure_password';

GRANT

授予权限或角色成员关系。

授予对象权限:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

授予角色成员关系:

GRANT role_name [, ...] TO role_specification [, ...] [ WITH ADMIN OPTION ]

示例:

-- 授予表查询权限
GRANT SELECT ON TABLE users TO app_readonly;

-- 授予 Schema 使用权
GRANT USAGE ON SCHEMA public TO app_readonly;

-- 授予所有表的查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- 将角色授予用户
GRANT app_readonly TO app_user;

-- 授予预定义角色
GRANT pg_monitor TO monitoring_user;

REVOKE

回收权限或角色成员关系。

语法:

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | ... } [, ...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] table_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

示例:

-- 回收表查询权限
REVOKE SELECT ON TABLE users FROM app_readonly;

-- 回收 PUBLIC 的默认权限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 回收数据库连接权限
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

ALTER DEFAULT PRIVILEGES

设置将来创建对象的默认权限。

语法:

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

示例:

-- 设置默认权限:未来在 public Schema 中创建的所有表都对 app_readonly 可读
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

-- 设置默认权限:未来创建的函数对 PUBLIC 回收 EXECUTE 权限
ALTER DEFAULT PRIVILEGES
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

REASSIGN OWNED

将目标角色拥有的所有对象的所有权转移给另一个角色。

语法:

REASSIGN OWNED BY old_role [, ...] TO new_role

示例:

-- 将 u1 拥有的所有对象的所有权转移给 u2
REASSIGN OWNED BY u1 TO u2;

DROP OWNED

删除目标角色拥有的对象,并移除授予该角色的所有权限。

语法:

DROP OWNED BY role_name [, ...] [ CASCADE | RESTRICT ]

示例:

-- 删除 u1 拥有的对象并移除其权限
DROP OWNED BY u1;

DROP ROLE

删除数据库角色。

语法:

DROP ROLE [ IF EXISTS ] name [, ...]

示例:

-- 删除角色(需先执行 REASSIGN OWNED 和 DROP OWNED)
DROP ROLE u1;

Demo 简单示例

以下是一个完整的 Node.js 示例,演示了 PostgreSQL 权限管理的核心操作。

运行说明

环境要求:

  • Node.js 16+
  • PostgreSQL 14+
  • pg npm 包

安装依赖:

npm init -y
npm install pg

启动 PostgreSQL:

# 确保 PostgreSQL 服务已启动
pg_ctl -D /usr/local/var/postgres start

运行脚本:

node index.js

代码实现

const { Client } = require('pg');

// 数据库连接配置
const config = {
    host: 'localhost',
    port: 5432,
    database: 'postgres',
    user: 'postgres',
    password: 'postgres'
};

// 辅助函数:执行 SQL 并打印结果
async function executeSQL(client, sql, label) {
    console.log(`\n--- ${label} ---`);
    console.log(`SQL: ${sql}`);
    try {
        const res = await client.query(sql);
        if (res.rows && res.rows.length > 0) {
            console.log('结果:', JSON.stringify(res.rows, null, 2));
        } else {
            console.log('执行成功 (无返回数据)');
        }
        return res;
    } catch (err) {
        console.error(`错误: ${err.message}`);
        throw err;
    }
}

async function main() {
    const client = new Client(config);
    await client.connect();
    console.log('已连接到 PostgreSQL');

    try {
        // ============================================================
        // 1. 创建测试数据库和 Schema
        // ============================================================
        await executeSQL(client, 'CREATE DATABASE demo_db', '创建数据库');
        await executeSQL(client, '\\c demo_db', '切换到 demo_db');

        // 注意: \c 是 psql 元命令,在 node-pg 中需使用单独连接
        // 重新连接到 demo_db
        const demoClient = new Client({ ...config, database: 'demo_db' });
        await demoClient.connect();
        console.log('已连接到 demo_db');

        await executeSQL(demoClient, 'CREATE SCHEMA app_schema', '创建 Schema');

        // ============================================================
        // 2. 创建角色(无登录权限)和用户(有登录权限)
        // ============================================================
        await executeSQL(demoClient, 
            'CREATE ROLE app_readonly_role NOLOGIN', 
            '创建只读角色 (NOLOGIN)'
        );
        
        await executeSQL(demoClient, 
            'CREATE USER app_user WITH PASSWORD \'test123\'', 
            '创建用户 (默认 LOGIN)'
        );

        // ============================================================
        // 3. 创建测试表
        // ============================================================
        await executeSQL(demoClient, 
            `CREATE TABLE app_schema.users (
                id SERIAL PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL
            )`,
            '创建测试表'
        );

        await executeSQL(demoClient, 
            `INSERT INTO app_schema.users (name, email) VALUES 
                ('Alice', 'alice@example.com'),
                ('Bob', 'bob@example.com')`,
            '插入测试数据'
        );

        // ============================================================
        // 4. 授予权限
        // ============================================================
        // 4.1 Schema 使用权
        await executeSQL(demoClient, 
            'GRANT USAGE ON SCHEMA app_schema TO app_readonly_role',
            '授予 Schema USAGE 权限'
        );

        // 4.2 表查询权限
        await executeSQL(demoClient, 
            'GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_readonly_role',
            '授予表 SELECT 权限'
        );

        // 4.3 数据库连接权限
        await executeSQL(demoClient, 
            'GRANT CONNECT ON DATABASE demo_db TO app_user',
            '授予数据库 CONNECT 权限'
        );

        // 4.4 将角色授予用户
        await executeSQL(demoClient, 
            'GRANT app_readonly_role TO app_user',
            '将只读角色授予用户'
        );

        // ============================================================
        // 5. 验证权限
        // ============================================================
        // 5.1 查看角色的权限
        await executeSQL(demoClient, 
            `SELECT 
                r.rolname,
                r.rolcanlogin,
                r.rolinherit
            FROM pg_roles r
            WHERE r.rolname IN ('app_readonly_role', 'app_user')`,
            '查看角色属性'
        );

        // 5.2 查看表级权限
        await executeSQL(demoClient, 
            `SELECT 
                grantee,
                table_schema,
                table_name,
                privilege_type
            FROM information_schema.table_privileges
            WHERE grantee = 'app_readonly_role'`,
            '查看表级权限'
        );

        // ============================================================
        // 6. 测试只读用户连接
        // ============================================================
        console.log('\n--- 测试只读用户连接 ---');
        const readOnlyClient = new Client({
            ...config,
            database: 'demo_db',
            user: 'app_user',
            password: 'test123'
        });
        
        try {
            await readOnlyClient.connect();
            console.log('只读用户连接成功');

            // 查询数据(应该成功)
            const queryRes = await readOnlyClient.query(
                'SELECT * FROM app_schema.users'
            );
            console.log('查询结果:', queryRes.rows);

            // 尝试写入数据(应该失败)
            try {
                await readOnlyClient.query(
                    'INSERT INTO app_schema.users (name, email) VALUES ($1, $2)',
                    ['Charlie', 'charlie@example.com']
                );
                console.log('⚠️ 警告: 只读用户居然可以写入数据!');
            } catch (writeErr) {
                console.log('✅ 写入被拒绝 (符合预期):', writeErr.message);
            }

            await readOnlyClient.end();
        } catch (connErr) {
            console.error('只读用户连接失败:', connErr.message);
        }

        // ============================================================
        // 7. 清理资源 (演示 REASSIGN OWNED + DROP OWNED + DROP ROLE)
        // ============================================================
        console.log('\n--- 清理资源 ---');
        
        // 7.1 将 app_user 拥有的对象转移给 postgres
        await executeSQL(demoClient, 
            'REASSIGN OWNED BY app_user TO postgres',
            '转移所有权 (REASSIGN OWNED)'
        );
        
        // 7.2 删除 app_user 的权限和残留对象
        await executeSQL(demoClient, 
            'DROP OWNED BY app_user',
            '删除权限和残留对象 (DROP OWNED)'
        );
        
        // 7.3 删除角色和用户
        await executeSQL(demoClient, 
            'DROP ROLE app_readonly_role',
            '删除只读角色'
        );
        await executeSQL(demoClient, 
            'DROP USER app_user',
            '删除用户'
        );

        // 7.4 删除测试表
        await executeSQL(demoClient, 
            'DROP TABLE app_schema.users CASCADE',
            '删除测试表'
        );
        await executeSQL(demoClient, 
            'DROP SCHEMA app_schema CASCADE',
            '删除 Schema'
        );

        await demoClient.end();
        console.log('\n✅ 所有测试完成,资源已清理');

    } catch (err) {
        console.error('❌ 执行失败:', err);
    } finally {
        await client.end();
    }
}

main().catch(console.error);

PostgreSQL 原生指令对照

上述 Node.js 代码对应的 PostgreSQL 原生 SQL 指令如下:

-- ============================================================
-- 1. 创建数据库和 Schema
-- ============================================================
CREATE DATABASE demo_db;
\c demo_db
CREATE SCHEMA app_schema;

-- ============================================================
-- 2. 创建角色和用户
-- ============================================================
CREATE ROLE app_readonly_role NOLOGIN;
CREATE USER app_user WITH PASSWORD 'test123';

-- ============================================================
-- 3. 创建测试表
-- ============================================================
CREATE TABLE app_schema.users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);
INSERT INTO app_schema.users (name, email) VALUES 
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

-- ============================================================
-- 4. 授予权限
-- ============================================================
GRANT USAGE ON SCHEMA app_schema TO app_readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_readonly_role;
GRANT CONNECT ON DATABASE demo_db TO app_user;
GRANT app_readonly_role TO app_user;

-- ============================================================
-- 5. 验证权限
-- ============================================================
SELECT rolname, rolcanlogin, rolinherit FROM pg_roles 
WHERE rolname IN ('app_readonly_role', 'app_user');

SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'app_readonly_role';

-- ============================================================
-- 6. 测试只读用户
-- ============================================================
-- 以 app_user 连接后执行:
SELECT * FROM app_schema.users;                    -- 成功
INSERT INTO app_schema.users (name, email) VALUES 
    ('Charlie', 'charlie@example.com');            -- 失败 (无权限)

-- ============================================================
-- 7. 清理资源
-- ============================================================
REASSIGN OWNED BY app_user TO postgres;
DROP OWNED BY app_user;
DROP ROLE app_readonly_role;
DROP USER app_user;
DROP TABLE app_schema.users CASCADE;
DROP SCHEMA app_schema CASCADE;

技术点总结

技术点说明
Role vs UserCREATE ROLE 默认 NOLOGIN,CREATE USER 默认 LOGIN
权限层级数据库连接 → Schema → 表 → 列/行,逐层授权
角色继承通过 GRANT role TO member 实现权限继承
预定义角色pg_read_all_data、pg_write_all_data(PG 14+)、pg_monitor 等
PUBLIC 伪角色代表所有用户,回收权限时需同时从 PUBLIC 回收
删除角色必须按 REASSIGN OWNED → DROP OWNED → DROP ROLE 顺序执行

多语言示例

以下分别使用 Go、Python 和 Java 实现了与 Node.js 示例完全相同的 PostgreSQL 权限管理演示逻辑:创建数据库、Schema、角色、用户、表,授予权限,验证只读用户行为,最后清理资源。

Go 示例

运行说明

环境要求:

  • Go 1.18+
  • PostgreSQL 14+
  • 依赖库:github.com/lib/pq(PostgreSQL 驱动)

安装依赖:

go mod init demo
go get github.com/lib/pq

启动 PostgreSQL:

pg_ctl -D /usr/local/var/postgres start

运行脚本:

go run main.go
代码实现
package main

import (
    "database/sql"
    "fmt"
    "log"
    "strings"

    _ "github.com/lib/pq"
)

// 数据库连接配置
const (
    host     = "localhost"
    port     = 5432
    user     = "postgres"
    password = "postgres"
    dbname   = "postgres"
)

// 辅助函数:执行 SQL 并打印结果
func execSQL(db *sql.DB, sqlStr string, label string) error {
    fmt.Printf("\n--- %s ---\n", label)
    fmt.Printf("SQL: %s\n", sqlStr)
    _, err := db.Exec(sqlStr)
    if err != nil {
        log.Printf("错误: %v", err)
        return err
    }
    fmt.Println("执行成功")
    return nil
}

// 查询并打印结果
func querySQL(db *sql.DB, sqlStr string, label string) error {
    fmt.Printf("\n--- %s ---\n", label)
    fmt.Printf("SQL: %s\n", sqlStr)
    rows, err := db.Query(sqlStr)
    if err != nil {
        log.Printf("错误: %v", err)
        return err
    }
    defer rows.Close()
    cols, _ := rows.Columns()
    for rows.Next() {
        vals := make([]interface{}, len(cols))
        for i := range vals {
            vals[i] = new(interface{})
        }
        rows.Scan(vals...)
        fmt.Printf("结果: %v\n", vals)
    }
    return nil
}

func main() {
    // 连接到 postgres 默认库
    connStr := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
    client, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer client.Close()
    if err := client.Ping(); err != nil {
        log.Fatal(err)
    }
    fmt.Println("已连接到 PostgreSQL")

    // 1. 创建测试数据库
    if err := execSQL(client, "CREATE DATABASE demo_db", "创建数据库"); err != nil {
        log.Fatal(err)
    }

    // 重新连接到 demo_db
    demoConnStr := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=demo_db sslmode=disable",
        host, port, user, password)
    demoDB, err := sql.Open("postgres", demoConnStr)
    if err != nil {
        log.Fatal(err)
    }
    defer demoDB.Close()
    if err := demoDB.Ping(); err != nil {
        log.Fatal(err)
    }
    fmt.Println("已连接到 demo_db")

    // 2. 创建 Schema
    if err := execSQL(demoDB, "CREATE SCHEMA app_schema", "创建 Schema"); err != nil {
        log.Fatal(err)
    }

    // 3. 创建角色(无登录)和用户(有登录)
    if err := execSQL(demoDB, "CREATE ROLE app_readonly_role NOLOGIN", "创建只读角色 (NOLOGIN)"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "CREATE USER app_user WITH PASSWORD 'test123'", "创建用户 (默认 LOGIN)"); err != nil {
        log.Fatal(err)
    }

    // 4. 创建测试表
    createTableSQL := `
        CREATE TABLE app_schema.users (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT NOT NULL
        )
    `
    if err := execSQL(demoDB, createTableSQL, "创建测试表"); err != nil {
        log.Fatal(err)
    }
    insertSQL := `INSERT INTO app_schema.users (name, email) VALUES 
        ('Alice', 'alice@example.com'),
        ('Bob', 'bob@example.com')`
    if err := execSQL(demoDB, insertSQL, "插入测试数据"); err != nil {
        log.Fatal(err)
    }

    // 5. 授予权限
    if err := execSQL(demoDB, "GRANT USAGE ON SCHEMA app_schema TO app_readonly_role", "授予 Schema USAGE 权限"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_readonly_role", "授予表 SELECT 权限"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "GRANT CONNECT ON DATABASE demo_db TO app_user", "授予数据库 CONNECT 权限"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "GRANT app_readonly_role TO app_user", "将只读角色授予用户"); err != nil {
        log.Fatal(err)
    }

    // 6. 验证权限
    if err := querySQL(demoDB, `SELECT rolname, rolcanlogin, rolinherit FROM pg_roles 
        WHERE rolname IN ('app_readonly_role', 'app_user')`, "查看角色属性"); err != nil {
        log.Fatal(err)
    }
    if err := querySQL(demoDB, `SELECT grantee, table_schema, table_name, privilege_type 
        FROM information_schema.table_privileges WHERE grantee = 'app_readonly_role'`, "查看表级权限"); err != nil {
        log.Fatal(err)
    }

    // 7. 测试只读用户连接
    fmt.Println("\n--- 测试只读用户连接 ---")
    readOnlyConnStr := fmt.Sprintf("host=%s port=%d user=app_user password=test123 dbname=demo_db sslmode=disable",
        host, port)
    roClient, err := sql.Open("postgres", readOnlyConnStr)
    if err != nil {
        log.Fatal(err)
    }
    defer roClient.Close()
    if err := roClient.Ping(); err != nil {
        log.Printf("只读用户连接失败: %v", err)
    } else {
        fmt.Println("只读用户连接成功")
        // 查询
        rows, err := roClient.Query("SELECT * FROM app_schema.users")
        if err != nil {
            log.Printf("查询失败: %v", err)
        } else {
            for rows.Next() {
                var id int
                var name, email string
                rows.Scan(&id, &name, &email)
                fmt.Printf("查询结果: id=%d, name=%s, email=%s\n", id, name, email)
            }
            rows.Close()
        }
        // 尝试写入(应失败)
        _, err = roClient.Exec("INSERT INTO app_schema.users (name, email) VALUES ('Charlie', 'charlie@example.com')")
        if err != nil {
            fmt.Println("✅ 写入被拒绝 (符合预期):", err)
        } else {
            fmt.Println("⚠️ 警告: 只读用户居然可以写入数据!")
        }
    }

    // 8. 清理资源
    fmt.Println("\n--- 清理资源 ---")
    if err := execSQL(demoDB, "REASSIGN OWNED BY app_user TO postgres", "转移所有权 (REASSIGN OWNED)"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "DROP OWNED BY app_user", "删除权限和残留对象 (DROP OWNED)"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "DROP ROLE app_readonly_role", "删除只读角色"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "DROP USER app_user", "删除用户"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "DROP TABLE app_schema.users CASCADE", "删除测试表"); err != nil {
        log.Fatal(err)
    }
    if err := execSQL(demoDB, "DROP SCHEMA app_schema CASCADE", "删除 Schema"); err != nil {
        log.Fatal(err)
    }

    fmt.Println("\n✅ 所有测试完成,资源已清理")
}
代码说明
  • 驱动:使用 github.com/lib/pq,这是 Go 最常用的 PostgreSQL 驱动,支持 database/sql 标准接口。
  • 连接管理:先连接 postgres 默认库创建 demo_db,再切换连接至新库执行后续操作。
  • 权限操作:execSQL 封装了 Exec 执行 DDL/DML;querySQL 使用 Query 查询并打印结果。
  • 错误处理:每个步骤检查错误并立即退出,便于调试。
  • 资源清理:按标准流程 REASSIGN OWNED → DROP OWNED → DROP ROLE/USER → 删除表/Schema。
技术点总结
技术点说明
数据库驱动lib/pq 实现 database/sql 驱动,支持连接池和上下文
连接字符串使用 key=value 格式,指定 sslmode=disable 简化测试
事务处理未显式使用事务,但所有操作均为原子 SQL 语句
错误处理显式检查 error,符合 Go 惯用法
资源释放使用 defer 确保连接和 rows 关闭

Python 示例

运行说明

环境要求:

  • Python 3.8+
  • PostgreSQL 14+
  • 依赖库:psycopg2-binary(推荐)或 psycopg2

安装依赖:

pip install psycopg2-binary

启动 PostgreSQL:

pg_ctl -D /usr/local/var/postgres start

运行脚本:

python main.py
代码实现
import psycopg2
import psycopg2.extras
import sys

# 数据库连接配置
config = {
    'host': 'localhost',
    'port': 5432,
    'user': 'postgres',
    'password': 'postgres',
    'database': 'postgres'
}

def execute_sql(conn, sql, label):
    """执行 SQL 并打印结果"""
    print(f"\n--- {label} ---")
    print(f"SQL: {sql}")
    cur = conn.cursor()
    try:
        cur.execute(sql)
        conn.commit()
        print("执行成功")
    except Exception as e:
        print(f"错误: {e}", file=sys.stderr)
        conn.rollback()
        raise
    finally:
        cur.close()

def query_sql(conn, sql, label):
    """查询并打印结果"""
    print(f"\n--- {label} ---")
    print(f"SQL: {sql}")
    cur = conn.cursor()
    try:
        cur.execute(sql)
        rows = cur.fetchall()
        if rows:
            for row in rows:
                print(f"结果: {row}")
        else:
            print("无数据")
    except Exception as e:
        print(f"错误: {e}", file=sys.stderr)
        raise
    finally:
        cur.close()

def main():
    # 连接到 postgres 默认库
    try:
        conn = psycopg2.connect(**config)
        conn.autocommit = False
    except Exception as e:
        print(f"连接失败: {e}", file=sys.stderr)
        sys.exit(1)
    print("已连接到 PostgreSQL")

    try:
        # 1. 创建测试数据库
        execute_sql(conn, "CREATE DATABASE demo_db", "创建数据库")

        # 重新连接到 demo_db
        demo_config = config.copy()
        demo_config['database'] = 'demo_db'
        demo_conn = psycopg2.connect(**demo_config)
        demo_conn.autocommit = False
        print("已连接到 demo_db")

        # 2. 创建 Schema
        execute_sql(demo_conn, "CREATE SCHEMA app_schema", "创建 Schema")

        # 3. 创建角色和用户
        execute_sql(demo_conn, "CREATE ROLE app_readonly_role NOLOGIN", "创建只读角色 (NOLOGIN)")
        execute_sql(demo_conn, "CREATE USER app_user WITH PASSWORD 'test123'", "创建用户 (默认 LOGIN)")

        # 4. 创建测试表
        create_table = """
            CREATE TABLE app_schema.users (
                id SERIAL PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL
            )
        """
        execute_sql(demo_conn, create_table, "创建测试表")
        insert_sql = "INSERT INTO app_schema.users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com')"
        execute_sql(demo_conn, insert_sql, "插入测试数据")

        # 5. 授予权限
        execute_sql(demo_conn, "GRANT USAGE ON SCHEMA app_schema TO app_readonly_role", "授予 Schema USAGE 权限")
        execute_sql(demo_conn, "GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_readonly_role", "授予表 SELECT 权限")
        execute_sql(demo_conn, "GRANT CONNECT ON DATABASE demo_db TO app_user", "授予数据库 CONNECT 权限")
        execute_sql(demo_conn, "GRANT app_readonly_role TO app_user", "将只读角色授予用户")

        # 6. 验证权限
        query_sql(demo_conn, "SELECT rolname, rolcanlogin, rolinherit FROM pg_roles WHERE rolname IN ('app_readonly_role', 'app_user')", "查看角色属性")
        query_sql(demo_conn, "SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'app_readonly_role'", "查看表级权限")

        # 7. 测试只读用户连接
        print("\n--- 测试只读用户连接 ---")
        ro_config = {
            'host': 'localhost',
            'port': 5432,
            'user': 'app_user',
            'password': 'test123',
            'database': 'demo_db'
        }
        try:
            ro_conn = psycopg2.connect(**ro_config)
            print("只读用户连接成功")
            cur = ro_conn.cursor()
            cur.execute("SELECT * FROM app_schema.users")
            rows = cur.fetchall()
            for row in rows:
                print(f"查询结果: {row}")
            cur.close()
            # 尝试写入
            try:
                cur = ro_conn.cursor()
                cur.execute("INSERT INTO app_schema.users (name, email) VALUES ('Charlie', 'charlie@example.com')")
                ro_conn.commit()
                print("⚠️ 警告: 只读用户居然可以写入数据!")
            except Exception as write_err:
                print(f"✅ 写入被拒绝 (符合预期): {write_err}")
            finally:
                cur.close()
            ro_conn.close()
        except Exception as conn_err:
            print(f"只读用户连接失败: {conn_err}")

        # 8. 清理资源
        print("\n--- 清理资源 ---")
        execute_sql(demo_conn, "REASSIGN OWNED BY app_user TO postgres", "转移所有权 (REASSIGN OWNED)")
        execute_sql(demo_conn, "DROP OWNED BY app_user", "删除权限和残留对象 (DROP OWNED)")
        execute_sql(demo_conn, "DROP ROLE app_readonly_role", "删除只读角色")
        execute_sql(demo_conn, "DROP USER app_user", "删除用户")
        execute_sql(demo_conn, "DROP TABLE app_schema.users CASCADE", "删除测试表")
        execute_sql(demo_conn, "DROP SCHEMA app_schema CASCADE", "删除 Schema")

        demo_conn.close()
        print("\n✅ 所有测试完成,资源已清理")

    except Exception as e:
        print(f"执行失败: {e}", file=sys.stderr)
        sys.exit(1)
    finally:
        conn.close()

if __name__ == "__main__":
    main()
代码说明
  • 驱动:使用 psycopg2(二进制版),这是 Python 最成熟稳定的 PostgreSQL 适配器。
  • 连接管理:通过字典传递连接参数,支持 autocommit 模式(此处显式关闭,手动提交)。
  • 游标使用:每个操作创建新游标,执行后关闭,避免资源泄漏。
  • 异常处理:捕获异常并回滚事务,确保数据一致性。
  • 日志输出:使用 print 输出过程信息,便于观察。
技术点总结
技术点说明
数据库驱动psycopg2 支持 Python DB-API 2.0,提供连接池和异步选项
事务管理默认自动提交关闭,需显式 commit() 或 rollback()
参数化查询示例中使用字符串拼接(仅演示),生产环境需使用 %s 占位符
游标迭代fetchall() 获取所有结果,适合小数据集
资源释放使用 close() 释放游标和连接,或使用上下文管理器

Java 示例

运行说明

环境要求:

  • JDK 11+
  • PostgreSQL 14+
  • 依赖库:PostgreSQL JDBC 驱动 org.postgresql:postgresql:42.7.2

构建与运行(使用 Maven):

创建 pom.xml:

<project>
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>pg-demo</artifactId>
    <version>1.0</version>
    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.7.2</version>
        </dependency>
    </dependencies>
</project>

编译运行:

mvn clean compile
mvn exec:java -Dexec.mainClass="com.example.Main"

或直接使用 javac 和 java(需将 JAR 加入 classpath)。

启动 PostgreSQL:

pg_ctl -D /usr/local/var/postgres start
代码实现
package com.example;

import java.sql.*;
import java.util.Properties;

public class Main {

    private static final String HOST = "localhost";
    private static final int PORT = 5432;
    private static final String USER = "postgres";
    private static final String PASSWORD = "postgres";
    private static final String DBNAME = "postgres";

    // 辅助:执行 SQL(无结果集)
    private static void execSQL(Connection conn, String sql, String label) throws SQLException {
        System.out.println("\n--- " + label + " ---");
        System.out.println("SQL: " + sql);
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("执行成功");
        } catch (SQLException e) {
            System.err.println("错误: " + e.getMessage());
            throw e;
        }
    }

    // 辅助:执行查询并打印结果
    private static void querySQL(Connection conn, String sql, String label) throws SQLException {
        System.out.println("\n--- " + label + " ---");
        System.out.println("SQL: " + sql);
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            while (rs.next()) {
                StringBuilder sb = new StringBuilder("结果: ");
                for (int i = 1; i <= colCount; i++) {
                    if (i > 1) sb.append(", ");
                    sb.append(meta.getColumnName(i)).append("=").append(rs.getString(i));
                }
                System.out.println(sb.toString());
            }
        } catch (SQLException e) {
            System.err.println("错误: " + e.getMessage());
            throw e;
        }
    }

    public static void main(String[] args) {
        Properties props = new Properties();
        props.setProperty("user", USER);
        props.setProperty("password", PASSWORD);
        props.setProperty("ssl", "false");

        String url = String.format("jdbc:postgresql://%s:%d/%s", HOST, PORT, DBNAME);

        try (Connection conn = DriverManager.getConnection(url, props)) {
            conn.setAutoCommit(false);
            System.out.println("已连接到 PostgreSQL");

            // 1. 创建数据库
            execSQL(conn, "CREATE DATABASE demo_db", "创建数据库");
            conn.commit();

            // 切换到 demo_db
            String demoUrl = String.format("jdbc:postgresql://%s:%d/demo_db", HOST, PORT);
            try (Connection demoConn = DriverManager.getConnection(demoUrl, props)) {
                demoConn.setAutoCommit(false);
                System.out.println("已连接到 demo_db");

                // 2. 创建 Schema
                execSQL(demoConn, "CREATE SCHEMA app_schema", "创建 Schema");

                // 3. 创建角色和用户
                execSQL(demoConn, "CREATE ROLE app_readonly_role NOLOGIN", "创建只读角色 (NOLOGIN)");
                execSQL(demoConn, "CREATE USER app_user WITH PASSWORD 'test123'", "创建用户 (默认 LOGIN)");

                // 4. 创建表
                String createTable = "CREATE TABLE app_schema.users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL)";
                execSQL(demoConn, createTable, "创建测试表");
                String insert = "INSERT INTO app_schema.users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com')";
                execSQL(demoConn, insert, "插入测试数据");

                // 5. 授予权限
                execSQL(demoConn, "GRANT USAGE ON SCHEMA app_schema TO app_readonly_role", "授予 Schema USAGE 权限");
                execSQL(demoConn, "GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_readonly_role", "授予表 SELECT 权限");
                execSQL(demoConn, "GRANT CONNECT ON DATABASE demo_db TO app_user", "授予数据库 CONNECT 权限");
                execSQL(demoConn, "GRANT app_readonly_role TO app_user", "将只读角色授予用户");

                demoConn.commit();

                // 6. 验证权限
                querySQL(demoConn, "SELECT rolname, rolcanlogin, rolinherit FROM pg_roles WHERE rolname IN ('app_readonly_role', 'app_user')", "查看角色属性");
                querySQL(demoConn, "SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'app_readonly_role'", "查看表级权限");

                // 7. 测试只读用户
                System.out.println("\n--- 测试只读用户连接 ---");
                Properties roProps = new Properties();
                roProps.setProperty("user", "app_user");
                roProps.setProperty("password", "test123");
                roProps.setProperty("ssl", "false");
                String roUrl = String.format("jdbc:postgresql://%s:%d/demo_db", HOST, PORT);
                try (Connection roConn = DriverManager.getConnection(roUrl, roProps)) {
                    System.out.println("只读用户连接成功");
                    try (Statement st = roConn.createStatement();
                         ResultSet rs = st.executeQuery("SELECT * FROM app_schema.users")) {
                        while (rs.next()) {
                            System.out.printf("查询结果: id=%d, name=%s, email=%s%n",
                                    rs.getInt("id"), rs.getString("name"), rs.getString("email"));
                        }
                    }
                    // 尝试写入
                    try (Statement st = roConn.createStatement()) {
                        st.executeUpdate("INSERT INTO app_schema.users (name, email) VALUES ('Charlie', 'charlie@example.com')");
                        System.out.println("⚠️ 警告: 只读用户居然可以写入数据!");
                    } catch (SQLException writeErr) {
                        System.out.println("✅ 写入被拒绝 (符合预期): " + writeErr.getMessage());
                    }
                } catch (SQLException connErr) {
                    System.err.println("只读用户连接失败: " + connErr.getMessage());
                }

                // 8. 清理资源
                System.out.println("\n--- 清理资源 ---");
                execSQL(demoConn, "REASSIGN OWNED BY app_user TO postgres", "转移所有权 (REASSIGN OWNED)");
                execSQL(demoConn, "DROP OWNED BY app_user", "删除权限和残留对象 (DROP OWNED)");
                execSQL(demoConn, "DROP ROLE app_readonly_role", "删除只读角色");
                execSQL(demoConn, "DROP USER app_user", "删除用户");
                execSQL(demoConn, "DROP TABLE app_schema.users CASCADE", "删除测试表");
                execSQL(demoConn, "DROP SCHEMA app_schema CASCADE", "删除 Schema");
                demoConn.commit();

                System.out.println("\n✅ 所有测试完成,资源已清理");
            } catch (SQLException e) {
                System.err.println("在 demo_db 中出错: " + e.getMessage());
                e.printStackTrace();
            }
        } catch (SQLException e) {
            System.err.println("主连接错误: " + e.getMessage());
            e.printStackTrace();
        }
    }
}
代码说明
  • 驱动:使用 PostgreSQL JDBC 驱动(org.postgresql.Driver),通过 DriverManager 获取连接。
  • 连接参数:通过 Properties 设置用户名、密码和 SSL 选项。
  • 事务控制:显式设置 setAutoCommit(false),手动 commit() 确保操作原子性。
  • 资源管理:使用 try-with-resources 自动关闭 Statement、ResultSet 和 Connection。
  • 异常处理:捕获 SQLException 并打印,确保流程清晰。
技术点总结
技术点说明
JDBC 驱动标准 Java 数据库连接,支持所有 SQL 操作
连接字符串jdbc:postgresql://host:port/db 格式
事务关闭自动提交,手动控制事务边界
资源释放try-with-resources 确保资源自动关闭
元数据使用 ResultSetMetaData 动态获取列信息

多语言对比

对比维度Node.js (参考)GoPythonJava
驱动/库pg (npm)github.com/lib/pqpsycopg2-binaryorg.postgresql:postgresql
连接方式new Client(config)sql.Open("postgres", connStr)psycopg2.connect(**config)DriverManager.getConnection(url, props)
事务控制默认自动提交,可手动 BEGIN/COMMIT默认自动提交,可 Begin()默认自动提交,可设置 autocommit=False默认自动提交,可 setAutoCommit(false)
错误处理回调或 try/catch显式 if err != niltry/excepttry/catch (SQLException)
资源释放client.end()defer rows.Close()cur.close() / conn.close()try-with-resources 自动关闭
SQL 执行client.query() / client.query()db.Exec() / db.Query()cur.execute() / cur.fetchall()stmt.execute() / stmt.executeQuery()
连接池内置通过 sql.DB 内置连接池需额外库(如 psycopg2.pool)通过 HikariCP 等第三方库
参数化查询$1, $2 占位符$1, $2 占位符%s 或 %(name)s? 或 $1(驱动支持)
类型映射自动映射 JavaScript 类型映射 Go 基本类型映射 Python 类型映射 Java 类型(getString, getInt 等)
适用场景全栈 JavaScript 生态,快速开发高并发、云原生微服务数据分析、快速原型、脚本企业级应用、Spring 生态

各语言实现均遵循相同的 PostgreSQL 权限管理逻辑,开发者可根据项目技术栈选择合适的语言。所有示例均完整可运行,并包含资源清理环节,确保测试环境无残留。

项目难点与解决方案

核心难点

PostgreSQL 权限体系的层次化设计和角色继承机制构成了主要的理解门槛。新手 DBA 常常遇到以下问题:

  1. 权限排查困难:用户拥有数据库连接权限,但查询表时报错 permission denied for schema,难以定位是哪一层权限缺失
  2. PUBLIC 隐式授权:回收了特定用户的权限后,用户仍能访问,因为 PUBLIC 的默认授权未被回收
  3. 角色删除失败:直接 DROP ROLE 报错 cannot be dropped because some objects depend on it,不清楚需要先处理哪些依赖

解决方案

  1. 分层排查法:按照 实例 → 数据库 → Schema → 表 → 列/行 的顺序逐层检查权限,使用 information_schema.table_privileges 和 pg_roles 系统表辅助排查
  2. PUBLIC 权限回收:在创建数据库之初,通过修改 template1 回收 PUBLIC 的默认权限,从源头规避问题
  3. 标准化删除流程:严格遵循 REASSIGN OWNED → DROP OWNED → DROP ROLE 的三步流程,并在每个相关数据库中执行

广度

本文覆盖了 PostgreSQL 权限管理的完整知识体系,包括:

  • 权限层级架构(实例 → 数据库 → Schema → 表 → 列 → 行)
  • 角色与用户的区别与联系
  • 角色继承机制
  • 预定义角色(PostgreSQL 10~17)
  • PUBLIC 伪角色的特殊性
  • 角色删除的标准流程
  • 权限审计与巡检(pg_permissions 扩展)

深度

深入剖析了以下关键技术细节:

  • CREATE ROLE 与 CREATE USER 的本质差异(仅 LOGIN 属性不同)
  • REASSIGN OWNED 与 DROP OWNED 的协同工作机制及执行顺序
  • pg_read_all_data 与 pg_write_all_data 的引入版本(PostgreSQL 14)及 BYPASSRLS 注意事项
  • 权限继承链在 NOINHERIT 处的终止行为

复杂度

PostgreSQL 权限管理的复杂度主要体现在:

  • 多层次嵌套:权限检查涉及 6 个层级,每层都有独立的授权模型
  • 隐式行为:PUBLIC 的默认授权、角色的自动继承等隐式行为增加了理解成本
  • 删除操作的依赖链:角色删除需要处理所有权转移和权限回收,涉及多个数据库的跨库操作

官方文档

参考链接

总结

PostgreSQL 的权限管理体系以 Role 为核心概念,通过层次化的权限检查机制(实例认证 → 数据库连接 → Schema 使用 → 表操作 → 列/行级控制)实现了精细的访问控制。Role 与 User 的本质区别仅在于 LOGIN 属性的默认值,这一设计使得 Role 天然适合作为权限模板进行批量授权管理。

在生产环境中,建议遵循以下最佳实践:

  1. 使用不带 LOGIN 的 Role 作为权限集合,再授予给实际的 User
  2. 充分利用预定义角色(尤其是 PostgreSQL 14+ 的 pg_read_all_data 和 pg_write_all_data)简化只读/读写用户的配置
  3. 注意 PUBLIC 伪角色的隐式授权,回收权限时需同时从 PUBLIC 回收
  4. 删除角色时严格遵循 REASSIGN OWNED → DROP OWNED → DROP ROLE 的标准流程
  5. 使用 pg_permissions 等工具进行定期的权限审计与巡检

理解并掌握这套权限体系,是保障 PostgreSQL 数据库安全稳定运行的基石。

VPS上PostgreSQL角色与权限最小可行体系实战 PostgreSQL角色(Role)是其核心访问控制机制,区别于MySQL的简单用户模型,它支持登录/非登录角色、继承式权限分配及行级安全等高级能力。在资源受限、暴露面固定的VPS环境中,合理设计角色层级与权限边界,不仅能防范误操作和提权攻击,更能提升审计可追溯性与运维稳定性。本文围绕‘登录角色→功能角色→数据角色’三层架构,详解如何基于GRANT/REVOKE、NOLOGIN角色、pg_hba.conf加固及默认权限(ALTER DEFAULT PRIVILEGES)构建轻量、安全、可验证的权限体系,覆盖 阅读详情

相关推荐

PostgreSQL生产环境数据库管理与治理实战指南

PostgreSQL数据库管理不仅是基础运维操作,更是涵盖实例隔离、权限分层控制、WAL归档策略、自动真空调优和连接池管控的全生命周期治理体系。其核心原理基于数据库(database)级物理隔离、schema级逻辑权限杠杆及role体系的最小权限继承机制,技术价值在于保障高并发下的稳定性、审计合规性与故障快速恢复能力。典型应用场景包括金融级交易系统、多租户SaaS平台及IoT海量写入架构。本文聚焦真实生产环境中的配置调优、权限设计与灾备实践,深度解析postgresql.conf关键参数、pg_hba.co

weixin_30673611的博客 556

PostgreSQL 权限管理

权限管理是所有数据库都绕不开的话题,PG 中使用 `角色(role)`机制来处理用户身份认证。 本篇文章介绍如何管理 PostgreSQL 中的 `角色 用户 组角色`。

分享既学习 7455

PostgreSQL 16 Schema 权限隔离实战:5步实现多租户应用数据安全

本文详细介绍了PostgreSQL 16中如何通过Schema权限隔离实现多租户应用的数据安全。通过5个实战步骤,包括创建租户专属Schema、设计角色权限矩阵、实现行级安全(RLS)、自动化权限回收和审计监控配置,帮助开发者在单一数据库实例中高效管理多租户数据,同时确保安全性和灵活性。

weixin_34175509的博客 436

PostgreSql 用户及权限管理

ALTER DEFAULT PRIVILEGES 允许设置将被应用于未来要创建的对象的特权(它不会影响分配给已经存在的对象的特权)。当前,只能修改用于模式、表(包括视图和外部表)、序列、函数和类型(包括域)的特权。其中,可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时,单词 FUNCTIONS 和 ROUTINES 是等效的。(推荐使用 ROUTINES,因为它是用来囊括函数和过程的一个标准术语。在较早的 PostgreSQL 发行版中,只允许单词 FUNCTIONS。

脑子进水养啥鱼?的博客 1万+

NocoDB全链路安全防护实战:从字段加密到权限管理的纵深防御体系

在数据驱动的应用开发中,数据安全是构建可信系统的基石。其核心原理在于通过加密、访问控制与审计等多层技术手段,确保数据在存储、传输与使用过程中的机密性、完整性与可用性。从技术价值看,一套完善的安全体系不仅能防范外部攻击与内部误操作,更是满足GDPR等合规要求、赢得用户信任的关键。尤其在低代码平台构建内部工具或客户门户时,安全设计直接影响核心业务数据的防护能力。本文聚焦NocoDB平台,深入探讨如何实现数据库字段级加密与精细化权限管理,构建从数据存储到用户访问的全链路纵深防御方案,为低代码应用提供企业级安全实践

weixin_30240349的博客 442

Plone权限模型详解:角色、本地角色与继承链的协同机制

访问控制(Access Control)是企业级内容管理系统的核心能力,其本质是将用户身份、资源上下文与操作行为三者动态绑定。Plone摒弃传统RBAC的扁平化设计,采用基于内容对象树的角色(Roles)、本地角色(Local Roles)和继承链(Acquisition)三层协同机制,实现上下文感知的细粒度权限控制。该模型支持运行时动态求值、零代码配置、即时生效与完整审计,适用于多部门协作、合规文档管理、ISO 27001认证等强管控场景。本文聚焦Plone 6 LTS中经生产验证的权限架构实践,涵盖全局

weixin_30650039的博客 385

PostgreSQL权限的分配和控制

中,权限用于控制用户对数据库对象的访问和操作。ALTER DEFAULT PRIVILEGES:用于设置默认权限,即当用户创建新对象时,默认分配给该对象的权限。语句可以设置默认权限,即当用户创建新对象时,默认分配给该对象的权限。中,默认情况下,数据库对象的所有权和访问权限都是由创建该对象的用户所拥有。提供了一组权限控制语句,用于分配和撤销用户对数据库对象的权限。中进行权限的分配和控制。REVOKE:用于撤销用户或用户组对数据库对象的某种权限。GRANT:用于授予用户或用户组对数据库对象的某种权限。

互联网知识分享 1748

SoC 验证老兵手册

《SoC验证老兵手册》摘要: 针对大型SoC系统级验证,手册提出Cache+DMA场景的高效验证方案。核心方法采用C测试用例(模拟Linux/RTOS真实场景)触发隐蔽bug,配合SV Checker精准捕捉硬件问题(如DMA读取脏缓存行)。特别强调验证方法论:C用例负责复杂行为模拟,SV专注RTL正确性检查;利用Linux天然并发性暴露硬件缺陷。手册还包含模拟面试攻防技巧,指出验证架构师需具备定义Stimulus/Checker边界的能力。终极判断标准:未通过Linux下Cache+DMA+SMP+中断测

元直的博客 1376

postgresql用户模式权限介绍_postgresql usage

2.创建一个普通用户3.为创建一个超级用户4.创建一个普通用户,并且赋予相关权限。

2401_84563561的博客 3155

【SOC估算】基于EKF与神经网络融合的锂电池SOC高精度估计及Matlab实现

本文探讨了基于EKF与神经网络融合的锂电池SOC高精度估计方法。通过结合扩展卡尔曼滤波(EKF)的模型驱动优势和神经网络的数据驱动学习能力,实现了对电池荷电状态(SOC)的精准预测。Matlab仿真验证了该混合算法在各种工况下均能显著降低估计误差,提升电池管理系统性能。

play7的博客 698

如何使用安路TD与Modelsim进行联合仿真?

1)安装安路FPGA开发软件;(本次实验使用版本:TD_5.6.3_Release)2)安装Modelsim仿真软件;(本次实验使用版本:Modelsim SE-64 10.1c)3)FPGA开发板;(本次实验使用型号:EG4X20BG256)4)LED点灯例程;

qq_17313923的博客 3226

GaussDB(for PostgreSQL)连接配置与权限管理实战指南

数据库连接与权限管理是数据库系统的核心功能,直接影响系统安全性和运维效率。PostgreSQL作为主流开源关系型数据库,其权限系统采用基于角色的访问控制(RBAC)模型,通过GRANT/REVOKE语句实现细粒度的权限分配。GaussDB(for PostgreSQL)作为企业级增强版本,在兼容PostgreSQL协议的基础上,优化了分布式架构下的权限管理体系,特别强化了SCRAM认证机制和四级权限分层控制。在实际工程实践中,合理的连接参数配置(如autoReconnect、connectTimeout)能

weixin_30575517的博客 280

字节系,ali系,ks,pdd最新抓包方案

各大厂安卓逆向

qq_38759383的博客 1148

PostgreSQL权限管理与存储优化:Schema、User和Tablespace的协同设计

本文深入探讨了PostgreSQL中Schema、User和Tablespace的协同设计,以实现高效的权限管理与存储优化。通过多租户隔离方案、精细化权限控制策略以及Tablespace性能优化技巧,帮助开发者提升数据库安全性和查询性能。文章还提供了典型问题的排查与解决方案,助力实现PostgreSQL的最佳实践。

year5的博客 370

Pytorch入门实战(2)-使用BP神经网络实现MNIST手写数字识别

上一篇:Pytorch入门实战(1) - 实现线性回归 涉及知识点 Pytorch nn.Module的基本使用 Pytorch nn.Linear的基本用法 PytorchVision Transforms的基本使用 Pytorch中DataLoader的基本用法 Pytorch详解NLLLoss和CrossEntropyLoss 如何确定神经网络的层数和隐藏层神经元数量 本文内容 本文将会使用BP神经网络(就是最普通的神经网络)实现一个MNIST手写数据集的实现。话不多说,直接开始。 首先先导入需要的包

iioSnail的博客 6619

STM32入门教程:水位传感器应用

这是一个简单的STM32水位检测系统的入门教程。希望通过学习本教程,您能对STM32微控制器的基本应用有更深入的了解,并能成功构建一个水位检测系统。如果您需要更详细的代码实例和教程,请参考相关的STM32开发手册和应用笔记。STM32是一款功能丰富且广泛应用于嵌入式系统的微控制器系列。在本教程中,我们将介绍如何使用STM32微控制器和水位传感器构建一个水位检测系统。

wx_linying1029的博客 534

PostgreSQL 权限审计与自动化:pg_permissions 插件部署与实战指南

本文详细介绍了PostgreSQL权限审计与自动化工具pg_permissions插件的部署与实战应用。通过聚合视图、基线管理和差异分析三大核心功能,帮助DBA高效解决权限可视化、基线管理和自动化审计等挑战,提升数据库安全管理效率。文章包含多版本适配指南、核心功能解析及企业级部署建议,是PostgreSQL权限管理的实用指南。

weixin_30394669的博客 312

RH850从0搭建Autosar开发环境【7】- Davinci Configurator之CanTp模块配置详解(上)

RH850从0搭建Autosar开发环境【7】- Davinci Configurator之CanTp模块配置详解(上)

weixin_42412049的博客 2301

PostgreSQL定时任务管理深度指南:pg_cron实战与优化

PostgreSQL作为企业级关系型数据库,其强大的扩展生态为复杂业务场景提供了丰富解决方案。在众多扩展中,pg_cron作为基于cron的PostgreSQL作业调度器,让数据库内部定时任务管理变得异常简单。本文将深入探讨pg_cron的核心价值、常见挑战、系统性解决方案以及性能优化技巧,为中级用户提供完整的实战指南。 ## 1. 项目核心价值与适用场景分析 pg_cron是一个轻量级但功能

gitblog_01103的博客 385

python爬虫:采集求职者简历信息并进行人才分析

通过 Python 爬虫抓取求职者简历信息并进行人才分析,可以帮助企业快速筛选和匹配人才。但在实施过程中,必须严格遵守法律法规,确保数据来源合法合规。如果需要更复杂的分析,可以结合自然语言处理(NLP)和机器学习技术,进一步挖掘数据价值。

数据知道的博客 1万+

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

Erupt erupt-admin 快速集成指南:一键获得带登录、角色与菜单权限的安全管理后台

`erupt-admin` 是 Erupt 通用数据管理框架下的 All-in-one 管理后台启动模块(Starter)。它本身几乎不含业务代码,而是把 `erupt-upms`(用户与权限管理)与 `erupt-security`(API 安全防护)两个能力模块打包进一个依赖:在 Spring Boot 应用中引入该模块后,无需任何额外配置,即可获得一个带登录、角色管理、菜单权限控制、操作日志

gitblog_01155的博客 394

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

在线考试与管理系统实战:基于 Express 的多角色全栈项目开发指南

> 本文是 Datawhale「easy-vibe」课程体系 Stage 2(初中级开发)综合大作业的完整实战指南。你将围绕一份真实的产品需求文档(PRD),从零构建一个包含官网前台、学生端、管理后台三个子系统的在线考试与管理系统,使用 Express 实现登录鉴权、角色权限、题库与考试管理、提交与自动判分、成绩统计的完整业务链路。读完本文,你将掌握"阅读 PRD → 提取任务清单 → 用 AI

gitblog_00315的博客 331

老版QQ原生表情gif动态

这是老版的QQ表情,不过全是动态的的,Windows照片查看器是不会动的,可以右击用浏览器打开查看,再说一遍是老版的,只是分享给宁愿用老版也要动态的人。

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

企业级PostgreSQL管理:PGAdmin实战案例

通过这个项目,我们总结了几个关键点:REST API调用要注意频率控制。

BlackironPanther23的博客 86

企业网络权限管理实战:从报错到解决

数据建模要全面案例表需要关联多种分类标签用户行为数据要详细记录解决方案版本需要管理搜索功能优化使用PostgreSQL的全文搜索添加搜索建议功能记录热门搜索词权限控制设计区分管理员和普通用户敏感操作需要二次确认操作日志完整记录用户体验细节解决方案分步骤展示提供一键复制命令功能支持问题关联和跳转。

SilverMoon18的博客 906

n8n企业级部署指南:高可用与安全最佳实践

n8n企业级部署指南:高可用与安全最佳实践 【免费下载链接】n8n n8n 是一个工作流自动化平台,它结合了代码的灵活性和无代码的高效性。支持 400+ 集成、原生 AI 功能以及公平开源许可,n8n 能让你在完全掌控数据和部署的前提下,构建强大的自动化流程。源项目地址:https://github.com/n8n-io...

gitblog_01078的博客 1557

数据库安全管理实战:从RBAC模型到细粒度权限控制

平台自动生成的后台管理界面包含用户角色分配模块,正好满足RBAC的演示需求,部署后团队成员都能在线体验权限管控效果。通过将财务、运营等部门的实际岗位抽象为"财务专员"、"区域经理"等角色,权限分配效率提升了60%。角色继承功能特别实用,比如"高级管理员"自动继承"普通管理员"的21项基础权限,避免了重复配置。在数据安全日益重要的今天,合理的权限管理系统已成为企业基础设施的关键组成部分。最近在实践数据库权限管理时,我深入研究了RBAC模型与细粒度控制方案,总结出一些值得分享的实战经验。

FrostfirePanther89的博客 426
上一篇: PostgreSQL笔记59:行列级权限机制与数据脱敏实践
下一篇: PostgreSQL笔记61:分区表核心概念、声明式分区与最佳实践
Wang's Blog
博客等级 码龄12年 7439粉丝 2424原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值