PostgreSQL
pg+debezium+kafka实时同步
三机patroni+etcd高可用
基于Python实现大批量dataframe格式数据快速存入postgresql
python数据批量插入postgreSQL数据库
pg 插件扩展(extension)开发
psql 连接数据库的方法
pg 函数返回表、数据集、结果集
pg 日志记录 异常处理
pg 作业调度器 pg_timetable
pg 查询对象的权限
正则表达式去除注释
pg 数据库高可用及负载均衡JDBC参数
pg 生成节假日
pg_recvlogical 解析日志
pg 查看锁表
pg upsert写法
pg 查看函数或存储过程的定义
plpgsql 代码块
pg 查看表的膨胀率
pg 获取表大小
pg 非物化视图所有者刷新物化视图
本站点使用 MrDoc 构建
-
+
首页
pg 查询对象的权限
参考:http://postgres.cn/docs/14/ddl-priv.html ## ACL权限缩写 | 权限 | 缩写 | 适用对象类型 | | ------------ | ------------ | ------------------------------------------------------------ | | `SELECT` | `r` | `LARGE OBJECT`, `SEQUENCE`, `TABLE` (and table-like objects), table column | | `INSERT` | `a` | `TABLE`, table column | | `UPDATE` | `w` | `LARGE OBJECT`, `SEQUENCE`, `TABLE`, table column | | `DELETE` | `d` | `TABLE` | | `TRUNCATE` | `D` | `TABLE` | | `REFERENCES` | `x` | `TABLE`, table column | | `TRIGGER` | `t` | `TABLE` | | `CREATE` | `C` | `DATABASE`, `SCHEMA`, `TABLESPACE` | | `CONNECT` | `c` | `DATABASE` | | `TEMPORARY` | `T` | `DATABASE` | | `EXECUTE` | `X` | `FUNCTION`, `PROCEDURE` | | `USAGE` | `U` | `DOMAIN`, `FOREIGN DATA WRAPPER`, `FOREIGN SERVER`, `LANGUAGE`, `SCHEMA`, `SEQUENCE`, `TYPE` | ## 访问权限摘要 | 对象类型 | 所有权限 | 默认 `PUBLIC` 权限 | psql 命令 | | -------------------------------- | --------- | ------------------ | --------- | | `DATABASE` | `CTc` | `Tc` | `\l` | | `DOMAIN` | `U` | `U` | `\dD+` | | `FUNCTION` or `PROCEDURE` | `X` | `X` | `\df+` | | `FOREIGN DATA WRAPPER` | `U` | none | `\dew+` | | `FOREIGN SERVER` | `U` | none | `\des+` | | `LANGUAGE` | `U` | `U` | `\dL+` | | `LARGE OBJECT` | `rw` | none | | | `SCHEMA` | `UC` | none | `\dn+` | | `SEQUENCE` | `rwU` | none | `\dp` | | `TABLE` (and table-like objects) | `arwdDxt` | none | `\dp` | | Table column | `arwx` | none | `\dp` | | `TABLESPACE` | `C` | none | `\db+` | | `TYPE` | `U` | `U` | `\dT+` | ## aclitem 函数 | 名称 | 返回类型 | 描述 | | ----------------------------------------------------- | -------------- | ----------------------------------------- | | `acldefault(type, ownerId)` | `aclitem[]` | 获取属于*`ownerId`*的对象的默认访问权限。 | | `aclexplode(aclitem[])` | `setof record` | 获取 `aclitem` 数组为元组 | | `makeaclitem(grantee, grantor, privilege, grantable)` | `aclitem` | 从输入中建立一个`aclitem`。 | > `acldefault`返回属于角色*`ownerId`*的*`type`*类型的对象的内置默认访问权限。 这些代表了当对象的ACL条目为空时将被假定的访问权限。 *`type`* 参数是一个 `CHAR`: 'c' for `COLUMN` 'r' for `TABLE` 和类表对象 's' for `SEQUENCE` 'd' for `DATABASE` 'f' for `FUNCTION` 或者 `PROCEDURE` 'l' for `LANGUAGE` 'L' for `LARGE OBJECT` 'n' for `SCHEMA` 't' for `TABLESPACE` 'F' for `FOREIGN DATA WRAPPER` 'S' for `FOREIGN SERVER` 'T' for `TYPE` 或者 `DOMAIN` > `aclexplode`返回一个`aclitem`数组作为行集。输出的列是grantor `oid`, grantee `oid` (`0` for `PUBLIC`), privilege_type `text` (`SELECT`, ...) 以及权限是否可以被授予`is_grantable`(`boolean`)。 `makeaclitem`执行反向操作,返回`aclitem`。 ## 查询rel对象(表、视图、物化视图、序列、外部表、分区表)的权限 ```sql SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' END as "Type", pg_catalog.array_to_string(coalesce(c.relacl,acldefault('r',c.relowner)), E'\n') AS "Access privileges", pg_catalog.array_to_string(ARRAY( SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ') FROM pg_catalog.pg_attribute a WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL ), E'\n') AS "Column privileges", pg_catalog.array_to_string(ARRAY( SELECT polname || CASE WHEN NOT polpermissive THEN E' (RESTRICTIVE)' ELSE '' END || CASE WHEN polcmd != '*' THEN E' (' || polcmd || E'):' ELSE E':' END || CASE WHEN polqual IS NOT NULL THEN E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid) ELSE E'' END || CASE WHEN polwithcheck IS NOT NULL THEN E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid) ELSE E'' END || CASE WHEN polroles <> '{0}' THEN E'\n to: ' || pg_catalog.array_to_string( ARRAY( SELECT rolname FROM pg_catalog.pg_roles WHERE oid = ANY (polroles) ORDER BY 1 ), E', ') ELSE E'' END FROM pg_catalog.pg_policy pol WHERE polrelid = c.oid), E'\n') AS "Policies" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','v','m','S','f','p') AND n.nspname = 'public' AND c.relname = 'test' ORDER BY 1, 2; ``` ## 查询数据库哪些用户具有什么权限(create, connect, temp) ```sql select usename, datname, pri, has_database_privilege (usename, datname, pri) as granted from pg_user, pg_database, (values ('CREATE'),('CONNECT'),('TEMP')) t (pri) order by 2,1,3; --以数据库维度聚合 select datname, array_agg(usename) filter (where pri='CONNECT' and has_database_privilege (usename, datname, pri)=true) as connect_granted , array_agg(usename) filter (where pri='CREATE' and has_database_privilege (usename, datname, pri)=true) as create_granted , array_agg(usename) filter (where pri='TEMP' and has_database_privilege (usename, datname, pri)=true) as temp_granted from pg_user, pg_database, (values ('CREATE'),('CONNECT'),('TEMP')) t (pri) group by datname; ``` ## 查询用户是哪些数据库的OWNER ```sql select datdba::regrole, array_agg(datname) datname from pg_database group by 1 ``` ## 指定用户对哪些数据库有权限 ```sql select usename, array_agg(datname) filter (where pri='CONNECT' and has_database_privilege (usename, datname, pri)=true) as connect_granted , array_agg(datname) filter (where pri='CREATE' and has_database_privilege (usename, datname, pri)=true) as create_granted , array_agg(datname) filter (where pri='TEMP' and has_database_privilege (usename, datname, pri)=true) as temp_granted from pg_user, pg_database, (values ('CREATE'),('CONNECT'),('TEMP')) t (pri) group by usename; ``` ## 查询OWNER的指定对象类型的默认权限 ```sql --数据库默认权限 select * from acldefault('d','postgres'::regrole); --relation默认权限 select * from acldefault('r','postgres'::regrole); ``` ## 使用aclexplode解读acl含义 ```sql select (aclexplode(acldefault)).grantor::regrole, (aclexplode(acldefault)).grantee::regrole, (aclexplode(acldefault)).* from acldefault('r','postgres'::regrole) ; ``` ## 列出权限检查函数 ```sql SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc' ELSE 'func' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.proname OPERATOR(pg_catalog.~) '^(.*privile.*)$' COLLATE pg_catalog.default ORDER BY 1, 2, 4; ```
vleity
2025年7月24日 17:06
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
PDF文档(打印)
分享
链接
类型
密码
更新密码