This short article guides you through the process of creating users with their own dedicated databases on PostgreSQL.

We will:

1. Establish a dedicated user for the database.
2. Create a database specifically for the designated user.
3. Implement access restrictions to ensure that only the owner can access the database.

Use Case

A common use case involves deploying a microservice that manages its own data and therefore maintains its own database.

This approach is employed to prevent unauthorized data manipulation by other services and to adhere to the principle of least privilege (PoLP), a fundamental principle in software security that advocates for granting users only the permissions necessary to perform their tasks.

Spinning up PostgreSQL locally

We'll be utilizing psql, a command-line interface (CLI) for interacting with PostgreSQL, in the following steps. In production environments, it's uncommon that you manage PostgreSQL on your own. Instead, you'll typically utilize a provider like Supabase, Managed PostgreSQL on Digital Ocean, CloudSQL on GCP, or RDS on AWS to name a few. Regardless of the chosen provider, the underlying cluster remains hosted and managed by them.

While the provider takes care of the cluster's infrastructure, you still retain responsibility for its management. This involves utilizing tools like psql to connect to the cluster and execute the necessary management tasks.

To emulate a hosting provider and simplify the process, we will establish a PostgreSQL instance locally within a container and connect to it.

docker run --name my-postgres -e POSTGRES_PASSWORD=supersecret -d postgres:16

After starting PostgreSQL, we can enter the container via:

docker exec -it my-postgres bash

Thankfully, the PostgreSQL base image on Docker Hub includes psql by default. This means we can easily connect to our local database using the following command:

psql -h localhost -U postgres

A command prompt starting with postgres=# should appear, indicating that we are ready for the next steps.

Creating the Dedicated Database User

Before creating the actual database, the first step involves creating the user for our service via the following command:

CREATE USER trinity WITH ENCRYPTED PASSWORD 'i<3neo' NOSUPERUSER NOCREATEDB NOCREATEROLE;

COMMENT ON ROLE trinity IS 'She really loves Neo!';

In this step, we have created a new user named trinity with the password i<3neo. This user is not granted superuser privileges (admin access) and is restricted from creating databases or additional users. To enhance clarity, we have added a comment to the user role.

Following the user creation, executing the `\du+`` command will list all existing users on the PostgreSQL instance:

List of roles
 Role name |                         Attributes                         |      Description      
-----------+------------------------------------------------------------+-----------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | 
 trinity   |                                                            | She really loves Neo!

Creating the User's Dedicated Database

In the next step, we will create the actual database and assign ownership to the previously created user:

CREATE DATABASE trinitydb WITH OWNER = 'trinity' ENCODING = 'UTF-8';

This statement create a new database named trinitydb and assigns ownership to the user trinity. The database encoding is set to UTF-8 for optimal character representation. Please adapt the encoding to suit the specific needs of your service.

Executing the \l (short for \list) command should produce the following output:

List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 trinitydb | trinity  | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
(4 rows)

Restricting Database Access to the Owner

Our task is not yet complete. As you can observe in the previous output, the Access privileges column currently displays an empty state. This implies that:

The database trinitydb is accessible to every user on the PostgreSQL instance.

This is not our intended outcome! Therefore, we must restrict access to this database solely to the owner user by executing the following commands:

REVOKE ALL PRIVILEGES ON DATABASE trinitydb FROM PUBLIC;

GRANT ALL PRIVILEGES ON DATABASE trinitydb TO 'trinity';

Summary

This article has equipped you with the knowledge and skills to create dedicated databases for users on PostgreSQL, ensuring data isolation and adhering to the principle of least privilege (PoLP) for enhanced security.

Here's a concise summary of the steps for your own cheat sheet:

-- Establish a dedicated user named `trinity``.
CREATE USER trinity WITH ENCRYPTED PASSWORD 'i<3neo' NOSUPERUSER NOCREATEDB NOCREATEROLE;
COMMENT ON ROLE trinity IS 'She really loves Neo!';

-- Establish a dedicated database named `trinitydb` and assign ownership to the user `trinity`.
CREATE DATABASE trinitydb WITH OWNER = 'trinity' ENCODING = 'UTF-8';

-- Implement access control to restrict database access solely to the owner, preventing unauthorized access from other users.
REVOKE ALL PRIVILEGES ON DATABASE trinitydb FROM PUBLIC;
GRANT ALL PRIVILEGES ON DATABASE trinitydb TO 'trinity';