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

时间:2021-03-01 19:00:26

Pg权限分为两部分,一部分是系统权限”或者数据库用户的属性,可以授予role或user(两者区别在于login权限);一部分为数据库对象上的操作权限

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

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

pg_roles:

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


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

名字 类型 引用 描述
rolname name   角色名。
rolsuper bool   是否有超级用户权限的角色。
rolcreaterole bool   是否可以创建更多角色的角色。
rolcreatedb bool   是否可以创建数据库的角色。
rolcatupdate bool   是否可以直接更新系统表的角色。
rolcanlogin bool   如果为真,表示是可以登录的角色。
rolpassword text   不是口令(总是 ********)。
rolvaliduntil timestamptz   口令失效日期(只用于口令认证);如果没有失效期,为NULL。
rolconfig text[]   运行时配置变量的会话缺省。

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 语法

CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

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)

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

属性

说明

login

只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。

superuser

数据库超级用户

createdb

创建数据库权限

createrole      

允许其创建或删除其他普通的用户角色(超级用户除外)

replication

做流复制的时候用到的一个用户属性,一般单独设定。

password

在登录时要求指定密码时才会起作用,比如md5或者password模式,跟客户端的连接认证方式有关

inherit

用户组对组员的一个继承标志,成员可以继承用户组的权限特性

...

...

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:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

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 赋予某账号只读权限

创建一个不带有权限的基本用户  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 

\dt