Postgresql的用户管理二

五、给已存在用户赋予各种权限

使用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 ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL

 

 

 

5.1 赋予bella 登录权限

 

a. 查看现在的角色属性

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#

b. 赋予登录权限

 

postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#

 

5.2 赋予renee 创建角色的权限

 

postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=#

 

 

5.3 赋予david 带密码登录权限

 

postgres=# ALTER ROLE david WITH PASSWORD 'ufo456';
ALTER ROLE
postgres=#

 

 

5.4 设置sandy 角色的有效期

 

postgres=# ALTER ROLE sandy VALID UNTIL '2014-04-24';
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=# SELECT * from pg_roles ;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword |     rolvaliduntil      | rolconfig |  oid  
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+------------------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |                        |           |    10
 bella    | f        | t          | f             | t           | f            | t           | f              |           -1 | ********    |                        |           | 49440
 renee    | f        | t          | t             | t           | f            | t           | f              |           -1 | ********    |                        |           | 49442
 david    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |                        |           | 49438
 sandy    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    | 2014-04-24 00:00:00+08 |           | 49439
(5 rows)

postgres=#

 

六、角色赋权/角色成员

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

6.1 创建组角色

 

postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'abc123';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 father    | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=#

 

6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。

 

postgres=# GRANT CONNECT ON DATABASE test to father;
GRANT
postgres=# \c test renee
You are now connected to database "test" as user "renee".
test=> \dt
No relations found.
test=> CREATE TABLE emp (
test(> id serial,
test(> name text);
NOTICE:  CREATE TABLE will create implicit sequence "emp_id_seq" for serial column "emp.id"
CREATE TABLE
test=> INSERT INTO emp (name) VALUES ('david');  
INSERT 0 1
test=> INSERT INTO emp (name) VALUES ('sandy');
INSERT 0 1
test=> SELECT * from emp;
 id | name  
----+-------
| david
| sandy
(2 rows)

test=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | emp  | table | renee
(1 row)

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=>

 

6.3 创建成员角色

 

test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123';
CREATE ROLE
postgres=#

 

这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。

6.4 将father 角色赋给son1

 

postgres=# GRANT father to son1;
GRANT ROLE
postgres=# 

 

还有另一种方法，就是在创建用户的时候赋予角色权限。

 

postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123' in role father;
CREATE ROLE
postgres=# 

 

6.5 测试son1 角色

 

postgres=# \c test son1
You are now connected to database "test" as user "son1".
test=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | emp  | table | renee
(1 row)

test=> SELECT * from emp;
 id | name  
----+-------
| david
| sandy
(2 rows)

test=>

 

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

 

test=> \c test renee
You are now connected to database "test" as user "renee".
test=> CREATE TABLE dept (
test(> deptid integer,
test(> deptname text);
CREATE TABLE
test=> INSERT INTO dept (deptid, deptname) values(1, 'ts');
INSERT 0 1
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 表的数据，测试成功。

6.6 查询角色组信息

test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 father    | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}
 son1      |                                                | {father}
 son2      |                                                | {father}

postgres=#

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

七、参考

• PostgreSQL 官方资料：http://www.postgresql.org/docs/9.2/static/user-manag.html

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/pxpxstudy/p/3732136.html