Lampros - Weird Bricks

PostgreSQL 9.3 - Connecting Remotely

21 August, 2016 | PostgreSQL

Following my last post on how to install and create a database on PostgreSQL 9.3 on CentOS, we’ll take a look on the steps necessary to make PostgreSQL listen to all interfaces and control access to it using the pg_hba.conf.

PostgreSQL has two main configuration files that everyone should know about:

That’s cool, but where are those files? That’s a common problem with PostgreSQL, but there is an easy solution - we can ask the database itself.

To figure out where the postgresql.conf is:

1. Become the postgres user:

su - postgres

2. Ask Postgres:

psql -c 'show config_file;'

Sample output:

-bash-4.1$ psql -c 'show config_file;'
config_file
-----------------------------------------
/var/lib/pgsql/9.3/data/postgresql.conf
(1 row)

To figure out where the pg_hba.conf is:

1. Become the postgres user:

su - postgres

2. Ask Postgres:

psql -c 'show hba_file';

Sample output:

-bash-4.1$ psql -c 'show hba_file';
hba_file
-------------------------------------
/var/lib/pgsql/9.3/data/pg_hba.conf
(1 row)

Awesome - now we know where those files are.

Now, let’s go ahead and modify the postgresql.conf so that Postgres listens to all interfaces:

With the editor of your choice edit /var/lib/pgsql/9.3/data/postgresql.conf

Find this part of the configuration:

#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)

As you can see the default is set to ‘localhost’ let’s uncomment the line and change the value to ‘*’.

When done it should look like this:

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

Save the file and restart the database:

service postgresql-9.3 restart

Sample output:

Stopping postgresql-9.3 service: [ OK ]
Starting postgresql-9.3 service: [ OK ]

Now check the output of netstat -ntlp|grep 5432 to confirm:

Sample output:

netstat -ntlp|grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1895/postmaster
tcp 0 0 :::5432 :::* LISTEN 1895/postmaster

Now on a second server, install the Postgres repository and clients:

yum -y install https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-2.noarch.rpm
yum -y --disablerepo=* --enablerepo=pgdg93 install postgresql93

Now from the second server, let’s try to access the one we just changed the listening configuration for:

psql -h 69.87.218.196
psql: FATAL: no pg_hba.conf entry for host "69.87.216.126", user "root", database "root", SSL off

As you can see it makes a connection to 69.87.216.126 but access is denied. It also tells us that it expected to find an entry in pg_hba.conf on 69.87.216.126.

Fine, fine, let's go ahead and edit pg_hba.conf:

Back on the first server, let’s take a look at /var/lib/pgsql/9.3/data/pg_hba.conf.

Open it with your favorite editor - here’s what I see once I’m past the comments:

# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

As you can see, there’s 3 lines that essentially allow access:

# "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

And all of them only allow connections from the localhost :). So we need to add another one to let us access this host remotely.

Let’s add the lines:

# allow 2nd server
host all postgres all md5

And reload Postgres (no restart is necessary)

service postgresql-9.3 reload

One more time...

Now try connecting again from the second server, but this time specify the postgres user with the -U switch:

psql -h 69.87.218.196 -U postgres
Password for user postgres:
psql: fe_sendauth: no password supplied

What went wrong here? We didn’t setup a password! to quote the manual:

PostgreSQL database passwords are separate from operating system user passwords. 
The password for each database user is stored in the pg_authid system catalog.
Passwords can be managed with the SQL commands CREATE USER and ALTER ROLE,
e.g., CREATE USER foo WITH PASSWORD 'secret'.
If no password has been set up for a user, the stored password is null and password authentication will always fail for that user.

Make sure you've set up the postgres user password!

So let’s go back to the first server and change the postgres user’s password:

We need the ALTER ROLE command

1. Become the postgres user:

su - postgres

2. Connect to postgres

psql

3. Change the password:

ALTER ROLE postgres WITH PASSWORD 'Z6tqXBjh';

Now go back to the second server and try again with the password we just set:

psql -h 69.87.218.196 -U postgres
Password for user postgres:
psql (9.3.14)
Type "help" for help.

postgres=#

Done!

Sources: