PostgreSQL应用用户无法访问迁移创建的表问题排查

问题根源 #

你设置的ALTER DEFAULT PRIVILEGES是针对postgres用户创建的对象生效的，但实际创建表（simple_entity、flyway_schema_history）的是flyway_migration用户，因此默认权限并未应用到这些表上，导致spring_application用户没有访问权限。

修复步骤 #

1. 修改数据库初始化脚本，设置正确的默认权限 #

更新初始化脚本中的默认权限配置，指定为flyway_migration用户创建对象时自动授权给spring_application：

CREATE DATABASE hot_update;

CREATE USER flyway_migration WITH PASSWORD 'flyway_secret';
CREATE USER spring_application WITH PASSWORD 'spring_secret';

\c hot_update

DROP SCHEMA public;

CREATE SCHEMA application_schema AUTHORIZATION spring_application;
ALTER ROLE spring_application SET search_path = application_schema;

GRANT USAGE, CREATE ON SCHEMA application_schema TO flyway_migration;
GRANT TEMPORARY ON DATABASE hot_update TO spring_application;

GRANT USAGE ON SCHEMA application_schema TO spring_application;

-- 关键修改：为flyway_migration用户在schema下创建的表设置默认授权
ALTER DEFAULT PRIVILEGES FOR ROLE flyway_migration
  IN SCHEMA application_schema
  GRANT ALL PRIVILEGES ON TABLES TO spring_application;

2. 为已创建的表手动补全权限 #

如果不想重新初始化数据库，可直接在PostgreSQL中执行以下SQL，为现有表授予权限：

GRANT ALL PRIVILEGES ON TABLE application_schema.simple_entity TO spring_application;
-- 若应用无需访问flyway_schema_history，可跳过这条
GRANT ALL PRIVILEGES ON TABLE application_schema.flyway_schema_history TO spring_application;

3. 验证权限 #

重新启动容器或执行授权后，用spring_application用户登录psql验证：

psql -U spring_application -h localhost -d hot_update

执行查询：

SELECT * FROM simple_entity;

此时应能正常访问表数据。

补充说明 #

• ALTER DEFAULT PRIVILEGES的作用范围是设置该权限的用户（或指定的FOR ROLE用户）创建的对象，必须明确指定创建表的用户，才能让后续创建的表自动继承权限。
• 应用用户spring_application拥有表的全部操作权限，但没有schema的修改权限（因为未授予CREATE权限），符合你"可使用表但无法修改schema"的需求。

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