Postgres12删除schema后如何自动保留非所有者用户的原有权限

有可落地的自动恢复方案，无需手动逐条赋权，前提是你需要先将被删除的schema1及下属表从备份恢复或重新创建完成——权限依附于数据库对象存在，对象未恢复时权限无法单独生效。具体方案如下： #

方案1：提前配置默认权限（预防场景） #

如果是提前做灾备准备，可由schema1的所有者或超级用户配置默认权限规则，后续只要schema1下新建表，都会自动给user1赋予预设权限：

-- 给user1预设schema1下所有未来新建表的操作权限，可按需调整权限类型
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user1;
-- 额外预设schema的使用权限
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO user1;

方案2：事件触发器自动授权（全自动化场景） #

配置事件触发器监听DDL操作，只要检测到schema1被重建、或schema1下新建表，就自动执行赋权操作，全程无需人工介入：

1. 先创建触发执行的授权函数

CREATE OR REPLACE FUNCTION auto_grant_schema1_perms()
RETURNS event_trigger AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'schema1') THEN
    -- 给user1授权schema使用权限
    GRANT USAGE ON SCHEMA schema1 TO user1;
    -- 给user1授权schema下所有表的操作权限，可按需调整权限类型
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema1 TO user1;
  END IF;
END;
$$ LANGUAGE plpgsql;

2. 创建事件触发器绑定上述函数

CREATE EVENT TRIGGER trigger_auto_grant_schema1
ON ddl_command_end
WHEN TAG IN ('CREATE SCHEMA', 'CREATE TABLE', 'ALTER TABLE')
EXECUTE FUNCTION auto_grant_schema1_perms();

方案3：权限备份批量恢复（应急场景） #

如果已经发生了schema删除事故，还没提前配置上述规则，可以提前导出user1的权限脚本备份，恢复schema后直接执行脚本即可完成权限恢复：
执行以下SQL可以直接生成user1在schema1下的所有权限赋权语句，将结果保存为SQL文件备用即可：

SELECT 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO user1;'
FROM information_schema.role_table_grants 
WHERE grantee = 'user1' AND table_schema = 'schema1'
UNION ALL
SELECT 'GRANT USAGE ON SCHEMA schema1 TO user1;';

注意事项 #

• 以上所有配置操作都需要超级用户或schema1的所有者执行，user1本身没有权限修改这类规则
• 可根据实际业务需求调整给user1开放的权限类型，不需要的权限不要额外开放，避免安全风险

内容的提问来源于stack exchange，提问作者Naty Bizz