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