How to Create a Database User Using Docker Postgres

1. Create a New Project
2. Define DDL for the User and Database
3. Define a Dockerfile for the Image
4. Build an Image
5. Run a Container
6. Connect to the Container
7. Login to PostgreSQL
8. Create a User Database Without a Script File
9. Conclusion

When developing applications, we usually use database management systems such as PostgreSQL, MySQL, MongoDB, and others to record the applications’ data.

Docker helps us run an instance of these application database management systems. This helps save time and computer storage because a DBMS is not needed on the computer.

Every database created from these DBMS has users that have different authorities on the database. In this tutorial, we will learn the different approaches to creating a database user using Docker Postgres.

Create a New Project

For this tutorial, we will use WebStorm IDE but feel free to use any development environment. Open WebStorm IDE and select File > New > Project to create a new project.

Select the Empty project option and on the window that opens, change the project name from untitled to initdb.d or use any name preferred.

Define DDL for the User and Database

To add additional initialization to our custom image using a PostgreSQL base image, we need to add *.sql, *.sql.gz, or *.sh under the folder /docker-entrypoint-initdb.d.

Since we want to create an SQL query, we will define our query using the file with the extension .sql. Create a file named db-config.sql under the current folder in our project and copy and paste the following SQL instructions into the file.

CREATE USER doe;
CREATE DATABASE employee_database;
GRANT ALL PRIVILEGES ON DATABASE employee_database TO doe;

The db-config.sql will be run after the entry point calls initdb to create the default Postgres user and database.

Note that the scripts in /docker-entrypoint-initdb.d are only run if the data directory is empty. This means that during startup, any database run before this will not be changed.

Define a Dockerfile for the Image

Create a file named Dockerfile under the current folder and copy and paste the following instructions into the file.

FROM postgres:15.1-alpine
COPY db-config.sql /docker-entrypoint-initdb.d/

FROM - Defines the base image on which to create a custom image using the subsequent instructions. In this case, we have used alpine, which helps us to optimize storage as it is a lightweight version of PostgreSQL.

COPY - Copies files and folders from the host to the image file system. In this case, we have copied the db-config.sql file to the /docker-entrypoint-initdb.d/ folder.

Build an Image

Open a new terminal window using the keyboard shortcut ALTF12 on your keyboard and use the following command to build an image with the tag postgres-image.

~/WebstormProjects/initdb.d$ docker build --tag postgres-image:latest .

Output:

 => [1/2] FROM docker.io/library/postgres:15.1-alpine@sha256:cc663286e63810373bdfc91a5ed24b772447fb5282d  0.0s
 => CACHED [2/2] COPY db-config.sql /docker-entrypoint-initdb.d/                                          0.0s
 => exporting to image                                                                                    0.4s
 => => exporting layers                                                                                   0.0s
 => => writing image sha256:fd33d80c880452dcb25de1d8f7d6415eeb874039bdab176cc3d3fe1c910ebcbc              0.1s
 => => naming to docker.io/library/postgres-image:latest

Run a Container

Using the same terminal window, use the following command to run a PostgreSQL container with the name postgres-container.

~/WebstormProjects/initdb.d$ docker run --name postgres-container -e POSTGRES_PASSWORD=postgres -d postgres-image

Output:

3b8e0f85c2b4ef4b1aa28e2bad169ae796751331580af6fbba251a1c05aa4fca

Note that we have used an environment variable named POSTGRES_PASSWORD in the run command. When running a PostgreSQL, we can pass several environment variables to provide superuser details, including the database and username.

The POSTGRES_PASSWORD environment variable is a required variable that provides the superuser password. The default password for PostgreSQL is postgres.

Other environment variables that can be passed are optional, which include POSTGRES_USER, POSTGRES_DB, and POSTGRES_INITDB_ARGS, among others.

Since we are running the container in detached mode, we cannot see the execution of the db-config.sql file. However, this file gets executed behind the scenes by the superuser.

Connect to the Container

We need to connect to the container using an interactive shell so that we can be able to login into PostgreSQL using the new user and database. To achieve this, use the following command to connect to the container.

~/WebstormProjects/initdb.d$ docker exec -it postgres-container bash

Output:

bash-5.1#

Login to PostgreSQL

Once we have access to the containers’ shell, use the following command to log in to the employee_database as user doe.

bash-5.1# psql -d employee_database -U doe

Output:

psql (15.1)
Type "help" for help.

employee_database=>

Create a User Database Without a Script File

In the previous section, we learned how to create a database user in Docker Postgres by adding an SQL containing the DDL to /docker-entrypoint-initdb.d/.

There is an easier approach to doing this without writing any script files. This approach uses the environment variables mentioned in the previous section to define the details of a new user.

These environment variables are added to the Dockerfile using the ENV instruction and will be available to the container. To see this in action, replace the instructions in the Dockerfile with the instructions provided below.

FROM postgres:15.1-alpine
ENV POSTGRES_USER=doe
ENV POSTGRES_DB=employee_database

After adding the environment variables, repeat all the steps covered in the previous sections, from building an image, running a container, connecting to the container, and logging in to PostgreSQL.

Use the same command for all the steps. However, ensure you stop and remove the existing container.

You can also choose to create a new image and container.

Conclusion

In this tutorial, we have learned two approaches that we can use to create a database user with Docker Postgres. The first approach created a user by adding an SQL script to /docker-entrypoint-initdb.d/, and this file got executed during the initialization of initdb.

In the second approach, we have defined the user details in the Dockerfile by leveraging the Docker Postgres environment variables.