🐘 PostgreSQL

Updated at 2016-01-05 02:48

Accessing a PostgreSQL database requires:

  • access to the host machine (firewall, ssh) or the host port PostgreSQL listen to (firewall, postgresql.conf)
  • passing by client authentication policies (pg_hba.conf)
  • having CONNECT privileges for the database (database role definitions)

postgresql.conf contains definitions what addresses the server listens to. The file usually under /etc e.g. /etc/postgresql/9.3/main/postgresql.conf

listen_addresses = '*' # accept connections from IP address or hostname
port = 5432            # accept connections to this port

PostgreSQL client authentication policies are managed by pg_hba.conf. It's usually in the /etc directory like /etc/postgresql/9.3/main/pg_hba.conf. Although you can customize the location when starting up the server.

Each line defines a authentication record like so:

# TYPE    DATABASE        USER            ADDRESS                 METHOD
host      all             all               trust
  • Type:
    • local: local connection made through Unix socket
    • host: local or remote connection made through TCP/IP
  • Database:
    • name of the target database, sameuser for database with the user's name or all
  • User:
    • name of the user that this rule applies to or all
  • Address:
    • IPv4, IPv6 or host name address that this rules applies to, samehost, samenet or all
  • Method:
    • trust: allow connections unconditionally, don't combine with host on production
    • reject: reject connection unconditionally
    • md5: require double-MD5 hashed password
    • password: require unencrypted password, not for production
    • cert: require SSL authentication
    • peer: require db user to match OS user over local connection
    • ident: require db user to match OS user over host connection


# Open settings for development, not for production
local   all         all                               trust
host    all         all         all                   trust

# Decent security but could be better.
# Include database name, username and source address as possible.
local   all         postgres                          peer
host    all         all         all                   md5

Remember that you will have to restart the server after changing these.

sudo service postgresql restart
# or however you manage your PostgreSQL

The connecting database user must also have the CONNECT privilege.

ALTER ROLE my_username WITH ENCRYPTED PASSWORD 'my_password';
GRANT CONNECT ON DATABASE my_database TO my_username;