PostgreSQL在哪里存放默认的权限

先创建一个测试用户

postgres=# create user abce with login password 'abce';
CREATE ROLE
postgres=# create schema t;
CREATE SCHEMA
postgres=# alter default privileges in schema t grant select on tables to abce;
ALTER DEFAULT PRIVILEGES
postgres=# 

目录表pg_user中有个列：useconfig。我们可能会觉得是存在这里：

postgres=# \d pg_user
                View "pg_catalog.pg_user"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 usename      | name    |           |          | 
 usesysid     | oid     |           |          | 
 usecreatedb  | boolean |           |          | 
 usesuper     | boolean |           |          | 
 userepl      | boolean |           |          | 
 usebypassrls | boolean |           |          | 
 passwd       | text    |           |          | 
 valuntil     | abstime |           |          | 
 useconfig    | text[]  |           |          | 

postgres=# select * from pg_user where usename='abce';
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 abce    |    74849 | f           | f        | f       | f            | ******** |          | 
(1 row)

postgres=# 

但是，这里并没有存储默认的权限。

 

再来看看目录表pg_namespace

postgres=# select * from pg_namespace where nspname='t';
 nspname | nspowner | nspacl 
---------+----------+--------
 t       |       10 | 
(1 row)

postgres=# 

也没有存放在pg_namespace表中。但是，这里却给了我们一个提示：ACL(访问控制列表)。让我们来看看是否有相关的目录表存在：

postgres=# select * from pg_tables where tablename like '%acl%';
 schemaname |   tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+----------------+------------+------------+------------+----------+-------------+-------------
 pg_catalog | pg_default_acl | postgres   |            | t          | f        | f           | f
(1 row)

postgres=# 

可以看到，有个pg_default_acl目录表。

 

继续往下查看：

postgres=# select * from pg_default_acl where defaclnamespace='t'::regnamespace;
 defaclrole | defaclnamespace | defaclobjtype |     defaclacl     
------------+-----------------+---------------+-------------------
         10 |           74850 | r             | {abce=r/postgres}
(1 row)

postgres=# 

这里“abce=r”表示用户abce在所有对象上有read的权限。

 

再次尝试修改abce的默认权限：

postgres=# alter default privileges in schema t grant insert on tables to abce;
ALTER DEFAULT PRIVILEGES
postgres=# select * from pg_default_acl where defaclnamespace='t'::regnamespace;
 defaclrole | defaclnamespace | defaclobjtype |     defaclacl      
------------+-----------------+---------------+--------------------
         10 |           74850 | r             | {abce=ar/postgres}
(1 row)

postgres=# 

现在abce就被增加a权限，a表示append(insert)。权限的缩写以及含义可以查看文档：https://www.postgresql.org/docs/current/ddl-priv.html

 

这里的“/postgres”表示schema的属主。

postgres=# alter user abce superuser;
ALTER ROLE
postgres=# \c postgres abce
You are now connected to database "postgres" as user "abce".
postgres=# create schema t2;
CREATE SCHEMA
postgres=# select * from pg_default_acl where defaclnamespace='t2'::regnamespace;
 defaclrole | defaclnamespace | defaclobjtype | defaclacl 
------------+-----------------+---------------+-----------
(0 rows)

postgres=# create user abce2;
CREATE ROLE
postgres=# alter default privileges in schema t2 grant select on tables to abce2;
ALTER DEFAULT PRIVILEGES
postgres=# select * from pg_default_acl where defaclnamespace='t2'::regnamespace;
 defaclrole | defaclnamespace | defaclobjtype |   defaclacl    
------------+-----------------+---------------+----------------
      74849 |           74852 | r             | {abce2=r/abce}
(1 row)

postgres=#