Postgres: Connecting remotely failed with security configurations

Trying to connect remotely to postgres db with pgAdmin and it failed. This message looks exactly like the postgres db is down.

"could not connect to server: Connection refused ..."

It says ...

“For PostgreSQL servers starting with version 8.0, this is controlled using the "listen_addresses" parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use '*' to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you'll need to set the "tcpip_socket" parameter to 'true'. “

So, uncomment, add ‘*’ to postgresql.conf then restart the instance

listen_addresses = '*'          # what IP address(es) to listen on;

-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl stop -D /var/lib/pgsql/9.3/data

waiting for server to shut down.... done

server stopped

-bash-4.1$ ps -ef|grep postgres

avahi 1892 1  0  2014 ?     00:00:09 avahi-daemon: running postgres 21160 21018  0 11:00 pts/0 00:00:00 grep postgres

-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl start -D /var/lib/pgsql/9.3/data

server starting

-bash-4.1$ < 2015-02-20 11:00:44.354 EST >LOG:  redirecting log output to logging collector process

< 2015-02-20 11:00:44.354 EST >HINT:  Future log output will appear in directory "pg_log".

Crap .. now something else ..

"Access to database deinied .. " FATAL" no pg_hba.conf entry for host"

This is an authentication issue. On the server, locate the pg_hba.conf and make the following changes. Allowing all IPs in trust mode.

FROM

# TYPE  DATABASE     USER         ADDRESS              METHOD

# "local" is for Unix domain socket connections only

local   all          all                                  peer

# IPv4 local connections:

host all          all          127.0.0.1/32         ident

# IPv6 local connections:

host all          all          ::1/128              ident

TO

# TYPE  DATABASE     USER         ADDRESS              METHOD

# "local" is for Unix domain socket connections only

local   all          all                                  peer

# IPv4 local connections:

host     all               all         0.0.0.0/0                trust

# IPv6 local connections:

host all          all          ::1/128              ident

3 ways to reload pg_hba.conf

with root

[root@postgres ~]# service postgresql-9.3 reload

with postgres

/usr/pgsql-9.3/bin/pg_ctl reload -D /var/lib/pgsql/9.3/data

-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl reload -D /var/lib/pgsql/9.3/data

server signaled

within the database

select pg_reload_conf();

-bash-4.1$ psql

psql (9.3.5)

Type "help" for help.

postgres=# select pg_reload_conf();

pg_reload_conf

----------------

t

(1 row)

Coming from Oracle world, these security frameworks appeared rather awkward to me. Oracle remote access overlord is the orapwd. ODBC and JDBC connectivity accesses are allowed or restricted to the schema level. Never had to setup these restricitions for IPs and security type. If someone had the root password, the Oracle database would have been compromised and pointless to have these security methods of md5, peer, trust and etc.I guess, once getting used to it, this can be a good thing where it put another layer of security. md5 would have been ideal, other method of security except ‘trust’, it can increase the workloads for a postgres dba.