[b]一、角色与用户的区别 [/b]角色就相当于岗位：角色可以是经理，助理。 用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。 在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性，而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)。 [b]1.1 创建角色与用户[/b] 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 创建david 角色和sandy 用户 postgres=# CREATE ROLE david;　　//默认不带LOGIN属性 CREATE ROLE postgres=# CREATE USER sandy;　　//默认具有LOGIN属性 CREATE ROLE postgres=# du                              List of roles  Role name |                   Attributes                   | Member of -----------+------------------------------------------------+-----------  david     | Cannot login                                   | {}  postgres  | Superuser, Create role, Create DB, Replication | {}  sandy     |                                                | {} postgres=# postgres=# SELECT rolname from pg_roles ;  rolname  ----------  postgres  david  sandy (3 rows) postgres=# SELECT usename from pg_user;         //角色david 创建时没有分配login权限，所以没有创建用户  usename  ----------  postgres  sandy (2 rows) postgres=# 1.2 验证LOGIN属性 postgres@CS-DEV:~> psql -U david psql: FATAL:  role "david" is not permitted to log in postgres@CS-DEV:~> psql -U sandy psql: FATAL:  database "sandy" does not exist postgres@CS-DEV:~> psql -U sandy -d postgres psql (9.1.0) Type "help" for help. postgres=> dt No relations found. postgres=> 用户sandy 可以登录，角色david 不可以登录。 1.3 修改david 的权限，增加LOGIN权限 postgres=# ALTER ROLE david LOGIN ; ALTER ROLE postgres=# du                              List of roles  Role name |                   Attributes                   | Member of -----------+------------------------------------------------+-----------  david     |                                                | {}  postgres  | Superuser, Create role, Create DB, Replication | {}  sandy     |                                                | {} postgres=# SELECT rolname from pg_roles ;  rolname  ----------  postgres  sandy  david (3 rows) postgres=# SELECT usename from pg_user;　　//给david 角色分配login权限，系统将自动创建同名用户david  usename  ----------  postgres  sandy  david (3 rows) postgres=# 1.4 再次验证LOGIN属性 postgres@CS-DEV:~> psql -U david -d postgres psql (9.1.0) Type "help" for help. postgres=> du                              List of roles  Role name |                   Attributes                   | Member of -----------+------------------------------------------------+-----------  david     |                                                | {}  postgres  | Superuser, Create role, Create DB, Replication | {}  sandy     |                                                | {} postgres=> david 现在也可以登录了。 [b]二、查看角色信息[/b] psql 终端可以用du 或du+ 查看，也可以查看系统表 select * from pg_roles; postgres=> du                              List of roles  Role name |                   Attributes                   | Member of -----------+------------------------------------------------+-----------  david     | Cannot login                                   | {}  postgres  | Superuser, Create role, Create DB, Replication | {}  sandy     |                                                | {} postgres=> du+                                     List of roles  Role name |                   Attributes                   | Member of | Description -----------+------------------------------------------------+-----------+-------------  david     | Cannot login                                   | {}        |  postgres  | Superuser, Create role, Create DB, Replication | {}        |  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  david    | f        | t          | f             | f           | f            | f           | f              |           -1 | ********    |               |           | 49438  sandy    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |               |           | 49439 (3 rows) postgres=> [b]三、角色属性（Role Attributes）[/b] 一个数据库角色可以有一系列属性，这些属性定义了他的权限。

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

  [b]四、创建用户时赋予角色属性 [/b] 从pg_roles 表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。 postgres@CS-DEV:~> psql -U david -d postgres psql (9.1.0) Type "help" for help. postgres=> du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- david | | {} postgres | Superuser, Create role, Create DB, Replication | {} sandy | | {} postgres=> CREATE DATABASE test; ERROR: permission denied to create database postgres=> 如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。 首先切换到postgres 用户。 4.1 创建角色bella 并赋予其CREATEDB 的权限。 postgres=# CREATE ROLE bella CREATEDB ; CREATE ROLE postgres=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- bella | Create DB, Cannot login | {} david | | {} postgres | Superuser, Create role, Create DB, Replication | {} sandy | | {} postgres=# 4.2 创建角色renee 并赋予其创建数据库及带有密码登录的属性。 postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN; CREATE ROLE 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=# 4.3 测试renee 角色 a. 登录 postgres@CS-DEV:~> psql -U renee -d postgres psql (9.1.0) Type "help" for help. postgres=> 用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。 b. 查找原因 在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。 查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。 [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100116_0_75000.jpg[/img] 将local 的METHOD 更改为password，然后保存重启postgresql。 c. 再次验证 [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100116_1_46594.jpg[/img] 提示输入密码，输入正确密码后进入到数据库。 d. 测试创建数据库 [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100117_2_34864.jpg[/img] 创建成功。 [b]五、给已存在用户赋予各种权限[/b] 使用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=# [b]六、角色赋权/角色成员[/b] 在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在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  ----+-------   1 | david   2 | 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  ----+-------   1 | david   2 | 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 角色组。