Creation of postgresql table on docker run

Question

I want to create a Dockerfile that runs a PostgreSQL database when I run that container.

So what I did was making a Dockerfile:

FROM postgres:latest

COPY *.sh /docker-entrypoint-initdb.d/

The Dockerfile copies the initialization script "postgres.sh" to /docker-entrypoint-initdb.d/. The postgres.sh script contains the following:

#!/bin/sh

su postgres -c "psql << EOF
ALTER USER postgres WITH PASSWORD '123';
create database shippingchallenge;
\c shippingchallenge;
create table people(firstname CHAR(20), surname CHAR(30));
insert into people values ('Pieter', 'Pauwels');
EOF"

So what I want to do is create a database "shippingchallenge" with a table "people" with 2 values: "firstname " and "surname". This works fine if I would use sudo docker exec -it <container> /bin/bash and I paste this.

But when I use the method described above and I run the image created from this sudo docker run -p 5432:5432 -e POSTGRES_PASSWORD=123 <image> I get 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 "UTF18".
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 dynamic shared memory implementation ... posix 
selecting default maxconnections ... 100 
selecting default shared_buffers ... 128MB 
selecting default time zone ... Etc/UTC 
creating configuration files ... ok 
running bootstrap script ... ok 
performing post-bootstrap initialization ... ok 
syncing data to disk ... ok 

initdb: 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.
 
Success. You can now start the database server using: 

    pg_ctl -D /var/lib/postgresql/data -l logfile start 
    
waiting for server to start....2020-12-19 12:42:09.749 UTC [45] LOG: starting PostgreSQL 13.1 (Debian 13.1-1 pgdg100+1) on x86_64 pc linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-12-19 12:42:09.754 UTC [45] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-12-19 12:42:09.771 UTC [46] LOG: database system was shut down at 2020-12-19 12:42:07 UTC
2020-12-19 12:42:09.778 UTC [45] LOG: database system is ready to accept connections
 done 
server started 

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/postgres.sh 
Password: su: Authentication failure

Content of /etc/pam.d/su:

auth       sufficient pam_rootok.so





session       required   pam_env.so readenv=1
session       required   pam_env.so readenv=1 envfile=/etc/default/locale

session    optional   pam_mail.so nopen

session    required   pam_limits.so

@include common-auth
@include common-account
@include common-session

Thanks in advance for the help!

don't you need to specify the user as well sudo docker run -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=123 <image>? — αғsнιη
– αғsнιη, Commented Dec 19, 2020 at 18:44
I tried this, still the same error. By default the user postgres is used. Docker Hub postgres documentation: "POSTGRES_USER This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used." — Pieter Pauwels
– Pieter Pauwels, Commented Dec 19, 2020 at 18:50
omit the password sudo docker run -p 5432:5432 <image> and see if it still fails, sounds you have wrong password, 123 should be password to db user — αғsнιη
– αғsнιη, Commented Dec 19, 2020 at 19:53
So without the -e POSTGRES_PASSWORD=123 option? Then I get the following: [sudo] password for pieter: Error: Database is uninitialized and superuser password is not specified. You must specify POSTGRES_PASSWORD to a non-empty value for the superuser. For example, "-e POSTGRES_PASSWORD=password" on "docker run". You may also use "POSTGRES_HOST_AUTH_METHOD=trust" to allow all connections without a password. This is *not* recommended. See PostgreSQL documentation about "trust": https://www.postgresql.org/docs/current/auth-trust.html — Pieter Pauwels
– Pieter Pauwels, Commented Dec 19, 2020 at 20:22

Lipstick Slaver · Accepted Answer · 2021-02-14 19:18:24Z

0

Placement of a script file with SQL statements into the docker-entrypoint-initdb.d directory does the thing, when one builds custom image with the official Postgres base image https://github.com/docker-library/postgres

docker-context-dir
  /docker-entrypoint-initdb.d
    |-create-table.sql
  |-Dockerfile

Dockerfile

FROM postgres:latest
COPY docker-entrypoint-initdb.d /docker-entrypoint-initdb.d

create-table.sql

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    ...);

answered Feb 14, 2021 at 19:18

Lipstick Slaver

1

Add a comment |

Reda Salih · Accepted Answer · 2020-12-20 11:00:02Z

-1

Since no issue was found on /bin/su executable permissions and the content of /etc/pam.d/su and /etc/pam.d/su-l. The image should be fine !

After I've pulled the image locally, it seems that the error is due to su postgres -c ; It should be deleted because if you add whoami to the script you will notice that the script will be run per default with postgresql and that's because the container made an su to postgres when launching because the daemon needs to be started with this user.

Docker file :

FROM registry.hub.docker.com/library/postgres
COPY postgres.sh /docker-entrypoint-initdb.d/
RUN chmod +x /docker-entrypoint-initdb.d/postgres.sh

Script :

#!/bin/bash
whoami 
psql << EOF
ALTER USER postgres WITH PASSWORD '123';
create database shippingchallenge;
\c shippingchallenge;
create table people(firstname CHAR(20), surname CHAR(30));
insert into people values ('Pieter', 'Pauwels');
EOF

TEST :

docker exec -it <container> bash 
su postgres 
psql
postgres=# \l

OUTPUT (the table have been created and the owner is postgres) :

shippingchallenge|postgres|UTF8|en_US.utf8|en_US.utf8|

That's why su was asking for the password of postgres user and since nothing is passed you got Authentication Failure during the build.

edited Dec 20, 2020 at 11:00

answered Dec 19, 2020 at 16:26

Reda Salih

1,8227 silver badges9 bronze badges

2

Why would the su executable have the wrong permissions in the container? How would that chmod command remove the need to provide the postgres user's password?

Kusalananda
– Kusalananda ♦

2020-12-19 17:15:06 +00:00
Commented Dec 19, 2020 at 17:15
Indeed, this has no effect. Thanks for thinking along. Still looking further for a solution.

Pieter Pauwels
– Pieter Pauwels

2020-12-19 18:31:55 +00:00
Commented Dec 19, 2020 at 18:31
Where did you get the image ? I've tried it with docker.io and it's fine there is no permission issue. Use instead /bin/su, i have updated the answer and check / post the permissions in the question.

Reda Salih
– Reda Salih

2020-12-19 19:38:38 +00:00
Commented Dec 19, 2020 at 19:38
Ok, share the following outputs : docker run -it <image> bash -c 'cat /etc/pam.d/su; cat /etc/pam.d/su-l' ; docker run -it <image> bash -c 'grep postg /etc/passwd' ; From where did you get this image ? which registry ?

Reda Salih
– Reda Salih

2020-12-19 19:56:41 +00:00
Commented Dec 19, 2020 at 19:56
1

I see , Your welcome :)

Reda Salih
– Reda Salih

2020-12-20 13:03:08 +00:00
Commented Dec 20, 2020 at 13:03

| Show 11 more comments

Stack Exchange Network

Creation of postgresql table on docker run

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Creation of postgresql table on docker run

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions