ToB企服应用市场:ToB评测及商务社交产业平台
标题:
02：PostgreSQL用户和权限
[打印本页]
作者:
篮之新喜
时间:
2024-8-1 06:24
标题:
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权限。
权限管理
权限分为两类：
对于用户/角色权限（创建语句中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;
复制代码
查看用户/角色对象权限（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,'=|/'))[1] as grantee
,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[2] as privs
,(regexp_split_to_array(unnest(relacl)::text,'=|/'))[3] 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+
复制代码
其中UC字母分别表现：Usage、Create权限。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)
Powered by Discuz! X3.4
