Configuring the pg_hba.conf file to allow access from other servers to the database

I have a postgres database on one server and I need access it from another server.

I need to edit the pg_hba.conf file, but have now idea what are the steps to edit this file. Server Centos

I need to add the following line in the file

host    all         all         10.0.2.12         255.255.255.255   trust

I located it in var/lib/pgsql/data/

Now basically I'm not sure what are the correct steps to do this


Solution 1:

First take a backup copy of the given pg_hba.conf

sudo tar cvf /var/tmp/mybackup_pg_hba_conf.tar /var/lib/pgsql/data/pg_hba.conf

That way you have the older version ready if you screw up. A nice feature of tar archive is that preserves permissions and directories. So when the pg_hba.conf is broken beyond recognition. You can:

cd /
sudo tar xvf /var/tmp/mybackup_pg_hba_conf.tar

This will restore the backup pg_hba.conf from the archive you created earlier.

As for the actual insertion of line:

sudo su -c 'echo "host    all         all         10.0.2.12         255.255.255.255   trust" >> /var/lib/pgsql/data/pg_hba.conf'

(NOTE: Command edited. Thanks bortzmeyer!)

Should do it. Note that this just appends the line into the pg_hba.conf. If you ever need to change it you have to use an actual editor. Like vi. It's learning curve can be a bit steep, but after you have learned at least the basics you should be quite safe in most every unix-environment you encounter.

Now that I'm back on the track you should next try to restart your postgresql database. On a Centos machine this I believe is done by:

sudo service postgresql restart

or you can just reload configuration without restart:

su - postgres
pg_ctl reload

Now you should be able to access your Centos-machines postrgresql database from 10.0.2.12

Solution 2:

Don't forget to edit postgresql.conf and change/add line

listen_addresses = '*'

Postgresql by default listens on localhost