如何使用 SELECT、INSERT、UPDATE、... [DML] 访问在 PostgreSQL 中创建用户 [英] How to create a User in PostgreSQL with SELECT, INSERT, UPDATE,... [DML] access

问题描述

我正在尝试在 PostgreSQL 中创建两个用户

I'm trying to create two users in PostgreSQL

1. Who(user1) 可以访问 db1 中的表，并且应该能够运行 (DML) SELECT、INSERT、UPDATE...

1. Who(user1) can access the tables in db1 and should be able to run (DML) SELECT, INSERT, UPDATE...

Who(user2) 只能在特定数据库中创建表(EX: db1)

Who(user2) can only create the tables in a particular database(EX: db1)

我尝试过的命令如下所示，但是当我使用 user2 创建新表时，user1 无法选择/插入表.

Commands I tried are like below, but when I create a new table using user2, user1 not able to select/insert on tables.

用户 1:

grant CONNECT ON DATABASE db1 to user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public grant SELECT, INSERT, UPDATE, DELETE ON TABLES to user1;

用户 2:

GRANT CREATE ON SCHEMA public TO user2;
grant CONNECT ON DATABASE db1 to user2;
revoke SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public from user2;

有人可以帮助我在这里缺少什么

Can someone help me what I'm missing here

谢谢

推荐答案

您需要更改 user1 创建的对象的默认权限.这是通过选项 for role 作为 alter default privileges 语句的一部分来完成的.

You need to change the default privileges for objects created by user1. This is done by the option for role as part of the alter default privileges statement.

alter default privileges 
   for role user2 ---<< this
   IN SCHEMA public 
   grant SELECT, INSERT, UPDATE, DELETE ON TABLES to user1;

如果您不指定该选项，默认值将应用于用户运行(ALTER)语句创建的对象.

If you don't specify that option, the defaults are applied to objects created by the user running the (ALTER) statement.

这篇关于如何使用 SELECT、INSERT、UPDATE、... [DML] 访问在 PostgreSQL 中创建用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！