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 ]
Thanks for reading this post, if you like you can follow me up. thanks.
