PostgreSQL数据库中的角色(Role)与用户（User）的基本操作

SQL和数据库技术 2022-09-21

2414

编者按：

PostgreSQL基础。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关，仅供参考。

编辑|SQL和数据库技术(ID:SQLplusDB)

PostgreSQL中使用“角色”的概念管理数据库访问权限，用户表示“拥有LOGIN权限的角色”。

CREATE USER和CREATE ROLE命令都用于定义一个新的数据库角色，唯一的区别是 CREATE USER中LOGIN 被作为默认值，而NOLOGIN是 CREATE ROLE的默认值。

参考：

https://www.postgresql.org/docs/14/sql-createuser.html
https://www.postgresql.org/docs/14/sql-createrole.html
复制

一个角色是一个实体，它可以拥有数据库对象（例如，表和函数）并且拥有某些数据库特权；也可以把对象上的权限或者成员资格赋予给其他角色来控制谁能访问哪些对象（可以被继承）；并且数据库角色在一个数据库集簇（Cluster）安装范围内是全局的（实例级别，非某个数据库内）。

数据库初始创建后，会创建一个预定义特权角色（superuser）：postgres用于连接和访问等操作。

例：查看角色

postgres-# \set ECHO_HIDDEN on
postgres-# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************


                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


postgres-#
复制

例：查看用户

postgres-# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************


                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
复制

角色相关操作命令主要包括如下

CREATE ROLE
ALTER ROLE
DROP ROLE
GRANT 
REVOKE
CREATE USER
SET ROLE
复制

CREATE ROLE 创建角色

postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


postgres=# create role role1;
CREATE ROLE
postgres=# create role role2 with password 'pass';
CREATE ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     | Cannot login                                               | {}
复制

ALTER ROLE修改角色属性

postgres=# alter role role2 with login;
ALTER ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}


postgres=#
复制

修改登录属性后该角色可以登录。

ubuntu@pg-vm:~$ psql -U role2 -h localhost -d postgres
Password for user role2:
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.


postgres=>
复制

DROP ROLE删除属性

postgres=# create role role3;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 role3     | Cannot login                                               | {}


postgres=# drop role role3;
DROP ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}


postgres=#
复制

GRANT赋予权限

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}


postgres=# grant postgres to role2;
GRANT ROLE
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {postgres}


复制

REVOKE移除权限

postgres=# revoke postgres from role2;
REVOKE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}


复制

CREATE USER创建用户

postgres=# create user user1 with password 'pass';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 user1     |                                                            | {}


复制

SET ROLE设置角色

可以通过 SET ROLE设置当前会话的当前用户标识符，即控制使用的角色。

postgres=> \du
                                       List of roles
 Role name |                         Attributes                         |    Member of
-----------+------------------------------------------------------------+------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 user1     |                                                            | {postgres,role2}


postgres=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 user1        | user1
(1 row)


postgres=> set role role2;
SET
postgres=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 user1        | role2
(1 row)


postgres=> drop user role1;
ERROR:  permission denied to drop role
postgres=> set role postgres;
SET
postgres=# SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 user1        | postgres
(1 row)


postgres=# drop user role1;
DROP ROLE
postgres=# \du
                                       List of roles
 Role name |                         Attributes                         |    Member of
-----------+------------------------------------------------------------+------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role2     |                                                            | {}
 user1     |                                                            | {postgres,role2}


postgres=#
复制

大数据 postgresql

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

PostgreSQL数据库中的角色(Role)与用户（User）的基本操作

评论