PostgreSQL用户角色及其属性介绍
1.CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性，如下：postgres=# CREATE ROLE pg_test_user_1; /*默认不带LOGIN属性*/CREATE ROLEpostgres=# CREATE USER pg_test_user_2; /*默认具有LOGIN属性*/CREATE ROLEpostgres=# \duList of rolesRole name |Attributes| Member of----------------+--------------+-----------pg_test_user_1 | Cannot login | {}pg_test_user_2 | | {}postgres | Superuser | {}: Create role: Create DBpostgres=# 2.在创建用户时赋予角色属性
postgres=# CREATEROLE pg_test_user_3 CREATEDB; /*具有创建数据库的属性*/CREATE ROLEpostgres=# \duList of rolesRole name |Attributes| Member of----------------+--------------+-----------pg_test_user_1 | Cannot login | {}pg_test_user_2 | | {}pg_test_user_3 | Create DB | {}: Cannot loginpostgres | Superuser | {}: Create role: Create DBpostgres=# CREATE ROLE pg_test_user_4 CREATEDB PASSWORD '123456'; /*具有创建数据库及带有密码登陆的属性 */CREATE ROLEpostgres=# \duList of rolesRole name |Attributes| Member of----------------+--------------+-----------pg_test_user_1 | Cannot login | {}pg_test_user_2 | | {}pg_test_user_3 | Create DB | {}: Cannot loginpg_test_user_4 | Create DB | {}: Cannot loginpostgres | Superuser | {}: Create role: Create DBpostgres=# 3.给已存在用户赋予各种权限
使用ALTER ROLE即可。
postgres=# \duList of rolesRole name |Attributes| Member of----------------+--------------+-----------pg_test_user_3 | Create DB | {}: Cannot loginpg_test_user_4 | Create DB | {}: Cannot loginpostgres | Superuser | {}: Create role: Create DBpostgres=# ALTER ROLE pg_test_user_3 WITH LOGIN; /*赋予登录权限*/ALTER ROLEpostgres=# \duList of rolesRole name |Attributes| Member of----------------+--------------+-----------pg_test_user_3 | Create DB | {}pg_test_user_4 | Create DB | {}: Cannot loginpostgres | Superuser | {}: Create role: Create DBpostgres=# ALTER ROLE pg_test_user_4 WITH CREATEROLE;/*赋予创建角色的权限*/ALTER ROLEpostgres=# \duList of rolesRole name |Attributes| Member of----------------+--------------+-----------pg_test_user_3 | Create DB | {}pg_test_user_4 | Create role| {}: Create DB: Cannot loginpostgres | Superuser | {}: Create role: Create DBpostgres=# ALTER ROLE pg_test_user_4 WITH PASSWORD '654321';/*修改密码*/ALTER ROLEpostgres=# ALTER ROLE pg_test_user_4 VALID UNTIL 'JUL 7 14:00:00 2012 +8'; /*设置角色的有效期*ALTER ROLE 4.查看角色表中的信息:
postgres=# SELECT * FROM pg_roles;rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |oid----------------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+------------------------+-----------+-------postgres | t | t | t | t | t | t | -1 | ******** | || 10pg_test_user_3 | f | t | f | t | f | t | -1 | ******** | || 16390pg_test_user_4 | f | t | t | t | f | f | -1 | ******** | 2012-07-07 14:00:00+08 || 16391(3 rows)postgres=#
5.ALTER ROLE语句简介：
ALTER ROLE名称ALTER ROLE -- 修改一个数据库角色语法ALTER ROLE name [ [ WITH ] option [ ... ] ]这里的 option 可以是：SUPERUSER | NOSUPERUSER| CREATEDB | NOCREATEDB| CREATEROLE | NOCREATEROLE| CREATEUSER | NOCREATEUSER| INHERIT | NOINHERIT| LOGIN | NOLOGIN| CONNECTION LIMIT connlimit| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'| VALID UNTIL 'timestamp' ALTER ROLE name RENAME TO newnameALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }ALTER ROLE name RESET configuration_parameter描述ALTER ROLE 修改一个数据库角色的属性。
页:
[1]