PostgreSQL Security Quick Reference - pg_hba.conf

Host Based Authentication (HBA) is a feature that adds an extra layer of security for postgres. Connection to a postgres server is controlled by a configuration file. This is quite different from many other RDBMs and may not be obvious for someone migrating from other database systems.

Postgres accepts only connection types specified in this file. Each line in this file is a record specifying a connection type. Each field in every line is separated by whitespace.

Format of a record (single line) will look like the following:

 <connection_type>   <database>   <user>   <address>  <authentication_method> 

Fields

<connection_type>:
	local: Unix-domain socket. Connections from within the server can use this.
	host: A plain or SSL-encrypted TCP/IP socket
<database>
	all: Connection to any database
	<database_name> : Connection to database with specified name
	replication: A replication connection
	sameuser: Database name with the same name as the connecting user
	samerole: Connecting user should be member of a role with same name as user

<user>:
	all: Connection from any user
	<username>: Connection from a specific user with specified name
<address>:
	Client machine IP address(es) OR a network range in CIDR mask notation
<authentication_method>:
	trust: Allow the client to connection unconditionally
	md5: Require client to supply a password (send to server in md5 encryption)

Defaults

When first installed, Postgres is configured to only accept connections from clients on localhost - from the same server. This secure-by-default approach means that applications running on a different server or desktop client will not be able to connect to the database until you change the HBA settings.

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local & remote connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Note: Forgetting to change these defaults is the most common reason an application cannot connect to a Postgres database server

Updating HBA Configuration

To allow access for your application servers add a line similar to the following:

# Allow any user from host 192.168.12.10 to connect to the app db 
# if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    (app db name)   all             192.168.12.10/32        md5  

Postgres (postmaster process) reads this file on startup OR When it gets a SIGHUP signal. So if you edit this file on an active postgres instance, you will need to signal postmaster by "pg_ctl reload" or "kill -HUP"

More Detail

For a complete list of options, refer to the PostreSQL documentation.