From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to set default privilege for new users to have no access to other databases? |
Date: | 2023-08-12 08:17:08 |
Message-ID: | 20230812081708.7wa3oq24xehval5t@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Dürr Software | 2023-08-12 09:01:18 | Re: PgSQL 15.3: Execution plan not using index as expected |
Previous Message | rob stone | 2023-08-12 02:25:36 | Re: PgSQL 15.3: Execution plan not using index as expected |