How do I add a postgres database and user from a docker container using docker-compose?

I am trying to create a devcontainer that uses docker files for two processes.

One is a nodejs server - it works

One is a postgres DB - it is not creating the user or the table

This is the error I am getting:

2025-03-18 21:54:00.493 UTC [62] FATAL:  role "postgres" does not exist
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" 

failed: FATAL:  role "postgres" does not exist

The server IS running ... and on the port that it says it can't find:

2025-03-18 21:54:00.813 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

Here are the details

.Env file

I have a .env file that contains the following and these environment variables are loaded by the docker files

POSTGRES_SUPERPW="super_pw"
POSTGRES_USER="rp_user"
POSTGRES_PASSWORD="rp_pw"
POSTGRES_HOST="localhost"
POSTGRES_PORT=5432
POSTGRES_DB="rp_db"

Docker-compose file

I am using a docker compose file, here is the contents

version: '3.8'

services:
  api_service:
    build:
      context: ..
      dockerfile: Dockerfile
    image: api_service
    container_name: api_service_container
    volumes:
      - .:/usr/src/app
      - ./../.env:/usr/src/app/.env 
    ports:
      - '5010:5010' # Exposes app port
      - '9229:9229' # Exposes debugging port

    # this will prevent docker compose from starting the service up and let me debug it
    command: sleep infinity
    env_file:
      - ../.env # Load environment variables !!
    depends_on:
      - postgres

  postgres:
    build:
      context: .
      dockerfile: Dockerfile.postgres
    image: postgres:15
    container_name: postgres_container
    restart: always
    env_file:
      - ../.env # Load environment variables !!
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - '5432:5432'

volumes:
  postgres_data:

Dockerfile

here is the Dockerfile for the nodejs

FROM node:18
WORKDIR /usr/src/app
COPY package*.json ./
RUN npm install
COPY . .
RUN npm install --only=development
RUN npm install dotenv
ENV DOCKER_ENV=true
EXPOSE 5010
EXPOSE 9229
CMD ["node", "--inspect=0.0.0.0:9229", "index.js"]

Dockerfile.postgres

Here is the Dockerfile.postgres contents:

FROM postgres:15
RUN apt-get update && apt-get install -y \
    build-essential \
    git \
    postgresql-server-dev-all \
    && rm -rf /var/lib/apt/lists

# Add custom initialization scripts
COPY init.db.sh /docker-entrypoint-initdb.d/
COPY init.db.users.sh /docker-entrypoint-initdb.d/

# this changes the authentication scheme of how the user can log in (to md5)
COPY pg_hba.conf /docker-entrypoint-initdb.d/pg_hba.conf

# Expose the port the db runs on
EXPOSE 5432

Scripts

this was done on both scripts

 chmod +x init.db.sh
 chmod +x init.db.users.sh

Here is the init.db.sh content:

#!/bin/bash
set -e

# Add logging for debugging
echo "Initializing database setup..."

# Ensure the 'postgres' role exists
echo "Checking if the 'postgres' role exists..."
psql -v ON_ERROR_STOP=1 --username "postgres" <<-EOSQL
    DO \$\$ BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'postgres') THEN
            CREATE ROLE postgres WITH LOGIN PASSWORD '$POSTGRES_SUPERPW';
            ALTER ROLE postgres CREATEDB;
            -- Log role creation
            RAISE NOTICE 'Role "postgres" created.';
        ELSE
            -- Log role already exists
            RAISE NOTICE 'Role "postgres" already exists.';
        END IF;
    END \$\$;
EOSQL

# Now, create the user and database as specified
echo "Creating database and user..."
psql -v ON_ERROR_STOP=1 --username "postgres" <<-EOSQL
    CREATE ROLE $POSTGRES_USER WITH LOGIN PASSWORD '$POSTGRES_PASSWORD';
    ALTER ROLE $POSTGRES_USER CREATEDB;
    CREATE DATABASE $POSTGRES_DB OWNER $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON DATABASE $POSTGRES_DB TO $POSTGRES_USER;
    -- Enable the uuid-ossp extension
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    -- Log creation
    RAISE NOTICE 'Database and user created: $POSTGRES_DB, $POSTGRES_USER';
EOSQL

echo "Initialization complete."

Here is the init.db.users.sh contents

#!/bin/bash
set -e

# Use the PostgreSQL environment variables to create the user and database
psql -v ON_ERROR_STOP=1 --username "postgres" <<-EOSQL
    -- Create the users table
    CREATE TABLE IF NOT EXISTS users (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      username VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL
    );
EOSQL

here is the pg_hba.conf

# pg_hba.conf - Custom configuration for PostgreSQL authentication

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

# Allow external connections (md5 authentication)
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5


# This means that any user connecting remotely is expected to authenticate using the scram-sha-256 password method.
# If devuser has not been configured to use scram-sha-256, or if the password is not correct, the authentication will fail
# host all all all scram-sha-256

# This will allow clients to connect using md5-encrypted passwords
host all all all md5

# end of file

Logs and comments

What is strange is the postgres service starts in it's docker container:

docker ps

769e52b3dfa3   postgres:15   "docker-entrypoint.s…"   19 seconds ago   Up 17 seconds   0.0.0.0:5432->5432/tcp                           postgres_container

when I get the logs ( docker logs 769e52b3dfa3 ) from the container I see this:

creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2025-03-18 21:54:00.295 UTC [48] LOG:  starting PostgreSQL 15.12 (Debian 15.12-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2025-03-18 21:54:00.298 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-03-18 21:54:00.302 UTC [51] LOG:  database system was shut down at 2025-03-18 21:54:00 UTC
2025-03-18 21:54:00.307 UTC [48] LOG:  database system is ready to accept connections
 done
server started

then it tries to run my script and I see this: ( I know my script is running because I put in code to log Initializing database setup... and Checking if the 'postgres' role exists... )

CREATE DATABASE

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.db.sh
Initializing database setup...
Checking if the 'postgres' role exists...
2025-03-18 21:54:00.493 UTC [62] FATAL:  role "postgres" does not exist
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

PostgreSQL Database directory appears to contain a database; Skipping initialization

Then it spits out this:

2025-03-18 21:54:00.806 UTC [1] LOG:  starting PostgreSQL 15.12 (Debian 15.12-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2025-03-18 21:54:00.809 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-03-18 21:54:00.809 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2025-03-18 21:54:00.813 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-03-18 21:54:00.816 UTC [28] LOG:  database system was interrupted; last known up at 2025-03-18 21:54:00 UTC
2025-03-18 21:54:00.895 UTC [28] LOG:  database system was not properly shut down; automatic recovery in progress
2025-03-18 21:54:00.896 UTC [28] LOG:  redo starts at 0/1501470
2025-03-18 21:54:00.911 UTC [28] LOG:  invalid record length at 0/1920C08: wanted 24, got 0
2025-03-18 21:54:00.911 UTC [28] LOG:  redo done at 0/1920BC0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-03-18 21:54:00.915 UTC [26] LOG:  checkpoint starting: end-of-recovery immediate wait
2025-03-18 21:54:00.954 UTC [26] LOG:  checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.012 s, sync=0.020 s, total=0.040 s; sync files=301, longest=0.008 s, average=0.001 s; distance=4222 kB, estimate=4222 kB
2025-03-18 21:54:00.957 UTC [1] LOG:  database system is ready to accept connections

I have deleted ALL the containers, images and volumes from docker and rebuilt the container ... with no success.

Why can't I create a table and a user ?

Solution

Project

The settings provided by the postgres image are sufficient. You do not need to create a Dockerfile and init.db.sh or init.db.users.sh to create user accounts, passwords, and databases.
In addition, if you need to create database tables and data, you can also add the data through init.sql.

To simplify verification, only the part about Porstgresql is provided below.

├── docker-compose.yml
└── init.sql

docker-compose.yml

version: '3.8'

services:
  postgres:
    image: postgres:15
    container_name: postgres_container
    environment:
      POSTGRES_USER: rp_user
      POSTGRES_PASSWORD: rp_pw
      POSTGRES_DB: rp_db
      POSTGRES_ROOT_USER: root
      POSTGRES_ROOT_PASSWORD: root_pw
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    restart: always

volumes:
  postgres_data:

init.sql

-- Create Table
CREATE TABLE IF NOT EXISTS STUDENTS (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade VARCHAR(10),
    email VARCHAR(100),
    enrollment_date DATE
);

-- Insert sample data
INSERT INTO STUDENTS (name, age, grade, email, enrollment_date)
VALUES
('PostgreSQL Alice', 20, 'A', 'postgresql.alice@example.com', '2023-01-15'),
('PostgreSQL Bob', 22, 'B', 'postgresql.bob@example.com', '2023-02-10'),
('PostgreSQL Charlie', 21, 'A', 'postgresql.charlie@example.com', '2023-03-12'),
('PostgreSQL David', 23, 'C', 'postgresql.david@example.com', '2023-04-08'),
('PostgreSQL Eva', 20, 'B', 'postgresql.eva@example.com', '2023-05-05');

Run

docker-compose -f docker-compose.yml up -d

Connect to DB

Test connection In Host

host: localhost
port: 5432
db: rp_db
user: rp_user
password: rp_pw

Test

Connect

docker exec -it postgres_container psql -U rp_user -d rp_dbdb

List Database

\l

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

Show Tables

\dt

rp_db=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner  
--------+----------+-------+---------
 public | students | table | rp_user
(1 row)

Show Table students

\d students;

rp_db=# \d students;
                                        Table "public.students"
     Column      |          Type          | Collation | Nullable |               Default                
-----------------+------------------------+-----------+----------+--------------------------------------
 id              | integer                |           | not null | nextval('students_id_seq'::regclass)
 name            | character varying(100) |           | not null | 
 age             | integer                |           |          | 
 grade           | character varying(10)  |           |          | 
 email           | character varying(100) |           |          | 
 enrollment_date | date                   |           |          | 
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)

Query Data

SELECT * FROM students;

rp_db=# SELECT * FROM students;
 id |        name        | age | grade |             email              | enrollment_date 
----+--------------------+-----+-------+--------------------------------+-----------------
  1 | PostgreSQL Alice   |  20 | A     | postgresql.alice@example.com   | 2023-01-15
  2 | PostgreSQL Bob     |  22 | B     | postgresql.bob@example.com     | 2023-02-10
  3 | PostgreSQL Charlie |  21 | A     | postgresql.charlie@example.com | 2023-03-12
  4 | PostgreSQL David   |  23 | C     | postgresql.david@example.com   | 2023-04-08
  5 | PostgreSQL Eva     |  20 | B     | postgresql.eva@example.com     | 2023-05-05
(5 rows)