All about pg_hba.conf (authentication methods- PostgreSQL)

pg_hba.conf is the PostgreSQL access policy configuration file, which is located in the /var/lib/pgsql/10/data/ directory (PostgreSQL10) by default.

The configuration file has 5 parameters, namely: TYPE (host type), DATABASE (database name), USER (user name), ADDRESS (IP address and mask), METHOD (encryption method)

host all all 192.168.109.103/22 md5

host dbName user 192.168.109.106/22 trust

Modify the server-side pg_hba.conf file

Make the shell can connect to the postgres database secretly:

Modify the authentication file $PGDATA/pg_hba.conf, add the following lines, and reload to make the configuration take effect immediately.
host pankajconnect postgresql 192.168.8.103/32 trust

Reload to take effect: pg_ctl reload -D $PGDATA

Examples:
1. Allow local login to the database using PGAdmin3, database address localhost, user user1, database user1db:

host user1db user1 127.0.0.1/32 md5

2. Allow 10.1.1.0~10.1.1.255 network segments to log in to the database:

host all all 10.1.1.0/24 md5

3. Trust 192.168.1.10 to log in to the database:

host all all 192.168.1.10/32 trust
After pg_hba.conf is modified, use pg_ctl reload to re-read the pg_hba.conf file. If pg_ctl cannot find the database, use -D /…/pgsql/data/ to specify the database directory, or export PGDATA=/…/ pgsql/data/ imports environment variables.

Option Details:

1. trust

Allow connections unconditionally. This method allows any user who can connect to the PostgreSQL database server to log in as any PostgreSQL database user they desire without requiring a password or any other authentication.

2. reject

Unconditionally reject the connection. This helps to “filter out” specific hosts from a group. For example, a reject line can block a specific host connection, while the following line allows the remaining hosts in a specific network to connect.

3. md5

The client is required to provide a double MD5 encrypted password for authentication.

4. password

The client is required to provide an unencrypted password for authentication. Because passwords are sent over the network in clear text, we should not use this method on untrusted networks.

5. gss

Use GSSAPI to authenticate users. Only available for TCP/IP connections.

6. sspi

Use SSPI to authenticate users. Only available on Windows.

7. ident

Obtain the client’s operating system name by contacting the client’s ident server, and check whether it matches the requested database user name. Ident authentication can only be used on TCIP/IP connections. When this authentication method is specified for local connection, peer authentication will be used instead.

8. peer

Obtain the operating system user of the client from the operating system and check whether it matches the requested database user name. This is only available for local connections.

9. ldap

Use LDAP server for authentication.

10. radius

Authentication with RADIUS server.

11. cert

Use SSL client certificate authentication.

12. pam

Use the pluggable authentication module service (PAM) authentication provided by the operating system.

13. bsd

Use the BSD authentication service provided by the operating system for authentication.

Common Errors:
The server doesn’t grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host “192.168.0.123”, user “postgres”, database “postgres” FATAL: no pg_hba.conf entry for host “192.168. 0.123”, user “postgres”, database “postgres”

For security, the PostgreSQL database will not listen to all connection requests except for the local. When the user accesses through JDBC, it will report some exceptions as follows:

org . postgresql . util .PSQLException: FATAL: no pg_hba . conf entry for host

To solve this problem, just in PostgreSQL find / data / installation directory database pg_hba . Conf , find “# IPv4 local connections:”

Add the IP of the machine requesting to connect under it

host all all 127.0.0.1/32 md5

pg_ctl start [-w] [-s] [-D datadir ] [-l filename ] [-o options ] [-p path ]
pg_ctl stop [-W] [-s] [-D datadir ] [-ms[mart ] | f[ast] | i[mmediate]]
pg_ctl restart [-w] [-s] [-D datadir ] [-ms[mart] | f[ast] | i[mmediate]] [-o options ]
pg_ctl reload [-s] [-D datadir ]
pg_ctl status [-D datadir ]
pg_ctl kill [ signal_name ] [ process_id ]

Reload the file after configuration:

pg_ctl reload [-s] [-D datadir ]