Обсуждение: How to set default privilege for new users to have no access to other databases?

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?

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)?

On 2023-08-09 14:35:40 -0400, Erik Nelson wrote:
> 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.

New databases are created as copies of a template database (template1 by
default). You can either alter template1 to your liking or create a new
template database and use that for creating your new databases (the
latter is especially useful if you need several different templates).

You could also use pg_hba.conf to restrict or grant access to specific
databases. This would probably mean that you would have to add a line to
pg_hba.conf each time you create a database.

And of course if you use the same database schema for several
applications you probably already have a script to set up a database.
Adding one or more REVOKE and/or GRANT statements to such a script would
seem to be a rather obvious way to do it.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"