02：PostgreSQL用户和权限

环境：

• 操纵系统：CentOS 7.9 64bit
  
• PostgreSQL 版本：16.x 或 15.x
  
• 安装用户：postgres
  
• 软件安装目标路径：/usr/pgsql-<version>
  
• 数据库数据目次：/pgdata
  

目次
用户和角色
  创建用户或角色
  权限管理
  查看权限

---

用户和角色

• 数据库在初始化时，会自动初始化一个超等用户。该超等用户与初始化的操纵系统用户名相同，通常为postgres。
  
• 数据库对象都有其owner属主。owner默认拥有全部权限，且无需授权。对对象alter和drop的权限为owner专属，不能grant授权别人。
  
• 在PostgreSQL中，用户与角色险些是没有区别，可以等同利用。
  

  创建用户或角色

1. --创建用户示例
2. CREATE USER user_zyp WITH CREATEDB CREATEROLE LOGIN PASSWORD '123456';
4. --语法格式
5. CREATE ROLE name [ [ WITH ] option [ ... ] ]
6. CREATE USER name [ [ WITH ] option [ ... ] ]
8. where option can be:
9.       SUPERUSER | NOSUPERUSER  （默认NOSUPERUSER）
10.     | CREATEDB | NOCREATEDB  （默认NOCREATEDB）
11.     | CREATEROLE | NOCREATEROLE  （默认NOCREATEROLE）
12.     | INHERIT | NOINHERIT  （默认INHERIT）
13.     | LOGIN | NOLOGIN  （创建ROLE默认NOLOGIN，创建USER默认LOGIN）
14.     | REPLICATION | NOREPLICATION  （默认NOREPLICATION）
15.     | BYPASSRLS | NOBYPASSRLS  （默认NOBYPASSRLS）
16.     | CONNECTION LIMIT connlimit  （默认-1不限制）
17.     | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
18.     | VALID UNTIL 'timestamp'
19.     | IN ROLE role_name [, ...]
20.     | IN GROUP role_name [, ...]
21.     | ROLE role_name [, ...]
22.     | ADMIN role_name [, ...]
23.     | USER role_name [, ...]

复制代码

  创建用户和角色唯一的区别是：创建用户默认有login权限，创建角色默认没有login权限。
  

  权限管理

 权限分为两类：

• 对于用户/角色权限（创建语句中option指定的权限），必要利用ALTER ROLE命令修改。
  
• 对于对象权限（用户/角色对某写对象的权限），必要利用GRANT和REVOKE举行赋权或收回。
  

赋权或收回命令通常的命令格式（不同范例略有不同，拜见官方文档）：

1. --语句一般格式（不同类型略有不同）
2. GRANT some_privileges ON object_type object_name TO role_name  [ WITH GRANT OPTION ];
3. REVOKE some_privileges ON object_type object_name FROM role_name  [ WITH GRANT OPTION ];
5. --指定某类所有对象授权语句一般格式（不同类型略有不同）
6. GRANT some_privileges ON ALL 类型(复数) IN object_type object_name TO role_name  [ WITH GRANT OPTION ];
7. REVOKE some_privileges ON ALL 类型(复数) IN object_type object_name FROM role_name  [ WITH GRANT OPTION ];
9. 其中some_privileges可以为：
10.    SELECT
11.    INSERT
12.    UPDATE
13.    DELETE
14.    TRUNCATE
15.    REFERENCES
16.    TRIGGER
17.    CREATE
18.    CONNECT
19.    TEMPORARY或TEMP
20.    EXECUTE
21.    USAGE
22.    ALL全部
24. 其中role_name除了可为具体用户名/角色名外，还可以为PUBLIC表示所有用户

复制代码

若grant ALL on DATABASE 数据库 to role_name不会自动把该数据库中schema、Table等对象授权给role_name

  查看权限

查看用户/角色权限（创建语句中option指定的权限）：

1. --可用以下语句查询或使用\du+
2. SELECT * FROM pg_roles;

复制代码

查看用户/角色对象权限（GRANT赋予的权限）： 

1. SELECT owner
2.       ,relname as object_name,grantor,grantee
3.       ,CASE
4.          WHEN (t.privilege = 'r'::text) THEN 'SELECT'::text
5.          WHEN (t.privilege = 'a'::text) THEN 'INSERT'::text
6.          WHEN (t.privilege = 'd'::text) THEN 'DELETE'::text
7.          WHEN (t.privilege = 'w'::text) THEN 'UPDATE'::text
8.          WHEN (t.privilege = 'D'::text) THEN 'TRUNCATE'::text
9.          WHEN (t.privilege = 'X'::text) THEN 'EXECUTE'::text
10.          WHEN (t.privilege = 'x'::text) THEN 'REFERENCES'::text
11.          WHEN (t.privilege = 'U'::text) THEN 'USAGE'::text
12.          WHEN (t.privilege = 't'::text) THEN 'TRIGGER'::text
13.          WHEN (t.privilege = 'C'::text) THEN 'CREATE'::text
14.          WHEN (t.privilege = 'c'::text) THEN 'CONNECT'::text
15.          WHEN (t.privilege = 'T'::text) THEN 'TEMPORARY'::text
16.          ELSE t.privilege
17.         END AS privilege
18.   FROM (select CASE
19.                  WHEN grantee IS NOT NULL AND grantee!=''
20.                    THEN has_table_privilege(grantee, oid, 'TRUNCATE')
21.                  ELSE NULL
22.                END AS TRUNCATE_privs
23.               ,regexp_split_to_table(privs, ''::text) AS privilege
24.               ,t_cls.*
25.           from (select (regexp_split_to_array(unnest(relacl)::text,'=|/'))[1] as grantee
26.                       ,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[2] as privs
27.                       ,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[3] as grantor
28.                       ,relname
29.                       ,relacl
30.                       ,(select usename from pg_user where usesysid = c.relowner) as owner
31.                       ,(SELECT nspname FROM pg_namespace n WHERE n.oid = c.relnamespace) as nspname
32.                       ,c.oid
33.                   FROM pg_class c
34.                  WHERE 1=1
35.                    AND relkind in ('r', 'S', 't', 'p','P')
36.                  order by owner,relname
37.         ) t_cls
38.       ) t;

复制代码

查看某个表上哪些用户拥有什么权限： 

1. SELECT * FROM information_schema.table_privileges
2. WHERE table_name = 'your_table_name';

复制代码

查看schema能被哪些访问：  

1. \d+

复制代码

其中UC字母分别表现：Usage、Create权限。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。