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>
<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)
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"
For a complete list of options, refer to the PostreSQL documentation.