What is the policy-based login security feature?
Policy-based login security is a security feature provided in Fujitsu Enterprise Postgres from version 15 SP1 onwards. This feature enhances security by enforcing database users to use login passwords that meets certain predefined criteria.
For an overview of this feature, check our Key features webpage.
For full details on this feature, check the Operation Guide > Policy-based login security .How it provides enhanced security for user login
Following are a few examples of how enhanced security is provided for user logins to database.
- By preventing a user from logging into a database if the password has expired.
- By preventing a user from logging into a database if the user has not logged into the database for a defined period.
- By locking the password if a user exceeds a defined number of login attempts with incorrect passwords.
- By prompting a user to change his password if it has expired while in a defined grace period.
- By preventing a user from reusing his old password before a certain defined number of changed passwords.
How is policy-based login security enforced
Policy-based login security is implemented through profiles. Database objects of type Profile are created using function pgx_create_profile(), as shown in the example below.
The named Profile objects have several attributes that are stored in database dictionary table pgx_profile, as shown below for a default profile:
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pgx_profile;
-[ RECORD 1 ]-----------+-----------------
oid | 92095
prfpasswordlifetime | -2
prfpasswordgracetime | -2
prfpasswordreusetime | -2
prfpasswordreusemax | -2
prfpasswordlocktime | -2
prffailedloginattempts | -2
prfpasswordallowhashed | 1
prfinactiveusertime | -2
prfpasswordrollovertime | 0
The values assigned to these attributes define the login policy. The named profile is then assigned to a user through the function pgx_assign_profile_to_user(). Once the named profile is assigned to the user, the database server enforces the login policy for the user login to the configured database.
Implementing policy-based login security
Follow the steps below
- Set the configuration parameter in postgresql.conf
Just one parameter needs to be setup in postgresql.conf file. The following parameter setup indicates that login profile is valid for login to testdb01 database.
userprofile_database = 'testdb01'
- Create a profile
postgres=# SELECT pgx_create_profile('mytest_profile01', '{
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.005,
postgres'# "PASSWORD_GRACE_TIME":1,
postgres'# "PASSWORD_ALLOW_HASHED":true
postgres'# }' );
pgx_create_profile
----------------------------
(1 row) - Verify that a new profile has been created
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pgx_profile;
-[ RECORD 1 ]-----------+-----------------
oid | 92095
prfname | default
prfpasswordlifetime | -2
prfpasswordgracetime | -2
prfpasswordreusetime | -2
prfpasswordreusemax | -2
prfpasswordlocktime | -2
prffailedloginattempts | -2
prfpasswordallowhashed | 1
prfinactiveusertime | -2
prfpasswordrollovertime | 0
-[ RECORD 2 ]-----------+-----------------
oid | 16562
prfname | mytest_profile01
prfpasswordlifetime | 86400
prfpasswordgracetime | 86400
prfpasswordreusetime | 0
prfpasswordreusemax | 4
prfpasswordlocktime | 432
prffailedloginattempts | 3
prfpasswordallowhashed | 1
prfinactiveusertime | -1
prfpasswordrollovertime | -1
postgres=# - Assign a profile to the user
postgres=# SELECT pgx_assign_profile_to_user('user01','mytest_profile01'); pgx_assign_profile_to_user
----------------------------
(1 row)
postgres=# - Update pg_hba.conf for password access of the user with entries as below:
# TYPE DATABASE USER ADDRESS METHOD
local testdb01 user01 password
local all all trust
Once the above steps are implemented, the policies can be validated for user01 login to database testdb01.
As we could see, it is quite simple and convenient to implement policy-based login security in Fujitsu Enterprise Postgres.
