PG笔记（3）用户（角色）权限管理

运维笔记本 2019-12-20

2949

用户（角色）权限管理

“
PostgreSQL 从最近的几个版本不再使用“用户”和“组”这两个术语，但是社区讨论的时候还是有人会使用这两个术语，请记住“用户”和“组”分别代表“可登陆角色”和“组角色”，为保持向前兼容 create user 和 create group 这两个命令也是支持的，但是不建议使用，建议使用 create role

查看所有数据库

test=> \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)

切换数据库

test=> \c postgres
You are now connected to database "postgres" as user "leo".
postgres=>

查看所有用户

DB=# select * from pg_user;
DB=# select * from pg_shadow;

查看当前用户

DB=# \c
DB=# select current_user;

切换用户

test=> \c - leo

切换用户到指定数据库

postgres=# \c test haoxy
You are now connected to database "test" as user "haoxy".

数据库名在前，用户名在后

创建可登录角色（用户）

创建具有登陆权限的角色

postgres=# create role leo login password 'king' createdb valid until 'infinity';
CREATE ROLE

创建具备超级用户权限的角色

postgres=# create role regina login password 'queen' superuser valid until '2020-1-1 00:00';
CREATE ROLE

查看

postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  |        valuntil        | useconfig
----------+----------+-------------+----------+---------+--------------+----------+------------------------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |                        |
 leo      |    16388 | t           | f        | f       | f            | ******** | infinity               |
 regina   |    16389 | f           | t        | f       | f            | ******** | 2020-01-01 00:00:00+00 |
(3 rows)

“
pg_user 只显示有登陆权限的角色

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 leo       | Create DB                                                 +| {}
           | Password valid until infinity                              |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 regina    | Superuser                                                 +| {}
           | Password valid until 2020-01-01 00:00:00+00                |

修改角色

#赋予postgres角色登陆权限，并修改密码为postgres
postgres=# alter role postgres LOGIN password 'postgres';
ALTER ROLE

“
\du 显示所有的角色和权限列表

创建组角色

“
一般不应该授予组角色登陆权限

postgres=# create role royalty inherit;

“
inherit 关键字表示角色 royalty 的任何一个成员角色都能继承它除了“超级用户权限”外的所有权限

postgres=# CREATE ROLE group_role;
postgres=# CREATE ROLE sales;

把角色授予角色

postgres=# GRANT group_role to sales;
postgres=# grant sales to leo;
postgres=# \du
                                     List of roles
 Role name  |                         Attributes                         |  Member of
------------+------------------------------------------------------------+--------------
 group_role | Cannot login                                               | {}
 leo        | Create DB                                                 +| {sales}
            | Password valid until infinity                              |
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 regina     | Superuser                                                 +| {}
            | Password valid until 2020-01-01 00:00:00+00                |
 royalty    | Cannot login                                               | {}
 sales      | Cannot login                                               | {group_role}

“
注意最后一个字段

角色回收

postgres=# REVOKE group_role FROM sales;
REVOKE ROLE
postgres=# revoke sales from leo;
REVOKE ROLE
postgres=# \du
                                    List of roles
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+-----------
 group_role | Cannot login                                               | {}
 leo        | Create DB                                                 +| {}
            | Password valid until infinity                              |
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 regina     | Superuser                                                 +| {}
            | Password valid until 2020-01-01 00:00:00+00                |
 royalty    | Cannot login                                               | {}
 sales      | Cannot login                                               | {}

从组角色继承权限

CREATE ROLE doe LOGIN INHERIT;
CREATE ROLE sales NOINHERIT;
CREATE ROLE marketing NOINHERIT;
GRANT sales to doe;
GRANT marketing to sales;

“
说明：由于 sales 和 marketing 加了关键字 NOINHERIT，除了 sales 自己的权限，其他角色的权限则不会被 doe 继承

SET ROLE sales;

“
该会话将只拥有那些赋予 admin 的权限，而不包括那些赋予 joe 的权限。然后：

SET ROLE marketing;

“
该会话讲只能使用赋予 marketing 的权限，而不包括赋予 joe 或者 admine 的权限。

原来的权限可以用下列之一恢复：

SET ROLE joe;
SET ROLE NONE;
RESET ROLE;

删除角色

drop role sales;

附录：

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 | 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

ALTER ROLE 语法

ALTER ROLE 语法：
ALTER 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 | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL

属性	说明
login	只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser	数据库超级用户
createdb	创建数据库权限
createrole	允许其创建或删除其他普通的用户角色(超级用户除外)
replication	做流复制的时候用到的一个用户属性，一般单独设定。
password	在登录时要求指定密码时才会起作用，比如 md5 或者 password 模式，跟客户端的连接认证方式有关
inherit	用户组对组员的一个继承标志，成员可以继承用户组的权限特性
...	...

postgresql

最后修改时间：2020-01-14 09:47:28

文章转载自运维笔记本，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。