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

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!"