02：PostgreSQL用户和权限
环境：[*]操纵系统：CentOS 7.9 64bit
[*]PostgreSQL 版本：16.x 或 15.x
[*]安装用户：postgres
[*]软件安装目标路径：/usr/pgsql-<version>
[*]数据库数据目次：/pgdata
目次
用户和角色
创建用户或角色
权限管理
查看权限
用户和角色
[*]数据库在初始化时，会自动初始化一个超等用户。该超等用户与初始化的操纵系统用户名相同，通常为postgres。
[*]数据库对象都有其owner属主。owner默认拥有全部权限，且无需授权。对对象alter和drop的权限为owner专属，不能grant授权别人。
[*]在PostgreSQL中，用户与角色险些是没有区别，可以等同利用。
创建用户或角色
--创建用户示例
CREATE USER user_zyp WITH CREATEDB CREATEROLE LOGIN PASSWORD '123456';
--语法格式
CREATE ROLE name [ [ WITH ] option [ ... ] ]
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER（默认NOSUPERUSER）
| CREATEDB | NOCREATEDB（默认NOCREATEDB）
| CREATEROLE | NOCREATEROLE（默认NOCREATEROLE）
| INHERIT | NOINHERIT（默认INHERIT）
| LOGIN | NOLOGIN（创建ROLE默认NOLOGIN，创建USER默认LOGIN）
| REPLICATION | NOREPLICATION（默认NOREPLICATION）
| BYPASSRLS | NOBYPASSRLS（默认NOBYPASSRLS）
| CONNECTION LIMIT connlimit（默认-1不限制）
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...] 创建用户和角色唯一的区别是：创建用户默认有login权限，创建角色默认没有login权限。
https://img-blog.csdnimg.cn/direct/8f7d23d9573243dfb156454146424af4.png
权限管理
权限分为两类：
[*]对于用户/角色权限（创建语句中option指定的权限），必要利用ALTER ROLE命令修改。
[*]对于对象权限（用户/角色对某写对象的权限），必要利用GRANT和REVOKE举行赋权或收回。
赋权或收回命令通常的命令格式（不同范例略有不同，拜见官方文档）：
--语句一般格式（不同类型略有不同）
GRANT some_privileges ON object_type object_name TO role_name[ WITH GRANT OPTION ];
REVOKE some_privileges ON object_type object_name FROM role_name[ WITH GRANT OPTION ];
--指定某类所有对象授权语句一般格式（不同类型略有不同）
GRANT some_privileges ON ALL 类型(复数) IN object_type object_name TO role_name[ WITH GRANT OPTION ];
REVOKE some_privileges ON ALL 类型(复数) IN object_type object_name FROM role_name[ WITH GRANT OPTION ];
其中some_privileges可以为：
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY或TEMP
EXECUTE
USAGE
ALL全部
其中role_name除了可为具体用户名/角色名外，还可以为PUBLIC表示所有用户 若grant ALL on DATABASE 数据库 to role_name不会自动把该数据库中schema、Table等对象授权给role_name
查看权限
查看用户/角色权限（创建语句中option指定的权限）：
--可用以下语句查询或使用\du+
SELECT * FROM pg_roles;
https://img-blog.csdnimg.cn/direct/6539fec4ad934fbaa127cc13f47ea3bf.png
查看用户/角色对象权限（GRANT赋予的权限）：
SELECT owner
,relname as object_name,grantor,grantee
,CASE
WHEN (t.privilege = 'r'::text) THEN 'SELECT'::text
WHEN (t.privilege = 'a'::text) THEN 'INSERT'::text
WHEN (t.privilege = 'd'::text) THEN 'DELETE'::text
WHEN (t.privilege = 'w'::text) THEN 'UPDATE'::text
WHEN (t.privilege = 'D'::text) THEN 'TRUNCATE'::text
WHEN (t.privilege = 'X'::text) THEN 'EXECUTE'::text
WHEN (t.privilege = 'x'::text) THEN 'REFERENCES'::text
WHEN (t.privilege = 'U'::text) THEN 'USAGE'::text
WHEN (t.privilege = 't'::text) THEN 'TRIGGER'::text
WHEN (t.privilege = 'C'::text) THEN 'CREATE'::text
WHEN (t.privilege = 'c'::text) THEN 'CONNECT'::text
WHEN (t.privilege = 'T'::text) THEN 'TEMPORARY'::text
ELSE t.privilege
END AS privilege
FROM (select CASE
WHEN grantee IS NOT NULL AND grantee!=''
THEN has_table_privilege(grantee, oid, 'TRUNCATE')
ELSE NULL
END AS TRUNCATE_privs
,regexp_split_to_table(privs, ''::text) AS privilege
,t_cls.*
from (select (regexp_split_to_array(unnest(relacl)::text,'=|/')) as grantee
,(regexp_split_to_array(unnest(relacl)::text,'=|/')) as privs
,(regexp_split_to_array(unnest(relacl)::text,'=|/')) as grantor
,relname
,relacl
,(select usename from pg_user where usesysid = c.relowner) as owner
,(SELECT nspname FROM pg_namespace n WHERE n.oid = c.relnamespace) as nspname
,c.oid
FROM pg_class c
WHERE 1=1
AND relkind in ('r', 'S', 't', 'p','P')
order by owner,relname
) t_cls
) t; 查看某个表上哪些用户拥有什么权限：
SELECT * FROM information_schema.table_privileges
WHERE table_name = 'your_table_name'; 查看schema能被哪些访问：
\d+ https://img-blog.csdnimg.cn/direct/946876b401e04404857ebd8ea71d030b.png
其中UC字母分别表现：Usage、Create权限。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]