Understanding the pg_hba conf file in PostgreSQL

Understanding the pg_hba conf file in PostgreSQL

Understanding the pg_hba conf file in PostgreSQL

The pg_hba.conf file which stands for PostgreSQL Host-based Authentication configuration file, controls client/ip  access to a PostgreSQL database. The pg_hba.conf file is stored in the database cluster data directory by default and can be moved to a different location if needed (configuration needed).

The pg_hba.conf file is loaded/read on start-up and if changes are made to it afterwards it will need to tell the postmaster using pg_ctl reload or kill -HUP to re-read the file.

The pg_hba.conf file consist of 5 headings:

Type: This allows the type of connections that are allowed to the database for a specific line in the configuration file. There are 4 values for this heading namely:

  • Local – This is only applicable in Unix (not windows), and allows an user connection to the database only from the machine which host the database. e.g Database is installed on server: A, user can only connect to the database from server: A.
  • Host – This specifies external I.Ps that can connect to the PostgreSQL database. Specifying host will cover both hostssl and hostnossl connections
  • Hostssl – This option will only match external connections that are made with SSL encryption.
  • Hostnossl – This option will only match external connections that are made without SSL encryption.

Database: under this heading, we specify the database(s) we will be connecting to or granting access to.

We can specify a single database, multiple databases comma(,) separated, a list of databases in a .txt file that is stored in the cluster directory or all for, you guessed it, all databases.

User: similar to the database heading, the user heading allows us to list the users who can access a specific database or databases. We can specify a single user, multiple users comma(,) separated, a list of users in a .txt file that is stored in the cluster directory or all for all users.

Address: Under this heading we specify the client’s ip address from which connections are allowed. For this record we can specify IP-address/netmask (CIDR ADDRESS format), IP-address netmask (IP-ADDRESS IPMASK format) or all.

Method: This heading specifies the authentication method that a user will use to connect to the database or databases specified. There are 4 methods in which we can use:

  • Trust: for this option users can connect to the database without specifying a password. When using this option one should be cautious.
  • Reject: This option rejects a connection to a database(s) for a user for a particular record in the file.
  • Password: this option prompts the user for a password before connecting to the database. When this method is specified the password is not encrypted between the client and the database.
  • Md5: this option prompts the user for a password before connecting to the database. When this method is specified the client is required to supply a double-MD5-hashed password for authentication.
  • Ldap – Authenticate using a LDAP server.
  • Radius – Authenticate using a RADIUS server.
  • Cert – Authenticate using SSL client certificates.
  • Pam – Authenticate using the Pluggable Authentication Modules (PAM) service provided by the operating system.
  • Ident –  Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead.
  • Sspi – Use SSPI to authenticate the user. This is only available on Windows. SSPI is a Windows technology for secure authentication with single sign-on.
  • Gss – Use GSSAPI to authenticate the user. This is only available for TCP/IP connections.

 

Understanding the pg_hba.conf file in PostgreSQL

Pg_hba.conf file example

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
# TYPE    DATABASE        USER        ADDRESS       METHOD
  local     all           all                       trust

# The same using local loopback TCP/IP connections.

# TYPE    DATABASE        USER            ADDRESS               METHOD
 host     all            all            127.0.0.1/32            trust

# The same as the previous line, but using a separate netmask column

# TYPE    DATABASE     USER            IP-ADDRESS     IP-MASK           METHOD
  host    all          all             127.0.0.1     255.255.255.255    trust

#Allow a user access from all IP addresses

# TYPE    DATABASE        USER            ADDRESS         METHOD
 host     testdb        testuser         0.0.0.0/0         md5

#Allow multiple users access from all IP addresses (CIDR Address format)

# TYPE    DATABASE              USER                           ADDRESS       METHOD
  host   postgres,postgres2    testuser,testuser1,testuser2   0.0.0.0/0      md5
  host   postgres            @users.txt                      0.0.0.0/0       md5
  host   postgres             all                            0.0.0.0/0       md5

#Allow a user access from a specific IP addresse (CIDR Address format)

  host  postgres              testuser                    192.168.55.25/32   md5

#Allow a user access

Important: in the pg_hba.conf file there is no fall-through, once a record is chosen for authentication other records following that record are ignored. Please see the example below.

#the first line of the configuration below allows testuser to connect to the postgres database using a password from # all ip addresses

# The second line is trying to prevent the same user from accessing the same database. The second line will be #ignored

# TYPE    DATABASE      USER           ADDRESS           METHOD
 host    postgres      testuser      0.0.0.0/0            md5
 host    postgres      testuser      0.0.0.0/0           reject

#The example below: the user connection is rejected from the database. Second line is ignored.

# TYPE    DATABASE     USER         ADDRESS           METHOD
 host     postgres    testuser     0.0.0.0/0          reject
 host     postgres    testuser     0.0.0.0/0          md5

 

Conclusion

The pg_hba conf file in postgreSQL is use to control access to the database server and must be used with other internal database controls such as the ALTER command to achieve efficiency. For more on the pg_hba conf file check out the official documentation also check out our other tutorials: 3 easy ways to create users in Postgresql