PostgreSQL在哪里存放默认的权限

先创建一个测试用户

?

1 2 3 4 5 6 7

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。我们可能会觉得是存在这里：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

?

1 2 3 4 5 6 7

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

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

?

1 2 3 4 5 6 7

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目录表。

 

继续往下查看：

?

1 2 3 4 5 6 7

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的默认权限：

?

1 2 3 4 5 6 7 8 9

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的属主。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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=#