Trouble with only read user in Postgresql 14

Question

I have a cluster where running around 20 postgres databases. I need to create read only user for every db. On this server i have service user e.g. service_user that do automaticly all operations in database like migrations, update, insert and more. And i have super user super_user that have all the privelegies. So i need to create user on every db, that will have permissions to select only in schema public in database he belongs to, and should not see other databses, and would not have permission to create new objects. In my case all entites belongs to service_user.

What i do now - i'm connecting to db with super_user who have all permissions. Than i do this commands:

create user read_only_user with password 'password';
grant select on all tables in schema public to read_only_user;
alter default privileges in schema public grant select on tables to read_only_user;

In my mind this should work perfectly. I expect that he would see only:

Public schema. All tables in public User can see all info in current tables User can see info in tables that will be created in future User can't see other databases What i have:

User don't have permissions in tables that created in future by service_user. He will have permissions only if table created by super_user. User can create tables, but shouldn't. He can see other databases names. I want to make sure he doesn't have that opportunity.
I tried to do grant select: create user read_only_user with password 'password'; grant select on all tables in schema public to read_only_user;

In this case user won't have permissions to select from tables that will be created in the future.

What am i missing and what should i do to create user that will have priveleges to select from all tables in scema public and will not see other databases? And does Postgres provide this feature or should I solve this problem in an exotic way?

Answer 1

I can identify three problems in what you write:

1. ALTER DEFAULT PRIVILEGES doesn't work the way you want.

   That's a frequently asked question. That statement only affects objects created by the user in the FOR ROLE clause, or the current user if you don't specify FOR ROLE. So you will have to run a second ALTER DEFAULT PRIVILEGES statement for service_user.

2. The user can create object in schema public.

   That means that you are using PostgreSQL v14 or lower. Revoke the unsafe default privileges on that schema:

   REVOKE CREATE ON SCHEMA public FROM PUBLIC;
   

3. The user can see other databases.

   That's always the case, and there is nothing you can do to prevent that. Live with it.

   As an alternative, create the other databases in different database clusters.