PostgreSQL的用户、角色和权限管理

た入场券 2024-02-18 20:15 4阅读 0赞

Pg权限分为两部分，一部分是“系统权限”或者数据库用户的属性，可以授予role或user（两者区别在于login权限）；一部分为数据库对象上的操作权限。

超级用户不做权限检查，其它走acl。

对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走acl。在pg里，对acl模型做了简化，组和角色都是role，用户和角色的区别是角色没有login权限。

**pg\_roles:**

该视图提供访问数据库角色有关信息的接口。这个视图只是pg\_authid表的公开可读部分的视图化，同时把口令字段用空白填充。

<table style="border:1px solid #C0C0C0;color:rgb(0,0,0);font-family:Verdana, 'Lucida Grande', Arial, Helvetica, sans-serif;font-size:12px;background-color:rgb(255,255,255);"> 
 <tbody> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;text-align:center;">名字</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;text-align:center;">类型</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;text-align:center;">引用</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;text-align:center;">描述</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolname</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">name</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">角色名。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolsuper</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">bool</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">是否有超级用户权限的角色。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolcreaterole</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">bool</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">是否可以创建更多角色的角色。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolcreatedb</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">bool</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">是否可以创建数据库的角色。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolcatupdate</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">bool</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">是否可以直接更新系统表的角色。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolcanlogin</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">bool</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">如果为真，表示是可以登录的角色。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolpassword</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">text</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">不是口令(总是 ********)。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolvaliduntil</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">timestamptz</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">口令失效日期(只用于口令认证)；如果没有失效期，为NULL。</td> 
 </tr> 
 <tr> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">rolconfig</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">text[]</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">&nbsp;</td> 
 <td style="font-size:12px;border-color:#C0C0C0;border-collapse:collapse;padding:3px;">运行时配置变量的会话缺省。</td> 
 </tr> 
 </tbody> 
</table>

1.CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性，如下：

**postgres=\# \\du  
List of roles  
Role name | Attributes | Member of  
\-----------+------------------------------------------------------------+-----------  
 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | \{\}  
  
postgres=\# CREATE ROLE test\_user\_1; /\*默认不带LOGIN属性\*/  
CREATE ROLE  
postgres=\# CREATE user test\_user\_2; /\*默认具有LOGIN属性\*/  
CREATE ROLE  
postgres=\# \\du  
List of roles  
Role name | Attributes | Member of  
\-------------+------------------------------------------------------------+-----------  
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | \{\}  
test\_user\_1 | Cannot login | \{\}  
test\_user\_2 | | \{\}**

2.在创建用户时赋予角色属性

postgres=\# CREATE ROLE test\_user\_3 CREATEDB; /\*具有创建数据库的属性\*/  
CREATE ROLE  
postgres=\# \\du  
List of roles  
Role name | Attributes | Member of  
\-------------+------------------------------------------------------------+-----------  
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | \{\}  
test\_user\_1 | Cannot login | \{\}  
test\_user\_2 | | \{\}  
test\_user\_3 | Create DB, Cannot login  | \{\}  
  
postgres=\# CREATE ROLE test\_user\_4 CREATEDB PASSWORD '123456'; /\*具有创建数据库及带有密码登陆的属性 \*/  
  
CREATE ROLE  
postgres=\# \\du  
List of roles  
Role name | Attributes | Member of  
\-------------+------------------------------------------------------------+-----------  
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | \{\}  
test\_user\_1 | Cannot login | \{\}  
test\_user\_2 | | \{\}  
test\_user\_3 | Create DB, Cannot login | \{\}  
test\_user\_4 | Create DB, Cannot login | \{\}

3.给已存在用户赋予各种权限

使用ALTER ROLE即可。

**postgres=\# ALTER ROLE test\_user\_3 WITH LOGIN; /\*赋予登录权限\*/  
ALTER ROLE  
postgres=\# ALTER ROLE test\_user\_4 WITH CREATEROLE;/\*赋予创建角色的权限\*/  
  
ALTER ROLE  
postgres=\# \\du  
List of roles  
Role name | Attributes | Member of  
\-------------+------------------------------------------------------------+-----------  
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | \{\}  
test\_user\_1 | Cannot login | \{\}  
test\_user\_2 | | \{\}  
test\_user\_3 | Create DB | \{\}  
test\_user\_4 | Create role, Create DB, Cannot login | \{\}  
postgres=\# 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  
test\_user\_3 | f | t | f | t | t | f | -1 | \*\*\*\*\*\*\*\* | | f | | 16389  
postgres | t | t | t | t | t | t | -1 | \*\*\*\*\*\*\*\* | | t | | 10  
pg\_read\_all\_stats | f | t | f | f | f | f | -1 | \*\*\*\*\*\*\*\* | | f | | 3375  
pg\_monitor | f | t | f | f | f | f | -1 | \*\*\*\*\*\*\*\* | | f | | 3373  
test\_user\_2 | f | t | f | f | t | f | -1 | \*\*\*\*\*\*\*\* | | f | | 16388  
test\_user\_1 | f | t | f | f | f | f | -1 | \*\*\*\*\*\*\*\* | | f | | 16387  
test\_user\_4 | f | t | t | t | f | f | -1 | \*\*\*\*\*\*\*\* | | f | | 16390  
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  
(10 rows)**    
postgres=\# ALTER ROLE pg\_test\_user\_4 WITH PASSWORD '654321';/\*修改密码\*/  
ALTER ROLE   
postgres=\# ALTER ROLE pg\_test\_user\_4 VALID UNTIL 'JUL 7 14:00:00 2012 +8'; /\*设置角色的有效期\*  
ALTER ROLE

实验：

先创建一个角色，

再创建一个超级用户csm、

普通用户csm\_ca，

在csm用户下创建一个数据库testdb，在这个数据库里创建一个schema：csm\_ca，然后赋予普通用户csm\_ca操作数据库testdb里schema：csm\_ca里的表的权限。

#### 1.1.1 实验 ####

#### 1.1.1.1 创建角色与用户 ####

CREATE ROLE 语法

**1) 创建david 角色和sandy 用户**

CREATE ROLEdavid;　　//默认不带LOGIN属性

CREATE USERsandy;　　//默认具有LOGIN属性

验证LOGIN属性

连接数据库

psql –h IP -U david; //不能登录

psql –h IP -U sandy; //能登录

修改david 的权限，增加LOGIN权限  
ALTER ROLE david LOGIN ;

ALTER ROLE davidwith password 'david';

ALTER ROLE sandywith password 'sandy';

再次验证LOGIN属性  
psql -h 127.0.0.1 -U sandy -d postgres

**2) 查看角色信息**

psql 终端可以用\\du 或\\du+ 查看，也可以查看系统表 select \* from pg\_roles;

**3) 角色属性（Role Attributes）**

一个数据库角色可以有一系列属性，这些属性定义了他的权限。

<table> 
 <tbody> 
 <tr> 
 <td>属性</td> 
 <td>说明</td> 
 </tr> 
 <tr> 
 <td>login</td> 
 <td>只有具有&nbsp;LOGIN&nbsp;属性的角色可以用做数据库连接的初始角色名。</td> 
 </tr> 
 <tr> 
 <td>superuser</td> 
 <td>数据库超级用户</td> 
 </tr> 
 <tr> 
 <td>createdb</td> 
 <td>创建数据库权限</td> 
 </tr> 
 <tr> 
 <td>createrole &nbsp; &nbsp; &nbsp;</td> 
 <td>允许其创建或删除其他普通的用户角色(超级用户除外)</td> 
 </tr> 
 <tr> 
 <td>replication</td> 
 <td>做流复制的时候用到的一个用户属性，一般单独设定。</td> 
 </tr> 
 <tr> 
 <td>password</td> 
 <td>在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关</td> 
 </tr> 
 <tr> 
 <td>inherit</td> 
 <td>用户组对组员的一个继承标志，成员可以继承用户组的权限特性</td> 
 </tr> 
 <tr> 
 <td>...</td> 
 <td>...</td> 
 </tr> 
 </tbody> 
</table>

**4) 创建用户时赋予角色属性**

从pg\_roles表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。

Psql -h ip -U david -d postgres

CREATE DATABASE test;

ERROR: permission denied to create database

如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。  
首先切换到postgres 用户。  
创建角色bella 并赋予其CREATEDB 的权限。

CREATE ROLE bella CREATEDB ;

创建角色renee 并赋予其创建数据库及带有密码登录的属性。  
postgres=\# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;

a.测试renee 角色

psql -h 127.0.0.1 -U renee -d postgres

用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。

b. 查找原因  
在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。

查看pg\_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。

将local 的METHOD 更改为password，然后保存重启postgresql。

c. 再次验证

提示输入密码，输入正确密码后进入到数据库。

d. 测试创建数据库

创建成功。

**5) 给已存在用户赋予各种权限**

使用ALTER ROLE 命令。  
ALTER ROLE 语法：  
ALTER ROLE name \[ \[ WITH \] option \[ ... \] \]  
where option can be:

ALTER ROLE name RENAME TO new\_name

ALTER ROLE name \[ IN DATABASE database\_name \] SETconfiguration\_parameter \{ TO | = \} \{ value | DEFAULT \}  
ALTER ROLE name \[ IN DATABASE database\_name \] SET configuration\_parameter FROMCURRENT  
ALTER ROLE name \[ IN DATABASE database\_name \] RESET configuration\_parameter  
ALTER ROLE name \[ IN DATABASE database\_name \] RESET ALL  
赋予bella 登录权限  
a. 查看现在的角色属性

ALTER ROLE bella WITH LOGIN;

赋予renee 创建角色的权限  
postgres=\# ALTER ROLE renee WITH CREATEROLE;

赋予david 带密码登录权限  
postgres=\# ALTER ROLE david WITH PASSWORD 'ufo456';

设置sandy 角色的有效期  
postgres=\# ALTER ROLE sandy VALID UNTIL '2014-04-24';

postgres=\# SELECT \* from pg\_roles ;

**6) 角色赋权/角色成员**

在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。

创建组角色  
CREATE ROLE father login nosuperusernocreatedb nocreaterole noinherit encrypted password 'abc123';

给father 角色赋予数据库test 连接权限和相关表的查询权限。  
GRANT CONNECT ON DATABASE test to father;

\\c test renee

create table emp ( idserial,name text);

INSERT INTO emp (name) VALUES ('david');

INSERT INTO emp (name) VALUES ('sandy');

SELECT \* from emp;

test=> GRANT USAGE ON SCHEMA public to father;  
WARNING: no privileges were granted for "public"  
GRANT

test=> GRANT SELECT on public.emp to father;  
GRANT  
test=>  
创建成员角色  
test=> \\c postgres postgres  
You are now connected to database "postgres" as user"postgres".

postgres=\# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inheritencrypted password 'abc123';  
CREATE ROLE  
postgres=\#  
这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。  
将father 角色赋给son1  
postgres=\# GRANT father to son1;  
GRANT ROLE  
postgres=\#  
还有另一种方法，就是在创建用户的时候赋予角色权限。  
postgres=\# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inheritencrypted password 'abc123' in role father;  
CREATE ROLE  
postgres=\#  
测试son1 角色  
postgres=\# \\c test son1

用renee 角色新创建一张表，再次测试

test=> \\c test renee

CREATE TABLE dept (deptid integer,deptname text);

INSERT INTO dept (deptid, deptname) values(1, 'ts');

test=> \\c test son1  
You are now connected to database "test" as user "son1".  
test=> SELECT \* from dept ;  
ERROR: permission denied for relation dept  
test=>  
son1 角色只能查询emp 表的数据，而不能查询dept 表的数据，测试成功。  
查询角色组信息

test=> \\c postgres postgres  
You are now connected to database "postgres" as user "postgres".  
postgres=\#  
postgres=\# \\du

postgres=\#  
“ Member of ” 项表示son1 和son2 角色属于father 角色组。

#### 1.1.1.2 [赋予某账号只读权限][Link 1] ####

创建一个不带有权限的基本用户pg\_client

Create user client;

\\du

登录账号并赋予pg\_client查询表的权限，但是在list of rolesattributes 并未出现select信息

\\l

./psql -U ctsi\_lbs -d ctsi\_lbs\_db

grantselect on all tables in schema public to pg\_client;

登录pg\_client账号，验证权限是否成功

./psql-U client -d ctsi\_lbs\_db