For streaming replication, the policy is enabled on both the primary and standby servers. The user lock status and user password status (Life Time, Grace Time, Expiration) are reflected and shared by all servers in the cluster, even if they are detected on either the primary or standby server.
Perform changing the contents of the profile, creating and assigning profiles, releases the lock, and change the password can only be done on the primary server.
This section describes how to setting streaming replication configuration.
Sets parameters in the postgresql.conf file
Specifies the database name that can be connected to the userprofile_database parameter. If omitted, the default database "postgres" is assumed.
Refer to "Parameters for the Policy-based Password Management" for parameters.
Grant privilege to users for streaming replication
The standby server connects to the primary server and propagates any state changes it detects to the primary server. The used users for streaming replication (specified in primary_conninfo) is used to connect for state change. Grant privilege to this user. Allow the user to SET ROLE directly to the group role pgx_update_profile_status.
[Example]
# GRANT pgx_update_profile_status TO repluser WITH SET TRUE;
This action enables users for streaming replication to lock all other database users or unlock the lock state due to policy deviations. Also, membership in the pgx_update_profile_status group role should only be granted to users for streaming replication.
Add a record to pg_hba.conf
Add a record to accept connections from the standby server. Duplicate the record for streaming replication and specify the database name in the userprofile_database parameter as the destination database name. Authentication methods other than password authentication are recommended.
host replication repluser standbyServerAddress authenticationMethod # For Streaming Replication host postgres repluser standbyServerAddress authenticationMethod # For state change propagation
Information
If the primary and standby servers are disconnected, profile-based restrictions still apply on each server. However, the standby server itself cannot change its password or unlock it explicitly. After removing the cause of the disconnect, restore the connection to the primary server and business operations, and then change the password or explicitly unlock.
Connection information for reflecting status changes to the primary server or standby server is output as "User profile status sender" to application_name in the pg_stat_replication view. When monitoring the streaming replication status with the pg_stat_replication view, exclude the "User profile status sender" line from the monitoring target.
