Postgresql数据库权限详解

2023年12月14日 · 838 字 · 2 分钟 · Postgresql权限

Postgresql数据库权限详解

权限相关概念

用户（User）：

在 PostgreSQL 中，用户是一个可以连接到数据库并访问其对象的实体。每个用户都有一个唯一的用户名和密码。

角色（Role）：

角色是一组用户的集合，可以通过角色进行权限管理。角色可以包含其他角色，并且可以授予或撤销权限。

权限（Privileges）：

权限是指用户或角色对数据库对象执行某些操作的权利。常见权限包括 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP 等。

查看用户权限

在 PostgreSQL 15.0 中查看用户 your_username 的权限情况，可以使用系统的 pg_catalog 或 information_schema 来查询。

在执行这些查询之前，请确保您的用户账户有足够的权限来检视这些系统表和视图。 如果您权限不足，需要请求数据库管理员为您提供相应的查询权限。

查询用户对所有 表的权限：

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

查询用户对所有 序列的权限：

# 测试不可用
SELECT grantee, sequence_schema, sequence_name, privilege_type
FROM information_schema.sequence_privileges
WHERE grantee = 'your_username';

#测试可用
SELECT
    nspname AS schema,
    relname AS sequence_name,
    rolname AS role_name,
    has_sequence_privilege(rolname, quote_ident(nspname) || '.' || quote_ident(relname), 'SELECT') AS has_select,
    has_sequence_privilege(rolname, quote_ident(nspname) || '.' || quote_ident(relname), 'USAGE') AS has_usage
FROM
    pg_class c
    JOIN
    pg_namespace ns ON ns.oid = c.relnamespace
    JOIN
    pg_roles r ON r.oid = c.relowner
WHERE
    c.relkind = 'S' -- 'S' stands for sequence
  AND r.rolname = 'your_username';

查询用户对所有 函数的权限：

SELECT grantee, specific_schema, specific_name, privilege_type
FROM information_schema.usage_privileges
WHERE grantee = 'your_username';

查询用户的 角色信息和其他权限：

可以查询 pg_roles 和 pg_auth_members 表格：

-- 查看用户的角色
SELECT rolname
FROM pg_roles
WHERE rolname = 'your_username';

-- 查看用户属于哪些角色
SELECT a.rolname as "role_name", b.rolname as "member_of"
FROM pg_auth_members
         JOIN pg_roles as a ON a.oid = roleid
         JOIN pg_roles as b ON b.oid = member
WHERE b.rolname = 'your_username';

给用户授权

表 授权

# 1. 直接修改 table 的拥有者
ALTER TABLE your_table OWNER TO your_username;
# 2. 直接授权
-- 授予用户对表的SELECT（查询/插入/更新/删除）权限
GRANT SELECT ON your_table TO your_username;
GRANT INSERT ON your_table TO your_username;
GRANT UPDATE ON your_table TO your_username;
GRANT DELETE ON your_table TO your_username;

-- 授予用户对表的所有权限（select, insert, update, delete 等）
GRANT ALL PRIVILEGES ON your_table TO your_username;

-- 如果想要授权用户对表所有行的操作权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_username;

-- 如果还想要授权用户能够更改表结构（如添加或删除列），需要添加ALTER
GRANT ALL PRIVILEGES ON your_table TO your_username WITH GRANT OPTION;

sequence序列授权

# 1. 直接修改sequence的拥有者
ALTER SEQUENCE xx_pk_seq OWNER TO your_username;
# 2. 直接授权
GRANT USAGE, SELECT ON SEQUENCE xx_pk_seq TO your_username;

# 3. 批量修改 sequence的拥有者 为xxx
DO $$
    DECLARE
    seq_record RECORD;
BEGIN
    FOR seq_record IN
SELECT sequence_schema, sequence_name
FROM information_schema.sequences
    LOOP
EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO xxx', seq_record.sequence_schema, seq_record.sequence_name);
END LOOP;
END $$;

参考资料