PostgreSQL 中没有直接的GRANT DML语法DML数据操纵语言权限需拆分为SELECT,INSERT,UPDATE,DELETE分别授予。同时必须确保用户拥有数据库连接权限和 Schema 的使用权限。以下是最常用的批量授权方案假设目标 Schema 为public用户为app_usersql-- 1. 授予数据库连接权限在 postgres 或 template1 库中执行或当前库外执行GRANT CONNECT ON DATABASE your_database_name TO app_user;--哪个库无所谓-- 2.切换到目标数据库后执行以下命令\c your_database_name --3. 授予 Schema 使用权否则无法访问其中的表GRANT USAGE ON SCHEMA public TO app_user;-----default是默认有的但是新表的权限没有需要通过下面的sql赋权默认有public上 一扎表的权限-- 4. 授予现有所有表的 DML 权限GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;-- 5. 授予序列权限若表中有自增主键/serial 列必须授予此权限否则 INSERT 会报错 GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;-- 6. 设置默认权限确保未来新建的表自动拥有 DML 权限由创建表的角色执行通常是 owner 或 postgresALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO default 能不能给default-----没有default 但是有public----------------------------postgres# \c test123You are now connected to database test123 as user postgres.test123# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO defaulttest123-# ;ERROR: syntax error at or near defaultLINE 1: ...ic GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO default^test123# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO public;ALTER DEFAULT PRIVILEGEStest123# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO default;ERROR: syntax error at or near defaultLINE 1: ...T, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO default;^test123# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO public;GRANTtest123# \dtList of tablesSchema | Name | Type | Owner----------------------------------------------public | xx | table | postgres(1 row)test123# create user test123;CREATE ROLEtest123# \c test123 test123You are now connected to database test123 as user test123.test123 \dtList of tablesSchema | Name | Type | Owner----------------------------------------------public | xx cmn_pa_submit_part1 | table | postgres(1 row)test123 select *from xx;id | mem_doc_id | doc_uuid_list |uest_idpostgres# create database test1;CREATE DATABASEpostgres# create user test1;CREATE ROLEpostgres# \c test1You are now connected to database test1 as user postgres.test1# create table abc (a varchar);CREATE TABLEtest1# \c test1 test1You are now connected to database test1 as user test1.test1 select *from abc;ERROR: permission denied for table abctest1 select *From pg_tables;schemaname | tablename------to public-----------------postgres# \c test1You are now connected to database test1 as user postgres.test1# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO public;GRANTtest1# \c test1 test1You are now connected to database test1 as user test1.test1 select *from abc;a---(0 rows)test1ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_user;‌关键注意‌ALL TABLES IN SCHEMA仅对执行命令时已存在的表生效。若使用 PostgreSQL 14可直接使用预定义角色简化操作GRANT pg_read_all_data, pg_write_all_data TO app_user;需注意该角色包含所有 schema且不含 BYPASSRLS。如果表的所有者不是当前执行授权的用户可能需要先修改表所有者或使用ALTER DEFAULT PRIVILEGES FOR ROLE table_owner...。pg_read_all_data是 PostgreSQL 14 引入的‌预定义角色‌授予成员对数据库中所有表、视图、序列的 SELECT 权限以及对所有 Schema 的 USAGE 权限相当于一个全局只读角色。‌这个角色主要解决了以前创建只读账号要逐库、逐 schema 手动授权的麻烦现在一条命令就能搞定sqlGRANT pg_read_all_data TO appuser;使用时有几点要注意‌⚠️ ‌不绕过行级安全RLS‌该角色默认没有BYPASSRLS属性如果表启用了 RLS仍可能读不到受策略保护的数据需要管理员额外设置BYPASSRLS。⚠️ ‌并非绝对只读‌它隐式包含对pg_catalog的读写能力某些扩展或函数如pg_stat_reset()可能间接触发写操作也不阻止临时表操作。⚠️ ‌不能直接登录‌该角色是NOLOGIN必须授予给一个可登录的角色才能使用。‌如果你只想让某个用户读特定 schema 的数据而不想给全局只读权限更安全的做法是手动授权sqlGRANT USAGE ON SCHEMA public TO reporter; GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;需要我帮你对比 pg_read_all_data 和手动授权的性能差异吗能帮你判断哪种方案更适合你的使用场景。