Postgresql角色创建.docx
《Postgresql角色创建.docx》由会员分享，可在线阅读，更多相关《Postgresql角色创建.docx（13页珍藏版）》请在冰豆网上搜索。
Postgresql角色创建
一、角色与用户的区别
角色就相当于岗位：
角色可以是经理，助理。
用户就是具体的人：
比如陈XX经理，朱XX助理，王XX助理。
在PostgreSQL里没有区分用户和角色的概念，"CREATEUSER"为"CREATEROLE"的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATEUSER"命令创建的用户默认带有LOGIN属性，而"CREATEROLE"命令创建的用户默认不带LOGIN属性（CREATEUSERisequivalenttoCREATEROLEexceptthatCREATEUSERassumesLOGINbydefault,whileCREATEROLEdoesnot）。
1.1创建角色与用户
CREATEROLE语法
CREATEROLEname[[WITH]option[...]]
whereoptioncanbe:
SUPERUSER|NOSUPERUSER
|CREATEDB|NOCREATEDB
|CREATEROLE|NOCREATEROLE
|CREATEUSER|NOCREATEUSER
|INHERIT|NOINHERIT
|LOGIN|NOLOGIN
|REPLICATION|NOREPLICATION
|CONNECTIONLIMITconnlimit
|[ENCRYPTED|UNENCRYPTED]PASSWORD'password'
|VALIDUNTIL'timestamp'
|INROLErole_name[,...]
|INGROUProle_name[,...]
|ROLErole_name[,...]
|ADMINrole_name[,...]
|USERrole_name[,...]
|SYSIDuid
创建david角色和sandy用户
postgres=#CREATEROLEdavid; //默认不带LOGIN属性
CREATEROLE
postgres=#CREATEUSERsandy; //默认具有LOGIN属性
CREATEROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
david|Cannotlogin|{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
sandy||{}
postgres=#
postgres=#SELECTrolnamefrompg_roles;
rolname
----------
postgres
david
sandy
（3rows）
postgres=#SELECTusenamefrompg_user;//角色david创建时没有分配login权限，所以没有创建用户
usename
----------
postgres
sandy
（2rows）
postgres=#
1.2验证LOGIN属性
postgres@CS-DEV:
~>psql-Udavid
psql:
FATAL:
role"david"isnotpermittedtologin
postgres@CS-DEV:
~>psql-Usandy
psql:
FATAL:
database"sandy"doesnotexist
postgres@CS-DEV:
~>psql-Usandy-dpostgres
psql（9.1.0）
Type"help"forhelp.
postgres=>\dt
Norelationsfound.
postgres=>
用户sandy可以登录，角色david不可以登录。
1.3修改david的权限，增加LOGIN权限
postgres=#ALTERROLEdavidLOGIN;
ALTERROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
sandy||{}
postgres=#SELECTrolnamefrompg_roles;
rolname
----------
postgres
sandy
david
（3rows）
postgres=#SELECTusenamefrompg_user; //给david角色分配login权限，系统将自动创建同名用户david
usename
----------
postgres
sandy
david
（3rows）
postgres=#
1.4再次验证LOGIN属性
postgres@CS-DEV:
~>psql-Udavid-dpostgres
psql（9.1.0）
Type"help"forhelp.
postgres=>\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
sandy||{}
postgres=>
david现在也可以登录了。
二、查看角色信息
psql终端可以用\du或\du+查看，也可以查看系统表select*frompg_roles;
postgres=>\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
david|Cannotlogin|{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
sandy||{}
postgres=>\du+
Listofroles
Rolename|Attributes|Memberof|Description
-----------+------------------------------------------------+-----------+-------------
david|Cannotlogin|{}|
postgres|Superuser,Createrole,CreateDB,Replication|{}|
sandy||{}|
postgres=>SELECT*frompg_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
（3rows）
postgres=>
三、角色属性（RoleAttributes）
一个数据库角色可以有一系列属性，这些属性定义了他的权限。
属性
说明
login
只有具有LOGIN属性的角色可以用做数据库连接的初始角色名。
superuser
数据库超级用户
createdb
创建数据库权限
createrole
允许其创建或删除其他普通的用户角色（超级用户除外）
replication
做流复制的时候用到的一个用户属性，一般单独设定。
password
在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit
用户组对组员的一个继承标志，成员可以继承用户组的权限特性
...
...
四、创建用户时赋予角色属性
从pg_roles表里查看到的信息，在上面创建的david用户时，默认没有创建数据库等权限。
postgres@CS-DEV:
~>psql-Udavid-dpostgres
psql（9.1.0）
Type"help"forhelp.
postgres=>\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
sandy||{}
postgres=>CREATEDATABASEtest;
ERROR:
permissiondeniedtocreatedatabase
postgres=>
如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。
首先切换到postgres用户。
4.1创建角色bella并赋予其CREATEDB的权限。
postgres=#CREATEROLEbellaCREATEDB;
CREATEROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB,Cannotlogin|{}
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
sandy||{}
postgres=#
4.2创建角色renee并赋予其创建数据库及带有密码登录的属性。
postgres=#CREATEROLEreneeCREATEDBPASSWORD'abc123'LOGIN;
CREATEROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB,Cannotlogin|{}
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
renee|CreateDB|{}
sandy||{}
postgres=#
4.3测试renee角色
a.登录
postgres@CS-DEV:
~>psql-Urenee-dpostgres
psql（9.1.0）
Type"help"forhelp.
postgres=>
用renee用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。
b.查找原因
在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。
查看pg_hba.conf文件，发现local的METHOD为trust，所以不需要输入密码。
将local的METHOD更改为password，然后保存重启postgresql。
c.再次验证
提示输入密码，输入正确密码后进入到数据库。
d.测试创建数据库
创建成功。
五、给已存在用户赋予各种权限
使用ALTERROLE命令。
ALTERROLE语法：
ALTERROLEname[[WITH]option[...]]
whereoptioncanbe:
SUPERUSER|NOSUPERUSER
|CREATEDB|NOCREATEDB
|CREATEROLE|NOCREATEROLE
|CREATEUSER|NOCREATEUSER
|INHERIT|NOINHERIT
|LOGIN|NOLOGIN
|REPLICATION|NOREPLICATION
|CONNECTIONLIMITconnlimit
|[ENCRYPTED|UNENCRYPTED]PASSWORD'password'
|VALIDUNTIL'timestamp'
ALTERROLEnameRENAMETOnew_name
ALTERROLEname[INDATABASEdatabase_name]SETconfiguration_parameter{TO|=}{value|DEFAULT}
ALTERROLEname[INDATABASEdatabase_name]SETconfiguration_parameterFROMCURRENT
ALTERROLEname[INDATABASEdatabase_name]RESETconfiguration_parameter
ALTERROLEname[INDATABASEdatabase_name]RESETALL
5.1赋予bella登录权限
a.查看现在的角色属性
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB,Cannotlogin|{}
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
renee|CreateDB|{}
sandy||{}
postgres=#
b.赋予登录权限
postgres=#ALTERROLEbellaWITHLOGIN;
ALTERROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB|{}
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
renee|CreateDB|{}
sandy||{}
postgres=#
5.2赋予renee创建角色的权限
postgres=#ALTERROLEreneeWITHCREATEROLE;
ALTERROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB|{}
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
renee|Createrole,CreateDB|{}
sandy||{}
postgres=#
5.3赋予david带密码登录权限
postgres=#ALTERROLEdavidWITHPASSWORD'ufo456';
ALTERROLE
postgres=#
5.4设置sandy角色的有效期
postgres=#ALTERROLEsandyVALIDUNTIL'2014-04-24';
ALTERROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB|{}
david||{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
renee|Createrole,CreateDB|{}
sandy||{}
postgres=#SELECT*frompg_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-2400:
00:
00+08||49439
（5rows）
postgres=#
六、角色赋权/角色成员
在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。
在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership权限赋给独立的角色即可。
6.1创建组角色
postgres=#CREATEROLEfatherloginnosuperusernocreatedbnocreaterolenoinheritencryptedpassword'abc123';
CREATEROLE
postgres=#\du
Listofroles
Rolename|Attributes|Memberof
-----------+------------------------------------------------+-----------
bella|CreateDB|{}
david||{}
father|Noinheritance|{}
postgres|Superuser,Createrole,CreateDB,Replication|{}
renee|Createrole,CreateDB|{}
sandy||{}
postgres=#
6.2给father角色赋予数据库test连接权限和相关表的查询权限。
postgres=#GRANTCONNECTONDATABASEtesttofather;
GRANT
postgres=#\ctestrenee
Youarenowconnectedtodatabase"test"asuser"renee".
test=>\dt
Norelations
升级会员 