How to Access Linux PostgreSQL Remotely

  • Post author:
  • Post category:Tools

1. Login to your Linux box as user postgres.

> su postgres

2. If needed, create the user that needs remote access.

> createuser -W

3. Edit the PostgreSQL pg_hba.conf file. In our system, it’s under /var/lib/pgsql/data/.

> vi /var/lib/pgsql/data/pg_hba.conf

4. Append the following lines to pg_hba.conf. In this example, we are allowing remote access to all databases for user MYUSER with a 0.0.0.0/0 CIDR mask.

host all MYUSER 0.0.0.0/0 md5

5. Edit postgresql.conf and change the listen_addresses property.

> vi /var/lib/pgsql/data/postgresql.conf
listen_addresses='*'

6. Reload PostgreSQL for the changes to take effect. Here we are using the data directory /var/lib/pgsql/data.

> pg_ctl reload -D /var/lib/pgsql/data

7. Connect remotely to PostgreSQL using your favorite client, like pgAdmin III.