VPS上PostgreSQL权限管理终极指南：角色、用户与授权实战

1 年 ago

文, 翔

5 minutes

引言

PostgreSQL，简称Postgres，是一款开源的关系型数据库管理系统。与其它关系型数据库类似，PostgreSQL将数据存储在由行和列组成的表中。用户可以使用结构化查询语言（SQL）来定义、操作、控制和查询数据。PostgreSQL是一个强大的工具，可用于在虚拟专用服务器（VPS）上管理应用程序和网络数据。

本指南将展示如何正确管理权限并授予用户权限。这样可以使您的应用程序获得所需的特权，而同时不影响独立的数据库。

先决条件

要跟随本教程，您需要准备以下物品：

一个已按照《Ubuntu 22.04初始服务器设置指南》配置好的Ubuntu 22.04服务器。完成此先决条件教程后，您的服务器应拥有一个具备sudo权限的非root用户和一个基本防火墙。
已完成《如何在Ubuntu 22.04上安装和使用PostgreSQL》教程的第1步，确保您的服务器上已安装Postgres。

在准备好您的环境并在服务器上运行Postgres后，您可以开始学习Postgres如何处理权限。

在PostgreSQL中查看角色和权限

PostgreSQL通过“角色”的概念来管理权限。角色与传统的Unix风格权限不同，它们没有用户和组的区别。角色可以被操作以符合这两种约定，但它们也更加灵活。在安装时，PostgreSQL被设置为使用对等验证，这意味着它将PostgreSQL角色与匹配的Unix/Linux系统账户关联起来。如果在PostgreSQL中存在一个角色，一个与该角色同名的Unix/Linux用户名可以作为该角色登录。

安装过程创建了一个名为postgres的用户账户，该账户与默认的Postgres角色关联。为了使用Postgres，您可以登录该账户。

首先，使用systemctl start命令确保您的服务器正在运行。

sudo systemctl start postgresql.service

然后，您可以输入命令切换到postgres账户。

sudo -i -u postgres

您现在可以立即通过输入以下命令来访问PostgreSQL提示符：

psql

要列出您的Postgres实例中的角色，请键入以下命令:

\du

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



目前，只有一个默认角色具有许多强大的特权。
在PostgreSQL中创建角色
在Postgres中，有多种不同的方法可以创建角色。可以从Postgres内部或命令行创建角色。
在PostgreSQL中创建角色
在Postgres提示界面中，创建新角色的一种方法是从内部创建。以下是在Postgres提示界面中创建新角色的语法：
CREATE ROLE new_role_name;
为了证明这一点，创建一个名为demo_role的新角色。
CREATE ROLE demo_role;
再次检查已定义的用户：
\du
OutputList of roles
 Role name |                         Attributes                         | Member of 
----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}



您的结果将显示两个用户。
通过命令行创建角色
创建角色的另一种方法是使用命令行中的createuser命令。
首先，输入exit命令退出PostgreSQL命令提示符。
\q
然后，登录到postgres账户中。
sudo -i -u postgres
您可以使用createuser命令在命令行中创建新角色。使用--interactive参数将提示您输入新角色的名称，并询问是否应该拥有超级用户权限。
以postgres账户登录后，可以通过输入以下命令创建新用户：
createuser --interactive
脚本将提示您进行选择，并根据您的回答执行符合您要求的正确的Postgres命令。
OutputEnter name of role to add: test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n



通过对所有这些提示回答“否”(n)，您将创建一个类似于上一个用户的用户。
重新登录到您的psql Postgres提示符中。
psql
然后执行du命令以揭示这两个新角色之间的差异。这个命令以\开头，因为它是一个特定于psql的元命令，由psql自身而不是PostgreSQL处理。
\du
这是文章《如何在VPS上使用角色和管理授予权限的PostgreSQL使用方法》的第2部分（共5部分）。
角色列表
List of roles
 Role name |                         Attributes                         | Member of 
----------|------------------------------------------------------------|-----------
demo_role | Cannot login                                               | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user |                                                            | {}

请注意，从命令行创建的用户没有将“无法登录”列为属性。
在PostgreSQL中删除角色
您可以使用以下语法删除一个角色。
DROP ROLE role_name;
为了演示，通过输入命令删除demo_role角色。
DROP ROLE demo_role;
如果你对一个不存在的用户发出命令，你将收到一个错误信息。
Output
ERROR:  role "demo_role" does not exist
为了避免这种情况，并使得删除命令在用户存在时删除用户，在用户不存在时不做任何操作，使用以下语法：
DROP ROLE IF EXISTS role_name;
使用此选项指定后，无论角色的有效性如何，命令都会成功完成。尝试使用上述命令删除demo_role将导致出现以下情况。
DROP ROLE IF EXISTS demo_role;
OutputNOTICE:  role "demo_role" does not exist, skipping
DROP ROLE
该角色已被删除。
在角色创建时定义权限
现在，您已经准备好使用更改后的权限重新创建demo_role。您可以通过在主CREATE子句之后指定所需的权限来实现这一点，如下所示：
CREATE ROLE role_name WITH assigned_permissions;
要查看全部选项的完整列表，请输入：
\h CREATE ROLE
OutputCommand:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

URL: https://www.postgresql.org/docs/14/sql-createrole.html
您可以通过输入以下命令给 demo_role 用户提供登录权限：
CREATE ROLE demo_role WITH LOGIN;
通过使用\du命令检查属性，这两个用户现在拥有相同的权限。
OutputList of roles
 Role name |                         Attributes                         | Member of 
----------|------------------------------------------------------------|-----------
demo_role |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user |                                                            | {}
您可以在创建每个角色时，无需指定LOGIN属性来达到此状态。通过使用以下CREATE USER命令，它会自动赋予角色登录权限。
CREATE USER role_name;
角色是自动赋予权限而创建的。
在PostgreSQL中更改角色权限
要更改已创建角色的属性，请使用ALTER ROLE命令。该命令的语法如下：
ALTER ROLE role_name WITH attribute_options;
这个命令允许您定义权限更改，而无需像之前演示的那样删除和重新创建用户。例如，您可以通过发出此命令将demo_role更改回先前的状态“无法登录”。
ALTER ROLE demo_role WITH NOLOGIN;
您可以用\du命令来确认此变更。
\du
Output
这是文章《如何在VPS上使用角色和管理授予权限的PostgreSQL使用方法》的第3部分（共5部分）。
角色列表
List of roles
 Role name |                         Attributes                         | Member of 
----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {}
要将其更改回具有登录权限的角色，请使用以下命令：
 ALTER ROLE demo_role WITH LOGIN;
现在角色已经恢复原状。
在PostgreSQL中以不同用户身份登录
默认情况下，只有当系统用户名与PostgreSQL用户名匹配时，用户才能进行本地登录。您可以通过更改登录类型或指定PostgreSQL使用回环网络接口来进行更改。这样会将连接类型更改为远程连接，尽管实际上是本地连接。
首先，为您想要连接的用户创建一个密码，以便进行身份验证。您可以尝试使用之前创建的test_user，并给予它一个密码。
\password test_user
您将被要求输入并确认密码。现在，使用以下命令退出PostgreSQL界面，并返回到您的普通用户。
\q
PostgreSQL认为当您登录时，您将使用与操作系统用户名匹配的用户名，并且您将连接到具有相同名称的数据库。
为了明确指定您想要使用的选项，请使用以下语法加上您的参数。
psql -U user_name -d database_name -h 127.0.0.1 -W
以下是指令中每个项目的简要说明：

user_name应替换为您想要连接的用户名。
database_name应是您有权访问的现有数据库的名称。
-h 127.0.0.1部分指定您将连接到本地机器，但通过网络接口进行连接，这允许您即使系统用户名不匹配也能进行身份验证。
-W标志告诉PostgreSQL您将输入密码。

要使用您的测试用户登录，请执行以下指令：
sudo psql -U test_user -d postgres -h 127.0.0.1 -W
在执行此命令后，您需要输入密码。
在这个例子中，您使用的是数据库postgres。这是在安装过程中默认设置的数据库。如果您尝试在这个会话中执行一些操作，您会发现您没有很多能力来做很多事情。这是因为test_user没有被授予管理权限。
退出当前会话
\q
然后再次进入管理的PostgreSQL会话：
sudo -u postgres psql
接下来您将授予权限。
在PostgreSQL中授予权限
创建数据库或表时，通常只有创建者角色（不包括具有超级用户权限的角色）才有权限对其进行修改。可以通过授予其他角色权限来改变这种行为。
您可以使用GRANT命令来授予权限，其一般语法如下：
GRANT permission_type ON table_name TO role_name;
您可以使用以下命令创建一张表格来练习这些概念。
CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
欲查看表格，请输入此命令：
\d
OutputList of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | demo        | table    | postgres
 public | demo_id_seq | sequence | postgres
(2 rows)
请注意只有一个表类型和一个序列类型。当您在创建表时使用了id serial命令，序列会为您生成。这将生成一个自增的整数。
现在您可以将一些权限授予demo_role的新演示表。要这样做，请使用以下命令向demo_role用户授予UPDATE权限。
GRANT UPDATE ON demo TO demo_role;
您可以将权限类型替换为”ALL”来授予用户完全权限。使用以下命令将此权限授予test_user用户：
GRANT ALL ON demo TO test_user;
如果您想为系统上的每个用户指定权限，可以使用PUBLIC而不是指定某个特定用户。
这是文章《如何在VPS上使用角色和管理授予权限的PostgreSQL使用方法》的第4部分（共5部分）。
GRANT INSERT ON demo TO PUBLIC;
使用以下命令可以查看授权表：
\z
输出
Access privileges
 Schema |    Name     |   Type   |     Access privileges      | Column privileges | Policies 
--------+-------------+----------+----------------------------+-------------------+----------
 public | demo        | table    | postgres=arwdDxt/postgres +|
        |             |          | demo_role=w/postgres      +|
        |             |          | test_user=arwdDxt/postgres+|
        |             |          | =a/postgres                |
 public | demo_id_seq | sequence |                            |
(2 rows)
这显示了所有已分配的授予权限。
从PostgreSQL中删除权限
你可以使用REVOKE命令来撤销权限。REVOKE命令的语法几乎与GRANT命令相同。
REVOKE permission_type ON table_name FROM user_name;
您可以在命令中同样使用这两个缩写词，即ALL和PUBLIC。
REVOKE INSERT ON demo FROM PUBLIC;
您之前设定的权限已经被取消了。
在PostgreSQL中使用组角色
角色足够灵活，可以允许将其他角色进行分组，以实现广泛的权限控制。例如，您可以创建一个名为“temporary_users”的新角色，然后将“demo_role”和“test_user”添加到该组中。
首先创建一个将作为组使用的新角色。
CREATE ROLE temporary_users;
然后将用户分配给新创建的temporary_users组。
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;
现在，这两个用户可以通过操作“temporary_users”组角色来管理其权限，而不是单独管理每个成员。
您可以通过输入以下命令来查看角色成员资料：
\du
输出
List of roles
    Role name    |                         Attributes                         |     Member of     
-----------------+------------------------------------------------------------+-------------------
 demo_role       |                                                            | {temporary_users}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 temporary_users | Cannot login                                               | {}
 test_user       |                                                            | {temporary_users}
只要使用SET ROLE命令，群组中的任何成员角色都可以充当他们所属的群组角色。由于您当前登录的postgres用户具有超级用户特权，即使它不是temporary_users组的成员，您仍然可以使用SET ROLE命令。
SET ROLE temporary_users;
现在，任何创建的表都是由临时用户角色拥有的。
CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);
现在，通过输入这个命令来检查表的所有权:
\d
输出
List of relations
 Schema |     Name     |   Type   |      Owner      
--------+--------------+----------+-----------------
 public | demo         | table    | postgres
 public | demo_id_seq  | sequence | postgres
 public | hello        | table    | temporary_users
 public | hello_id_seq | sequence | temporary_users
(4 rows)
临时用户角色拥有与序列数据类型相关联的新表。
要恢复到原始角色权限，请输入以下命令：
RESET ROLE;
如果您在ALTER ROLE命令中给予用户继承属性，那么该用户将自动拥有其所属角色的所有特权，无需使用SET ROLE命令。
ALTER ROLE test_user INHERIT;
现在，test_user将具有他所属角色的所有权限。您可以使用DROP ROLE命令删除组角色或任何角色。您可以通过输入以下命令来测试temporary_users组的权限：
DROP ROLE temporary_users;
输出
错误: 角色 "temporary_users" 无法删除，因为有对象依赖于它
详情: 序列 hello_id_seq 的所有者
表 hello 的所有者
这个操作会输出一个错误，因为 hello 表是由 temporary_users 拥有的。你可以通过将所有权转移到不同的角色来解决这个问题。
ALTER TABLE hello OWNER TO demo_role;
您可以通过以下方法检查 temporary_users 是否不再拥有任何表：
\d
输出
关系列表
 模式  |     名称     |   类型   |   所有者   
--------+--------------+----------+-----------
 public | demo         | table    | postgres
 public | demo_id_seq  | sequence | postgres
 public | hello        | table    | demo_role
 public | hello_id_seq | sequence | demo_role
(4 行)
通过执行以下命令，您现在可以成功删除 temporary_users 角色。
DROP ROLE temporary_users;
这将废除 temporary_users 的角色。temporary_users 的前成员不会被移除。
结论
现在你已经掌握了管理 PostgreSQL 数据库权限所需的基本技能。了解如何管理权限非常重要，这样你的应用程序可以访问它们所需的数据库，同时不会干扰其他应用程序使用的数据。
如果你想了解更多关于 PostgreSQL 及其使用方法，我们鼓励你查看以下指南：

SQLite vs MySQL vs PostgreSQL: 关系型数据库管理系统比较
通过 SQL 练习运行查询

#PostgreSQL #VPS #数据库安全 #权限管理 #角色管理