用户和权限概述
数据库用户的主要用途是连接数据库、访问数据库对象和执行SQL语句。在连接数据库时，必须使用一个已经存在的数据库用户。因此，作为数据库管理员，需要为每一个需要连接数据库的使用者规划一个数据库用户。
用户和权限的关系：
本数据库系统是通过角色来管理数据库访问权限的，我们可以将一个角色看成是一个数据库用户，或者一组数据库用户。角色可以拥有数据库对象，如表、索引，也可以把这些对象上的权限赋予其它角色，以控制哪些用户对哪些对象拥有哪些权限。
默认情况下，数据库用户可以分为两大类，详细信息见下表。
用户分类：
分类 | 描述 |
超级用户 | 具有数据库的所有权限，完成集群安装后，会有一个默认的超级用户就是安装用户。 |
普通用户 | 普通用户的权限可以指定和修改。 |
用户通过create role/user的方式创建，这两者的区别是create role创建的用户默认没有login权限，必须通过with login的方式指定登录权限。
权限分类：
本系统权限分为两部分，一部分是数据库系统权限，可以授予role或user(两者区别在于后者默认具有login权限)；一部分为数据库对象的操作权限。
对超级用户不做权限检查，其它走acl。
分类 | 描述 |
系统权限 | 在系统表pg_roles中可以查到每个角色的系统权限。 rolsuper：是否具有超级用户权限 rolinherit：是否可以继承其他角色的权限 rolcreaterole：是否可以创建更多角色 rolcreatedb：是否可以创建数据库 rolcanlogin：是否可以登录 rolreplication：是否可以进行流复制 rolconnlimit：该角色可以连接的次数，如果没有限制，为-1 rolpassword：口令 rolvaliduntil：口令失效日期（只用于口令认证）；如果没有失效期，为 NULL |
操作权限 | 数据库对象的操作权限可以从表pg_class中查询，对应的列是relacl。 r -- SELECT ("读") w -- UPDATE ("写") a -- INSERT ("追加") d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (针对表，对于其他对象该权限列表会变化) |
操作权限说明：
- SELECT：该权限用来查询表或是表上的某些列，或是视图，序列。
- INSERT：该权限允许对表或是视图进行插入数据操作，也可以使用COPY FROM进行数据的插入。
- UPDATE：该权限允许对表或是或是表上特定的列或是视图进行更新操作。
- DELETE：该权限允许对表或是视图进行删除数据的操作。
- TRUNCATE：允许对表进行清空操作。
- REFERENCES：允许给参照列和被参照列上创建外键约束。
- TRIGGER：允许在表上创建触发器。
- CREATE：对于数据库，允许在数据库上创建Schema；对于Schema，允许对Schema上创建数据库对象；对于表空间，允许把表或是索引指定到对应的表空间上。
- CONNECT：允许用户连接到指定的数据库上。
- TEMPORARY或是TEMP：允许在指定数据库的时候创建临时表。
- EXECUTE：允许执行某个函数。
- USAGE：对于程序语言来说，允许使用指定的程序语言创建函数；对于Schema来说，允许查找该Schema下的对象；对于序列来说，允许使用currval和nextval函数；对于外部封装器来说，允许使用外部封装器来创建外部服务器；对于外部服务器来说，允许创建外部表。
- ALL PRIVILEGES：表示一次性给予可以授予的权限。
创建用户并分配权限
在“psql”中通过 `\h create user` 可以得到创建用户的帮助信息：
postgres=# \h create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
在帮助信息中，可以看到，在创建用户的同时，可以指定用户的一些权限，比如是否超级用户、是否可以创建数据库，同时还可以限制该用户的连接数、用户有效期等。
创建用户后，可以通过grant命令给用户分配权限。
只有超级用户才能创建超级用户。
举例说明：
- 创建普通用户（create user默认只有login权限）
create user antdbt_user password '123';
- 角色权限的继承
CREATE ROLE role_father xxx;
CREATE ROLE role_son xxx;
grant role_father to role_son;
- 新建只读角色
CREATE ROLE role1_select WITH
LOGIN
NOSUPERUSER
CREATEDB
CREATEROLE
INHERIT
REPLICATION
CONNECTION LIMIT 10
VALID UNTIL '2018-12-31T17:01:15+08:00'
PASSWORD '123456';
COMMENT ON ROLE role1_select IS 'for read';
grant SELECT on table t1 to role1_select;
- 新建写角色
CREATE ROLE role1_insert WITH
LOGIN
NOSUPERUSER
CREATEDB
CREATEROLE
INHERIT
REPLICATION
CONNECTION LIMIT 10
VALID UNTIL '2018-12-31T17:01:15+08:00'
PASSWORD '123456';
COMMENT ON ROLE role1_insert IS 'for write';
grant INSERT,UPDATE,DELETE,TRUNCATE on table t1 to role1_insert;
- 新建用户并赋予指定角色
创建一个默认用户，只有login权限，其他权限都收回。
CREATE USER antdbt_user WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD 'xxxxxx';
赋予只读角色
grant role1_select TO antdbt_user ;
赋予写角色
grant role1_insert TO antdbt_user ;
- 对sequence类型的授权
usage有currval,nextval这两个函数可用；
update有setval这个函数可用。
grant usage on sequence sq1 to antdbt_user;
grant update on sequence sq1 to antdbt_user;
查看用户及权限
- 查看集群中有哪些用户
创建完成后，通过“\du”命令可以看到集群中有哪些用户：
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
danghb | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
antdbt_user | | {}
- 查看某一个用户的权限
postgres=# \du mass
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
mass | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
修改用户和权限
修改用户
通过alter user命令可以修改用户的名字、权限等。
操作举例：
- 修改用户名：
alter role mass rename to youxing;
- 增加用户为超级/非超级用户
alter role mass with superuser/nosuperuser;
- 修改用户为有登录和没有登录权限
alter role mass with nologin/login;
撤销权限
通过revoke命令可以撤销用户的权限，具体命令请参考帮助信息“\h grant”。grant命令作用和revoke命令相反。
操作举例：
从用户mass中撤销对company表的所有权限。
REVOKE ALL ON COMPANY FROM mass;
删除用户
drop命令可以删除用户，具体使用可以参考帮助信息“\h drop user” “\h drop role”。
操作举例：
DROP ROLE dmp;
注意：删除组role只会删除组的role本身，组的成员并不会被删除。
行安全策略
除了可以通过GRANT使用 SQL 标准的 特权系统之外，表还可以具有 行安全性策略，它针对每一个用户限制哪些行可以 被普通的查询返回或者可以被数据修改命令插入、更新或删除。这种特性也被称为行级安全性。默认情况下，表不具有任何策略，这样用户根据 SQL 特权系统具有对表的访问特权，对于查询或更新来说其中所有的行都是平等的。
当在一个表上启用行安全性时，所有对该表选择行或者修改行的普通访问都必须被一条行安全性策略所允许（表的拥有者通常不服从行安全性策略）。
行安全性策略可以针对特定的命令、角色或者两者。一条策略可以被指定为 适用于ALL命令，或者SELECT、 INSERT、UPDATE、或者DELETE。可以为一条给定策略分配多个角色，并且通常的角色成员关系和继承规则也适用。
语法：
Command: CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
CREATE POLICY name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]
举例：
- 创建一张表，插入一些数据。
create table rsl(id int,name varchar(20));
insert into rsl values(1,'aa');
insert into rsl values(5,'aa');
insert into rsl values(6,'aa');
insert into rsl values(7,'aa');
insert into rsl values(8,'aa');
insert into rsl values(1,'bb');
insert into rsl values(2,'bb');
insert into rsl values(3,'bb');
insert into rsl values(4,'bb');
insert into rsl values(5,'bb');
insert into rsl values(6,'bb');
- 表上开启行安全策略
ALTER TABLE rs1 ENABLE ROW LEVEL SECURITY;
- 创建一条policy
- 只有name字段为a1的用户才有select权限：
create policy a1_select_rsl ON rsl for select to a1 using(name=a1);
ms用户只能查询id为1或者2的行“
CREATE POLICY rs1_ids on rs1 TO ms USING (id = 1 or id =2);
关于AntDB数据库
AntDB数据库始于2008年，在运营商的核心系统上，为全国24个省份的10亿多用户提供在线服务，具备高性能、弹性扩展、高可靠等产品特性，峰值每秒可处理百万笔通信核心交易，保障系统持续稳定运行近十年，并在通信、金融、交通、能源、物联网等行业成功商用落地。
