.pgpass for PostgreSQL replication in Dockerized environment

Question

I try to set up an PostgreSQL slave using Docker and a bash script (I use Coreos). I have not found any way to supply a valid .pgpass.

I know I could create a PGPASSWORD environment variable, but do not wish to do so for security reasons (as stated here, http://www.postgresql.org/docs/current/static/libpq-envars.html),, and because this password should be accessible every time the recovery.conf file is used (for the primary_conninfo variable).

Dockerfile

# ...
# apt-get installs and other config
# ...

USER postgres
# Create role and db
RUN /etc/init.d/postgresql start &&\
    psql --command "CREATE USER replicator WITH ENCRYPTED PASSWORD 'THEPASSWORD';" &&\
    psql --command "CREATE DATABASE db WITH OWNER replicator;"

# Set the pg_pass to allow connection to master
ADD ./pgpass.conf /home/postgres/.pgpass # pgpass.conf comes my root git folder
USER root
RUN chmod 0600 /home/postgres/.pgpass

In my bash file

# ...

pg_basebackup -h host.of.master.ip -D /var/pgbackup/backup_data -U replicator -v -P

# ...

The problems seems to be that the pgpass file is not read. It seems I should use the password of the user I'm sudoing to (https://serverfault.com/questions/526170/psql-fe-sendauth-no-password-supplied), but in this case the replicator role is naturally not an available bash user. (Note that neither copying the pgpass to /home/root not /home/postgres works).

Note: my pgpass file and by remote database conf

# pgpass.conf
host.of.master.ip:5432:replication:replicator:THEPASSWORD
host.of.master.ip:5432:*:replicator:THEPASSWORD

# pg_hba.conf
host    replication   replicator    host.of.slave.ip/24    md5

Cashandcarry

Have your tried setting PGPASSFILE? – Lowenstern

Yes I tried export PGPASSFILE = /root/to/pgpass – Cashandcarry

Did you figure out how to do it? Working on the same issue now.. – Lutz

Hi @Justin. I ended up doing it by precising the full path PGPASSFILE=/home/postgres/pgpass pg_basebackup -x etc. I hope it helps! – Cashandcarry

Answer 1

You have to create a .pgpass on the home folder of the user who's going to be running the commands (in this case, postgres). Each line of the file has to be in the format hostname:port:database:username:password and supports wildcards, so you can just set the database to "*" for example.

In my case, I have something like this...

$ sudo echo "${PRIMARY_IP}:5432:*:${REPL_USER}:${REPL_PASS}" > /var/lib/postgresql/.pgpass
$ sudo chown postgres:postgres /var/lib/postgresql/.pgpass
$ sudo chmod 0600 /var/lib/postgresql/.pgpass
$ sudo -u postgres pg_basebackup -h $PRIMARY_IP -D /var/lib/postgresql/9.4/main -U ${REPL_USER} -v -P --xlog-method=stream

Those variables (e.g. PRIMARY_IP) are set when I run the docker container with -e PRIMARY_IP=x.x.x.x

Atbara

* in the database name certainly solved my docker implementation. Thanks! – Heathen