什么是PostgreSQL
PostgreSQL是一个使用SQL查询语言的开源数据库管理系统。 PostgreSQL是一个强大的工具，可用于管理您的VPS上的应用程序和Web数据。
在这个gude中，我们将讨论如何正确管理权限和用户授权权限。 这将允许您为应用程序提供他们需要的特权，而不允许他们自由地影响单独的数据库。
我们将在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 | {}
我们提供的唯一信息是新角色没有登录权限。
这是现在好了。
如何从命令行创建角色
创建角色的另一种方法是使用“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”角色。 我们可以通过在main create子句后指定我们想要的权限来做到这一点：
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”。 使用以下命令为用户提供“UPDATE”权限：
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 temporary_users;
现在，创建的任何表都由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角色。 临时用户的前成员不被删除。
结论
您现在应该具有管理PostgreSQL数据库权限所需的基本技能。 重要的是知道如何管理权限，以便您的应用程序可以访问他们需要的数据库，而不会中断其他应用程序使用的数据。
