I have a test server running with a few databases for separate apps. I want to make sure all future users won't be able to see what's going on in their neighbours. My idea is to set default connect privileges to deny, and to selectively set connect permission on each database.
The first I tried was to revoke connect (
REVOKE CONNECT ON schema public FROM public) on all databases, but unless I do it for each and every existing (and future) DB, it doesn't work as I want it to.
I then tried to revoke connect privileges on the template DB to the public role (
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC), but it doesn't work either.
I also tried directly revoking connect rights from public role, to fail.
For now I'm creating users with
NOCREATEROLE NOCREATEDB NOSUPERUSER NOINHERIT properties.
I tried suggested solution from various other topics like
But for now I'm completely stuck.