This short article guides you through the process of creating users with their own dedicated databases on PostgreSQL.
- Establish a dedicated user for the database.
- Create a database specifically for the designated user.
- Implement access restrictions to ensure that only the owner can access the database.
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 rolesRole 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.
\l (short for
\list) command should produce the following output:
List of databasesName | 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/postgrestemplate1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +| | | | | | | | postgres=CTc/postgrestrinitydb | 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:
trinitydbis 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';
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';
I hope you found this article beneficial and gained valuable insights for your journey. If you wish to support my work, please follow me on X: @ItsAndreKoenig.