Creating a table in single user mode in postgres

Question

Im trying to create a Dockerfile from the postgres image. The repo says that initialization should be handled by placing a shell script in /docker-entrypoint-initdb.d/. I put the following script based on an example I found online:

#!/bin/bash
echo "******CREATING DOCKER DATABASE******"
gosu postgres postgres --single <<- EOSQL
   CREATE DATABASE orpheus;
   CREATE USER docker WITH ENCRYPTED PASSWORD 'pwd_docker';
   GRANT ALL PRIVILEGES ON DATABASE orpheus to docker;
   CREATE TABLE profiles ( \
     profile_id    SERIAL UNIQUE PRIMARY KEY, \
     user_id integer NOT NULL UNIQUE, \
     profile_photo_id integer NOT NULL UNIQUE, \
     age integer \
   );
   CREATE TABLE hidden_user ( \
     owner_id    integer NOT NULL PRIMARY KEY, \
     target_id integer NOT NULL \
   );
EOSQL
echo ""
echo "******DOCKER DATABASE CREATED******"

The backslashes seem required since otherwise I get a parse error. The script runs without error and all of the commands except for the CREATE TABLE commands seem to have had an effect.

Is it that table creation is not supported in single user mode? If so, is there a better way to have a dockerfile set up an image with tables created in postgres?

Answer 1

@a_horse_with_no_name got me on the right track with his comment. I decided to ditch the single user mode even if it was "recommended". Instead I start postgres with pg_ctl, load some sql files containing my table creations, and stop the server with pg_ctl.

My shell script looks like this:

#!/bin/bash
echo "******CREATING DOCKER DATABASE******"

echo "starting postgres"
gosu postgres pg_ctl -w start

echo "bootstrapping the postgres db"
gosu postgres psql -h localhost -p 5432 -U postgres -a -f /db/bootstrap.sql

echo "initializing tables"
gosu postgres psql -h localhost -p 5432 -U postgres -d orpheus -a -f /db/setup.sql

echo "stopping postgres"
gosu postgres pg_ctl stop

echo "stopped postgres"


echo ""
echo "******DOCKER DATABASE CREATED******"

Answer 2

If you want to prevent PostgreSQL from being accessible to users before whatever setup you need to perform is done, start it with only loopback access or only a unix socket, do your initialisation, then restart it for general access.

I don't speak Docker, but if you were doing this in a regular environment you'd do something like:

mkdir -p /db/temp_socket
chown -r postgres:postgres /db

PGHOST=/db/temp_socket pg_ctl -D /path/to/datadir -o "-c listen_addresses='' -c unix_socket_directories='/db/temp_socket'" -l "/db/dbsetup.log" -w start

# Do your work
PGHOST=/db/temp_socket psql -f some_script

PGHOST=/db/temp_socket pg_ctl -D /path/to/datadir -m fast -w stop

pg_ctl -D /path/to/datadir -w start ...normalstartupoptionsblah...

i.e. start PostgreSQL not listening on any TCP/IP sockets, and with a non-default unix_socket_directories. Do your setup. Then restart it with the default (or configured) unix_socket_directories and listen_addresses once it's ready for general access.

Instead of this you could:

• Modify pg_hba.conf to only allow access from your setup user / only on the loopback address / etc
• Start Pg
• Do your setup
• Replace pg_hba.conf with the production one
• pg_ctl reload or SELECT pg_reload_conf() to load the new settings and allow general access

... however this will permit applications to connect then reject their authentication during the setup stage; that may not be what you want, and not all applications cope with this correctly.

Answer 3

I tested your script and it is almost working fine. Using Postgresql 9.4 I managed to make the following to work:

gosu postgres postgres --single -jE <<- EOSQL
   CREATE DATABASE orpheus;
EOSQL
echo    
gosu postgres postgres --single -jE orpheus <<- EOSQL
   CREATE USER docker WITH ENCRYPTED PASSWORD 'pwd_docker';
   GRANT ALL PRIVILEGES ON DATABASE orpheus to docker;
   CREATE TABLE profiles (
     profile_id    SERIAL UNIQUE PRIMARY KEY,
     user_id integer NOT NULL UNIQUE,
     profile_photo_id integer NOT NULL UNIQUE,
     age integer
   );
   CREATE TABLE hidden_user (
     owner_id    integer NOT NULL PRIMARY KEY,
     target_id integer NOT NULL
   );
EOSQL
echo

Basically I had to split the script in two as the postgresql was complaining that the create database couldn't be used in multiline scripts. And the other was only to add the database name, orpheus, on the second command.

And voilà