PgSQL用户和角色

PostgreSQL使用角色role的概念来管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常会把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在PostgreSQL中，角色与用户是没有区别的，一个用户也是一个角色，因此可以把一个用户的权限赋给另一个用户。

用户和角色在整个数据库实例中都是全局的,即在同一个实例中的不同数据库中,看到的用户也都是相同的。

在初始化数据库实例时，会创建一个预定义的超级用户，这个用户的名称与初始化该数据库实例的操作系统用户名相同。比如: 如果数据库实例是建立在操作系统用户dba (通常使用 postgres 用户)下的，这个数据库超级用户的名称也会叫dba。可以用这个超级用户连接数据库，注意：dba默认会连接同名的数据库dba，而默认dba不存在，所以需要登录时指定连接数据库postgres进行登录,然后再创建其它的用户

创建用户和角色

在PostgreSQL中，用户与角色是没有区别的。

用户和角色可以用来实现以下功能：

• 用来登陆数据库实例
• 管理数据库对象

创建用户与角色的语法如下：

CREATE USER name [[WITH] option [ ...]]
CREATE ROLE name [[WITH] option [ ...]]
#上面两个命令都可以创建用户,不同的是CREATE USER创建的用户默认可以登录,而CREATE ROLE不可以登录
#除了CREATE USER 默认创建出来的用户有LOGIN 的权限，而CREATE ROLE 创建出来的用户没有“LOGIN"的权限之外，CREATE RULE 与 CREATE USER没有其他任何的区别。
#上面语法中的“option”可以是如下内容。
SUPERUSER | NOSUPERUSER:表示创建出来的用户是否为超级用户。只有超级用户才能创建超级用户。
CREATEDB | NOCREATEDB:指定创建出来的用户是否有执行”CREATE DATABASE“的权限。
CREATEROLE | NOCREATEROLE:指定创建出来的用户是否有创建其他角色的权限。
CREATEUSER | NOCREATEUSER:指定创建出来的用户是否有创建其他用户的权限。
INHERIT | NOINHERIT:如果创建的一个用户拥有某一个或某几个角色，这时若指定INHERIT，则表示用户自动拥有相应角色的权限，否则这个用户没有该角色的权限。
LOGIN | NOLOGIN:指定创建出来的用户是否有“LOGIN”的权限，可以临时地禁止一个用户的“LOGIN”权限，这时此用户就不能连接到数据库
CONNECTION LIMIT connlimit:指定该用户可以使用的并发连接数量。默认值是-1，表示没有限制。
[ENCRYPTED | UNENCRYPTED ] PASSWORD'password' : 用于控制存储在系统表里面的口令是否加密。
VALID UNTIL 'timestamp':密码失效时间，如果不指定这个子句，那么口令将永远有效。
INROLE role name [,...]:指定用户成为哪些角色的成员，请注意没有任何选项可以把新角色添加为管理员，必须使用独立的GRANT命令来做这件事情。
IN GROUP role_name [,...]:与IN ROLE相同，是已过时的语法。
ROLE role_name [,...]: role_name将成为这个新建的角色的成员。
ADMIN role_name [,...]: role_name将有这个新建角色的WITH ADMIN OPTION权限。
USER role_name[,.…]:与ROLE子句相同，但已过时。
SYSID uid:此子句主要是为了SQL向下兼容，实际没有什么用处。

用户管理案例

#查看帮助
\h create user
\h alter user
\h drop user
\h create role
\h alter role
\h drop role

#以下两个命令用法相似
create user  #创建的用户默认可以连接
create role  #创建的用户默认无法连接

#修改用户
alter user

#删除用户
drop user

#显出所有用户和角色
#\du和\dg命令等价。原因是在PostgreSQL数据库中用户和角色不分的。
\du
\dg

常用操作

#创建可以登录的用户和密码
CREATE USER wang WITH PASSWORD '123456';

#创建不可登录用户
create role zhao WITH PASSWORD '123456';

#创建可以连接的用户
CREATE ROLE li WITH LOGIN PASSWORD '123456' VALID UNTIL '2020-07-01'

#创建管理员
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456' ;

#创建复制用户
CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD '123456';

#修改密码
ALTER USER admin with password '654321';

#修改权限和密码
alter user wang with nologin password '123';
alter user zhao with login ;

#删除用户
DROP USER song;

#查看用户信息
\du
             List of roles
Role name |             Attributes             | Member of
-----------+------------------------------------------------------------+-----------
li    | Password valid until 2020-07-01 00:00:00+00        | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
wang   |                              | {} 
zhao

#查看指定用户信息
\du wang
     List of roles
Role name | Attributes | Member of
-----------+------------+-----------
wang   |      | {}
\du zhao
     List of roles
Role name | Attributes | Member of
-----------+--------------+-----------
zhao   | Cannot login | {}


#修改postgres用户密码
#使用postgres用户登录（PostgresSQL安装后会自动创建postgres用户）
[root@rocky8 ~]#su - postgres
#登录postgresql数据库
[root@rocky8 ~]# psql -U postgres
#安全起见,修改数据库管理员postgres用户的密码
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD '123456';
ALTER ROLE

权限管理

在PostgreSQL数据库中，每个数据库的对象（包括数据库)都有一个所有者,也就是说任何数据库对象都是属于某个用户的，所有者默认就拥有所有权限。所以不需要把对象的权限再赋给所有者。自己创建的数据库对象，自己当然有全部的权限。当然，所有者出于安全考虑也可以选择废弃一些自己的权限。在PostsgreSQL 数据库中，删除一个对象及任意修改它的权利是所有者固有的,不能被赋予或撤销。所有者也隐含地拥有把操作该对象的权限赋给别人的权利。

一个用户的权限分为两类，一类是在创建用户时就指定的权限，这些权限如下:

• 超级用户的权限
• 创建数据库的权限
• 是否允许LOGIN的权限

以上这些权限是创建用户时指定的，后续可使用ALTER ROLE命令来修改。

还有一类权限，是由命令GRANT和 REVOKE来管理的，这些权限如下:

• 在数据库中创建模式（schema）
• 允许在指定的数据库中创建临时表连接某个数据库
• 在模式中创建数据库对象，如创建表、视图函数等
• 在一些表中做SELECT、UPDATE、INSERT、DELETE等操作

#GRANT命令有两个作用
#1.让某个用户成为某个角色的成员，从而使其拥有角色的权限:
GRANT role_name [, ...] T0 role_name [, ...] [ WITH ADMIN OPTION ];

#2.把某些数据库逻辑结构对象的操作权限赋予某个用户(或角色)，命令的格式如下:
GRANT some privileqes ON database_object_type object_name TO role_name;

#其中，“some _privileges”表示在这个数据库对象中的权限,“database_object_type”是数据库对象的类型,如“TABLE”、“SEQUENCE”、“SCHEMA”，等。

PostgreSQL中的权限是按以下几个层次进行管理的：

• cluster权限：实例权限通过pg_hba.conf配置
• 管理赋在用户特殊属性上的权限：如超级用户的权限、创建数据库的权限、创建用户的权限、Login权限等。
• 在数据库中创建模式的权限
• 表空间权限：通过grant/revoke​控制权限操作表、物化视图、索引等
• 在模式中创建数据库对象的权限：如创建表、创建索引等等
• 查询表、往表中插入数据、更新表、删除表中数据的权限
• 操作表中某些字段的权限

常用操作

#授权创建新数据库
postgres=# alter user wang with CREATEDB;

#database权限设置
GRANT create ON DATABASE testdb TO wang;

#schema权限
ALTER SCHEMA wang OWNER to wang;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA wang TO wang;

#创建test的schema指定所有者为joe
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

#object权限
GRANT select,insert,update,delete ON testdb.t1 TO wang;

#创建数据库并指定所有者的用户
create user wang with password '123456';
CREATE DATABASE zabbix OWNER wang;

创建业务用户和授权

postgres=# create database pinxixi;
postgres=#\c pinxixi
pinxixi=#create user wanrentuan with password '123456';

#方法1
pinxixi=#create schema wanrentuan;
pinxixi=#ALTER SCHEMA wanrentuan OWNER to wanrentuan;

#方法2
pinxixi=#CREATE SCHEMA AUTHORIZATION wanrentuan;

#方法3
pinxixi=#GRANT select, insert,update,delete oN ALL TABLES IN SCHEMA wanrentuan to wanrentuan;

授权案例

#将创建一个名为“readonly”的用户
CREATE USER readonly with password '123456';

#把在public的schema下现有的所有表的SELECT 权限赋给用户readonly
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

‍