Understanding PostgreSQL User Management and Password Encryption

In PostgreSQL, user management plays a crucial role in controlling access to databases, ensuring data security, and facilitating efficient database administration. Understanding how PostgreSQL handles user accounts, privileges, and authentication mechanisms is essential for maintaining a secure and well-managed database environment. In this blog post, we will delve into how PostgreSQL handles user authentication, password storage, and the implementing of changing password encryption methods.

PostgreSQL User Management

PostgreSQL uses a role-based authentication system where users (or roles) can be granted specific privileges to access databases and perform operations. Let’s explore a practical example using PostgreSQL’s pg_shadow view, which provides insights into user account details, including password hashes.

postgres=# select usename,usesysid,passwd from pg_shadow;
 usename  | usesysid |                                                                passwd                                                                 
----------+----------+---------------------------------------------------------------------------------------------------------------------------------------
 postgres |       10 | SCRAM-SHA-256$4096:E7aNSt5s+P8/Y8wkhiUiig==$KzGClfCwduklVgojVIDm/zTnIZ9k4+r9vpY2ul6NagU=:/McQWOQi0AJ5aBRK5cmvSfQOquPZAl5mPOoEM4fCwgg=
 akhil    |    25884 | SCRAM-SHA-256$4096:N020XkU3CMC9tCvXrEwZMQ==$BWnkZUvu4pD9l+RKYsoEa+RdpzAk1tMQnOXZd2XdWvM=:t4qO9sJgxWfOEdyWL8tkGc3C6k91pxr4hkiX96klt98=
(2 rows)

In the above query, pg_shadow displays user details including password hashes encrypted using SCRAM-SHA-256

Changing User Passwords

Changing the password for role akhil

mydb=# ALTER ROLE akhil PASSWORD 'newpassword';

This command updates the password for the user akhil to newpassword. PostgreSQL automatically encrypts the password using the configured encryption method (scram-sha-256 by default).

Managing Password Encryption Methods

PostgreSQL allows administrators to configure password encryption methods globally. Here’s how we can alter the encryption method

mydb=# ALTER SYSTEM set password_encryption to md5;
ALTER SYSTEM
mydb=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
mydb=# show password_encryption ;
 password_encryption 
---------------------
 md5
(1 row)

Changing the password encryption to md5 alters how PostgreSQL encrypts user passwords.

Supported values are scram-sha-256, md5 and password but it is recommended not to use the password option since the password would be sent in clear text and hence might be a security concern.

Impact of not altering the Password

Even though , we have altered the password encryption from SCRAM-SHA-256 to md5 ,but the password will be still in the old hash i.e, SCRAM-SHA-256 because it is encrypted before altering the password encryption method.

postgres=# show password_encryption;
 password_encryption 
---------------------
 md5
(1 row)

postgres=# select usename,usesysid,passwd from pg_shadow;
 usename  | usesysid |                                                                passwd                                                                 
----------+----------+---------------------------------------------------------------------------------------------------------------------------------------
 postgres |       10 | SCRAM-SHA-256$4096:E7aNSt5s+P8/Y8wkhiUiig==$KzGClfCwduklVgojVIDm/zTnIZ9k4+r9vpY2ul6NagU=:/McQWOQi0AJ5aBRK5cmvSfQOquPZAl5mPOoEM4fCwgg=
 akhil    |    25884 | SCRAM-SHA-256$4096:N020XkU3CMC9tCvXrEwZMQ==$BWnkZUvu4pD9l+RKYsoEa+RdpzAk1tMQnOXZd2XdWvM=:t4qO9sJgxWfOEdyWL8tkGc3C6k91pxr4hkiX96klt98=
(2 rows)

So, we need to alter the password after changing the password encryption method. PostgreSQL will now save the new password in md5

postgres=# ALTER ROLE akhil PASSWORD 'somepassword';
ALTER ROLE
postgres=# select usename,usesysid,passwd from pg_shadow;
 usename  | usesysid |                                                                passwd                                                                 
----------+----------+---------------------------------------------------------------------------------------------------------------------------------------
 postgres |       10 | SCRAM-SHA-256$4096:E7aNSt5s+P8/Y8wkhiUiig==$KzGClfCwduklVgojVIDm/zTnIZ9k4+r9vpY2ul6NagU=:/McQWOQi0AJ5aBRK5cmvSfQOquPZAl5mPOoEM4fCwgg=
 akhil    |    25884 | md575cb1fb77702b4fdd2501914c6d6d1a3
(2 rows)

Now ,we can see the password hash for user akhil is stored in md5 encryption method.

Use-Case

Assume you want to use a extension/tool which supports the md5 only and your password encryption method is SCRAM-SHA-256 . In this case ,changing only password encryption to md5 doesn’t allow us to access/use the extension/tool .We need to alter the password too after changing the encryption as above. Recently , one of our beloved client has faced the same issue.

Conclusion

PostgreSQL offers robust mechanisms for managing user authentication and password security. By understanding how PostgreSQL encrypts passwords and configuring encryption methods, administrators can ensure database security aligns with organizational policies and best practices.

Managing PostgreSQL users and passwords effectively not only enhances security but also ensures seamless operation and compliance with data protection standards. In this blog, we delved into how PostgreSQL handles user authentication, password storage, and the implementing of changing password encryption methods and also the impact and necessity of changing the password after altering the password encryption method.

Stay tuned for more info