Fujitsu has introduced a new feature in Fujitsu Enterprise Postgres calledpolicy-based login security. In this blog, I will delve into this new feature and how to implement it.
In today’s digital era, where data breaches and cyberattacks are becoming increasingly common, safeguarding sensitive information comes first and foremost. One crucial aspect of cybersecurity is login security. Weak or compromised passwords are a significant vulnerability that can be the soft target to exploit for malicious attackers.
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, Data Masking, a dedicated Audit Log, cloud-based key management for Transparent Data Encryption, and Confidentiality Management.
Now, a new feature, designed and developed based on customer feedback, calledpolicy-based login security is available.
What is policy-based login security?
Policy-based login security is a new feature to apply login security 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 login security:
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.
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.
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- [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 ~]$
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. 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.
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=#
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 login security 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.
Nishchay Kothari
Technical Consultant, Fujitsu Enterprise Postgres Center of Excellence
Nishchay Kothari is an outstanding technical consultant with over 13 years of expertise in relational database management systems (RDBMS). Nishchay has experience with a wide range of database technologies, including PostgreSQL, SQL Server, and Oracle. Nishchay has positioned himself as a go-to resource for organizations wanting to optimize their database infrastructure and architectural solutions driven by his passion for addressing complicated technological challenges.
