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

warren
  • 18,369
  • 23
  • 84
  • 135
Elitmiar
  • 775
  • 3
  • 15
  • 31

2 Answers2

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

Sergey Vlasov
  • 103
  • 3
pyhimys
  • 1,287
  • 10
  • 10
  • 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 '09 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 '09 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 '12 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 . – Sergey Vlasov Oct 17 '15 at 13:03
13

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

listen_addresses = '*'

Postgresql by default listens on localhost

user16233
  • 149
  • 2
  • 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 '12 at 16:49