Initialize PostgreSQL Container with docker-entrypoint-initdb.d script

Question

I am trying to create a PostgreSQL 11.5 docker container. In doing so, I want to run a SQL script that creates the necessary users, tables, etc. However, whenever the container starts I see the following error:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

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


WARNING: enabling "trust" authentication for local connections
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.
****************************************************
WARNING: No password has been set for the database.
         This will allow anyone with access to the
         Postgres port to access your database. In
         Docker's default configuration, this is
         effectively any other container on the same
         system.

         Use "-e POSTGRES_PASSWORD=password" to set
         it in "docker run".
****************************************************
waiting for server to start....2019-09-16 17:16:26.568 UTC [42] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-09-16 17:16:26.677 UTC [43] LOG:  database system was shut down at 2019-09-16 17:16:25 UTC
2019-09-16 17:16:26.691 UTC [42] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
/docker-entrypoint-initdb.d/init.sql: Permission denied

My Dockerfile looks like this:

FROM postgres:11.5

ADD ./scripts/init.sql /docker-entrypoint-initdb.d/
ENTRYPOINT ["docker-entrypoint.sh"]
EXPOSE 5432
CMD ["postgres"]

And, my init.sql file looks like this:

CREATE USER mydb WITH PASSWORD 'password';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO mydb;

You'll notice neither of them does anything terribly complicated. However, I'm still getting the permission denied error. I've connected to the running container and confirmed that the init.sql file is in place on the filesystem. Any idea what I could be doing wrong here?

@Adiii I've tried running the docker command, as well as using docker-compose as shown in one of the submitted answers. Either way, I still get the error. — Shadowman
– Shadowman, Commented Sep 16, 2019 at 18:29

Jinna Baalu · Accepted Answer · 2024-10-04 08:57:37Z

17

Initialize Postgres container with Data

Create a docker-compose.yml

services:
  postgress-postgresql:
    image: postgres:bullseye
    container_name: postgres
    volumes:
      - postgresql_data:/var/lib/postgresql/data/
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    environment:
      - POSTGRES_USER=postgress
      - POSTGRES_PASSWORD=MyStrongPassword123
    ports:
      - 5432:5432
    networks:
      - postgres
networks:
  postgres:
volumes:
  postgresql_data:

Create a init.sql with the script

 CREATE USER vbv WITH PASSWORD 'vbv';
 CREATE DATABASE vbvdb;
 GRANT ALL PRIVILEGES ON DATABASE vbvdb TO vbv;
 
 \connect vbvdb;
 
 CREATE TABLE employees (
     id SERIAL PRIMARY KEY,
     name VARCHAR(100),
     position VARCHAR(50),
     salary DECIMAL(10, 2)
 );
 
 INSERT INTO employees (name, position, salary) VALUES
 ('Bhuvi', 'Manager', 675000.00),
 ('Vibhu', 'Developer', 555000.00),
 ('Rudra', 'Analyst', 460000.00);
 
 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO vbv;

RUN with docker-compose up -d

Connect to the container psql client - docker exec -it postgres bash. Check the data exists.

 ➜  ~ docker exec -it postgres bash
 root@96a599122941:/# psql -U vbv -d vbvdb
 psql (17.0 (Debian 17.0-1.pgdg110+1))
 Type "help" for help.
 
 vbvdb=> SELECT * FROM employees;
  id | name  | position  |  salary  
 ----+-------+-----------+----------
   1 | Bhuvi | Manager   | 675000.00
   2 | Vibhu | Developer | 555000.00
   3 | Rudra | Analyst   | 460000.00
 (3 rows)
 
 vbvdb=>

You can also validate docker logs -f postgres contains the following.

 2024-10-04 08:04:03.810 UTC [48] LOG:  database system is ready to accept connections
  done
 server started
 CREATE DATABASE
 
 
 /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
 CREATE ROLE
 CREATE DATABASE
 GRANT
 You are now connected to database "vbvdb" as user "postgress".
 CREATE TABLE
 INSERT 0 3
 GRANT

Added the activity logs into the gist https://gist.github.com/jinnabaalu/89bd8eeba3b8845cf337b85a807748f1

edited Oct 4, 2024 at 8:57

answered Sep 16, 2019 at 17:49

Jinna Baalu

7,96740 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shadowman Over a year ago

I copied & pasted your code verbatim and still received the same error. I'm running Docker 19.03.2 and Docker-compose 1.24.1

Rob Audenaerde Over a year ago

How to hide this 'platops' password in a docker-secret?

Jinna Baalu Over a year ago

docs.docker.com/engine/reference/commandline/secret_create

Martin Larizzate Over a year ago

Using "./directory" instead of just "directory" solved the issue for me

Adiii · Accepted Answer · 2019-09-16 19:44:34Z

14

So from this Dockerfile I assume the user is postgress.

Try with this Dockerfile

FROM postgres:11.5
USER postgres
RUN whoami
ADD ./scripts/init.sql /docker-entrypoint-initdb.d/
ENTRYPOINT ["docker-entrypoint.sh"]
EXPOSE 5432
CMD ["postgres"]

update:

Seems like the file not owned by Postgres user.

Try to set permission

ADD ./scripts/init.sql /docker-entrypoint-initdb.d/
RUN chown postgres:postgres /docker-entrypoint-initdb.d/init.sql

edited Sep 16, 2019 at 19:44

answered Sep 16, 2019 at 18:39

Adiii

61.1k10 gold badges180 silver badges174 bronze badges

1 Comment

Adiii Over a year ago

Let us continue this discussion in chat.

Alex Schörg · Accepted Answer · 2020-11-05 15:15:53Z

1

I had the same issue, mounted a .sh file via docker volumes. I checked permissions via ls -lah, in my case it was just -rw-r-----.

Using chmod 644 filename solved my issue.

answered Nov 5, 2020 at 15:15

Alex Schörg

751 silver badge4 bronze badges

Comments

Juraj Mlich · Accepted Answer · 2020-05-13 18:35:11Z

0

The underlaying problem in our case was that the sql script was stored on a ntfs partition mounted with ntfs-3g which by default has got permissions' functionality disabled (https://superuser.com/questions/451475/chmod-doesnt-work). Running it on a normal ext4 partition solved the problem.

answered May 13, 2020 at 18:35

Juraj Mlich

6881 gold badge8 silver badges15 bronze badges

Collectives™ on Stack Overflow

Initialize PostgreSQL Container with docker-entrypoint-initdb.d script

4 Answers 4

Initialize Postgres container with Data

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Initialize Postgres container with Data

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related