Stronger Security with CloudSQL for PostgreSQL: Implementing Password Policies.
Password policies set define governance in terms of complexity, expiry and reusability. It enforces users to follow a set of standards for password as minimum requirement or rules.Well defined password policies about minimum length, complexity and expiration enforce an additional layer of security for database users using built-in authentication.
Recently CloudSQL for PostgreSQL launched enforcement of password policy at the instance level as an additional security measure. It is primarily enforce for built-in authentication i.e. authenticate with passwords for database logins.
This blog will walkthrough on all available options as policies that can be applied at instance level and cases it won’t be enforce.
As an example, we will enable all policies around minimum password requirement, reusability and expiry rules.
Password policies set is enforced for all CloudSQL users at instance level and applies to all Users created or altered henceforth.
Let’s try some sample user creation and check password policies enforcement using psql command line.
Length Check
Controls minimum length require for password.
postgres=> create user sample with password ‘test123’;
ERROR: INVALID_PASSWORD: password is not long enough.Complexity Check
Controls complexity on password with lower\upper case, number, symbols.
postgres=> create user sample with password ‘test1234’;
ERROR: INVALID_PASSWORD: password should contain at least one lowercase, one uppercase, one number and one non-alphanumeric characterspostgres=> create user sample with password ‘test@1234’;
ERROR: INVALID_PASSWORD: password should contain at least one lowercase, one uppercase, one number and one non-alphanumeric characters
Reusability Check
Controls and restrict reuse of password based on minimum password change set.
postgres=> alter user sample with password ‘tesT@1234’;
ERROR: INVALID_PASSWORD: the password should not reuse recent passwords.Username in password check
Controls check to avoid creation of user and password being same.
postgres=> create user sample with password ‘sample’;
ERROR: INVALID_PASSWORD: password is not long enough.postgres=> create user “Sample@123” with password ‘Sample@123’;
ERROR: INVALID_PASSWORD: password should not contain username.
Passwords changed too frequently.
We can configure minimum time for which password must be use before it can change, any attempt to change it before specified time will cause violation.
postgres=> alter user sample with password ‘TesT@!234$’;
ERROR: INVALID_PASSWORD: password changes too frequently.We have enforced use of set password for a specific defined time, hence any attempt to change it within that period will invoke INVALID_PASSWORD.
Using Password policies we can enforce rules for build it authentication and set predefined standard across database on password.For cases having users altered or created with encrypted password, CloudSQL does not enforce any policies and it can skip.
Let’s take a closer look on scenario, we can counter on password policies being not enforce, we will first walkthrough issue with logging of plain password into PostgreSQL logs and how some workaround might not enforce password policies.
Logging of plain password as part of PostgreSQL logs
Most PostgreSQL databases enabled logging at different levels and DDL being mostly common due to default logging with log_statement as DDL running alter user/create user will get logged as plaintext in the logs. Passwords getting logged can pose a security threat for exposing passwords in plain text as part of logs.
postgres=> show log_statement;
log_statement
— — — — — — — -
ddl
(1 row)
postgres=> create user sample with password 'samplE@123';If we check logs for above statement, it will show plain password as part of PostgreSQL logs.
Mitigation — logging plan password
\password option of psql Client
One of best practices to avoid logging of plain password as part of log or command line is to leverage the \password option of the psql command line to change password.It implicitly encrypts the password as part of the alter statement with encryption as per password_encryption flag.
postgres=> \password sample1
Enter new password for user "sample1":
Enter it again:postgres=> \q--using cloudsql proxy for connection hence -h localhost.
deepakmahto$ PGPASSWORD=test psql -h localhost -p 5432 -d postgres -U sample1
psql (14.2)
Type "help" for help.
postgres=> \conninfo
You are connected to database "postgres" as user "sample1" on host "localhost" (address "127.0.0.1") at port "5432".
Though we have choose password as “test” it was allowed as it is encrypted.Now if we check the logs it will be logged but as encrypted not as plain text.
Post password policies enforcement, if we leverage the above suggested option it will not enforce defined policies due to password being encrypted. For encrypted passwords, policies configured at instance level are not enforced.
pgaudit extension for logging
An additional approach to avoid logging of plain passwords is using pgaudit.log options instead of log_statement for logging DDL.
We will enable below two flags for leveraging pgaudit logging as part of CloudSQL and disable logging using log_statement flag.
cloudsql.enable_pgaudit
pgaudit.logPost applying database flags, we can verify the setting.
postgres=> show cloudsql.enable_pgaudit;
cloudsql.enable_pgaudit
- — — — — — — — — — — — -
onpostgres=> show pgaudit.log;
pgaudit.log
- — — — — — -
ddl
Now if we try to create\alter users for changing passwords it won’t get logged but other DDL like create tables should be logged.
postgres=> create table test_sample(col1 integer);
CREATE TABLEpostgres=> create user sample1 with password ‘Test@1234’;
CREATE ROLE
Only audit logs for the Create table will be logged due to the pgaudit feature i.e including all DDL except related to roles\users.
Overall password policies enforcement add additional layer of security on creating or altering user with passwords. It govern rules and set a standard across users within instance to follow policies set for passwords. With various option available Users can configured as per best practise set as compliance for CloudSQL users as part of built-in authentications.
Check out additional blog on Migrating user with password and grants assignment post DMS.
