PostgreSQL用户角色及其属性介绍
1.CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性，如下：
- postgres=# CREATE ROLE pg_test_user_1; /*默认不带LOGIN属性*/
- CREATE ROLE
- postgres=# CREATE USER pg_test_user_2; /*默认具有LOGIN属性*/
- CREATE ROLE
- postgres=# \du
- List of roles
- Role name | Attributes | Member of
- ----------------+--------------+-----------
- pg_test_user_1 | Cannot login | {}
- pg_test_user_2 | | {}
- postgres | Superuser | {}
- : Create role
- : Create DB
- postgres=#
2.在创建用户时赋予角色属性
- postgres=# CREATE ROLE pg_test_user_3 CREATEDB; /*具有创建数据库的属性*/
- CREATE ROLE
- postgres=# \du
- List of roles
- Role name | Attributes | Member of
- ----------------+--------------+-----------
- pg_test_user_1 | Cannot login | {}
- pg_test_user_2 | | {}
- pg_test_user_3 | Create DB | {}
- : Cannot login
- postgres | Superuser | {}
- : Create role
- : Create DB
- postgres=# CREATE ROLE pg_test_user_4 CREATEDB PASSWORD '123456'; /*具有创建数据库及带有密码登陆的属性 */
- CREATE ROLE
- postgres=# \du
- List of roles
- Role name | Attributes | Member of
- ----------------+--------------+-----------
- pg_test_user_1 | Cannot login | {}
- pg_test_user_2 | | {}
- pg_test_user_3 | Create DB | {}
- : Cannot login
- pg_test_user_4 | Create DB | {}
- : Cannot login
- postgres | Superuser | {}
- : Create role
- : Create DB
- postgres=#
3.给已存在用户赋予各种权限
使用ALTER ROLE即可。
- postgres=# \du
- List of roles
- Role name | Attributes | Member of
- ----------------+--------------+-----------
- pg_test_user_3 | Create DB | {}
- : Cannot login
- pg_test_user_4 | Create DB | {}
- : Cannot login
- postgres | Superuser | {}
- : Create role
- : Create DB
- postgres=# ALTER ROLE pg_test_user_3 WITH LOGIN; /*赋予登录权限*/
- ALTER ROLE
- postgres=# \du
- List of roles
- Role name | Attributes | Member of
- ----------------+--------------+-----------
- pg_test_user_3 | Create DB | {}
- pg_test_user_4 | Create DB | {}
- : Cannot login
- postgres | Superuser | {}
- : Create role
- : Create DB
- postgres=# ALTER ROLE pg_test_user_4 WITH CREATEROLE;/*赋予创建角色的权限*/
- ALTER ROLE
- postgres=# \du
- List of roles
- Role name | Attributes | Member of
- ----------------+--------------+-----------
- pg_test_user_3 | Create DB | {}
- pg_test_user_4 | Create role | {}
- : Create DB
- : Cannot login
- postgres | Superuser | {}
- : Create role
- : Create DB
- postgres=# ALTER ROLE pg_test_user_4 WITH PASSWORD '654321';/*修改密码*/
- ALTER ROLE
- postgres=# 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 | rol
- config | oid
- ----------------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+------------------------+----
- -------+-------
- postgres | t | t | t | t | t | t | -1 | ******** | |
- | 10
- pg_test_user_3 | f | t | f | t | f | t | -1 | ******** | |
- | 16390
- pg_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 newname
- ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }
- ALTER ROLE name RESET configuration_parameter描述
- ALTER ROLE 修改一个数据库角色的属性。
相关推荐
IT之家 2020-03-11
graseed 2020-10-28
zbkyumlei 2020-10-12
SXIAOYI 2020-09-16
jinhao 2020-09-07
impress 2020-08-26
liuqipao 2020-07-07
淡风wisdon大大 2020-06-06
yoohsummer 2020-06-01
chenjia00 2020-05-29
baike 2020-05-19
扭来不叫牛奶 2020-05-08
hxmilyy 2020-05-11
黎豆子 2020-05-07
xiongweiwei00 2020-04-29
Cypress 2020-04-25
冰蝶 2020-04-20