PostgreSQL – Accept Subdomain Wildcards on Ubuntu

postgresqlUbuntu

I want to accept all db connections from servers with domain mydomain.com so that all subdomains like tiger.mydomain.com, venus.mydomain.com will have access to the postgresql server.

I've read that you have to create a reverse dns server so that it can lookup the domain and get the appropriate ip address. but it is not clear how to do this or a postgresql specific case.

Best Answer

From the manual:

address

Specifies the client machine addresses that this record matches. This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

...

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). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match.

...

A host name specification that starts with a dot (.) matches a suffix of the actual host name. So .example.com would match foo.example.com (but not just example.com).

In other words, use

  host all all .mydomain.com md5

or similar in pg_hba.conf, along with reverse DNS configured appropriately as above.

Related Solutions

PostgreSQL failover and replication

slaves wont understand new master. you should manually do that.
yes they are different and you should create new ones for old master.however old standby will continue to work as a master but you should set max_wal_senders on that node. you should also set pg_hba.conf of the new master after failover. after failover (when nodes changes roles master->slave slave->master), you should transfer new wal files to new standby folders data directory which you set in recovery.conf file. or simply you can use rsync.
may be you can use pgbouncer. this way you will just change the pgbouncer server adres to new master.
EnterpriseDB has some commercial tools. may be you can check them.

and finally yes you are right. there is no single fully automatic solution to solve these questions.

Connect to Remote PostgreSQL Database on Ubuntu Using pgAdmin3

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections.

Proto Recv-Q Send-Q Local Address   Foreign Address  State   PID/Program name
tcp        0      0 127.0.0.1:5432  0.0.0.0:*        LISTEN  3561/postgres

So it can only accept local tcp connections regardless of what permissions you've specified in pg_hba.conf. pg_hba.conf only specifies allowed connections, but does not specify what interfaces your service will listen to.

The addresses the server listens on is specified with the listen_addresses GUC in postgresql.conf. If you want the server to listen for remote connections you should specify the ip(s) you want it to listen on or * to listen on all available interfaces on the host.

To have your postgresql server listen on all interfaces on the host, you should have the following line in postgresql.conf:

listen_addresses = '*'

Best Answer

Related Solutions

PostgreSQL failover and replication

Connect to Remote PostgreSQL Database on Ubuntu Using pgAdmin3

Related Question