英文:
Create true read only user in PostgreSQL
问题
我已经在Digital Ocean上创建了一个PostgreSQL数据库。现在我需要为其创建一组用户和数据库,我考虑创建多个数据库(production
,staging
等),并为每个数据库创建两个相关的角色,一个具有只读权限,一个具有读写权限(production_ro
,production_rw
,staging_ro
,staging_rw
等)。我的想法是,通过拥有这些角色,我现在可以创建个别用户并分配给他们其中一个角色,以便在发生违规情况时能够快速更改或删除他们。
我已经在这方面进行了研究,我可以找到的所有页面都有类似于此处的一组类似的指令(https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/):
-- 从 'public' 角色中撤销权限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
-- 只读角色
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;
-- 读写角色
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
-- 创建用户
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';
-- 授予权限给用户
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;
我已经仔细按照这些指令进行操作,并监控确保没有失败,但在成功运行它们之后,我发现实际上有了看似只读的用户,他们实际上可以创建表,无法看到其他用户创建的表,并可以切换数据库。
我做错了什么?
--- 编辑 ---
这是 \dn+
命令的结果:
模式列表
名称 | 拥有者 | 访问权限 | 描述
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| 标准公共模式
| | =UC/postgres |
--- 编辑 2 ---
由于安全原因,这是我所做的操作(我会将用户名称编辑为<USER_A>
,<USER_B>
等,这些编辑后的用户名称将与实际用户名称一一对应):
$ psql "postgresql://USER_A:<PASSWORD>@<DOMAIN>:<PORT>/<DEFAULT_DB>?sslmode=require"
psql (15.1, 服务器 14.6)
SSL 连接(协议:TLSv1.3,密码:TLS_AES_256_GCM_SHA384,压缩:关闭)
键入 "help" 获取帮助。
<DEFAULT_DB>=> \connect production
psql (15.1, 服务器 14.6)
SSL 连接(协议:TLSv1.3,密码:TLS_AES_256_GCM_SHA384,压缩:关闭)
您现在以用户 "USER_A" 连接到数据库 "production"。
production=> \du
角色列表
角色名称 | 属性 | 成员
-----------------+-----------------------------------------------------+---------------------------------------------------------------------------
USER_B | 超级用户,复制 | {}
USER_A | 创建角色,创建数据库,复制,绕过RLS | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,r_production_ro}
postgres | 超级用户,创建角色,创建数据库,复制,绕过RLS | {}
production_ro | 无法登录 | {}
production=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
警告: 无法撤销 "public" 的权限
REVOKE
production=>
--- 编辑 3 ---
已联系DigitalOcean,以下是他们的回应:
只是想让您知道我们正在调查此问题,目前我能够重现此行为。似乎为了从用户中删除创建表的权限,我们需要“REVOKE CREATE ON SCHEMA public FROM PUBLIC;”,但由于doadmin用户不是超级用户,因此不允许撤销此权限,否则会影响其他角色。
英文:
I've spanned a PostgreSQL database in Digital Ocean. I now need to come with a set of users and databases for which I've thought on creating several databases (production
, staging
, etc) and having 2 associated roles for each database with read-only and read-write permissions (production_ro
, production_rw
, staging_ro
, staging_rw
, etc). My idea is that, by having those roles, I can now create individual users and assign them one of the roles so that I can quickly change/remove them in case of a breach.
I've been researching on this and all pages I can find have a set of instructions similar to the ones in here:
-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;
-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';
-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;
I've carefully followed those instructions and monitored that none of them failed but, after successfully running them all, I'm left with supposedly read-only users that can, in fact, create tables, not see the tables created by other users, and switch databases.
What am I doing wrong?
--- Edit ---
This is the result of the \dn+
command:
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
--- Edit 2 ---
Here is what I do (for security reasons, I'll redact the users as <USER_A>
, <USER_B>
, etc. those redacted users will match 1 to 1 to the real ones):
$ psql "postgresql://USER_A:<PASSWORD>@<DOMAIN>:<PORT>/<DEFAULT_DB>?sslmode=require"
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
<DEFAULT_DB>=> \connect production
psql (15.1, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "production" as user "USER_A"
production=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+---------------------------------------------------------------------------
USER_B | Superuser, Replication | {}
USER_A | Create role, Create DB, Replication, Bypass RLS | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,r_production_ro}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
production_ro | Cannot login | {}
production=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
WARNING: no privileges could be revoked for "public"
REVOKE
production=>
--- Edit 3 ---
Got in contact with DigitalOcean. This is their response:
> Just to let you know that we are investigating this issue, so far I
> was able to reproduce the behavior. It seems that in order to remove
> the create table from the public schema from a user we would need to
> "REVOKE CREATE ON SCHEMA public FROM PUBLIC;" which is not allowed as
> the doadmin use is not a superuser and revoking this privilege would
> impact other roles.
答案1
得分: 1
如果用户可以创建表格,那么它在相关模式上具有CREATE
权限。在psql
中使用\dn+ public
查看这些权限。识别相关权限并REVOKE
它们。
或者,如果您使用的是 PostgreSQL v15 或更高版本,可能您的数据库用户直接或间接是预定义角色pg_write_all_data
的成员。撤销该成员关系。
在您的具体情况下,正确的做法是从PUBLIC
,即所有人,撤销模式public
上的默认CREATE
权限:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
您说您已经这样做了,但它引发了警告并没有产生效果。这不是 PostgreSQL 的正常行为。您需要咨询修改了 PostgreSQL 的人,也就是在您的情况下是 DigitalOcean。
英文:
If the user can create tables it has CREATE
permissions on the schema in question. Look at those permissions with \dn+ public
in psql
. Identify the permissions in question and REVOKE
them.
Alternatively, if you use PostgreSQL v15 or above, it micht be that your database user is directly or indirectly a member of the predefiled role pg_write_all_data
. Revoke that membership.
In your specific case, the correct thing to do is to revoke the default CREATE
privilege on schema public
from PUBLIC
, that is, everyone:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
You say you already did that, and it caused a warning and had no effect. That is not what PostgreSQL would do. You'll have to ask the people who modified PostgreSQL, in your case DigitalOcean.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论