16

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

2 Answers 2

16

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

4
  • 1
    The sudo in the last command won't work since the priviledged operation is done by a redirection, which is handled by the shell, not by the program you run with sudo (echo, in this case).
    – bortzmeyer
    Sep 11, 2009 at 12:00
  • Good point! It should be written as sudo "echo 'host all all 10.0.2.12 255.255.255.255 trust' >> /var/lib/pgsql/data/pg_hba.conf"
    – pyhimys
    Sep 12, 2009 at 15:12
  • The ideal way would be to put it under version control in an admin location and deploy it after changing it via SSH etc
    – vfclists
    Jun 23, 2012 at 16:50
  • 1
    The default location of pg_hba.conf file is a bit different in 9.4: /var/lib/pgsql/9.4/data/pg_hba.conf . Oct 17, 2015 at 13:03
13

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

listen_addresses = '*'

Postgresql by default listens on localhost

1
  • Isn't your list_addresses example too permissive? That is a gaping security hole if the server runs on multiple IPs, ie public, internal, VPN etc.
    – vfclists
    Jun 23, 2012 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .