Postgresql角色创建

1、一、角色与用户的区别角色就相当于岗位：角色可以是经理，助理。用户就是具体的人：比如陈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 CREAT

2、E ROLE does not)。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

3、| 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 ROLEpostgres=# CREATE USER sandy;/默认具有LOGIN属性CREATE ROLEpost

4、gres=# duList of rolesRole name | Attributes | Member of -+-+-david | Cannot login | postgres | Superuser, Create role, Create DB, Replication | sandy | | postgres=# postgres=# SELECT rolname from pg_roles ;rolname -postgresdavidsandy(3 rows)postgres=# SELECT usename from pg_user; /角色david 创建时没有分配lo

5、gin权限，所以没有创建用户usename -postgressandy(2 rows)postgres=# 1.2 验证LOGIN属性postgresCS-DEV: psql -U davidpsql: FATAL: role david is not permitted to log inpostgresCS-DEV: psql -U sandypsql: FATAL: database sandy does not existpostgresCS-DEV: psql -U sandy -d postgrespsql (9.1.0)Type help for help.postgres=

6、dtNo relations found.postgres= 用户sandy 可以登录，角色david 不可以登录。1.3 修改david 的权限，增加LOGIN权限postgres=# ALTER ROLE david LOGIN ;ALTER ROLEpostgres=# duList of rolesRole name | Attributes | Member of -+-+-david | | postgres | Superuser, Create role, Create DB, Replication | sandy | | postgres=# SELECT rolname

7、from pg_roles ;rolname -postgressandydavid(3 rows)postgres=# SELECT usename from pg_user;/给david 角色分配login权限，系统将自动创建同名用户davidusename -postgressandydavid(3 rows)postgres=# 1.4 再次验证LOGIN属性postgresCS-DEV: psql -U david -d postgrespsql (9.1.0)Type help for help.postgres= duList of rolesRole name | Attri

8、butes | Member of -+-+-david | | postgres | Superuser, Create role, Create DB, Replication | sandy | | postgres= david 现在也可以登录了。二、查看角色信息psql 终端可以用du 或du+ 查看，也可以查看系统表 select * from pg_roles;postgres= duList of rolesRole name | Attributes | Member of -+-+-david | Cannot login | postgres | Superuser, C

9、reate role, Create DB, Replication | sandy | | postgres= du+List of rolesRole name | Attributes | Member of | Description -+-+-+-david | Cannot login | | postgres | Superuser, Create role, Create DB, Replication | | sandy | | | postgres= SELECT * from pg_roles;rolname | rolsuper | rolinherit | rolcr

10、eaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid -+-+-+-+-+-+-+-+-+-+-+-+-postgres | t | t | t | t | t | t | t | -1 | * | | | 10david | f | t | f | f | f | f | f | -1 | * | | | 49438sandy | f | t | f | f | f | t | f |

11、 -1 | * | | | 49439(3 rows)postgres= 三、角色属性（Role Attributes）一个数据库角色可以有一系列属性，这些属性定义了他的权限。属性说明login只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。superuser数据库超级用户createdb创建数据库权限createrole 允许其创建或删除其他普通的用户角色(超级用户除外)replication做流复制的时候用到的一个用户属性，一般单独设定。password在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关inherit用户组对组员

12、的一个继承标志，成员可以继承用户组的权限特性.四、创建用户时赋予角色属性从pg_roles 表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。 postgresCS-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 | sa

13、ndy | | 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

14、 | 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 -+-+- bel

15、la | Create DB, Cannot login | david | | postgres | Superuser, Create role, Create DB, Replication | renee | Create DB | sandy | | postgres=# 4.3 测试renee 角色 a. 登录 postgresCS-DEV: psql -U renee -d postgres psql (9.1.0) Type help for help. postgres= 用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。 b. 查找原因 在角色属性中

16、关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。将local 的METHOD 更改为password，然后保存重启postgresql。c. 再次验证提示输入密码，输入正确密码后进入到数据库。d. 测试创建数据库创建成功。五、给已存在用户赋予各种权限使用ALTER ROLE 命令。ALTER ROLE 语法：ALTER ROLE name WITH option . where option can be:SUPERU

17、SER | NOSUPERUSER| CREATEDB | NOCREATEDB| CREATEROLE | NOCREATEROLE| CREATEUSER | NOCREATEUSER| INHERIT | NOINHERIT| LOGIN | NOLOGIN| REPLICATION | NOREPLICATION| CONNECTION LIMIT connlimit| ENCRYPTED | UNENCRYPTED PASSWORD password| VALID UNTIL timestampALTER ROLE name RENAME TO new_nameALTER ROLE

18、name IN DATABASE database_name SET configuration_parameter TO | = value | DEFAULT ALTER ROLE name IN DATABASE database_name SET configuration_parameter FROM CURRENTALTER ROLE name IN DATABASE database_name RESET configuration_parameterALTER ROLE name IN DATABASE database_name RESET ALL5.1 赋予bella 登录

19、权限a. 查看现在的角色属性postgres=# duList of rolesRole 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 ROLEpostgres=# duList

20、 of rolesRole 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 ROLEpostgres=# duList of rolesRole name | Attribute

21、s | 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 ROLEpostgres=#5.4 设置sandy 角色的有效期postgres=# ALTER ROLE sandy VAL

22、ID UNTIL 2014-04-24;ALTER ROLEpostgres=# duList of rolesRole 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 | ro

23、lcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid -+-+-+-+-+-+-+-+-+-+-+-+-postgres | t | t | t | t | t | t | t | -1 | * | | | 10bella | f | t | f | t | f | t | f | -1 | * | | | 49440renee | f | t | t | t | f | t |

24、f | -1 | * | | | 49442david | f | t | f | f | f | t | f | -1 | * | | | 49438sandy | f | t | f | f | f | t | f | -1 | * | 2014-04-24 00:00:00+08 | | 49439(5 rows)postgres=# 六、角色赋权/角色成员在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。6.1

25、 创建组角色postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password abc123;CREATE ROLEpostgres=# duList of rolesRole name | Attributes | Member of -+-+-bella | Create DB | david | | father | No inheritance | postgres | Superuser, Create role, Create DB, Replica

26、tion | renee | Create role, Create DB | sandy | | postgres=#6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。postgres=# GRANT CONNECT ON DATABASE test to father;GRANTpostgres=# c test reneeYou are now connected to database test as user renee.test= dtNo relations found.test= CREATE TABLE emp (test( id serial,te

27、st( name text);NOTICE: CREATE TABLE will create implicit sequence emp_id_seq for serial column emp.idCREATE TABLEtest= INSERT INTO emp (name) VALUES (david); INSERT 0 1test= INSERT INTO emp (name) VALUES (sandy);INSERT 0 1test= SELECT * from emp;id | name -+-1 | david2 | sandy(2 rows)test= dtList of

28、 relationsSchema | Name | Type | Owner -+-+-+-public | emp | table | renee(1 row)test= GRANT USAGE ON SCHEMA public to father;WARNING: no privileges were granted for publicGRANTtest= GRANT SELECT on public.emp to father;GRANTtest= 6.3 创建成员角色test= c postgres postgresYou are now connected to database

29、postgres as user postgres.postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password abc123;CREATE ROLEpostgres=# 这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。6.4 将father 角色赋给son1postgres=# GRANT father to son1;GRANT ROLEpostgres=# 还有另一种方法，就是在创建用户的时候赋予角色权限。postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password abc123 in role father;CREATE ROLEpostgres=# 6.5 测试son1 角色postgres=# c test son1You are now connected to database test as user son1.test=