如何在Postgres中对所有新表授予用户权限? [英] How to make permissions to a user on all new tables in Postgres?

问题描述

假设，我在Postgres 9.6中有两个用户，分别是user1和user2.我想在user1创建的任何新表上授予user2的权限，而无需为创建的表添加额外的显式授予命令.换句话说，我要使以下命令正常工作:

Supposing, I have two users in Postgres 9.6 named user1 and user2. I want to make permissions to user2 on any new table created by user1 without extra explicit grant commands for the created table. In other words, I want to make the following commands work correctly:

1. 授予user2的权限(我不知道这里应该是什么！)
2. CREATE TABLE t1 (id int)(在user1下)
3. INSERT INTO t1 VALUES (5)(在user2下)

1. Giving permissions to user2 (I don't know what should be here!)
2. CREATE TABLE t1 (id int) (under user1)
3. INSERT INTO t1 VALUES (5) (under user2)

请注意，在步骤2和3之间，我不应该设置任何权限.

Please, notice, I am not supposed to make any permissions between step 2 and 3.

在第3步中，我总是出现错误:

On step 3 I always have the error:

permission denied for relation t1

我尝试过:

GRANT ALL PRIVILEGES ON DATABASE test TO user2;

和

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user2;

没有成功.

我也尝试过:

GRANT user1 TO user2

它帮助了.但是我不能将其视为解决方案，因为user1可能具有我不想与user2共享的权限(例如，可能是postgres).

and it helped. But I can't consider it as the solution because user1 may have too high permissions (for example, it can be postgres) that I don't want to share with user2.

推荐答案

您可以通过创建默认权限:

ALTER DEFAULT PRIVILEGES
FOR USER user1
GRANT ALL ON TABLES TO user2

这篇关于如何在Postgres中对所有新表授予用户权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！