Postgres:为不同角色/用户创建的未来表授予对角色/用户的访问权限 [英] Postgres: granting access to a role/user for future tables created by a different role/user

问题描述

我正在构建一个 Spring Boot 应用程序.Flyway 数据库迁移在应用程序启动时执行.

I'm building a spring boot application. Flyway database migrations are executed at the application startup.

我决定使用两个不同的角色:role__app(read/write 对表的权限，app 架构中的序列)和 role__migration(应用程序/迁移架构中的高级权限).

I decided to use two different roles: role__app (read/write rights on tables, sequences in app schema) and role__migration (advanced rights in app/migration schemas).

Flyway 迁移在 role__migration 下执行，因此它成为创建对象的所有者.我认为以下陈述会有所帮助:

Flyway migrations are executed under role__migration so it becomes the owner of the created objects. I thought that the following statements would help:

ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role__app;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT USAGE ON SEQUENCES TO role__app;

但是当新表添加到 app 架构时，user__app(属于 role__app)无法访问表格.

But when the new tables are added to the app schema the user__app (belongs to the role__app) doesn't have access to the tables.

是否可以通过 Postgres 或任何其他方式维持这样的流程(通过应用程序、迁移用户/角色)?

Is it possible to maintain such a flow (with app, migrattion users/roles) by Postgres or by any other means?

作为旁注，我应该提到我在目标数据库上运行以下语句:

As a side note I should mention that I run the following statements on the target database:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE myDb FROM PUBLIC;

<小时>

更新 1

---

Update 1

我添加了 FOR ROLE 子句，但我仍然收到用户 app.property) 的权限被拒绝消息>user__app.表的所​​有者是 user__mig.

I added the FOR ROLE clause, yet I'm still getting the permission denied message for a created table (app.property) in app schema for user user__app. The owner of the table is user__mig.

更新 2

在 dbeaver 中以 postgres 用户身份登录后，我们可以看到 user__mig 已勾选所有必要的权限，而 user__app 对 app.property<没有任何权限/strong> 表:

After logging in as postgres user in dbeaver we can see that user__mig has all necessary permissions ticked whereas the user__app has no permissions on the app.property table at all:

这里是重现问题的要点:https://gist.github.com/happygrizzly/849a6a791f028ba5b191f73180ae35d1

Here is a gist to reproduce the problem: https://gist.github.com/happygrizzly/849a6a791f028ba5b191f73180ae35d1

推荐答案

你应该写

ALTER DEFAULT PRIVILEGES FOR USER role__migration ...

如果省略 FOR USER 子句，则权限仅授予运行 ALTER DEFAULT PRIVILEGES 的用户创建的对象.

If you omit the FOR USER clause, the privileges are only granted on objects created by the user who ran ALTER DEFAULT PRIVILEGES.

使用上面的语句，当role__migration 创建一个对象时，权限被授予.这不扩展到角色role__migration的成员.

With the above statement, the privileges are granted when role__migration creates an object. That does not extend to members of the role role__migration.

这篇关于Postgres:为不同角色/用户创建的未来表授予对角色/用户的访问权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！