Postgressql connection Authentication Settings (Collection)

Pg_hba.conf is the main file for access authentication. The format is one row per record and one access authentication is specified for each row. An access authentication rule consists of seven parts: connection mode (type), database, user, IP address (IP-address), and subnet mask (IP-mask) authentication method and authentication configuration (authentication-option) are described in the following sections:

 

 

Connection Type)

There are three Connection Methods: local, host, and hostssl.

 

 

Local

 

This record matches the connection attempt through the Unix domain socket. Without such a record, the connection of the UNIX domain socket is not allowed.

 

 

Host

 

This record matches the connection attempt through the TCP/IP network. Note that unless the server has the-I option or PostgreSQL enabled. the tcpip_socket configuration parameter set in conf is enabled. Otherwise, the TCP/IP connection is disabled.

 

 

Hostssl

 

This record matches the SSL connection attempt on the TCP/IP. The host record can match the SSL connection attempt and the non-SSL connection attempt, but the hostssl record requires the SSL connection.

 

 

Database)

The database where the declared record matches. The value all indicates that the record matches all databases, and the value sameuser indicates that if the requested database and the requested user have the same name, the record matches. Samegroup indicates that the requested user must be a member of a group with the same name as the database. In other cases, this is a specific PostgreSQL name. You can declare multiple databases by using commas. A file containing the database name can be declared by the @ prefix of the file. The file must be in the same directory as pg_cmd.conf.

 

 

User)

 

Declare the matched PostgreSQL user for this record. The value all indicates that it matches all users. Otherwise, it is the name of a specific PostgreSQL user. Multiple user names can be declared by using a comma-separated method, and group names can be declared by using + as the group name prefix. A file containing the user name can be declared by prefix @ in front of the file name. The file must be in the same directory as pg_cmd.conf.

 

 

IP address (IP-address) Subnet Mask (IP-mask)

These two fields contain the IP address/mask value in decimal notation. (IP addresses can only be declared in numbers, rather than domain names or host names.) They are put together to declare the IP address of the client that matches the record. The accurate logic is:

 

(Actual-IP-address xor ip-address-field) and IP-mask-field

 

The number of records to be matched must be zero.

 

If the connection mode is host or hostssl, you must specify these two items. Otherwise, you can leave them empty.

 

 

Authentication Method)

Trust

 

The connection is allowed unconditionally. This method allows anyone who can connect to the PostgreSQL database to connect as any PostgreSQL database user they want without a password.

 

 

Reject

 

The connection is rejected unconditionally. It is often used to "filter" Some hosts from a group.

 

 

MD5

 

The client is required to provide an MD5 encrypted password for authentication. This method is the only method that allows the encrypted password to be stored in pg_shadow.

 

 

Crypt

 

Similar to the MD5 method, only the old-fashioned crypt encryption authentication is used for clients earlier than 7.2. For clients later than 7.2, MD5 is recommended.

 

 

Password

 

Like "MD5", the password is transmitted in plain text on the network. We should not use this method on insecure networks.

 

 

Krb4

 

Kerberos V4 is used to authenticate users and can only be used for TCP/IP connection. (Note: Kerberos, "corpolos", hence the long-headed dog of Pluto in Greek mythology. Kerberos is the base and symmetric encryption developed by MIT.AlgorithmThe authentication protocol and/or key exchange method, which features two servers for different purposes, one for identity authentication and the other for key exchange between users at both ends of the channel. At the same time, Kerberos has high requirements on network time synchronization to prevent replay attacks. Therefore, it is usually accompanied by the NTP service .)

 

 

Krb5

 

Use Kerberos V5 to authenticate users. It can only be used for TCP/IP connection. Kerberos v5 is an improvement of V4 above, mainly because it no longer relies on the DES algorithm and adds some new features .)

 

 

Ident

 

Obtain the user's operating system name (for TCP/IP connections, the user's identity is determined by connecting to the IDENT server running on the client, for local connections, it is obtained from the operating system .) Then, check to see if the user is allowed to join the database user as required by referring to the ing stated after the ident keyword.

 

If you use sameuser ing, assume that the user name is equal. If this keyword is not declared, find the ing name in the $ pgdata/pg_ident.conf file. If the file contains a record that identifies the ing between the username provided by the ident and the requested PostgreSQL username, the connection is accepted.

 

Local connections can be used only when the system supports Unix domain socket trust certificates (currently Linux, FreeBSD, NetBSD, and BSD/OS ).

 

 

Pam

 

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

 

 

 

[B: cf601_cc6] authentication configuration (authentication-option) [/B: cf601_cc6]

 

The meaning of this optional field depends on the selected authentication method.

 

 

 

After learning about the above content, we can start to set our own access authentication. Like MySQL, access authentication installed by PostgreSQL by default is insecure. After I installed PostgreSQL for the first time, I found that you can enter 'psql-u pgsql-D template1 'without any password and use the pgsql user (the highest database permission ), even after I used alter user to add a password to my database user, the situation did not improve. After some searches, I found that my pg_assist.conf content is as follows:

 

 

Local all trust

 

Host All all 0.0.0.0 0.0.0.0 Trust

 

 

This means that no one can access my database locally or through TCP/IP without any restrictions, so I used the MD5 authentication method for all the accesses, and changed my pg_mirror.conf:

 

 

Local all MD5

 

Host All all 0.0.0.0 0.0.0.0 MD5

 

 

The problem seems to have been solved. Anyone who wants to access my database needs to pass the password, and my database is secure. However, when I restarted my computer one day and found that my PostgreSQL was not properly started, I found that I had started the postgresql service startup script before the terminal, and prompted me to enter a password, it turns out that my pgsql requires a password to start the service after the MD5 authentication method is set. In order not to enter a password every time I start the service, my pgsql user authentication method must be trust:

 

 

Local pgsql all trust

 

Local all MD5

 

Host All all 0.0.0.0 0.0.0.0 MD5

 

 

In subsequent detection, I found that although the network is protected, if the connection is locally connected through a Unix domain socket, anyone can still use 'psql-u pgsql-D template1 'to easily access my database. This is obviously not the result I want. Now I need the assistance of unix_socket_permissions, when I set this item to 0700, only the socket owner, that is, the system user pgsql, can use the socket for connection. In addition, if my database has several administrators who need the highest permissions, 0770 is also an option, but I only have one administrator, so I chose 0700. Now my pg_mirror.conf is changed:

 

 

Local pgsql all trust

 

Host All all 0.0.0.0 0.0.0.0 MD5

 

 

After unix_socket_permissions is combined, only the System user pgsql can establish connections without any restrictions (of course, the great root exception ), in addition, I do not need to enter the password every time I start the terminal, and others can only connect through the network, even if I log on to the local computer, other users can only use the 'psql-u xx-D template1-H 127.0.0.1 'connection. This connection is protected by password authentication, which is exactly what I want.

 

 

The above is my setup process. We can also use PAM and other authentication methods to implement more complex access authentication, but those content is no longer within the scope of this article.