JIYIK CN >

When developing applications, we usually use database management systems such as PostgreSQL, MySQL, MongoDB, etc. to record application data.

Docker helps us run an instance of these application database management systems. This helps save time and computer storage space as no DBMS is required on the computer.

Each database created from these DBMS has users with different permissions on the database. In this article, we will learn different ways to create database users using Docker Postgres.

Create a new project

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

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

Define DDL for users and databases

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

Since we are going to create an SQL query, we will use a file with a .sql extension to define our query. Create a file called db-config.sql in the current folder of 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;

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

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

Define a Dockerfile for the image

Create a file called Dockerfile in your current folder and copy and paste the following instructions into it.

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

1. FROM - defines the base image on which the custom image is created with subsequent instructions. In this case, we used alpine, which helps us optimize storage as it is a lightweight version of PostgreSQL.
2. 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.

Building an Image

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

~/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

Running the container

Using the same terminal window, run the PostgreSQL container named postgres-container using the following command.

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

Output:

3b8e0f85c2b4ef4b1aa28e2bad169ae796751331580af6fbba251a1c05aa4fca

请注意, we used an environment variable called POSTGRES_PASSWORD in the run command. When running PostgreSQL, we can pass several environment variables to provide superuser details, including database and username.

The POSTGRES_PASSWORD environment variable is required to provide the superuser password. The default password for PostgreSQL is postgres.

Other environment variables that can be passed are optional and 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, the file is executed behind the scenes by the super user.

Connecting to the container

We need to connect to the container using an interactive shell so that we can log in to PostgreSQL using the new user and database. To do this, connect to the container using the following command.

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

Output:

bash-5.1#

Log in to PostgreSQL

Once we have access to the container’s shell, log in to the employee_database as user doe using the following command.

bash-5.1# psql -d employee_database -U doe

Output:

psql (15.1)
Type "help" for help.

employee_database=>

Creating a user database without a script file

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

There is an easier way to do this without writing any script files. This method uses the environment variables mentioned in the previous section to define the details of the 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 ones 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 in the previous sections, from building the image, running the container, connecting to the container, and logging in to PostgreSQL.

Use the same command for all steps. However, make sure to stop and remove the existing container.

You can also choose to create new images and containers.

Summarize

In this article, we learned two methods that can be used to create a database user with Docker Postgres. The first method creates the user by adding a SQL script to /docker-entrypoint-initdb.d/ and the file is executed during initdb initialization.

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