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