0

(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...

  • Are these ${REP_USER} etc entries literal? Or are you editing for the sake of the post? – Craig Ringer Apr 9 '15 at 4:22
  • I'm replacing them with replication, which is a keyword, I think...maybe I should quote it? As in "${REP_USER}" – picardo Apr 9 '15 at 10:07
  • I changed the replaced username to rep but it didn't make a difference. – picardo Apr 9 '15 at 10:08
  • So this is some kind of input into a preprocessor that then produces the real pg_hba.conf? – Craig Ringer Apr 9 '15 at 11:13
  • yes, exactly right. – picardo Apr 9 '15 at 15:43
1

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.

  • I can connect to the DB_NAME with psql. That I noticed. But for the other one, shouldn't the error say something about not having a database by that name?? Why does it complain about pg_hba.conf entry? – picardo Apr 9 '15 at 15:46
  • Incidentally, I also tried adding host all all 0.0.0.0/0 md5 to see if that would make any difference, but it didn't. – picardo Apr 9 '15 at 15:47
  • @picardo: the error is understandable, because the pg_hba.conf filter comes first. If the user/db/host/origin combination doesn't match any line in pg_hba, it doesn't even check if the user and db exist. And if it did report it in the error message, that would be leaking information to unauthorized clients and wrong as such. – Daniel Vérité Apr 10 '15 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.