How to set default privilege for new users to have no access to other databases?

From: Erik Nelson <erik(at)nsk(dot)io>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to set default privilege for new users to have no access to other databases?
Date: 2023-08-09 18:35:40
Message-ID: CA+DbKDr3GRZff9sbKuNSRpSmkrH-G+YWF1CzfNMXzvBmN3dhCg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a lab with a database that I would like to use as a "multi-tenant"
database, in that I would like to create a database for each of the
applications that I'm running and segregate access so that user foo and
user bar cannot see anything about their neighbors. I'm somewhat surprised
to discover that any new user, *by default*, has the ability to list
databases, connect to them, and list their tables.

My understanding is that this ability is inherited from the public role
(could use confirmation of this)? I can think of two potential options, one
being more desirable:

- I know I can revoke CONNECT from an explicit database, but this
requires that I specify the database. I want to revoke this for all
current, *and future* databases as the default privilege.
- I could potentially create users with the NOINHERIT attribute
(assuming this ability is inherited from public), but I don't think that's
ideal because a new user could easily be created without this, and it would
have access.

Is this not something to be concerned about because even if a user connects
to a database, they can't really do anything inside of it?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ashok Patil 2023-08-10 05:11:37 Query regarding certificate authentication in postgres
Previous Message Stephen Frost 2023-08-09 15:43:42 Re: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored