Postgresql – Is it possible to insert multiple IP addresses in a single entry in a pg_hba.conf file

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.

There is no way to do this with native SQL in PostgreSQL. Also, Postgres manages the privileges and access in a very different way than MySQL.

I've built a VERY BASIC function (needs a lot of tweak and work around, specially handlers when the user already exists) that it adds straightly a user into the pg_hba file, create the user and grant the access to the database.

CREATE OR REPLACE FUNCTION addRemoteUser(
  username text,
  iptarget text DEFAULT '0.0.0.0/0',
  dbtarget text DEFAULT 'all',
  passtext text DEFAULT 'CHANGEME',
  methodauth text DEFAULT 'md5')
  RETURNS boolean
AS $$
  import os
  hbaLine = "host " + str(dbtarget) + "\t" + str(username) + "\t" + str(iptarget) + "\t" + str(methodauth)
  createuser = "CREATE USER " + str(username) + " WITH PASSWORD '" + str(passtext) + "'"
  grantuser = "GRANT connect ON DATABASE " + str(dbtarget) + " TO " + str(username)
  hbaFilePath = plpy.execute("select setting from pg_settings where name = 'hba_file'", 1)
  hs = open(hbaFilePath[0]['setting'],"a")
  hs.write(hbaLine + "\n")
  addUserCommand = plpy.execute(createuser, 1)
  grantUserCommand = plpy.execute(grantuser, 1)
  reload = plpy.execute("select pg_reload_conf()", 1)
  hs.close()
  return True
$$ LANGUAGE plpythonu;

(also posted here on GitHub.)

Obviously is not for production yet, but at least you can have the rough idea how to build the function to do so. It needs PL/Python on the database for this particular example.

I know is not the ideal for this particular case, however I think it's a valid example on how to extend your Postgres installation.

Then, once the function is compiled, you can use SQL to call the function:

SELECT addRemoteUser('mynewuser');