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

From: Erik Wienhold <ewie(at)ewie(dot)name>
To: Alex Lee <alexxlee133(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: How to securely isolate databases/users in a multi-tenant Postgresql?
Date: 2023-06-09 10:32:33
Message-ID: 495144148.178131.1686306753255@office.mailbox.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Mohsin Kazmi 2023-06-09 10:38:40 Active Active PostgreSQL Solution
Previous Message Wen Yi 2023-06-09 09:55:23 Why lex & yacc think this is a syntax error?