From: | Graham Leggett <minfrin(at)sharp(dot)fm> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Grant read-only access to exactly one database amongst many |
Date: | 2024-02-05 00:03:50 |
Message-ID: | A75E96A2-13D8-441A-8808-2FF7C4349B65@sharp.fm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi all,
I have a postgresql 15 instance with two databases in it, and I have a need to grant read-only access to one of those databases to a given user.
To do this I created a dedicated role for readonly access to the database db1:
CREATE ROLE "dv_read_db1"
GRANT CONNECT ON DATABASE db1 TO dv_read_db1
GRANT USAGE ON SCHEMA public TO “dv_read_db1"
GRANT SELECT ON ALL TABLES IN SCHEMA public TO “dv_read_db1"
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO “dv_read_db1"
CREATE USER minfrin LOGIN;
GRANT dv_read_db1 TO minfrin;
On the surface this works, I get readonly access to db1.
Trouble is, I can create tables in db1 which is write access. I can also connect to db2 (bad), and I can enumerate the tables in db2 (bad), although the queries of the contents say access is denied.
I appears the mechanism I am using above has insecure side effects.
What is the way to grant read only access to a single database, without exposing other databases, and being futureproof against future features offering potential write access to a read only user?
Regards,
Graham
—
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2024-02-05 00:11:48 | Re: to_regtype() Raises Error |
Previous Message | jian he | 2024-02-05 00:00:00 | Re: [PATCH] ltree hash functions |