翻译自：
https://www.red-gate.com/simple-talk/databases/postgresql/postgresql-basics-roles-and-privileges/
认证：确认用户的真实性。可通过密码、信任或其他方式控制；
授权：作为已经认证通过的用户，在系统内可以执行的操作；
在开始之前，先来明确几个术语：
角色，role：PostgreSQL仅有一种授权原则类型，即存在于整个群集范围的角色。通常说来，一个允许登录的角色称为用户（user），而一个不允许登录的角色称为组（group）。请注意，虽然当前存在CREATE USER和CREATE GROUP命令，但均为CREATE ROLE的别名；
数据库对象：所有在PostgreSQL中可以创建；或访问的即为对象。角色对数据库、模式、表、视图、存储过程、函数等每个对象均可以有不同的权限；
权限：可赋予角色对数据库对象的访问种类。通常是在数据库或模式级别，但也可以针对具体的单个对象赋权。授予一个角色的权限也可以授予其他角色，这通常通过将组角色（不可登陆的角色）赋予用户角色（可登录的角色）来实现，以辅助权限管理。
取决于你之前对于信息安全的经历（数据库、操作系统或应用程序方面的经历），PostgreSQL中各组件的协作方式可能与你所期望的有所不同。
文章中提及的PostgreSQL群集（cluster），为PostgreSQL中运行多数据库的单一数据库服务/实例，而非多节点环境的数据库服务。
最小权限原则
最小权限原则（the Principle of Least Privilege,PoLP），是一种仅授予用户所需最低权限的信息安全策略，任何对非其所有文件或数据的访问均需显式赋权。
虽然未在官方文档中明确指出，但是基于PostgreSQL中角色和权限的协作方式，可以看出其在安全方面是基于PoLP来考量的。
例如，若非显式授权，在PostgreSQL中仅数据库对象（模式、表、函数等）的属主可使用及修改数据库对象。而在PostgreSQL中，对象仅由一个角色所有，大多数情况下，其他角色若需访问，则需要显式授权。也可以针对新创建的对象设置默认权限，以将指定权限自动赋予其他角色，不过，对象的所属关系是理解PostgreSQL数据库中对象及角色创建的一个核心概念。
接下来，对对象的所属关系及权限做进一步阐释。
超级用户
在深入探讨角色创建以及权限授予之前，最后探讨一下超级用户的概念。在PostgreSQL中，超级用户是可以在数据库中做任何操作的存在，类似于Linux中的root用户或SQL Server中的sa用户。
当一个用户被赋予了超级用户权限（superuser），则其在执行DDL/DML语句或管理群集语句时，不会对其进行权限检查。唯一的检查是该用户是否被允许登录并连接到群集。作为超级用户，会跳过所有其他权限检查，包含行级安全（Row Level Security，RLS）检查也会跳过。这可能与其他数据库系统对于超级用户的行为有所不同。
为了正常运行，每个PostgreSQL群集需要至少一个超级用户来执行管理任务，这些任务包括创建数据以及添加角色等，在PostgreSQL 15中，安装扩展、更新某些数据库参数也需要超级用户。
在PostgreSQL中，默认的初始超级用户名为postgres，即一般与运行PostgreSQL群集进程的操作系统用户名相同。该初始超级用户名可在初始化数据库时使用initdb命令修改（一般不建议修改）。
最后，如果PostgreSQL数据库位于AWS RDS或Azure，则到手的用户可能只有大部分管理所需的权限以及创建数据库及对象的权限，而并没有超级用户权限；同时，即使你自己管理PostgreSQL群集且有超级用户角色，也建议创建一个拥有CREATE USER以及CREATE DATABASE权限的非超级用户，这样创建的用户，虽然可以创建新的用户或数据库，但不会跳过其他权限检查，这样可以控制其对其他非授权表数据的访问。
PostgreSQL角色
PostgreSQL中，无论用户（user）还是组（group）技术上来讲，都是角色（role）。他们都在群集级别创建且给以数据库及其他对象的权限。
在本文中，所有实例用户角色创建时均以密码验证。也可以使用其他验证方式，包括GSSPI，SSPI，Kerberos，Certificate等。
创建用户角色
可使用具有CREATEROLE权限的用户执行以下DDL，在PostgreSQL中创建一个用户角色。
postgres=# create role dev1 with login password 'supersecretpw';
CREATE ROLE
PostgreSQL也支持CREATE USER命令，不过它只是CREATE ROLE的一个别名，且在未来的版本应该会弃用CREATE USER。
这样，就创建了一个可以登录到群集的角色。而该角色可以执行的命令则取决于接下来对其的赋权。
在多个用户角色中维护单一权限会很麻烦且可能出错。因此，建议创建一个拥有一组权限的角色，然后其他用户继承此角色。如下图：
如果一个角色是reader角色的成员，则该角色就具有对public.table_name对象的SELECT权限；同样，若其是creator的成员，则具有public.table_name的INSERT权限。而角色reader_and_creator因是reader和creator的成员，则其具有public.table_name的SELECT及INSERT权限。使用这种方式，可通过继承来管理角色的权限，而不用通过向每个用户授予单独的权限来管理。
创建组角色
可通过创建没有登录权限的角色创建组角色：
postgres=# create role devgrp with nologin ;
CREATE ROLE
依旧，PostgreSQL支持CREATE GROUP命令，但不建议使用。
在创建或者修改角色时，可指定多种角色属性。以下给出几个常用属性角色并加粗其默认值：
现在我们有了角色，包括可登录的角色（即用户）以及不可登录的角色（即组），接下来，我们深入探讨下权限的授予及应用。
PUBLIC角色
每个PostgreSQL群集都有一个默认的角色：PUBLIC，该角色不可被删除。默认情况下，所有角色均为public的成员，拥有public的所有权限。默认情况下，public具有的权限如下：
postgres=# drop role public;
ERROR: cannot use special role specifier in DROP ROLE
请注意，PUBLIC角色默认有CONNECT权限，也即所有角色都可以连接到新创建的数据库。若没有连接到数据库的权限，则任何新创建的角色均没有什么用了。
连接到PostgreSQL群集，总是需要连接到指定的数据库。本文是以认证和授权开篇的，若需连接到PostgreSQL群集，用户首先需要认证（为有WITH LOGIN权限的角色提供认证），然后需要具有连接到数据库的权限。因为每个角色均为PUBLIC角色的成员，故而默认具有CONNECT权限，故而所有通过认证的角色均具有连接到数据库的权限。
许多默认的权限均可变更。作为数据库管理员，可以回收PUBLIC角色的CONNECT权限，然后单独将该权限赋予角色，但这样太复杂了，没必要这么搞。
CONNECT权限仅允许用户连接并看到public模式下高层级的对象（例如，表）。不过，若无其他显式授权，则不可以查看或更新表。
注意，PostgreSQL 15及以后得版本，对默认权限做了变更。之前的版本中，PUBLIC角色允许在数据库的public模式下创建对象，而默认情况下，每个数据库均有一个public模式。故而从PostgreSQL 15开始，public角色默认不再具有创建对象的权限。
测试新角色
可通过以下psql命令使用dev1连接到PostgreSQL群集。以下实例中为本地PostgreSQL群集，端口号为5432。
[postgres@practice1 ~]$ psql -h localhost -U dev1 -d postgres
psql (16.2)
Type "help" for help.
postgres=>
或者可以这样连接（不大常用）：
[postgres@practice1 ~]$ psql postgres://dev1:supersecretpw@localhost:5432/postgres
psql (16.2)
Type "help" for help.
postgres=>
之所以能连接成功，是因为public角色默认有connect权限，故而dev1可以连接到postgres数据库。
使用密码认证及一个DDL语句，我们就创建了一个可以登录到PostgreSQL实例的角色，并不困难，对吧？
现在，让我们使用新创建的dev1账号进行接下来的探索。
使用新用户创建对象
作为开发者，我首要做的事情是在数据库中创建对应到的表。程序中，需要一个表为所有客户存储社保账号。我并不确定需要支持处理多少社保账户，故而我将每个社保账号存放在一个基表中。
在以dev1登录后，使用如下命令创建表：
CREATE TABLE user_social (
user_id INT NOT NULL,
twitter_handle TEXT NULL,
facebook_handle TEXT NULL );
在PostgreSQL 14及以前的版本中，以上命令应该会成功执行；不过在PostgreSQL 15及更新的版本中，则会报错：
postgres=> CREATE TABLE user_social (
postgres(> user_id INT NOT NULL,
postgres(> twitter_handle TEXT NULL,
postgres(> facebook_handle TEXT NULL );
ERROR: permission denied for schema public
LINE 1: CREATE TABLE user_social (
^
那这时该如何呢？必须给dev1用户授权。
给角色授权
以下命令，为dev1赋予CREATE权限：
postgres=# grant create on schema public to dev1 ;
GRANT
为dev1授予CREATE权限后，可继续创建表。此时，可以打开多个会话，或者可以使用SET ROLE命令切换角色。若以超级用户登录，或者是某个角色的成员，则可以切换角色。
postgres=> set role dev1;
SET
postgres=> CREATE TABLE user_social (
user_id INT NOT NULL,
twitter_handle TEXT NULL,
facebook_handle TEXT NULL );
CREATE TABLE
postgres=> set role none;
SET
执行成功！不过，接下来，我们在关联查询读取数据时，却遇到了问题：
postgres=> set role dev1;
SET
postgres=> select * from t_user inner join user_social usoc using (user_id);
ERROR: permission denied for table t_user
注：t_user为在public模式下创建的表。
处理办法，即为dev1授予对表的select权限。
postgres=> set role postgres ;
SET
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO dev1;
GRANT
当然，也可以一次赋多个权限，例如，若用户需要public模式下表的SELECT,INSERT,UPDATE以及DELETE权限，则可使用以下语句一次授权：
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public TO dev1;
不过，为每个角色在每个数据库都维护一遍权限将会带来很大的工作量。比如要是再新建一个dev2用户，则需要再从头授一遍权限。
以下，给出一个更好的处理方式。
使用组授权
PostgreSQL提供将角色权限赋予另一个角色的功能。特别地，组角色（不能登录的角色）可作为一次性赋予许多用户一组权限的完美解决方案。
前面，我们创建过一个devgrp组，它不能登录，且暂未赋予其任何权限。此时，我们可以先将开发用户所需的权限都赋给devgrp，然后将devgrp赋予开发用户即可。这样，后面我们需要对权限进行管理的时候，则仅需更改devgrp角色即可。
GRANT CREATE ON SCHEMA public TO devgrp;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public TO devgrp;
这样，接下来所有devgrp成员均可具有这些权限：
GRANT devgrp TO dev1;
这样，dev1就拥有了devgrp的所有权限。此后，若需新创建开发角色，则可直接创建并赋予其devgrp角色即可：
postgres=# CREATE ROLE dev2 WITH LOGIN PASSWORD 'supersecretpw2'
postgres-# IN ROLE devgrp;
CREATE ROLE
总结
PostgreSQL的角色权限体系为对数据库对象和资源的访问控制提供了灵活的解决方案。本文中，我们学习了角色，使用角色管理用户及组以及如何为新创建的角色授权。
我们还探讨了超级用户角色的重要性及权限检查对其的忽略。且，最佳实践建议为日常维护创建一个具有CREATEROLE和CREATEDB权限的管理用户。
还探讨了在创建新的角色后，如何通过单独授权或通过组继承来获取搭建应用程序所需的对象权限。
在接下来的文章中，我们将深入探讨对象所有权及其在应用程序搭建中的重要性；理解了对象所有权对脚本运行方式以及对备份管理的影响，对构建更易管理的安全应用会有所帮助。
