Postgresql – no pg_hba.conf entry for replication connection from host

pg-hba.confpostgresqlreplication

(Also posted to pgsql-general)

I have been configuring a slave server that needs to connect to the host. Both the master and the standby servers have a pg_hba.conf that looks like this:

# Allow anyone to connect remotely so long as they have a valid username and
# password.
host    replication     ${REP_USER}     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

This should allow access from every IP address, right? Evidently, though, the standby server cannot connect using the REP_USER credentials via primary_conninfo 

primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432 user=${REP_USER} password=${REP_PASS}'

I know this doesn't work because I never see in my logs:

LOG:  streaming replication successfully connected to primary

..and I cannot connect to the master server via psql either:

psql -h $MASTER_PORT_5432_TCP_ADDR -U replication
psql: FATAL:  no pg_hba.conf entry for host "$SLAVE_PORT_5432_TCP_ADDR", user "replication", database "replication", SSL off

Why? I don't get it…

Best Answer

When doing this:

$ psql -h $MASTER_PORT_5432_TCP_ADDR -U replication
psql: FATAL:  no pg_hba.conf entry for host "$SLAVE_PORT_5432_TCP_ADDR", user "replication", database "replication", SSL off

Since there's no explicit database indicated with -d (for example -d postgres ), it takes replication as the database, the same as the user name. However that's probably not what you want. replication is not supposed to be a real database, as mentioned in the doc for pg_hba.conf:

http://www.postgresql.org/docs/static/auth-pg-hba-conf.html

The value replication specifies that the record matches if a replication connection is requested (note that replication connections do not specify any particular database). Otherwise, this is the name of a specific PostgreSQL database.

So if you expected that this line:

host    replication     ${REP_USER}     0.0.0.0/0               md5

would allow psql to connect with the command above, it's normal that it doesn't work, even assuming that ${REP_USER} is actually replication.

As for the other line:

host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

not knowing what are the real values for ${DB_NAME} and ${DB_USER} it's not really possible to comment on it.

If the real problem is the replication not happening, I don't think psql is very useful to test this since psql must connect to a specific database before doing anything useful, whereas the replication process must not and does not.

Related Question