五、给已存在用户赋予各种权限
使用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