Host-Based Authentication using pg_hba.conf

The host-based authentication is controlled by the pg_hba.conf file and defines which users can connect to which database and how they can connect to it. The file is a list of declarations, which are searched in order until one of the lines match. They list the access method, the database they are trying to connect to, the user trying to connect and the authentication method being used.

The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is quoted. Records cannot be continued across lines.

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

A record can have one of the seven formats

=========================================================================================

local      database  user  auth-method  [auth-option]
host       database  user  CIDR-address  auth-method  [auth-option]
hostssl    database  user  CIDR-address  auth-method  [auth-option]
hostnossl  database  user  CIDR-address  auth-method  [auth-option]
host       database  user  IP-address  IP-mask  auth-method  [auth-option]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-option]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-option]

=========================================================================================
Access methods

There are three different access methods:
local

This is for a user connecting via the unix socket on the local machine. A line for this method will be in the form:local DATABASE USER METHOD [OPTION]

host

This is matches connections over a TCP/IP network connection host DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] host DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]

hostnossl, hostssl

This is for users connecting over a non-encrypted or an encrypted TCP/IP connection using SSL. This is so that you can treat secure and non-secure connections differently. For example you might be happy to have clear text passwords over SSL, but only allow MD5 over non-secure connections. hostnossl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] hostnossl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] hostssl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] hostssl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]


You can list several databases by separating them by commas. There are two special database names, all and sameuser. all allows the person to connect to all databases on the server. sameuser allows the user to connect to a database with the same name as the user connecting. You can also supply a filename which lists databases they can connect to by using @filename where filename is a file in the same directory as the pg_hba.conf.

You can also list several users by separating them by commas. You can specify groups by prefixing the name with a +. Again you can use a filename with users in by using @filename where filename is a file in the same directory as pg_hba.conf. There is the special username all, which matches any user.

Authentication Methods

trust

This method allows any user to connect without a password. This should be avoided unless you know what you are doing as it could be a security risk.

reject

This is the reverse of trust, as it rejects any one. This is particularly useful where you want to enable access to a range of addresses, but want to block a particular host in that range. host sales alice 10.0.0.128 255.255.255.255 reject host sales alice 10.0.0.0 255.255.0.0 md5

password

This method allows someone to connect providing they have given the correct password for their user in the pg_shadow table. If the password field in that table is null, then they will be rejected. The password is sent in cleartext, so you probably only want to enable this for hostssl connections.

crypt

This is like password, except it is encrypted with the trivial unix crypt encryption. This is not a very strong encryption, so I suggest you don't use this.

md5

This is like crypt and password, except it uses the stronger MD5 to encrypt the password. If you have to use non-ssl connections, I recommend you using this method.

ident

With network connections, this method uses the ident protocol (RFC1413) to check which user on the remote system owns the network connection being used to talk to the server. This is easy to spoof, so you shouldn't reply on this for unsecured networks. I would recommend against it on all networks.

With local connections, it uses the unix user connecting to the unix socket and is much more secure. This allows local users to connect without a password.

This is the only method that requires an option, which is the name of a map in pg_ident.conf, which maps remote users to postgresql users. The format of pg_ident.conf is: map unixuser postgresuser
This allows you to have the same remote user map to different postgres users when they connect to different databases,

There is a special map name called sameuser, which uses the same remote username for the postgresql name.

krb4, krb5

These allow you to use kerberos authentication.

pam

This method allows you to authenticate users against the local system's pam (pluggable authentication modules) subsystem.

For local connections, I would recommend ident, password, crypt or md5. For hostssl connections any of the password methods will work, but md5 is preferable. For host and hostnossl, I can only recommend md5 and hostssl should be used in preference to host and hostnossl. local all postgres ident sameuser hostssl all postgres 0.0.0.0 0.0.0.0 md5 local sameuser all ident sameuser hostssl sameuser all 10.0.0.0 255.255.255.0 md5 hostssl sales alice 10.0.0.1/32 md5