PostgreSQL：将所有权限授予 PostgreSQL 数据库上的用户

All commands must be executed while connected to the right database cluster. Make sure of it.

角色是数据库cluster的对象.同一集群的所有数据库共享一组定义的角色.根据数据库/架构/表等授予/撤销权限.

显然，一个角色需要访问database.默认情况下是PUBLIC.其他:

GRANT CONNECT ON DATABASE my_db TO my_user;

博士后14岁及以上的基本特权

Postgres 14 adds the predefined, non-login roles pg_read_all_data / pg_write_all_data.
They have SELECT / INSERT, UPDATE, DELETE privileges for all tables, views, and sequences. Plus USAGE on schemas. We can GRANT membership in these roles:

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

这涵盖了所有基本的DML命令(但不包括DDL，也不包括一些特殊命令，如TRUNCATE或EXECUTE函数特权！).The manual:

pg_read_all_data

读取所有数据(表、视图、序列)，就像拥有SELECT个权限一样

pg_write_all_data

写下所有数据(表、视图、序列)，就好像有INSERT，

不使用预定义角色的所有权限(任何Postgres版本)

Commands must be executed while connected to the right database. Make sure of it.

该角色需要(至少)schema上的USAGE特权.再说一次，如果这被授予PUBLIC，你就有保险了.其他:

GRANT USAGE ON SCHEMA public TO my_user;

或者在all个自定义模式上授予USAGE:

DO
$$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE to see generated commands
   EXECUTE (
   SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
   FROM   pg_namespace
   WHERE  nspname <> 'information_schema' -- exclude information schema and ...
   AND    nspname NOT LIKE 'pg\_%'        -- ... system schemas
   );
END
$$;

Then, all permissions for all tables (requires Postgres 9.0 or later).
And don't forget sequences (if any):

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

或者，您可以使用"Grant Wizard" of pgAdmin 4来使用GUI.

这包括existing个对象的权限.要同时覆盖future 的对象，请设置DEFAULT PRIVILEGES.见:

• Grant privileges for a particular database in PostgreSQL
• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

还有一些其他对象，the manual for GRANT有完整的列表.从第14期开始:

数据库对象的权限(表、列、视图、外部表、序列、数据库、外部数据包装器、外部服务器、函数、过程、过程语言、模式或表空间)

但剩下的很少需要.更多详情:

• Grant privileges for a particular database in PostgreSQL
• How to grant all privileges on views to arbitrary user

考虑upgrading to a current version.