pg_hba.conf configuration details
This file is located in the database directory where the initial installation is
Edit the pg_hba.conf configuration file
postgres@clw-db1:/pgdata/9.6/poc/data> vi pg_hba.conf
TYPE parameter settings
TYPE indicates the host type, and the value may be:
If `local` is a socket connection of unix-domain,
If `host` is a TCP/IP socket
If `hostssl` is an SSL-encrypted TCP/IP socket
DATABASE parameter settings
DATABASE indicates the database name, and the value may be:
`all`,`sameuser`,`samerole`,`replication`,`database name`, or multiple
Use `comma` in the database name, note that ALL does not match replication
USER parameter settings
USER represents the user name, and the value can be:
`all`,`a username`,`a set of usernames`, and when multiple users are separated by `,` commas.
Or write a separate one in the user name prefix `+`; in the USER and DATABASE fields.
The file name is prefixed with `@`, which contains the database name or user name
ADDRESS parameter settings
This parameter can be `host name` or `IP/32(IPV4)` or `IP/128(IPV6)`.
The name begins with `.`, `samehost` or `samenet` to match any IP address
METHOD parameter settings
This value can be
"trust", "reject", "md5", "password", "scram-sha-256", "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert"
Note If it is `password`, the sent password is a plain text
Notice
Modify the parameters in this configuration file and restart the `postgreSql` service. To allow access to other IP addresses
For this host database, the parameter `listen_addresses` in `` must be modified to `*`
Restart: pg_ctl reload or execute SELECT pg_reload_conf()
Configure the following parameters
# TYPE DATABASE USER ADDRESS METHOD host all all 10.10.56.17/32 md5 "pg_hba.conf" 99L, 4720C
Parameter description
hostParameters indicate the host on which PostgreSQL is installed
all The first all represents all database instances on the host
allThe second all means all users
10.10.56.17/32Indicates the IP address that needs to be connected to the host, 32 means IPV4
md5Indicates verification method
That is, the above means that all users with IP address 10.10.56.17 can connect to all databases on the host through MD5's password verification method.
You can also specify the specific database name and user
# TYPE DATABASE USER ADDRESS METHOD host test pgtest 10.10.56.17/32 md5
That means that users with address 10.10.56.17 are allowed to connect to the test database on the host through an encrypted password using MD5.
You can also specify the entire network segment
# TYPE DATABASE USER ADDRESS METHOD host test pgtest 0.0.0.0/0 md5
That means that any iP is allowed to connect to the test database on the host through the password verification method with the username pgtest and md5.
No password verification
# TYPE DATABASE USER ADDRESS METHOD host test pgtest 0.0.0.0/0 trust
Indicates user with any IP address pgtest can directly connect to the host's test database without password verification
Supplement: Postgresql configuration file pg_hba.conf configuration, modify postgresql superuser password
Postgresql sets the username and password, but finds that you can log in without entering the password or if the password is entered incorrectly. So I checked online and found out that it was the problem with the configuration file pg_hba.conf.
1. Modify the pg_hba.conf file so that the database authentication method is encrypted login
The following command can find the path to pg_hba.conf
[root@localhost ~]# find / - name pg_hba.conf
Enter the configuration file
[root@localhost ~]# vi /home/postgres/pgsql/data/pg_hba.conf
It was found that the contents in the configuration file were commented out, as follows:
# local DATABASE USER METHOD [OPTIONS] # host DATABASE USER ADDRESS METHOD [OPTIONS] # hostssl DATABASE USER ADDRESS METHOD [OPTIONS] # hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
The above is equivalent to postgreql users who can log in without password, add
host all all 0.0.0.0/0 md5
Requires the client to provide an MD5 encrypted password for authentication, that is, you must have a password to log in.
After modifying the pg_hba.conf file, save and exit, enter the command to make the configuration take effect
(1) The first method of effective
[root@localhost data]# service postgresql reload
After the above command is executed, the following prompt message will appear:
Reload PostgreSQL： OK
Explanation: The modification of the pg_hba.conf configuration file has taken effect.
When connecting to the database on the client, you need to enter your account and corresponding password to log in. When pd_Admin connects to the database, if you select the save password, the password will be saved in pg_class.conf under the C:\Users***\AppData\Roaming\postgresql path, and you can view it.
Generally speaking, the Appdata folder is hidden and needs to display hidden files in the computer settings. The specific method is Baidu.
(2) The second method of effective
Note: After a few days, I am planning to change the connection verification method of another server. I found that the above method was used to modify pg_hba.conf when it took effect. The specific errors are as follows:
[root@localhost data]# service postgresql reload postgresql: unrecognized service
After searching for information online, make the following attempts.
① Switch to postgres user
[root@localhost ~]# su - postgres -bash-4.1$ pwd /var/lib/pgsql -bash-4.1$ ls -bash-4.1$ 9.5 -bash-4.1$ cd * -bash-4.1$ ls backups data
② Use the pg_ctl command to take effect on the file
-bash-4.1$ ./pg_ctl reload pg_ctl: no database directory specified and environment variable PGDATA unset Try "pg_ctl --help" for more information.
The error is reported as above. You need to add the path where the data folder is located after reload.
-bash-4.1$ ./pg_ctl reload -D /var/lib/pgsql/9.5/data server signaled
The above prompt appears: When server signed, it means the configuration takes effect
2. Modify the password of postgresql default superuser postgres
First log in to the postgresql database as the postgres user
[root@localhost ~]# sudo -u postgres psql
Then modify the postgresql login password, and the ending must have a semicolon ";", otherwise the modification is invalid, and ALTER ROLE will not appear after executing the command.
postgres =# alter user postgres with password '****'；
After the above command is executed, it will appear
ALTER ROLE
The explanation changes take effect, then exit postgresql
postgres =# \q
The password modification of this super user postgres has been completed and can be verified on the pgAdmin client.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.