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 —
