Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7ZQC-0005Dk-Ln for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 10:32:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q7ZQB-0003nK-JJ for pgsql-general@arkaria.postgresql.org; Fri, 09 Jun 2023 10:32:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q7ZQB-0003n4-7L for pgsql-general@lists.postgresql.org; Fri, 09 Jun 2023 10:32:43 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q7ZQ8-0013ym-Gs for pgsql-general@postgresql.org; Fri, 09 Jun 2023 10:32:42 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [IPv6:2001:67c:2050:b231:465::2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4Qcy81518Mz9sRB; Fri, 9 Jun 2023 12:32:33 +0200 (CEST) Date: Fri, 9 Jun 2023 12:32:33 +0200 (CEST) From: Erik Wienhold To: Alex Lee , pgsql-general@postgresql.org Message-ID: <495144148.178131.1686306753255@office.mailbox.org> In-Reply-To: References: Subject: Re: How to securely isolate databases/users in a multi-tenant Postgresql? MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Qcy81518Mz9sRB List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 09/06/2023 08:54 CEST Alex Lee 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