How to Change User Passwords in PostgreSQL: An In-Depth Guide for Developers

Passwords remain one of the most common mechanisms for authenticating users and controlling access to systems and data. However, compromised user credentials account for over 80% of hacking-related data breaches according to Verizon‘s 2020 DBIR report.

As a database administrator or full-stack developer, you are responsible for properly managing users and their passwords within PostgreSQL. This comprehensive guide dives deeper into Postgres‘ authentication methods, password management strategies, resetting forgotten passwords, security best practices, and how to programmatically generate strong credentials resistant to guessing and brute-force attacks.

Overview of PostgreSQL Authentication

PostgreSQL provides role-based access control (RBAC) driven by the concept of "users" and "groups". Behind the scenes, they are implemented as "roles" with different privileges assigned using GRANT and REVOKE statements.

The pg_hba.conf specifies the authentication mechanisms and policies for clients connecting to the Postgres server.

The key supported authentication methods include:

• Trust – Automatically assume user name is valid without a password
• Reject – Reject all connections
• MD5 – Send hashed password for validation
• Password – Compare plain text password
• GSS – Use Kerberos or SSPI to validate client identity
• SSL – Enforce certificate based authentication over TLS encrypted connections
• LDAP – Delegate to external LDAP server for verification
• RADIUS – Integrate with RADIUS protocol for centralized authentication
• PAM – Pluggable authentication modules like SSSD

Trust based authentication is convenient but highly insecure. The MD5 cryptographic hash approach is reasonably safe. For stronger security, Postgres recommends scram-sha-256 salted challenge response to prevent man-in-the-middle and replay attacks.

Storing Passwords in PostgreSQL

In PostgreSQL v10 onwards, user credentials including the password hashes are stored in the system table pg_authid.

Here is sample output:

postgres=# SELECT rolname, rolpassword FROM pg_authid;

     rolname      |                                   rolpassword                                    
------------------+----------------------------------------------------------------------------------
 pg_monitor       | md5d9f000b4c4b724cfdd255e3c6e3e9130
 postgres         | md53502b4702b50ea0d714e609c2 COMPLEXp4ssW0rd123@
 bob              | md5d276db2b5b5975d519516275f7bea1f3
 alice            | SCRAM-SHA-256$4096:34Vas4C2vLMUkTck:HqTDg/KQoRWEZV1NEoAdGPiRC70N+LFKsB9LLuV7DE0=:ENfykLYFC/XD+52lskq74w==

The rolpassword contains the encrypted password string for each user. This may be MD5 or SCRAM hashes depending on the defined mechanism. The encryption guards against leaking actual passwords.

By default, the UNENCRYPTED flag is set allowing storage of plain text passwords. For security, you should enable ENCRYPTED status:

ALTER USER bob WITH ENCRYPTED PASSWORD ‘123MyComplexPassword‘; 

This will store the salted SCRAM hashed value improving protection.

Now let‘s examine how to properly change user passwords in Postgres.

Changing Passwords in PostgreSQL

There are three main ways to change or reset passwords for users in PostgreSQL:

1. pgAdmin 4 GUI – Simple graphical interface
2. psql – Interactive terminal using meta-commands
3. ALTER USER SQL – Modify role attributes directly

Let‘s explore each approach for changing passwords.

Resetting Passwords in pgAdmin

pgAdmin 4 is the officially packaged GUI tool for PostgreSQL administration on Linux and Windows.

To change a password of a user via the pgAdmin interface:

1. Login to pgAdmin with an admin account.
2. Navigate to the Login/Group Roles tree.
3. Right click on the user and select Properties.

4. In the Password field, enter the new password.
5. Retype the password to confirm.
6. Save to update credentials.

This will reset the password for that particular database role or user.

Changing Passwords with psql

psql is a versatile command line client that comes bundled with Postgres installations. It allows you to connect to PostgreSQL and execute SQL statements along with meta-commands.

There are two approaches for changing passwords through psql:

1. \password meta command

This allows securely changing your current user‘s password:

\password
Enter new password:
Enter it again: 

You will be asked to input your new password without exposing it in plaintext.

For example:

postgres=> \c alice
Password for user alice:
You are now connected as new user alice.

alice=> \password  
Enter new password:
Enter it again:

This resets the password for user ‘alice‘ to the newly entered value.

2. ALTER USER Statement

As a database superuser, you can modify any user‘s attributes including their password as:

ALTER USER bob WITH PASSWORD ‘new_password‘; 

Optionally make the password expire on a specific date:

ALTER USER mary WITH PASSWORD ‘abcd1234‘ VALID UNTIL ‘2025-12-31‘;

This will force ‘mary‘ user to change the password after the specified expiry time.

While ALTER USER allows changing any user‘s password, it exposes passwords in plain text to the database server and logs. Thus, \password is more secure.

Resetting Forgotten Passwords

In case a user forgets their password, PostgreSQL does not have inherent password recovery mechanisms.

As a superadmin, you will need to directly reset forgotten passwords like:

ALTER USER john WITH PASSWORD ‘new_password‘;

Optionally force reset on first login:

ALTER USER john PASSWORD ‘temp123‘ VALID UNTIL ‘2023-01-01‘; 

This temporarily sets the password to ‘temp123‘ while ensuring the user changes it after logging in again.

For heightened security, consider integrating PostgreSQL with an SSO provider for centralized identity and access management across multiple applications.

Guidelines for Secure Passwords

According to benchmarks from the National Institute of Standards and Technology (NIST) in SP 800-63B, the effectiveness of user authentication depends on the strength and complexity of passwords.

Here are password policies you should enforce:

• Minimum 12 character length
• Combinations of upper and lowercase letters
• Inclusion of numeric and special symbols
• No repetitive characters or sequence patterns
• Disallow usernames, real names or common strings
• Encrypted storage using cryptographic hashing functions
• Enforce periodic expiry and history limitations

These rules frustrate brute force, dictionary and other password guessing attacks.

OWASP recommends enabling multi-factor authentication (MFA) by chaining multiple credential types like biometrics and one-time pins along with standard passwords.

Overall, you should align your Postgres user password controls with established standards like PCI DSS 3.2.1 and framework guidelines by NIST or OWASP.

Now let‘s look at how to programmatically generate robust passwords in PostgreSQL.

Generating Strong Random Passwords in SQL

Writing an SQL function to generate random passwords provides an easy way to assign protected credentials when bulk creating user accounts.

Here is an example Postgres SQL function to generate 16 character passwords with symbols:

CREATE OR REPLACE FUNCTION public.gen_random_password(length integer)  
 RETURNS text AS
$BODY$
DECLARE
  chars text[] := ‘{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,!,@,#,$,%,^,&,*,(,)}‘;
  password text := ‘‘;
  i integer := 0; 
BEGIN
  IF length < 8 THEN 
    RAISE EXCEPTION ‘Password length must be at least 8 characters‘;
  END IF;

  WHILE i < length LOOP
    password := password || chars[1+random()*(array_length(chars, 1)-1)];
    i := i + 1; 
  END LOOP; 

  RETURN password;
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

You can generate a random password for a new user like:

INSERT INTO users (name, pass)
VALUES (‘john‘, gen_random_password(16)); 

This securely creates user ‘john‘ with a 16 character randomized password.

Follow this approach to assign strong passwords when managing multiple user accounts.

Auditing User Activity

Beyond just passwords, you should proactively monitor user connections, queries and access to safeguard Postgres data security.

Enable logs like:

• PostgreSQL log – Records server messages, warnings, errors etc.
• Event trigger logs – Logs DDL and DML changes to objects
• csvlogs – Logs session and transaction data

In addition, you can use the built-in module pg_stat_statements to track execution statistics of all SQL statements executed by users.

Integrating intrusion detection systems (IDS) like ossec and monitoring tools like Prometheus further help to alert on anomalous activity.

Make sure to regularly review user permissions and remove any unnecessary privilege grants to enforce least privilege access control.

Conclusion

PostgreSQL utilizes role based access control powered by username and password credentials. Modifying user passwords is a common administrative task.

In this comprehensive guide, you learned various methods to properly change Postgres user passwords using pgAdmin GUI, psql commands and ALTER USER SQL statements. Resetting forgotten passwords of admin accounts was also covered.

Proper password policies secure against guessing, brute force and dictionary attacks attempted by hackers exploiting weak credentials. Referenced standards provide baseline rules for minimum length, complexity and expiries aided by technologies like MFA.

Programmatically generating strong random passwords using PL/pgSQL assists with assigning protected credentials when managing multiple user accounts.

Beyond just setting passwords, you should monitor user connections, activity logs and permissions to respond to suspicious access attempts that could compromise database security.

Overall, implementing robust identity and access management controls safeguards sensitive information within PostgreSQL against rampant cyber threats targeting user credentials.