PostgreSQL的用户、角色和权限管理

最新推荐文章于 2025-08-17 16:38:29 发布
原创 最新推荐文章于 2025-08-17 16:38:29 发布 · 9.2w 阅读 · 32 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#postgresql #postgreSQL #PostgreSQL #Postgresql #role #user #权限 #角色

本文介绍PostgreSQL中的权限管理系统，包括如何创建和管理角色、数据库对象的权限分配方式以及超级用户的特殊权限。通过实际操作演示了如何创建不同类型的用户并进行权限设置。

Pg权限分为两部分，一部分是“系统权限”或者数据库用户的属性，可以授予role或user（两者区别在于login权限）；一部分为数据库对象上的操作权限。对超级用户不做权限检查，其它走acl。对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走acl。在pg里，对acl模型做了简化，组和角色都是role，用户和角色的区别是角色没有login权限。

 

可以用下面的命令创建和删除角色，

CREATE ROLE name;

DROP ROLE name;

为了方便，也可以在 shell 命令上直接调用程序 createuser 和 dropuser，这些工具对相应命令提供了封装:

createuser name

dropuser name

 

 

数据库对象上的权限有：SELECT，INSERT， UPDATE，DELETE，RULE， REFERENCES，TRIGGER，CREATE， TEMPORARY，EXECUTE，和 USAGE等，具体见下面定义

 

typedefuint32AclMode;         /* a bitmask of privilege bits */

 

#define ACL_INSERT      (1<<0)  /* forrelations */

#defineACL_SELECT      (1<<1)

#defineACL_UPDATE      (1<<2)

#defineACL_DELETE      (1<<3)

#defineACL_TRUNCATE    (1<<4)

#defineACL_REFERENCES  (1<<5)

#defineACL_TRIGGER     (1<<6)

#defineACL_EXECUTE     (1<<7)  /* for functions */

#defineACL_USAGE       (1<<8)  /* for languages, namespaces, FDWs, and

                                 * servers */

#defineACL_CREATE      (1<<9)  /* for namespaces and databases */

#defineACL_CREATE_TEMP (1<<10) /* for databases */

#defineACL_CONNECT     (1<<11) /* for databases */

#defineN_ACL_RIGHTS    12      /* 1plus the last 1<<x */

#defineACL_NO_RIGHTS   0

/*Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */

#defineACL_SELECT_FOR_UPDATE   ACL_UPDATE

 

我们可以用特殊的名字 PUBLIC 把对象的权限赋予系统中的所有角色。 在权限声明的位置上写 ALL，表示把适用于该对象的所有权限都赋予目标角色。

beigang=#  grantall on schema csm_ca to public;

GRANT

beigang=# revoke all on schema csm_ca frompublic;

REVOKE

beigang=#

 

每种对象的all权限定义如下：

/*

 * Bitmasks defining "allrights" for each supported object type

 */

#defineACL_ALL_RIGHTS_COLUMN       (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)

#defineACL_ALL_RIGHTS_RELATION     (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER)

#defineACL_ALL_RIGHTS_SEQUENCE     (ACL_USAGE|ACL_SELECT|ACL_UPDATE)

#defineACL_ALL_RIGHTS_DATABASE     (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)

#define ACL_ALL_RIGHTS_FDW          (ACL_USAGE)

#defineACL_ALL_RIGHTS_FOREIGN_SERVER (ACL_USAGE)

#defineACL_ALL_RIGHTS_FUNCTION     (ACL_EXECUTE)

#defineACL_ALL_RIGHTS_LANGUAGE     (ACL_USAGE)

#defineACL_ALL_RIGHTS_LARGEOBJECT  (ACL_SELECT|ACL_UPDATE)

#defineACL_ALL_RIGHTS_NAMESPACE    (ACL_USAGE|ACL_CREATE)

#defineACL_ALL_RIGHTS_TABLESPACE   (ACL_CREATE)

 

 

用户的属性可参见下图：

视图 pg_roles提供访问数据库角色有关信息的接口。 它只是一个 pg_authid 表的公开可读部分的视图，把口令字段用空白填充了。

Table 42-39.pg_roles字段

名字

类型

引用

描述

rolname

name

 

角色名

rolsuper

bool

 

有超级用户权限的角色

rolcreaterole

bool

 

可以创建更多角色的角色

rolcreatedb

bool

 

可以创建数据库的角色

rolcatupdate

bool

 

可以直接更新系统表的角色。（除非这个字段为真，否则超级用户也不能干这个事情。）

rolcanlogin

bool

 

可以登录的角色，也就是说，这个角色可以给予初始化会话认证的标识符。

rolpassword

text

 

不是口令（总是 ********）

rolvaliduntil

timestamptz

 

口令失效日期（只用于口令认证）；如果没有失效期，为 NULL

rolconfig

text[]

 

运行时配置变量的会话缺省

 

 

 

下面实验验证

先创建一个角色xxx，再创建一个超级用户csm、普通用户csm_ca，csm用户创建一个数据库testdb，在这个数据库里创建一个schema：csm_ca，然后赋予普通用户csm_ca操作数据库testdb里schema：csm_ca里的表的权限。

1

Create role：

 

testdb=# create role xxx with superuser;

CREATE ROLE

 

2

Create user：

 

testdb=# create user csm with superuserpassword 'csm';

CREATE ROLE

testdb=# create user csm_ca with password 'csm_ca';

CREATE ROLE

testdb=#

 

3

验证

 

 

testdb=# \du

角色列表

-[ RECORD 1]--------------------------------------

角色名称 | csm

属性     | 超级用户

成员属于 | {}

-[ RECORD 2]--------------------------------------

角色名称 | csm_ca

属性     |

成员属于 | {}

-[ RECORD 3 ]--------------------------------------

角色名称 | postgres

属性     | 超级用户, 建立角色, 建立 DB, Replication

成员属于 | {}

-[ RECORD 4]--------------------------------------

角色名称 | xxx

属性     | 超级用户, 无法登录

成员属于 | {}

 

 

testdb=# SELECT * FROM pg_roles;

-[ RECORD 1 ]---------+---------

rolname               | postgres

rolsuper              | t

rolinherit            | t

rolcreaterole         | t

rolcreatedb           | t

rolcreatedblink       | t

rolcreatepublicdblink | t

roldroppublicdblink   | t

rolcatupdate          | t

rolcanlogin           | t

rolreplication        | t

rolconnlimit          | -1

rolpassword           | ********

rolvaliduntil         |

rolconfig             |

oid                   | 10

-[ RECORD 2 ]---------+---------

rolname               | csm

rolsuper              | t

rolinherit            | t

rolcreaterole         | f

rolcreatedb           | f

rolcreatedblink       | f

rolcreatepublicdblink | f

roldroppublicdblink   | f

rolcatupdate          | t

rolcanlogin           | t

rolreplication        | f

rolconnlimit          | -1

rolpassword           | ********

rolvaliduntil         |

rolconfig             |

oid                   | 24598

-[ RECORD 3 ]---------+---------

rolname               | csm_ca

rolsuper              | f

rolinherit            | t

rolcreaterole         | f

rolcreatedb           | f

rolcreatedblink       | f

rolcreatepublicdblink | f

roldroppublicdblink   | f

rolcatupdate          | f

rolcanlogin           | t

rolreplication        | f

rolconnlimit          | -1

rolpassword           | ********

rolvaliduntil         |

rolconfig             |

oid                   | 24599

-[ RECORD 4 ]---------+---------

rolname               | xxx

rolsuper              | t

rolinherit            | t

rolcreaterole         | f

rolcreatedb           | f

rolcreatedblink       | f

rolcreatepublicdblink | f

roldroppublicdblink   | f

rolcatupdate          | t

rolcanlogin           | f

rolreplication        | f

rolconnlimit          | -1

rolpassword           | ********

rolvaliduntil         |

rolconfig             |

oid                   | 24600




 

postgres=# \c beigang

You are now connected to database "beigang" as user "csm".

5

Csm用户在beigang里创建schema: csm_ca

beigang=#

beigang=#

beigang=# create schema csm_ca;

CREATE SCHEMA

beigang=#


 

6

验证模式csm_ca和用户csm_ca

beigang=# \dn

   架构模式列表

  名称  |  拥有者

--------+----------

 csm_ca | csm

 dbo    | postgres

 public | postgres

 sys    | postgres

(4 行记录)

 

 

beigang=# \du

                            角色列表

 角色名称 |                   属性                   | 成员属于

----------+------------------------------------------+----------

 csm      | 超级用户                                 | {}

 csm_ca   |                                          | {}

 postgres | 超级用户, 建立角色, 建立 DB, Replication | {}

 xxx      | 超级用户, 无法登录                       | {}

 

 

beigang=#

 

7

超级用户csm给普通用户csm_ca授予操作schema csm_ca的权限

 

beigang=#  grant all on schema csm_ca to csm_ca;

GRANT

beigang=# grant all on all tables in schema csm_ca to csm_ca;

GRANT

beigang=#

 

8

pg中组就是role，操作见以下

beigang=# grant xxx to csm_ca;

GRANT ROLE

beigang=# revoke xxx from csm_ca;

REVOKE ROLE

beigang=#

 

参考：

Pg documentation

src/include/nodes/parsenodes.h

src/include/utils/acl.h

 

-----------------

转载请著明出处，来自以下博客或mail至beigaang@gmail.com联系：
blog.csdn.net/beiigang
beigang.iteye.com
postgresql用户与权限管理
weixin_30895723的博客
08-20 6080
pg使用角色的概念管理数据库访问权限，角色是一系列相关权限的集合。为了管理方便，通常把一系列先关的权限赋予给一个角色，如果哪个用户需要这些权限，就把这些角色赋予给响应的用户。 由于用户也拥有一系列的相关权限，为了简化管理，在PG中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋值给另一个用户。 用户和角色在整个数据库实例中是全局的，在同一个实例的不同数据库中，看到的用户...
【PostgreSQL】实战篇——用户管理、角色和权限控制的高级用法及技巧
thinking_chou的专栏
10-04 2226
遵循最小权限原则：确保用户仅获得其工作所需的最低权限。使用角色管理权限：通过角色管理权限而不是直接将权限授予用户，简化权限管理。定期审计：定期检查用户和角色的权限，确保没有过期或不必要的权限。启用行级安全性：在需要时使用行级安全性，以提供更细粒度的访问控制。使用强密码：确保用户使用强密码，并定期更改密码。监控和日志：启用数据库审计和日志记录，监控用户活动并及时发现异常行为。
PostgreSQL——用户管理
最新发布
吴声子夜歌的博客
08-17 1302
PostgreSQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的各种权限。SUPERUSER拥有对数据库操作的最高权限，可以完成对数据库的所有权限检查。为了保证PostgreSQL的安全，建议用户谨慎使用SUPERUSER,不要轻易创建它，最好使用非超级用户完成用户的大多数工作。组角色可以拥有数据库对象（比如表），以及可以把这些对象上的权限赋予其他角色，以控制谁拥有访问哪些对象的权限。一个数据库角色可以有一系列属性，这些属性定义它的权限，以及与客户认证系统的交互。
PostgreSQL 权限管理详解
lsx202406的博客
03-15 588
PostgreSQL 的权限管理机制为数据库安全提供了强大的保障。通过合理地分配权限和进行权限控制，可以确保数据库的安全性和数据的完整性。本文详细介绍了 PostgreSQL 的权限类型、分配方法以及权限控制，旨在帮助读者更好地理解和应用 PostgreSQL 的权限管理。由于篇幅限制，本文未能全面覆盖 PostgreSQL 权限管理的所有方面。在实际应用中，建议读者结合 PostgreSQL 官方文档和相关资料进行深入学习。
Postgresql - 用户权限数据库
地理信息的专栏
07-07 845
用户权限数据库
PostgreSql 用户及权限管理
脑子进水养啥鱼？的博客
07-17 1万+
ALTER DEFAULT PRIVILEGES 允许设置将被应用于未来要创建的对象的特权（它不会影响分配给已经存在的对象的特权）。当前，只能修改用于模式、表（包括视图和外部表）、序列、函数和类型（包括域）的特权。其中，可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时，单词 FUNCTIONS 和 ROUTINES 是等效的。（推荐使用 ROUTINES，因为它是用来囊括函数和过程的一个标准术语。在较早的 PostgreSQL 发行版中，只允许单词 FUNCTIONS。
PostgreSQL 权限管理
分享既学习
06-21 7180
权限管理是所有数据库都绕不开的话题，PG 中使用 `角色（role）`机制来处理用户身份认证。 本篇文章介绍如何管理 PostgreSQL 中的 `角色 用户 组角色`。
PostgreSQL教程(十二)：角色和权限管理介绍
09-10
在PostgreSQL中，角色和权限管理是保证数据安全的重要环节，本篇教程将详细介绍角色和权限管理的知识点。 首先，角色可以被视为数据库用户或一组用户的集合，这是PostgreSQL权限管理的基本单元。一个角色可以是...
PostgreSQL 角色与用户管理介绍
09-10
在PostgreSQL中，角色可以属于其他角色，形成一种层级结构，这种结构有助于简化权限管理。使用INROLE，INGROUP等参数可以设置角色的层级关系。 例如，如果想让某个角色属于管理员角色，可以使用： ```sql postgres...
数据库角色和权限管理：深入解析与实践应用
11-02
通过合理地规划和设计角色权限，不仅可以实现数据的自动备份、负载均衡和高可用性，还可以在业务扩展和容灾恢复中起到重要作用。本文深入解析了数据库角色和权限管理的工作原理和技术实现，提供了具体的代码示例，...
10.1 PostgreSQL用户与权限管理
Python老吕的博客
09-23 1280
用户账户通常由用户名和密码组成，是用户在系统中的唯一标识。在数据库系统中，用户账户可以关联到具体的数据库用户，用于执行数据库操作。用户名：一个独特的标识符，用于区分不同的用户。密码：用于验证用户身份的敏感信息，通常需要保密。
PostgreSQL安装用户、角色与权限管理
trayvontang的博客
10-30 2134
记得使用-d指定数据库，默认的超级用户和创建的数据库都是postgres，如果只修改了超级用户名称，psql的时候记得使用-d指定数据库，否则默认连接的就是和用户名同名的数据库。psql命令非常方便，提供了很多默认选项，如果没有修改过默认配置，用起来非常方便顺手，但是如果修改了默认配置，就一定要多注意。PostgreSQL中用户与角色基本是一个东西，唯一区别是角色没有登录权限，用户有登录权限，可以登录。所以，我们涉及权限的时候，可以把权限分配给角色，在为用户指定不同的角色(一个用户可以有多个角色)
PostgreSQL（七）权限管理
shmily_coco的博客
11-28 3758
3、对超级用户 postgres不做权限检查，其它用户走ACL（Access Control list）；2、schema：PG的一个用户可以有多个schema，但一个schema只能属于一个用户；（可用此预防表被误删除）1、实例权限：控制部分用户、主机是否允许登录、访问数据库（白名单&黑名单）；1、每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限；4、对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL；3、object：表、视图、索引等对象的增删改查的权限。
postgresql用户模式权限介绍_postgresql usage
2401_84563561的博客
05-16 2872
2.创建一个普通用户3.为创建一个超级用户4.创建一个普通用户,并且赋予相关权限。
Postgresql 用户管理
Csdn129341的博客
05-10 512
一， 设置超级用户密码 1 修改 pg_hba.conf 使超级用户postgres 可以登录到数据库中 host all all 127.0.0.1/32 trust 2 修改 postgres 密码 $psql -U postgres -h 127.0.0.1 psql (9.6.4) Type "help" fo...
postgresql用户模式权限介绍
热门推荐
Brent的专栏
06-12 2万+
一.USER用户管理1.查看用户pg中的role,user,group基本是一样的,只是默认创建的role,group没有登录数据库的权限.用户分为普通用户和超级用户1.使用\du查看数据库中的用户,其中role name是用户名,第二列是用户的属性,第三列表示用户具有哪些成员,例如将suq赋予给brent postgres=# \du                               ...
PostgreSQL 用户权限管理
XwlyVue的博客
09-18 267
可以使用 CREATE USER 创建用户，使用 GRANT 授予权限，使用 REVOKE 撤销权限，使用 ALTER USER 修改密码，使用 DROP USER 删除用户。通过合理分配权限，可以确保用户在数据库中只能进行必要的操作，提高数据库的安全性。上述代码将 SELECT、INSERT 和 UPDATE 权限授予了 new_user 用户，使其能够在 table_name 表上执行这些操作。在 PostgreSQL 中，用户权限管理是非常重要的，它可以确保数据库的安全性和数据的完整性。
02：PostgreSQL用户和权限
zyplanke的专栏
05-21 5374
创建用户和角色唯一的区别是：创建用户默认有login权限，创建角色默认没有login权限。
postgresql user表
Claroja
06-04 4157
在postgresql中，默认有一张user表用来存放postgresql的用户，当我们自己建立一个user表的时候，优先访问系统默认的user表 SELECT * FROM user WHERE id = 1 当我们想访问自己创建的user表的时候，需要加public前缀 SELECT * FROM public.user WHERE id = 1 ...
PostgreSQL 中如何创建和管理用户角色？
08-16
### 创建用户角色 PostgreSQL 中的角色是一种数据库用户账户，用于管理数据库访问权限和登录权限。角色可以被赋予特定的权限，并用于管理数据库对象的安全性。创建角色的命令是 `CREATE ROLE`，该命令允许定义角色的属性，例如登录权限、密码、权限等。例如： ```sql CREATE ROLE role_name WITH LOGIN PASSWORD 'password'; ``` 上述命令创建了一个可以登录数据库的角色，并为其设置密码。角色可以被授予特定权限，例如访问特定表或执行特定操作的权限。若需为角色分配权限，可使用 `GRANT` 命令： ```sql GRANT SELECT, INSERT ON table_name TO role_name; ``` 该命令允许角色 `role_name` 对指定表执行 `SELECT` 和 `INSERT` 操作。此外，角色还可以被赋予其他角色的权限，形成角色继承关系，从而简化权限管理： ```sql GRANT role_name1 TO role_name2; ``` 上述命令将角色 `role_name1` 的权限授予角色 `role_name2`，使其继承所有权限[^1]。 ### 创建用户 PostgreSQL 中的用户是一种特殊的角色，具有登录权限。创建用户的命令是 `CREATE USER`，其语法与 `CREATE ROLE` 类似，但默认带有 `LOGIN` 属性。例如： ```sql CREATE USER user_name WITH PASSWORD 'password'; ``` 该命令创建了一个具有登录权限的用户，并为其设置密码。用户可以像角色一样被授予权限，例如访问特定表或执行特定操作的权限。此外，用户还可以被赋予其他角色的权限，以继承其访问控制特性： ```sql GRANT role_name TO user_name; ``` 上述命令将角色 `role_name` 的权限授予用户 `user_name`，使其获得相应的数据库访问权限[^1]。 ### 管理角色和用户 PostgreSQL 提供了多种命令用于管理角色和用户，包括修改密码、更改权限、删除角色或用户等。例如，修改角色或用户的密码可以使用以下命令： ```sql ALTER ROLE role_name WITH PASSWORD 'new_password'; ALTER USER user_name WITH PASSWORD 'new_password'; ``` 上述命令分别修改了角色和用户的密码。若需删除角色或用户，可以使用 `DROP ROLE` 或 `DROP USER` 命令： ```sql DROP ROLE role_name; DROP USER user_name; ``` 删除角色或用户时，需要注意其权限是否已被其他对象引用，否则可能导致权限管理问题。此外，角色和用户还可以被赋予超级用户权限，以管理整个数据库系统： ```sql CREATE USER admin_user WITH SUPERUSER PASSWORD 'password'; ``` 上述命令创建了一个具有超级用户权限的用户，可以管理所有数据库对象并执行管理操作[^1]。 ### 角色与用户管理的最佳实践 在 PostgreSQL 中，角色和用户的管理需要遵循一定的最佳实践，以确保数据库的安全性和稳定性。首先，应避免直接使用超级用户账户执行日常操作，而是创建具有有限权限的用户账户，并根据需要分配权限。其次，定期检查角色和用户的权限，确保其权限与其职责相符。最后，使用角色继承机制，简化权限管理，并确保权限分配的灵活性和可维护性[^1]。 ### 示例代码 以下是一些常用的 PostgreSQL 角色和用户管理命令示例： - 创建角色并设置密码： ```sql CREATE ROLE analyst WITH LOGIN PASSWORD 'secure_password'; ``` - 授予角色对特定表的访问权限： ```sql GRANT SELECT ON sales_data TO analyst; ``` - 创建用户并设置密码： ```sql CREATE USER john_doe WITH PASSWORD 'john_password'; ``` - 将角色权限授予用户： ```sql GRANT analyst TO john_doe; ``` - 修改角色密码： ```sql ALTER ROLE analyst WITH PASSWORD 'new_secure_password'; ``` - 删除用户： ```sql DROP USER john_doe; ``` - 删除角色： ```sql DROP ROLE analyst; ``` ###
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值