本文已参与好文召集令活动，点击查看：后端、大前端双赛道投稿，2万元奖池等你挑战！
承接上文，控制住了访问数据库的门后，接下来就要控制可以访问操作门后的哪些东西了，你若是这间屋子的主人，当然是可以操作屋里所有的一切，但你若只是一个普通的访客，还让你可以操作一切的话就不太合适了。
所有用户默认均可访问public模式
PostgreSql中，所有新建的数据库都会默认自带一个 public 模式，所有用户均可在这个模式下创建管理自己的对象，且在数据库中创建表、视图等基本对象时，若没有指定模式，这些对象默认也会存放在 pubilc 模式下。
--新建数据库d1,d2均自带 public 模式。
postgres=# create database d1;
CREATE DATABASE
postgres=# \c d1;
You are now connected to database "d1" as user "postgres".
d1=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
d1=# create database d2;
CREATE DATABASE
d1=# \c d2;
You are now connected to database "d2" as user "postgres".
d2=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
--新建用户即使不是 pubilc 的owner,也可以在这个模式下创建对象，换成其他模式则无权限，且不指定 schema 时，创建的对象就会存放在 public 模式下。
d1=# create user u1 password 'u1';
CREATE ROLE
d1=# \c - u1
You are now connected to database "d1" as user "u1".
d1=> \dn
List of schemas
Name | Owner
--------+----------
d1s1 | postgres
public | postgres
(2 rows)
d1=> create table t1(id int);
CREATE TABLE
d1=> insert into t1 values(1);
INSERT 0 1
d1=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | t1 | table | u1
(1 row)
d1=> create table d1s1.t1(id int);
ERROR: permission denied for schema d1s1
LINE 1: create table d1s1.t1(id int);
^
d1=> select * from t1;
id
----
1
(1 row)
d1=> select * from public.t1;
id
----
1
(1 row)
因为这个特性，生产环境中的业务用户创建对象时，若没有指定 schema，可能将表等对象误建在 pubilc 模式中，为防止这样的操作，可通过如下命令，回收权限。
d1=> \c - postgres
You are now connected to database "d1" as user "postgres".
d1=# revoke create on schema public from public;
REVOKE
d1=# \c - u1
You are now connected to database "d1" as user "u1".
d1=> create table t2(id int);
ERROR: permission denied for schema public
LINE 1: create table t2(id int);
谁是 owner，谁就有操作这对象的权限
每个数据库对象都有 owner 的概念，在其他用户未有额外授权时，除管理员用户外，只有 owner 可操作自己的对象。
--数据库的所属 owner 有在这个数据库下创建 schema 的权限，除管理员用户的其他用户则没有。
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
d2 | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(7 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
u1 | | {}
u2 | | {}
postgres=# \c d2 u1
You are now connected to database "d2" as user "u1".
d2=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
d2=> create schema d2s1;
ERROR: permission denied for database d2
d2=> \c - u2
You are now connected to database "d2" as user "u2".
d2=> create schema d2s1;
CREATE SCHEMA
d2=> \dn
List of schemas
Name | Owner
--------+----------
d2s1 | u2
public | postgres
(2 rows)
--schema 所属 owner 有在这个 schema 下创建表等对象的权限，除管理员用户的其他用户则没有。
d1=# \dn
List of schemas
Name | Owner
--------+----------
d1s1 | u1
public | postgres
(2 rows)
d1=# \c - u1
You are now connected to database "d1" as user "u1". ^
d1=> create table d1s1.t1(id int);
CREATE TABLE
d1=> insert into d1s1.t1 values(1);
INSERT 0 1
d1=> \c - u2
You are now connected to database "d1" as user "u2".
d1=> create table d1s1.t2(id int);
ERROR: permission denied for schema d1s1
LINE 1: create table d1s1.t2(id int);
表，视图等同上，除管理员用户，只有所属 owner 有查询，修改自身的权限，不在举例赘述。
查询权限
--查询数据库集簇中所有角色
d1=> select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
syd | f | t | f | f | t | f | -1 | ******** | | f | | 16387
u1 | f | t | f | f | t | f | -1 | ******** | | f | | 41008
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
u2 | f | t | f | f | t | f | -1 | ******** | | f | | 41009
repmgr | t | t | f | f | t | f | -1 | ******** | | f | | 32769
(13 rows)
用户和角色不是一样的吗？ 为啥此时不称之为用户呢？
因为此时角色的概念又同 oracle 中的角色一样，一些权限的集合，可被赋予其他用户。
d1=# grant pg_signal_backend,pg_read_server_files,postgres to syd,u1,u2;
GRANT ROLE
--查询数据库集簇中指定用户 u1 的权限
d1=> select * from information_schema.table_privileges where grantee = 'u1';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
u1 | u1 | d1 | d1s1 | t1 | INSERT | YES | NO
u1 | u1 | d1 | d1s1 | t1 | SELECT | YES | YES
u1 | u1 | d1 | d1s1 | t1 | UPDATE | YES | NO
u1 | u1 | d1 | d1s1 | t1 | DELETE | YES | NO
u1 | u1 | d1 | d1s1 | t1 | TRUNCATE | YES | NO
u1 | u1 | d1 | d1s1 | t1 | REFERENCES | YES | NO
u1 | u1 | d1 | d1s1 | t1 | TRIGGER | YES | NO
(7 rows)
授权权限
{}：表示必选项
[]：表示可选项
- 创建用户时额外授权
create user/role 用户名|public [[with] option];
- 已创建用户修改属性
create user/role 用户名|public [with] option;
option 可以为
superuser|nosuperuser：超级权限，拥有所有权限，默认 nosuperuser。
inherit|noinherit：继承权限，可以把除superuser权限继承给其他用户/角色，默认 inherit。
login|nologin：登录权限，作为连接的用户，默认 nologin，除非是 create user（默认登录）。
password 'password'|null：设置密码，密码仅用于有 login 属性的用户，不使用密码身份验证，则可以省略此选项，可以选择将空密码显式写为password null。
valid until 'timestamp'：密码有效期时间，不设置则用不失效。
createdb|nocreatedb：建库权限，默认 nocreatedb。
createrole|nocreaterole：建角色权限，拥有创建、修改、删除角色，默认nocreaterole。
replication|noreplication：复制权限，用于物理或则逻辑复制（复制和删除 slots），默认是 noreplication。
bypassrls|nobypassrls：安全策略rls权限，默认nobypassrls。
connection limit connlimit：限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
public：所有用户。
- 已创建用户授权数据库操作权限
grant {{create|connect|temporary|temp}|all[ privileges]} on database 数据库名 to 用户名|pubilc [with grant option];
create：对于数据库，允许在数据库中创建新的schema、table、index。
connect：允许用户连接到指定的数据库。在连接启动时检查此权限。
temporary、temp：允许在使用指定数据库时创建临时表。
all privileges：一次授予所有可用权限。
public：所有用户。
- 已创建用户授权模式操作权限
grant {{create|usage}|all [privileges]} on schema 模式名 to 用户名|pubilc [with grant option];
create：对于schema，允许在模式中创建新的 table、index等。
usage：对于schema，允许访问指定模式中包含的对象；对于 sequence，允许使用 currval 和nextval 函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
all privileges：一次授予所有可用权限。
public：所有用户。
- 已创建用户授权基本对象操作权限
grant {{select|insert|update|delete|truncate|references|trigger}|all [privileges]} on {[table] 表名|all tables in schema 模式名} to 用户名|pubilc [with grant option];
select：允许从指定表，视图或序列的任何列或列出的特定列进行 select。也允许使用 copy to。在 update 或 delete 中引用现有列值也需要此权限。对于序列，此权限还允许使用 currval函数。对于大对象，此权限允许读取对象。
insert：允许将新行 insert 到指定的表中。如果列出了特定列，则只能在 insert 命令中为这些列分配（因此其他列将接收默认值）。也允许 copy from。
update：允许更新指定表的任何列或列出的特定列，需要 select 权限。
delete：允许删除指定表中的行，需要 select 权限。
truncate：允许清空指定表。
references：允许创建引用指定表或表的指定列的外键约束。
trigger：允许在指定的表上创建触发器。
execute：允许使用指定的函数或过程以及在函数。
all privileges：一次授予所有可用权限。
public：所有用户。
--授权所有用户对数据库 d1 的所有操作权限。
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
d2 | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
syd | syd | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(7 rows)
postgres=# grant all on database d1 to public;
GRANT
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =CTc/postgres +
| | | | | postgres=CTc/postgres
d2 | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
syd | syd | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(7 rows)
--授权后 u1，u2 用户都可在 d1 数据库下创建模式。
postgres=# \c d1 u1
You are now connected to database "d1" as user "u1".
d1=> \dn
List of schemas
Name | Owner
--------+----------
d1s1 | u1
public | postgres
(2 rows)
d1=> create schema d1s2;
CREATE SCHEMA
d1=> \c - u2
You are now connected to database "d1" as user "u2".
d1=> create schema d1s3;
CREATE SCHEMA
d1=> \dn
List of schemas
Name | Owner
--------+----------
d1s1 | u1
d1s2 | u1
d1s3 | u2
public | postgres
(4 rows)
撤销权限
revoke [grant option for] {上述所有权限} from {用户名|public} [cascade|restrict];
public：所有用户。
cascade：级联回收权限。
restrict：不级联回收权限。
--接着上述授权操作，回收所有用户对数据库 d1 的所有权限
d1=# revoke all on database d1 from public;
REVOKE
d1=# \c d1 u1
FATAL: permission denied for database "d1"
DETAIL: User does not have CONNECT privilege.
--回收所以权限后发现，用户连接数据库的权限也没了，再次授予连接数据库 d1 的权限。
d1=# grant connect on database d1 to public;
GRANT
d1=# \c - u1
You are now connected to database "d1" as user "u1".
d1=> \dn
List of schemas
Name | Owner
--------+----------
d1s1 | u1
d1s2 | u1
d1s3 | u2
public | postgres
(4 rows)
d1=> create schema d1s4;
ERROR: permission denied for database d1
无权限创建 schema，可见上述 all 权限已被回收。
