Enable remote access for PostgreSQL

Published on 12.03.2012 - 00:00 - 2 minute read

Remote access to a Postgtres Database

In the last days i have worked a lot with PostgreSQL. We have some Django Application which needs some extra SQL love. I have a installation script what insert all the script to the extra SQL into the database. The Problem: " How to do this on a remote database host, without coping everything to it more often?" A try with psql command show also support for remote host. The great thing about psql is that will automatic using a SSL connection. The command is:
To make this work, i have first enable that the socket, to  listing on a different Interface then localhost. Change listen address in postgresql.conf
 # - Connection Settings -
 listen_addresses = '' # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost', '*' = all
 # (change requires restart)
 port = 5432 # (change requires restart)
 max_connections = 100 # (change requires restart)
 # Note: Increasing max_connections costs ~400 bytes of shared memory per
 # connection slot, plus lock space (see max_locks_per_transaction).
Add your accessing host to the pg_hpa.conf
# Database administrative login by UNIX sockets
local all postgres trust
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all trust
# IPv6 local connections:
host all all ::1/128 trust
host myDB all trust
Now restarting the postgreSQL and i can access via psql from my host.
psql (8.4.11)
Type "help" for help.

Security note:

The "trust" is taking every request reguardless who is doing. This IS very DANGEROUS! I did it becourse i'm a lazy admin and don't want to insert everytime a password during deployments. Be aware of Spoofing Attacks!
Creative Commons License
This work is licensed under a Creative Commons Attribution 4.0 International License.