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:

1. Public schema.
2. All tables in public
3. User can see all info in current tables
4. User can see info in tables that will be created in future
5. User can’t see other databases

What i have now:

1. User don’t have permissions to tables that created in future by service_user. He will have permissions only if table created by super_user.
2. User can create tables, but shouldn’t.
3. He can see other databases names. I wish he didn’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 create 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?