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.
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
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