介绍
PostgreSQL,或Postgres,是一个开源的关系数据库管理系统. 与其他关系数据库一样,PostgreSQL将数据存储在由行和列组成的表中. 用户可以使用结构化查询语言(更为常见的名称是 SQL来定义,操纵,控制和查询数据。
本指南将展示如何正确管理权限和授予用户权限,从而允许您在不影响单独数据库的情况下为您的应用提供所需的权限。
前提条件
要跟随这个教程,你需要:
- 一个 Ubuntu 22.04 服务器已通过遵循我们的 Initial Server Setup for Ubuntu 22.04]指南进行配置. 完成此前提教程后,您的服务器应该有一个具有 sudo 权限和基本防火墙的非根用户。
- 完成我们 如何在 Ubuntu 22.04上安装和使用 PostgreSQL 教程的 步骤 1,以便在您的服务器上安装 Postgres。
随着环境的准备和 Postgres 在您的服务器上运行,您可以开始了解 Postgres 如何处理权限。
在 PostgreSQL 中查看角色和权限
Postgres 通过 roles 概念来管理权限. 角色与传统的 Unix 类型的权限不同,因为用户和组之间没有区别。 角色可以被操纵以类似于这两个惯例,但它们也更灵活。 安装后,Postgres 设置为使用 _peer 身份验证,这意味着它将 Postgres 角色与匹配的 Unix/Linux 系统帐户相关联。
安装过程创建了一个名为 postgres的用户帐户,与默认的 Postgres 角色相关联。
首先,请通过使用systemctl start命令确保您的服务器运行:
1sudo systemctl start postgresql.service
然后,您可以通过键入转到 postgres帐户:
1sudo -i -u postgres
您现在可以通过键入立即访问 PostgreSQL 提示:
1psql
若要在您的 Postgres 实例中列出角色,请键入以下命令:
1\du
1[secondary_label Output]
2 List of roles
3 Role name | Attributes | Member of
4-----------+------------------------------------------------------------+-----------
5 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
目前,只有一个默认角色具有许多强大的特权。
在 PostgreSQL 中创建角色
创建 Postgres 角色有几种不同的方法,您可以从 Postgres 内部或命令行创建角色。
在 PostgreSQL 中创建角色
创建新角色的一种方法是从 Postgres 提示接口中创建一个新角色,以下是 Postgres 提示接口中创建新角色的语法:
1CREATE ROLE new_role_name;
要证明这一点,创建一个名为 demo_role的新角色:
1CREATE ROLE demo_role;
再次检查已定义的用户:
1\du
1[secondary_label Output]
2 List of roles
3 Role name | Attributes | Member of
4-----------+------------------------------------------------------------+-----------
5 demo_role | Cannot login | {}
6 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
您的输出将显示两个用户。
从命令行创建角色
创建角色的一种替代方法是使用命令行中的createuser命令。
首先,退出 PostgreSQL 命令提示片刻通过键入:
1\q
然后,登录到postgres帐户:
1sudo -i -u postgres
您可以使用createuser命令从命令行创建新角色。使用--互动旗帜将提示您选择新角色的名称,并询问它是否应该具有超级用户权限。
登录为 postgres帐户,您可以通过键入创建一个新的用户:
1createuser --interactive
该脚本将提示您一些选择,并根据您的回复,按照您的规格执行正确的 Postgres 命令:
1[secondary_label Output]
2Enter name of role to add: test_user
3Shall the new role be a superuser? (y/n) n
4Shall the new role be allowed to create databases? (y/n) n
5Shall the new role be allowed to create more new roles? (y/n) n
通过对所有这些提示回答n为不,您将创建一个与以前的用户相似的用户。
再次登入您的psql邮件提示:
1psql
然后执行du命令来揭示两个新角色之间的差异. 这个命令从\开始,因为它是由psql本身而不是PostgreSQL处理的psql特定的元命令:
1\du
1[secondary_label Output]
2 List of roles
3 Role name | Attributes | Member of
4-----------+------------------------------------------------------------+-----------
5 demo_role | Cannot login | {}
6 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
7 test_user | | {}
请注意,从命令行创建的用户没有Cannot login列为属性。
在 PostgreSQL 中删除角色
您可以使用以下语法删除角色:
1DROP ROLE role_name;
要演示,请通过键入删除 demo_role 角色:
1DROP ROLE demo_role;
如果您对一个不存在的用户发出命令,则会收到一个错误消息:
1[secondary_label Output]
2ERROR: role "demo_role" does not exist
要避免这种情况,并让下载命令删除用户如果存在,并且如果用户不存在,静静地做任何事情,请使用以下语法:
1DROP ROLE IF EXISTS role_name;
使用此选项,无论角色的有效性如何,命令都将顺利完成,试图用上面的命令删除demo_role将导致:
1DROP ROLE IF EXISTS demo_role;
1[secondary_label Output]
2NOTICE: role "demo_role" does not exist, skipping
3DROP ROLE
现在这个角色被删除了。
角色创作中的特权
现在,您已经准备好重新创建 demo_role 带有更改的权限,您可以通过在主创建条款后指定所需的权限来完成此操作:
1CREATE ROLE role_name WITH assigned_permissions;
要查看所有选项的完整列表,键入:
1\h CREATE ROLE
1[secondary_label Output]
2Command: CREATE ROLE
3Description: define a new database role
4Syntax:
5CREATE ROLE name [ [ WITH ] option [ ... ] ]
6
7where option can be:
8
9 SUPERUSER | NOSUPERUSER
10 | CREATEDB | NOCREATEDB
11 | CREATEROLE | NOCREATEROLE
12 | INHERIT | NOINHERIT
13 | LOGIN | NOLOGIN
14 | REPLICATION | NOREPLICATION
15 | BYPASSRLS | NOBYPASSRLS
16 | CONNECTION LIMIT connlimit
17 | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
18 | VALID UNTIL 'timestamp'
19 | IN ROLE role_name [, ...]
20 | IN GROUP role_name [, ...]
21 | ROLE role_name [, ...]
22 | ADMIN role_name [, ...]
23 | USER role_name [, ...]
24 | SYSID uid
25
26URL: https://www.postgresql.org/docs/14/sql-createrole.html
您可以让 demo_role用户通过键入登录的能力:
1CREATE ROLE demo_role WITH LOGIN;
通过使用\du命令检查属性,两个用户现在拥有相同的特权:
1[secondary_label Output]
2 List of roles
3 Role name | Attributes | Member of
4-----------+------------------------------------------------------------+-----------
5 demo_role | | {}
6 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
7 test_user | | {}
您可以通过使用以下命令CREATE USER自动赋予角色登录权限:
1CREATE USER role_name;
角色由自动授予的特权创建。
在 PostgreSQL 中更改角色特权
若要更改已创建的角色属性,请使用ALTER ROLE命令,该命令的语法是:
1ALTER ROLE role_name WITH attribute_options;
此命令允许您定义权限更改,而无需删除和重建用户,如前面所示,例如,您可以通过发出此命令将 demo_role 更改到以前的状态为无法登录:
1ALTER ROLE demo_role WITH NOLOGIN;
您可以通过\du命令确认更改:
1\du
1[secondary_label Output]
2 List of roles
3 Role name | Attributes | Member of
4-----------+------------------------------------------------------------+-----------
5 demo_role | Cannot login | {}
6 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
7 test_user | | {}
若要将其更改为具有登录访问的角色,请使用以下命令:
1ALTER ROLE demo_role WITH LOGIN;
现在这个角色已经被扭转了。
在 PostgreSQL 中作为不同的用户登录
默认情况下,用户只允许本地登录,如果系统用户名匹配 PostgreSQL 用户名. 您可以通过更改登录类型或指定 PostgreSQL 应该使用 _loopback 网络接口进行更改。
首先,为您要连接的用户创建密码,以便该用户可以进行身份验证. 您可以通过给它一个密码来尝试使用您之前创建的 test_user:
1\password test_user
您将被要求输入并确认密码. 现在,离开 PostgreSQL 接口,并使用此命令退出您的正常用户:
1\q
PostgreSQL假定,当您登录时,您将使用与您的操作系统用户名匹配的用户名,并且您将连接到具有相同名称的数据库。
要明确指定您要使用的选项,请使用以下语法与参数:
1psql -U user_name -d database_name -h 127.0.0.1 -W
以下是命令中的每个项目的简短分布:
*user_name应该被您想要连接的用户名取代。
*database_name应该是您可以访问的现有数据库的名称。
*h 127.0.0.1部分是指您将连接到本地机器的部分,但通过网络接口,允许您验证即使您的系统用户名不匹配。
若要使用你的 test_user 登录,请发出以下命令:
1sudo psql -U test_user -d postgres -h 127.0.0.1 -W
您需要在这个命令后输入密码。
在本示例中,您使用数据库 postgres. 这是安装过程中设置的默认数据库. 如果您尝试在该会话中执行某些操作,您将看到您无法执行许多事情。
1\q
然后回到管理 **postgres ** 会话:
1sudo u - postgres psql
接下来,你会得到许可。
在 PostgreSQL 中授予权限
当创建数据库或表时,通常只有创建该数据库或表的角色(不包括具有 superuser 状态的角色)有权更改数据库或表。
1GRANT permission_type ON table_name TO role_name;
您可以创建一个表来练习这些概念,使用以下命令:
1CREATE TABLE demo (
2name varchar(25),
3id serial,
4start_date date);
要查看您创建的表格,请输入此命令:
1\d
1[secondary_label Output]
2 List of relations
3 Schema | Name | Type | Owner
4--------+-------------+----------+----------
5 public | demo | table | postgres
6 public | demo_id_seq | sequence | postgres
7(2 rows)
请注意,有一个表类型和一个序列类型。当您在创建表时使用id serial命令时,为您生成序列。
您现在可以向新的 demo 表授予某些权限,以便向 demo_role. 为此,请使用以下命令向 demo_role 用户授予 UPDATE 权限:
1GRANT UPDATE ON demo TO demo_role;
您可以通过用以下命令向 test_user 授予此权限:
1GRANT ALL ON demo TO test_user;
如果您想为系统中的每个用户指定权限,则可以使用PUBLIC而不是特定用户:
1GRANT INSERT ON demo TO PUBLIC;
要查看 Grant 表,请使用以下命令:
1\z
1[secondary_label Output]
2 Access privileges
3 Schema | Name | Type | Access privileges | Column privileges | Policies
4--------+-------------+----------+----------------------------+-------------------+----------
5 public | demo | table | postgres=arwdDxt/postgres +| |
6 | | | demo_role=w/postgres +| |
7 | | | test_user=arwdDxt/postgres+| |
8 | | | =a/postgres | |
9 public | demo_id_seq | sequence | | |
10(2 rows)
这揭示了已授予的所有补贴许可。
在 PostgreSQL 中删除权限
您可以使用REVOKE命令删除权限,而REVOKE命令几乎使用与 grant 相同的语法:
1REVOKE permission_type ON table_name FROM user_name;
您也可以在命令中使用相同的短语All和Public:
1REVOKE INSERT ON demo FROM PUBLIC;
您之前设置的权限现在已经被取消。
在 PostgreSQL 中使用组角色
角色足够灵活,允许组合其他角色以允许广泛的权限控制,例如,您可以创建一个名为 temporary_users的新角色,然后将 demo_role和 test_user添加到该组。
首先创建将作为组使用的新角色:
1CREATE ROLE temporary_users;
然后将用户分配到新创建的 temporary_users 组:
1GRANT temporary_users TO demo_role;
1GRANT temporary_users TO test_user;
现在,这两个用户可以通过操纵 temporary_users 组角色来管理他们的权限,而不是单独管理每个成员。
您可以通过键入查看角色会员信息:
1\du
1[secondary_label Output]
2 List of roles
3 Role name | Attributes | Member of
4-----------------+------------------------------------------------------------+-------------------
5 demo_role | | {temporary_users}
6 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
7 temporary_users | Cannot login | {}
8 test_user | | {temporary_users}
任何组角色的成员都可以使用SET ROLE命令作为他们所属的组角色。由于您登录的 postgres 用户目前具有超级用户权限,您可以使用SET ROLE命令,即使它不是 temporary_users 组的成员:
1SET ROLE temporary_users;
现在,创建的任何表都属于 temporary_users 角色:
1CREATE TABLE hello (
2name varchar(25),
3id serial,
4start_date date);
现在,通过发出此命令来检查表的所有权:
1\d
1[secondary_label Output]
2 List of relations
3 Schema | Name | Type | Owner
4--------+--------------+----------+-----------------
5 public | demo | table | postgres
6 public | demo_id_seq | sequence | postgres
7 public | hello | table | temporary_users
8 public | hello_id_seq | sequence | temporary_users
9(4 rows)
新表和与序列数据类型相关的序列由 temporary_users 角色拥有。
若要返回原始角色权限,请输入以下命令:
1RESET ROLE;
如果您用ALTER ROLE命令给用户INHERIT属性,该用户将自动拥有其所属角色的所有权限,而不使用SET ROLE命令:
1ALTER ROLE test_user INHERIT;
现在 test_user 将拥有其成员的角色中的所有权限. 您可以使用DROP ROLE命令删除组角色或任何角色。
1DROP ROLE temporary_users;
1[secondary_label Output]
2ERROR: role "temporary_users" cannot be dropped because some objects depend on it
3DETAIL: owner of sequence hello_id_seq
4owner of table hello
這會導致錯誤,因為「Hello」表由 **temporary_users 擁有,您可以通過將所有權轉移到不同的角色來解決這個問題:
1ALTER TABLE hello OWNER TO demo_role;
您可以通过以下方式检查 temporary_users 是否不再拥有任何表:
1\d
1[secondary_label Output]
2 List of relations
3 Schema | Name | Type | Owner
4--------+--------------+----------+-----------
5 public | demo | table | postgres
6 public | demo_id_seq | sequence | postgres
7 public | hello | table | demo_role
8 public | hello_id_seq | sequence | demo_role
9(4 rows)
您现在可以通过发出此命令成功放弃 temporary_users 角色:
1DROP ROLE temporary_users;
这将破坏 temporary_users 角色,未删除 temporary_users 之前的成员。
结论
您现在拥有管理您的 PostgreSQL 数据库权限所需的基本技能,重要的是要知道如何管理权限,以便您的应用程序可以访问所需的数据库,而不会扰乱其他应用程序使用的数据。
如果您想了解更多关于 Postgres 以及如何使用它,我们鼓励您查看以下指南: