如何在VPS上的PostgreSQL中使用角色和管理授予权限
什么是 PostgreSQL
PostgreSQL 是一个使用 SQL 查询语言的开源数据库管理系统。 PostgreSQL 是一个强大的工具，可用于管理您的 VPS 上的应用程序和 Web 数据。
在本指南中，我们将讨论如何正确管理权限和用户授予权限。 这将允许您为您的应用程序提供所需的权限，而不允许它们自由影响单独的数据库。
我们将在 Ubuntu 12.04 云服务器上使用 PostgreSQL，但安装之外的所有内容在任何现代 Linux 发行版上都应该相同。
初始 PostgreSQL 设置
如果您尚未在 Ubuntu 上安装 PostgreSQL，请键入以下命令下载并安装它：
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
在安装过程中，PostgreSQL 会创建一个默认用户来操作。 我们将使用此用户进行初始步骤。 使用以下命令登录：
sudo su - postgres
我们的环境现在已经准备好，我们可以开始学习 PostgreSQL 如何处理权限了。
PostgreSQL 权限概念
PostgreSQL（或简称为“postgres”）通过“角色”的概念来管理权限。
角色与传统的 Unix 风格的权限不同，用户和组之间没有区别。 可以对角色进行操作以类似于这两种约定，但它们也更加灵活。
例如，角色可以是其他角色的成员，允许它们具有先前定义的角色的权限特征。 角色还可以拥有对象并控制其他角色对这些对象的访问。
如何在 PostgreSQL 中查看角色
我们可以通过使用以下命令登录到提示界面来查看 PostgreSQL 中当前定义的角色：
psql
要获取角色列表，请键入：
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
如您所见，默认角色只有一个，并且具有许多强大的权限。
如何在 PostgreSQL 中创建角色
有许多不同的方法可以为 Postgres 创建角色。 可以从 Postgres 中或从命令行创建角色。
如何从 PostgreSQL 中创建角色
创建新角色的最基本方法是在 Postgres 提示界面中。
您可以使用以下语法创建新角色：
CREATE ROLE new_role_name;
让我们创建一个名为“demo_role”的新角色：
CREATE ROLE demo_role;
CREATE ROLE
如果我们再次检查定义的用户，我们将得到以下信息：
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
The only information we are given is that the new role has no login privileges. This is okay for now.
如何从命令行创建角色
创建角色的另一种方法是使用“createuser”命令。
通过键入以下内容暂时退出 PostgreSQL 命令提示符：
\q
使用以下命令创建一个名为“test_user”的角色：
createuser 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”是否，您将创建一个类似于我们之前创建的用户的用户。
我们可以重新登录 Postgres 提示符并再次发出“\du”命令以查看两个新角色之间的差异：
psql \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
test_user | | {}
如您所见，这些命令不会产生相同的结果。 从命令行创建的用户没有将“无法登录”列为属性。
如何在 PostgreSQL 中删除角色
作为练习，让我们尝试让“demo_role”拥有与“test_user”相同的权限。 我们将首先在创建过程中尝试此操作，稍后将学习如何更改现有角色的权限。
在我们练习在创建时为“demo_role”定义权限之前，我们需要销毁当前角色以便我们可以重试。
您可以使用以下语法删除角色：
DROP ROLE role_name;
通过键入以下内容删除“demo_role”角色：
DROP ROLE demo_role;
DROP ROLE
如果我们对不存在的用户发出命令，我们将收到此错误：
DROP ROLE demo_role;
ERROR: role "demo_role" does not exist
为避免这种情况并让 drop 命令删除用户（如果存在）并在用户不存在时悄悄地不执行任何操作，请使用以下语法：
DROP ROLE IF EXISTS role_name;
如您所见，指定此选项后，无论角色的有效性如何，命令都将成功完成：
DROP ROLE IF EXISTS demo_role;
NOTICE: role "demo_role" does not exist, skipping DROP ROLE
如何在创建角色时定义权限
现在，我们准备重新创建具有更改权限的“demo_role”角色。 我们可以通过在主创建子句之后指定我们想要的权限来做到这一点：
CREATE ROLE role_name WITH optional_permissions;
您可以通过键入以下内容查看选项的完整列表：
\h CREATE ROLE
我们想让这个用户能够登录，所以我们将输入：
CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE
如果我们再次检查属性，我们可以看到两个用户现在拥有相同的权限：
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
test_user | | {}
如果我们想在不为每个角色创建指定“login”属性的情况下进入此状态，我们实际上可以使用以下命令而不是“CREATE ROLE”命令：
CREATE USER role_name;
这两个命令的唯一区别是“CREATE USER”自动赋予角色登录权限。
如何在 PostgreSQL 中更改角色的权限
要更改已创建角色的属性，我们使用“ALTER ROLE”命令。
该命令允许我们定义权限更改，而无需像前面演示的那样删除和重新创建用户。
基本语法是：
ALTER ROLE role_name WITH attribute_options;
例如，我们可以通过发出以下命令将“demo_role”更改回之前的状态：
ALTER ROLE demo_role WITH NOLOGIN;
ALTER ROLE
我们可以看到权限已恢复到以前的状态：
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
test_user | | {}
我们可以使用以下命令轻松地将其改回：
ALTER ROLE demo_role WITH LOGIN;
如何在 PostgreSQL 中以不同用户身份登录
默认情况下，仅当系统用户名与 PostgreSQL 用户名匹配时，才允许用户在本地登录。
我们可以通过更改登录类型或指定 PostgreSQL 应使用环回网络接口来解决此问题，这会将连接类型更改为远程，即使它实际上是本地连接。
我们将讨论第二种选择。 首先，我们需要为我们想要连接的用户提供密码，以便我们进行身份验证。
使用以下命令为“test_user”提供密码：
\password test_user
系统将提示您输入并确认密码。 现在，退出 PostgreSQL 界面并返回到您的普通用户。
\q exit
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 我们将输入密码。
要使用我们的“test_user”登录，我们可以发出以下命令：
psql -U test_user -d postgres -h 127.0.0.1 -W
Password for user test_user:
您将需要输入您配置的密码。 在我们的示例中，我们使用数据库“postgres”。 这是安装期间设置的默认数据库。
如果您尝试在此会话中执行某些操作，您会发现您没有能力做很多事情。 这是因为我们没有授予“test_user”权限来管理许多事情。
让我们退出并回到管理会话：
\q sudo su - postgres psql
如何在 PostgreSQL 中授予权限
创建数据库或表时，通常只有创建它的角色（不包括具有超级用户身份的角色）才有权修改它。 我们可以通过授予其他角色权限来改变这种行为。
我们可以使用“GRANT”命令授予权限。 一般语法在这里：
GRANT permission_type ON table_name TO role_name;
创建一个简单的表格来练习这些概念：
CREATE TABLE demo ( name varchar(25), id serial, start_date date);
NOTICE: CREATE TABLE will create implicit sequence "demo_id_seq" for serial column "demo.id" CREATE TABLE
我们可以看到结果：
\d
List of relations Schema | Name | Type | Owner --------+-------------+----------+---------- public | demo | table | postgres public | demo_id_seq | sequence | postgres (2 rows)
我们现在可以将新的“demo”表授予“demo_role”一些权限。 使用以下命令授予用户“更新”权限：
GRANT UPDATE ON demo TO demo_role;
我们可以通过用单词“all”替换权限类型来授予用户完全权限：
GRANT ALL ON demo TO test_user;
如果我们想为系统上的每个用户指定权限，我们可以使用“public”这个词而不是特定用户：
GRANT INSERT ON demo TO PUBLIC;
要查看授权表，请使用以下命令：
\z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------------+----------+----------------------------+--------------------------
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; 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 | {}
temporary_users | Cannot login | {}
test_user | | {temporary_users}
通过使用“set role”命令，组角色的任何成员都可以充当他们所属的组角色。
由于我们登录的“postgres”用户当前具有超级用户权限，即使我们不是该组的成员，我们也可以使用“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)
如您所见，新表（以及与串行数据类型关联的序列）归“temporary_users”角色所有。
我们可以使用以下命令恢复我们原来的角色权限：
RESET ROLE;
如果我们使用“alter role”命令为用户提供“inherit”属性，则该用户将自动拥有他们所属角色的所有权限，而无需使用“set role”命令：
ALTER ROLE test_user INHERIT;
现在 test_user 将拥有它所属角色的所有权限。
我们可以使用“drop role”命令删除组角色（或任何角色）：
DROP ROLE temporary_users;
ERROR: role "temporary_users" cannot be dropped because some objects depend on it DETAIL: owner of table hello owner of sequence hello_id_seq
这会给你一个错误，因为我们创建了一个由“temporary_users”拥有的表。 我们可以通过将所有权转移到不同的角色来解决这个问题：
ALTER TABLE hello OWNER TO demo_role;
如果我们检查，我们可以看到“temporary_users”不再拥有任何表：
\d
List of relations Schema | Name | Type | Owner --------+--------------+----------+----------- public | demo | table | postgres public | demo_id_seq | sequence | postgres public | hello | table | demo_role public | hello_id_seq | sequence | demo_role (4 rows)
我们现在可以通过再次发出命令成功删除“temporary_users”角色：
DROP ROLE temporary_users;
这将破坏temporary_users 角色。 不会删除temporary_users 的前成员。
结论
您现在应该具备管理 PostgreSQL 数据库权限所需的基本技能。 了解如何管理权限非常重要，这样您的应用程序才能访问所需的数据库，同时不会中断其他应用程序使用的数据。
