转载:PostgreSQL用户角色及其属性介绍

前端之家收集整理的这篇文章主要介绍了转载:PostgreSQL用户角色及其属性介绍前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

转载内容来自:http://www.jb51.cc/article/p-dgneyyud-pb.html


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 修改一个数据库角色的属性
原文链接:https://www.f2er.com/postgresql/196301.html

猜你在找的Postgre SQL相关文章