pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Wienhold <ewie@ewie.name>
To: Alex Lee <alexxlee133@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: How to securely isolate databases/users in a multi-tenant Postgresql?
Date: Fri, 9 Jun 2023 12:32:33 +0200 (CEST)
Message-ID: <495144148.178131.1686306753255@office.mailbox.org> (raw)
In-Reply-To: <CAL2iA8j=pQ3w-foNqFSpQqawuBauRHXTtqqZRk6Ggg9grAAdUg@mail.gmail.com>
References: <CAL2iA8j=pQ3w-foNqFSpQqawuBauRHXTtqqZRk6Ggg9grAAdUg@mail.gmail.com>

> On 09/06/2023 08:54 CEST Alex Lee <alexxlee133@gmail.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





view thread (2+ messages)

Message-ID: <495144148.178131.1686306753255@office.mailbox.org>
Permalink:  ../495144148.178131.1686306753255@office.mailbox.org/
Also on:    postgresql.org/message-id/495144148.178131.1686306753255@office.mailbox.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ewie@ewie.name, alexxlee133@gmail.com
  Subject: Re: How to securely isolate databases/users in a multi-tenant Postgresql?
  In-Reply-To: <495144148.178131.1686306753255@office.mailbox.org>
  References: <CAL2iA8j=pQ3w-foNqFSpQqawuBauRHXTtqqZRk6Ggg9grAAdUg@mail.gmail.com> <495144148.178131.1686306753255@office.mailbox.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox