In the ever-evolving landscape of data management, businesses cannot afford to overlook the critical importance of database security. Fujitsu Enterprise Postgres , the powerful database management system, has emerged as a trusted guardian of sensitive information by offering a wide range of security features. Security features include Transparent Data Encryption (TDE), Data Masking, a dedicated Audit Log, cloud-based key management for TDE, and Confidentiality Management.
Now, a new feature, designed and developed based on customer feedback, called "Policy-based password management” is available.
What is policy-based password management?
Policy-based password management is a new feature to apply password management to a group of postgres users. This is done by defining a set of password rules, guidelines, and criteria that must be followed into a password profile and then assigning relevant users into the applicable profile. Each profile can have multiple users, but each postgres user can only belong to one profile. This means different security profile attributes can be assigned to different job functions or individual user logins as opposed to service accounts. The policies are designed to enforce strong password practices and obviously reduce the risk of unauthorized access.
The key elements of Fujitsu Enterprise Postgres policy based password management:
- Password history: Users may be restricted from reusing their previous passwords to prevent recycling weak passwords.
- Password expiry: Passwords can be set to expire after a specified period, prompting users to change them regularly.
- Account lockout: Policies can define rules for locking out accounts after a certain number of failed login attempts, mitigating the risk of brute-force attacks.
Key benefits:
- Enhanced security: Strong password policies reduce the likelihood of password-related security breaches.
- Compliance: Many regulatory standards, such as GDPR and HIPAA, requires organizations to implement robust password policies.
- User education: Policies can educate users about secure password practices.
- Policy replication: The password lifetime & lock state is shared by all servers that are part of the replication setup. So, a user is locked based on their policy, not only for the primary server but also for consecutive failed logins to the standby server.
In this blog, I will demonstrate various profiling parameters available in version 15 SP1, and also show various use-cases of this new feature. To configure this feature and perform all the below mentioned operations, you must have the CREATEROLE privilege.
Profiling parameters
A profile is created using the “pgx_create_profile(profile_name name, password_parameter json)” syntax, where we can specify the limit to various password parameters.
pgx_create_profile( profile, ‘{
“FAILED_LOGIN_ATTEMPTS”: ,
“PASSWORD_LIFE_TIME”: ,
“PASSWORD_REUSE_TIME”: ,
”PASSWORD_REUSE_MAX”: ,
”PASSWORD_LOCK_TIME”: ,
”PASSWORD_GRACE_TIME”:,
“PASSWORD_ALLOW_HASHED”:
}’ );
In version 15 SP1, you will see the following password-related parameters.
- Parameters related to the prevention of long-term use of passwords
Parameter name Description PASSWORD_LIFE_TIME Number of days the same password can be used for authentication. PASSWORD_GRACE_TIME The number of days after a password life time is over before the password expires. - Parameters related to stopping password reuse
Parameter name Description PASSWORD_REUSE_TIME Number of days the same password cannot be reused. PASSWORD_REUSE_MAX Number of password changes required before password can be reused. PASSWORD_ALLOW_HASHED Whether to allow password specification by hash value when changing password. - Parameters related to prohibiting consecutive failed login attempts
Parameter name Description FAILED_LOGIN_ATTEMPTS Number of consecutive failed login attempts for a user before the user is locked. PASSWORD_LOCK_TIME The number of days the user will be locked after the specified number of consecutive failed login attempts.
Let’s see how to create a new password profile.
[fsepuser@localhost ~]$ psql -d postgres
psql (15.4)
Type "help" for help.
postgres=# select pgx_create_profile('profile1', '{
postgres'# "FAILED_LOGIN_ATTEMPTS":3,
postgres'# "PASSWORD_LIFE_TIME":1,
postgres'# "PASSWORD_REUSE_TIME":0,
postgres'# "PASSWORD_REUSE_MAX":4,
postgres'# "PASSWORD_LOCK_TIME":0.021,
postgres'# "PASSWORD_GRACE_TIME":1,
postgres'# "PASSWORD_ALLOW_HASHED":true
postgres'# }' );
pgx_create_profile
--------------------
(1 row)
Now, assign this new profile to a database user. In this example, I have an existing database user called “test_profile”.
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_profile | | {}
postgres=#
To assign the password profile, I have used “pgx_assign_profile_to_user(user_name name, profile_name name)” function.
postgres=# select
pgx_assign_profile_to_user('test_profile','profile1');
pgx_assign_profile_to_user
----------------------------
(1 row)
Feature demo
As we have assigned the recently created password policy called “profile1” to user called “test_profile”. Now, let’s see the different scenarios.
Policy violation: password expiration
- Login after password life time is completed.
[fsepuser@localhost ~]$ psql -d postgres -U test_profile
Password for user test_profile:
WARNING: your password has not been changed for a long time; please change your password
DETAIL: Your password will expire in 0.624618 days.
HINT: Use ALTER ROLE to change your password.
psql (15.4)
Type "help" for help.
postgres=> - Login after password expiry.
[fsepuser@localhost ~]$ psql -d postgres -U test_profile
Password for user test profile:
2023-10-13 14:22:19.255 +08 [19252] WARNING: your password has expired
2023-10-13 14:22:19.255 +08 [19252] DETAIL: You cannot execute any other SQL statement unless you change your password.
2023-10-13 14:22:19.255 +08 [19252] HINT: Use ALTER ROLE to change your password.
WARNING: your password has expired
DETAIL: You cannot execute any other SQL statement unless you change your password.
HINT: Use ALTER ROLE to change your password.
psql (15.4)
Type "help" for help.
postgres=> - Recovering from a password expired state.
Operations other than password changes are rejected in the expired state.
test_bkp=> select * from pg_database;
2023-10-13 14:24:16.606 +08 [19393] ERROR: You cannot execute any other SQL statement unless you change your password.
2023-10-13 14:24:16.606 +08 [19393] HINT: Use ALTER ROLE to change your password.
2023-10-13 14:24:16.606 +08 [19393] STATEMENT: select * from pg_database;
ERROR: You cannot execute any other SQL statement unless you change your password.
HINT: Use ALTER ROLE to change your password.
test_bkp->test_bkp=> ALTER role test_profile password 'fep321';A
ALTER ROLE
test_bkp-> select datname from pg_database;
datname
---------
postgres
test_bkp
templatel
template0
(4 rows)B
test_bkp=>AChange to a new password.
BAfter the password is changed, the user can operate normally.
Policy violation: password reuse
- PASSWORD_REUSE_MAX is 4, so the user must change the password at least four times before the same password can be reused.
postgres=# ALTER role test_profile password 'fep1234';
ALTER ROLE
postgres=# ALTER role test_profile password 'fep12345';A
ALTER ROLE
postgres=# ALTER role test_profile password 'fep123456';B
ALTER ROLE
postgres=# ALTER role test_profile password 'fep1234567';C
ALTER ROLE
postgres=# ALTER role test_profile password 'fep1234';
ERROR: could not reuse password
DETAIL: The profile applied to you restricts password reuse.
postgres=#
postgres=# ALTER role test_profile password 'fep12345678';D
ALTER ROLE
postgres=# ALTER role test_profile password 'fep1234';
ALTER ROLE
postgres=#AFirst password change.
BSecond password change.
CThird password change.
DFourth password change.
Policy violation: consecutive failed logins
- As we have configured FAILED_LOGIN_ATTEMPTS as 3, the account will be locked on the fourth failure attempt.
feuse@localhost-
Locked users are automatically unlocked based on the configuration of “PASSWORD_LOCK_TIME” parameter, and in our case it’s 1 day. So, “test_profile”, the locked database user, will be unlocked automatically after 1 day.
[fsepuser@localhost ~]$ psql -d postgres -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: password authentication failed for user "test_profile"
[fsepuser@localhost ~]$ psql -d postgres -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: password authentication failed for user "test_profile"
[fsepuser@localhost ~]$ psql -d postgres -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: password authentication failed for user "test_profile"
[fsepuser@localhost ~]$ psql -d postgres -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: role is locked
[fsepuser@localhost ~]$
If the administrator would like to unlock the locked user immediately without waiting until then, the administrator needs to execute the below query.
[fsepuser@localhost ~]$ psql -d postgres
Password for user fsepuser:
psql (15.4)
Type "help" for help.
postgres=# select pgx_unlock_user('test_profile');
pgx_unlock_user
----------------
(1 row)
postgres=#
Administration: expiring a user password
- Administrators can expire a user's password immediately, such as after the initial password is set for a new user.
fsepuser@localhost:~
[fsepuser@localhost ~]$ psql -d postgres
psql (15.4)
Type "help" for help.
postgres=# create role userl LOGIN password 'fep321';
CREATE ROLE
postgres=# select pgx_make_password_expire('userl');
pgx_make_password_expire
-------------------------
(1 row)
postgres=# | - Now when the user logins for the first time, they need to change the password immediately.
Otherwise, the user is not able to perform any SQL statements.[fsepuser@localhost ~]$ psql -d postgres -U userl
Password for user user1:
WARNING: your password has expired.
DETAIL: You cannot execute any other SQL statement unless you change your password.
HINT: Use ALTER ROLE to change your password.
psql (15.4)
Type "help" for help.
postgres=> select datname from pg_database;
ERROR: You cannot execute any other SQL statement unless you change your password.
HINT: Use ALTER ROLE to change your password.
postgres=>
Administration: view existing profiles
- To view the contents of existing profiles, execute the below query on pgx_profile system catalog.
postgres-> select * from pgx_profile;
-[ RECORD 1 ]----------+---------
oid | 9205
prfname | default
prfpasswordlifetime | -2
prfpasswordgracetime | -2
prfpasswordreusetime | -2
prfpasswordreusemax | -2
prfpasswordlocktime | -2
prffailedloginattempts | −2
prfpasswordallowhashed | 1
-[ RECORD 2 ]‒‒‒‒‒‒‒---+---------
oid | 40976
prfname | profile1
prfpasswordlifetime | 86400
prfpasswordgracetime | 86400
prfpasswordreusetime | 0
prfpasswordreusemax | 4
prfpasswordlocktime | 1814
prffailedloginattempts | 3
prfpasswordallowhashed | 1
postgres->
Streaming replication
The password profile feature significantly integrates into streaming replication environments. This feature will share the password life time state and lock state among the other servers which are part of the replication cluster.
After consecutive failed login attempts on the standby server, the user will be locked out from the entire cluster based on the configured policy.
To support this capability, in version 15 SP1, a new role called “pgx_update_profile_status“ is introduced. The purpose of this new built-in database role is to grant privileges. It determines whether a database user used in a streaming replication configuration can update the user status associated with this feature by belonging to the database group role pgx_update_profile_status.
To demonstrate, I have a working streaming replication cluster and “repluser” already assigned with replication privileges. Next, I GRANT the “pgx_update_profile_status“ to my “repluser” as below.
postgres=# CREATE ROLE repluser REPLICATION LOGIN password 'fep321';
CREATE ROLE
postgres=#
postgres=# GRANT pgx_update_profile_status to repluser;
GRANT ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------------------------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repluser | Replication | {pgx_update_profile_status}
postgres=#
postgres=# select * from pg_stat_replication;
- [ RECORD 1 ]----+------------------------------
pid | 3869
usesysid | 16385
usename | repluser
application_name | walreceiver
client_addr | 192.168.10.126
client_hostname |
client_port | 57180
backend_start | 2023-10-13 08:57:17.955417+08
backend_xmin |
state | streaming
sent_lsn | 0/3000060
write_lsn | 0/3000060
flush_lsn | 0/3000060
replay_lsn |
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-10-13 08:57:58.608855+08
postgres=#
As we can see, our "test_profile” user is also available at the Standby server.
[fsepuser@standby ~]$ psql
psql (15.4)
Type "help" for help.
postgres-# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------------------------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repluser | Replication | {pgx_update_profile_status}
test_profile | | {}
postgres=#
So now to test this unique feature, I have attempted three consecutive failed logins for the “test_profile” user on the standby server. That user subsequently becomes locked. The Failed logins count is completely based on the configured “profile1”.
[fsepuser@standby ~]$ psql -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: password authentication faile d for user "test_profile"
[fsepuser@standby ~]$
[fsepuser@standby ~]$ psql -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: password authentication faile d for user "test_profile"
[fsepuser@standby ~]$ psql -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: password authentication faile d for user "test_profile"
[fsepuser@standby ~]$ psql -U test_profile
Password for user test_profile:
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: role is locked
[fsepuser@standby ~]$
Since repluser is granted “pgx_update_profile_status“ role, user “test_profile” is locked at both the primary and the standby server for security consistency.
[fsepuser@primary ~]$ psql -U test_profile
psql: error: connection to server on socket "/tmp/.s.PGSQL.27500" failed: FATAL: role is locked
[fsepuser@primary ~]$
It is important to note that granting pgx_update_profile_status role to a replication user enables the user to lock all other database users or unlock the lock state in case of policy violation for the entire cluster.
Also, membership in the pgx_update_profile_status group role should only be granted to users for streaming replication.
Additionally, we also need to keep in mind that, if the primary and standby servers are disconnected due to any reason, the profile-based restrictions continue to apply on each server. However, the standby server itself is not able to change a password or unlock an account explicitly. Once the cause of disconnect is fixed and replication has resumed, then we can change a password or explicitly unlock any users on the primary server.
Conclusion
In conclusion, Fujitsu Enterprise Postgres policy-based password management offers a robust and innovative solution for enhancing the security of your database systems. With features that prohibit the long-term use of the same password, prevent password reuse, and block consecutive failed login attempts, it provides a comprehensive approach to safeguarding your data.
What truly sets Fujitsu Enterprise Postgres apart is its capability to lock out users based on policy violations, even on standby servers. This level of security and policy enforcement makes Fujitsu Enterprise Postgres a standout choice for organizations looking to strengthen their database security and access control.
