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

发表于： 2022-09-21 22:40:55
分类：数据库文章分享
浏览：浏览(81)

编者按：

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

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论