作者
digoal
日期
2019-05-05
标签
PostgreSQL , 权限 , has_database_privilege , 用户 , 数据库维度 , filter , group , aclexplode , acldefault
背景
指定数据库(或所有数据库)，有哪些用户有这个数据库的权限，分别是什么权限(create, connect, temp)。
指定用户(或所有数据库)，这个用户是哪些数据库的OWNER，对哪些数据库有权限（create, connect, temp）。
数据库的权限分为connect, create, temp。表示可以连接数据库，在数据库中创建SCHEMA，以及在数据库中创建临时对象。
指定用户，这个用户创建的对象，默认赋予什么权限。acldefault
解读ACL内容：aclexplode
例子
1 统计指定数据库（或所有数据库），有哪些用户有这个数据库的权限，分别是什么权限(create, connect, temp)。
用到has_database_privilege函数，pg_database, pg_user视图
postgres=# 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;
usename | datname | pri | granted
----------+-----------+---------+---------
digoal | _abc | CONNECT | t
digoal | _abc | CREATE | t
digoal | _abc | TEMP | t
postgres | _abc | CONNECT | t
postgres | _abc | CREATE | t
postgres | _abc | TEMP | t
r3 | _abc | CONNECT | t
r3 | _abc | CREATE | f
r3 | _abc | TEMP | t
test | _abc | CONNECT | t
test | _abc | CREATE | t
test | _abc | TEMP | t
......
以上为单行输出，可以以数据库为维度，聚合如下
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;
得到如下结果
datname | connect_granted | create_granted | temp_granted
-----------+---------------------------+---------------------------+---------------------------
db4 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
db5 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
db3 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
postgres | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
db2 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
_abc | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
newdb1 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
template1 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,digoal,test}
dd | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
db1 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
b | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
db | {postgres,r3,digoal,test} | {postgres,r3,digoal,test} | {postgres,r3,digoal,test}
template0 | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,digoal,test}
newdb | {postgres,r3,digoal,test} | {postgres,digoal,test} | {postgres,r3,digoal,test}
(14 rows)
是不是清爽了很多。一目了然。
2 指定用户(或所有数据库)，这个用户是哪些数据库的OWNER
SQL如下
postgres=# select datdba::regrole,array_agg(datname) from pg_database group by 1;
datdba | array_agg
----------+---------------------------------------------------------------------------
test | {dd}
postgres | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,db}
(2 rows)
一目了然，test是dd的owner。postgres是postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,db的owner。
3 指定用户(或所有数据库)，这个用户对哪些数据库有权限（create, connect, temp）。
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;
结果如下
-[ RECORD 1 ]---+-----------------------------------------------------------------------------
usename | postgres
connect_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
create_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
temp_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
-[ RECORD 2 ]---+-----------------------------------------------------------------------------
usename | r3
connect_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
create_granted | {db}
temp_granted | {postgres,b,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
-[ RECORD 3 ]---+-----------------------------------------------------------------------------
usename | test
connect_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
create_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
temp_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
-[ RECORD 4 ]---+-----------------------------------------------------------------------------
usename | digoal
connect_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
create_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
temp_granted | {postgres,b,template1,template0,db2,db1,db4,db5,newdb,newdb1,_abc,db3,dd,db}
同样一目了然，每个用户，哪些数据库有create权限，哪些有连接权限，哪些有创建临时对象权限。
4 指定用户，这个用户OWNER的指定对象类型的默认权限。
acldefault returns the built-in default access privileges for an object of type type belonging to role ownerId.
These represent the access privileges that will be assumed when an object's ACL entry is null.
(The default access privileges are described in Section 5.7.)
The type parameter is a CHAR: write
'c' for COLUMN, 'r' for TABLE and table-like objects,
's' for SEQUENCE, 'd' for DATABASE, 'f' for FUNCTION or PROCEDURE, 'l' for LANGUAGE,
'L' for LARGE OBJECT, 'n' for SCHEMA, 't' for TABLESPACE, 'F' for FOREIGN DATA WRAPPER,
'S' for FOREIGN SERVER, or 'T' for TYPE or DOMAIN.
例子
1、postgres用户创建的数据库，默认给public的权限包括temp, connect。
```
postgres=# select * from acldefault('d','postgres'::regrole);
acldefault
{=Tc/postgres,postgres=CTc/postgres}
(1 row)
```
2、postgres用户创建的表，默认只给自己arwdDxt权限。
```
postgres=# select * from acldefault('r','postgres'::regrole);
acldefault
{postgres=arwdDxt/postgres}
(1 row)
```
使用aclexplode解读acl含义
postgres=# select (aclexplode(acldefault)).grantor::regrole,(aclexplode(acldefault)).grantee::regrole,(aclexplode(acldefault)).* from acldefault('r','postgres'::regrole);
grantor | grantee | grantor | grantee | privilege_type | is_grantable
----------+----------+---------+---------+----------------+--------------
postgres | postgres | 10 | 10 | INSERT | f
postgres | postgres | 10 | 10 | SELECT | f
postgres | postgres | 10 | 10 | UPDATE | f
postgres | postgres | 10 | 10 | DELETE | f
postgres | postgres | 10 | 10 | TRUNCATE | f
postgres | postgres | 10 | 10 | REFERENCES | f
postgres | postgres | 10 | 10 | TRIGGER | f
(7 rows)
5 列出指定用户是否有列、表、SCHEMA、序列、函数、外部表、语言等等对象的指定权限。
PostgreSQL提供了权限检查函数，对所有对象都适用，如下。
postgres=# \df *.*privile*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------------------------------+------------------+----------------------------+------
pg_catalog | has_any_column_privilege | boolean | name, oid, text | func
pg_catalog | has_any_column_privilege | boolean | name, text, text | func
pg_catalog | has_any_column_privilege | boolean | oid, oid, text | func
pg_catalog | has_any_column_privilege | boolean | oid, text | func
pg_catalog | has_any_column_privilege | boolean | oid, text, text | func
pg_catalog | has_any_column_privilege | boolean | text, text | func
pg_catalog | has_column_privilege | boolean | name, oid, smallint, text | func
pg_catalog | has_column_privilege | boolean | name, oid, text, text | func
pg_catalog | has_column_privilege | boolean | name, text, smallint, text | func
pg_catalog | has_column_privilege | boolean | name, text, text, text | func
pg_catalog | has_column_privilege | boolean | oid, oid, smallint, text | func
pg_catalog | has_column_privilege | boolean | oid, oid, text, text | func
pg_catalog | has_column_privilege | boolean | oid, smallint, text | func
pg_catalog | has_column_privilege | boolean | oid, text, smallint, text | func
pg_catalog | has_column_privilege | boolean | oid, text, text | func
pg_catalog | has_column_privilege | boolean | oid, text, text, text | func
pg_catalog | has_column_privilege | boolean | text, smallint, text | func
pg_catalog | has_column_privilege | boolean | text, text, text | func
pg_catalog | has_database_privilege | boolean | name, oid, text | func
pg_catalog | has_database_privilege | boolean | name, text, text | func
pg_catalog | has_database_privilege | boolean | oid, oid, text | func
pg_catalog | has_database_privilege | boolean | oid, text | func
pg_catalog | has_database_privilege | boolean | oid, text, text | func
pg_catalog | has_database_privilege | boolean | text, text | func
pg_catalog | has_foreign_data_wrapper_privilege | boolean | name, oid, text | func
pg_catalog | has_foreign_data_wrapper_privilege | boolean | name, text, text | func
pg_catalog | has_foreign_data_wrapper_privilege | boolean | oid, oid, text | func
pg_catalog | has_foreign_data_wrapper_privilege | boolean | oid, text | func
pg_catalog | has_foreign_data_wrapper_privilege | boolean | oid, text, text | func
pg_catalog | has_foreign_data_wrapper_privilege | boolean | text, text | func
pg_catalog | has_function_privilege | boolean | name, oid, text | func
pg_catalog | has_function_privilege | boolean | name, text, text | func
pg_catalog | has_function_privilege | boolean | oid, oid, text | func
pg_catalog | has_function_privilege | boolean | oid, text | func
pg_catalog | has_function_privilege | boolean | oid, text, text | func
pg_catalog | has_function_privilege | boolean | text, text | func
pg_catalog | has_language_privilege | boolean | name, oid, text | func
pg_catalog | has_language_privilege | boolean | name, text, text | func
pg_catalog | has_language_privilege | boolean | oid, oid, text | func
pg_catalog | has_language_privilege | boolean | oid, text | func
pg_catalog | has_language_privilege | boolean | oid, text, text | func
pg_catalog | has_language_privilege | boolean | text, text | func
pg_catalog | has_schema_privilege | boolean | name, oid, text | func
pg_catalog | has_schema_privilege | boolean | name, text, text | func
pg_catalog | has_schema_privilege | boolean | oid, oid, text | func
pg_catalog | has_schema_privilege | boolean | oid, text | func
pg_catalog | has_schema_privilege | boolean | oid, text, text | func
pg_catalog | has_schema_privilege | boolean | text, text | func
pg_catalog | has_sequence_privilege | boolean | name, oid, text | func
pg_catalog | has_sequence_privilege | boolean | name, text, text | func
pg_catalog | has_sequence_privilege | boolean | oid, oid, text | func
pg_catalog | has_sequence_privilege | boolean | oid, text | func
pg_catalog | has_sequence_privilege | boolean | oid, text, text | func
pg_catalog | has_sequence_privilege | boolean | text, text | func
pg_catalog | has_server_privilege | boolean | name, oid, text | func
pg_catalog | has_server_privilege | boolean | name, text, text | func
pg_catalog | has_server_privilege | boolean | oid, oid, text | func
pg_catalog | has_server_privilege | boolean | oid, text | func
pg_catalog | has_server_privilege | boolean | oid, text, text | func
pg_catalog | has_server_privilege | boolean | text, text | func
pg_catalog | has_table_privilege | boolean | name, oid, text | func
pg_catalog | has_table_privilege | boolean | name, text, text | func
pg_catalog | has_table_privilege | boolean | oid, oid, text | func
pg_catalog | has_table_privilege | boolean | oid, text | func
pg_catalog | has_table_privilege | boolean | oid, text, text | func
pg_catalog | has_table_privilege | boolean | text, text | func
pg_catalog | has_tablespace_privilege | boolean | name, oid, text | func
pg_catalog | has_tablespace_privilege | boolean | name, text, text | func
pg_catalog | has_tablespace_privilege | boolean | oid, oid, text | func
pg_catalog | has_tablespace_privilege | boolean | oid, text | func
pg_catalog | has_tablespace_privilege | boolean | oid, text, text | func
pg_catalog | has_tablespace_privilege | boolean | text, text | func
pg_catalog | has_type_privilege | boolean | name, oid, text | func
pg_catalog | has_type_privilege | boolean | name, text, text | func
pg_catalog | has_type_privilege | boolean | oid, oid, text | func
pg_catalog | has_type_privilege | boolean | oid, text | func
pg_catalog | has_type_privilege | boolean | oid, text, text | func
pg_catalog | has_type_privilege | boolean | text, text | func
(78 rows)
利用以上函数，扩展到表、SCHEMA、函数、序列、字段等层面，可以做到与本文一样的效果。
参考
https://www.postgresql.org/docs/11/sql-expressions.html#SYNTAX-AGGREGATES
https://www.postgresql.org/docs/devel/functions-info.html
https://www.postgresql.org/docs/current/ddl-priv.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
