Enable remote access for PostgreSQL

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:

psql -h $DATABASE_HOST -d DATABASE -U $ROLE

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

#------------------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #------------------------------------------------------------------------------
 # - Connection Settings -
 listen_addresses = '0.0.0.0' # 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
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
host myDB all 172.17.7.2/27 trust

Now restarting the postgreSQL and i can access via psql from my host.

psql -h POSTGRESQL_HOST -U $USER -d myDB
psql (8.4.11)
Type "help" for help.
myDB=#

 

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!