主要是想弄清楚ACL column的意思,比如如下所示的\l命令结果显示:
+-----------+----------+----------+-------------+-------------+-----------------------+
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
+-----------+----------+----------+-------------+-------------+-----------------------+
| postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| tain | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +|
| | | | | | postgres=CTc/postgres+|
| | | | | | ss=CTc/postgres +|
| | | | | | sam=c*/postgres +|
| | | | | | jasmine=c/sam |
| template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +|
| | | | | | postgres=CTc/postgres |
| test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| vault_dev | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
| webdev | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
+-----------+----------+----------+-------------+-------------+-----------------------+
其中tain数据库行的Acess privileges列信息显示的,/后面表示granter(授权者);=前表示grentee(受权者),如果没有值表示public角色;
/前面表示授权权限;database和table的权限选项不同,具体可以参见官方文档说明(www.postgresql.org/docs/curren…
=Tc/postgres 表示postgres授权public角色,Truncate(T)和connect(c)权限
postgres=CTc/postgres 表示postgres授权postgres角色用户,数据库tain的默认权限create,truncate,connect权限,这个也是数据库的默认权限
ss=CTc/postgres 表示postgres授权用户ss,同样是默认角色
sam=c*/postgres 表示postgres授权用户sam,connect权限,并且connect权限可以再授权给其他用户,这个需要授权给sam时添加with grant option
jasmine=c/sam 表示sam授权用户jasmine,connect权限,并且connect权限可以不能再授权给其他用户
需要注意用户有database的Create权限,并不是表示创建table的权限,上面的sam是可以创建table的,但是不能创建schema:
CREATE
For databases, allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database.
For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.
For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace.
Note that revoking this privilege will not alter the existence or location of existing objects.
From < https://www.postgresql.org/docs/current/ddl-priv.html >
\l 显示database的信息
\dp test 显示test table的Access Privilege信息