2

Hello I'm trying to figure out the best way to allow connection from certain ips/hosts to my db instance.

I've got an api(s) pointing to the database and right now we do something like this (pg_hba.conf) :

host    prod_db   prod_user         xxx.xxx.77.xx/32        password

And since the api are getting deployed in cloud and with new instances, having new ips, we kind of change this config every time we deploy, which is not very good, cause we have to restart the db server and therefore have to put the whole eco system in the maintenance mode.

I'm just reading this :

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

This bit looks appealing to me:

# Allow any user from hosts in the example.com domain to connect to
# any database if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             .example.com            md5

So this means any incoming connection from api.example.com or api1.example.com or any from this domain will be accepted if the password is correct.

But my question is how does the db server know that it's coming from the something.example.com.

Does this mean that the server is doing some kind of reverse dns lookup before allowing connection?

Does this assume that example address 123.456.22.11 which points to api.example.com, by point I mean A record will be allowed connection from or how this works?

What do I need to do to make this work?

My ultimate goal is not to have to edit the pg_hba.conf every time there is a new api server instance booted up to add that new ip. And not having to restart postgres db instance

  • 1
    Right from the link you provided: "If a host name is specified (anything that is not an IP address or a special key word is processed as a potential host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used)". Btw: changes in pg_hba.conf don't require a Postgres restart, you only need to reload the configuration (pg_ctl reload ...) – a_horse_with_no_name Mar 6 '15 at 14:40
  • @a_horse_with_no_name hey man thanks. Can you please make that an answer. Wow I didn't know about the configuration reload, but still it's unnecessary if you can enable domain – Gandalf StormCrow Mar 6 '15 at 18:55
  • For "cloud" configs it's more typical to permit wider access in pg_hba.conf then use your cloud hosting provider's security group / firewall features to control access. – Craig Ringer Mar 7 '15 at 12:41

Your Answer

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

Browse other questions tagged or ask your own question.