SRE文档

深浅模式

1.acl ​

  • ACL 权限列表
Relacl CodePrivilege Name
aINSERT
rSELECT
wUPDATE
dDELETE
DTRUNCATE
xREFERENCES
tTRIGGER
arwdDxtALL

1. 通过 \dp 元子命令 ​

postgres@127.0.0.1 ~=#\dp pg_stat_statements;
                                       Access privileges
 Schema |        Name        | Type |     Access privileges     | Column privileges | Policies 
--------+--------------------+------+---------------------------+-------------------+----------
 public | pg_stat_statements | view | postgres=arwdDxt/postgres+|                   | 
        |                    |      | =r/postgres               |                   |
postgres@127.0.0.1 ~=#\dp pg_stat_statements;
                                       Access privileges
 Schema |        Name        | Type |     Access privileges     | Column privileges | Policies 
--------+--------------------+------+---------------------------+-------------------+----------
 public | pg_stat_statements | view | postgres=arwdDxt/postgres+|                   | 
        |                    |      | =r/postgres               |                   |

2.通过 aclexplode 函数 ​

postgres@127.0.0.1 ~=# select oid,relname ,relacl from pg_class where relname='pg_stat_statements';
  oid  |      relname       |                 relacl                  
-------+--------------------+-----------------------------------------
 16388 | pg_stat_statements | {postgres=arwdDxt/postgres,=r/postgres}
(1 row)
postgres@127.0.0.1 ~=# select oid,relname ,relacl from pg_class where relname='pg_stat_statements';
  oid  |      relname       |                 relacl                  
-------+--------------------+-----------------------------------------
 16388 | pg_stat_statements | {postgres=arwdDxt/postgres,=r/postgres}
(1 row)

解析：

=#select * From aclexplode('{postgres=arwdDxt/postgres,=r/postgres}');
 grantor | grantee | privilege_type | is_grantable 
---------+---------+----------------+--------------
      10 |      10 | INSERT         | f
      10 |      10 | SELECT         | f
      10 |      10 | UPDATE         | f
      10 |      10 | DELETE         | f
      10 |      10 | TRUNCATE       | f
      10 |      10 | REFERENCES     | f
      10 |      10 | TRIGGER        | f
      10 |       0 | SELECT         | f
 
#找到对应用户
#select oid,rolname from pg_roles where oid in (10,0);
 oid | rolname  
-----+----------
  10 | postgres
=#select * From aclexplode('{postgres=arwdDxt/postgres,=r/postgres}');
 grantor | grantee | privilege_type | is_grantable 
---------+---------+----------------+--------------
      10 |      10 | INSERT         | f
      10 |      10 | SELECT         | f
      10 |      10 | UPDATE         | f
      10 |      10 | DELETE         | f
      10 |      10 | TRUNCATE       | f
      10 |      10 | REFERENCES     | f
      10 |      10 | TRIGGER        | f
      10 |       0 | SELECT         | f
 
#找到对应用户
#select oid,rolname from pg_roles where oid in (10,0);
 oid | rolname  
-----+----------
  10 | postgres

3. 通过 table_privileges 视图 ​

postgres@127.0.0.1 ~=#select grantor,grantee,table_schema,table_name,privilege_type from information_schema.table_privileges where table_name='pg_stat_statements';
 grantor  | grantee  | table_schema |     table_name     | privilege_type 
----------+----------+--------------+--------------------+----------------
 postgres | postgres | public       | pg_stat_statements | INSERT
 postgres | postgres | public       | pg_stat_statements | SELECT
 postgres | postgres | public       | pg_stat_statements | UPDATE
 postgres | postgres | public       | pg_stat_statements | DELETE
 postgres | postgres | public       | pg_stat_statements | TRUNCATE
 postgres | postgres | public       | pg_stat_statements | REFERENCES
 postgres | postgres | public       | pg_stat_statements | TRIGGER
 postgres | PUBLIC   | public       | pg_stat_statements | SELECT
postgres@127.0.0.1 ~=#select grantor,grantee,table_schema,table_name,privilege_type from information_schema.table_privileges where table_name='pg_stat_statements';
 grantor  | grantee  | table_schema |     table_name     | privilege_type 
----------+----------+--------------+--------------------+----------------
 postgres | postgres | public       | pg_stat_statements | INSERT
 postgres | postgres | public       | pg_stat_statements | SELECT
 postgres | postgres | public       | pg_stat_statements | UPDATE
 postgres | postgres | public       | pg_stat_statements | DELETE
 postgres | postgres | public       | pg_stat_statements | TRUNCATE
 postgres | postgres | public       | pg_stat_statements | REFERENCES
 postgres | postgres | public       | pg_stat_statements | TRIGGER
 postgres | PUBLIC   | public       | pg_stat_statements | SELECT