Re: How to securely isolate databases/users in a multi-tenant Postgresql?

> On 09/06/2023 08:54 CEST Alex Lee <alexxlee133(at)gmail(dot)com> wrote:
>
> I want to make a service that gives each of my users their own PG user and
> database. I want to keep them isolated from each other. There are no special
> extensions installed, it's a pretty vanilla PG cluster.
>
> Are there any considerations beyond making each person their own user and
> owner of their own database like this, and letting them connect to the
> database?
>
> ```
> create user u2745;
> create database d2745 owner u2745;
> -- etc.
> ```

This works but you must revoke the CONNECT privileges on each database from
PUBLIC if you do not restrict connections in pg_hba.conf. By default every
user can connect to any database if allowed by pg_hba.conf. It then depends
on the default privileges granted to PUBLIC what users can do in a database
that is not their database. I would therefore also restrict connections with
pg_hba.conf:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Because you have to manage privileges anyway, it may be easier to use a single
database and define a separate schema for each user and only give him the USAGE
and CREATE privileges on that schema. But be aware of default privileges that
are granted to PUBLIC. That is described in the docs as the secure schema usage
pattern:

https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATTERNS

--
Erik