Password authentication methods in PostgreSQL - Step-by-Step Execution

Concept Flow - Password authentication methods

Client connects to server

↓

Server requests password

↓

Client sends password

↓

Server checks password method

↓

Compare password using method

↓

If match?

No→Reject connection

Yes↓

Allow connection

The client connects and sends a password. The server checks the password using the configured method. If it matches, connection is allowed; otherwise, rejected.

Execution Sample

PostgreSQL

CREATE USER alice WITH PASSWORD 'secret';
-- Client connects
-- Server uses md5 method
-- Password checked
-- Connection allowed if match

This example shows a user created with a password. When connecting, PostgreSQL uses the md5 method to verify the password.

Execution Table

Step	Action	Password Method	Password Sent	Check Result	Connection Status
1	Client connects	md5	sent hashed password	pending	pending
2	Server receives password	md5	received hashed password	compare hash	pending
3	Server compares password	md5	hashed password	match	pending
4	Password matches?	md5	hashed password	Yes	allow connection
5	Connection established	md5	hashed password	confirmed	connected

💡 Password matches using md5 method, connection allowed

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
password_method	undefined	md5	md5	md5	md5
password_sent	none	hashed password	hashed password	hashed password	hashed password
check_result	none	pending	compare hash	match	match
connection_status	disconnected	pending	pending	pending	connected

Key Moments - 2 Insights

Why does the server compare a hashed password instead of the plain password?

Because the md5 method stores and checks hashed passwords for security, as shown in execution_table step 2 and 3.

What happens if the password does not match?

The connection is rejected immediately after the check fails, as indicated by the 'Password matches?' decision in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the connection status at step 3?

A pending

B connected

C disconnected

D rejected

💡 Hint

Check the 'Connection Status' column at step 3 in the execution_table.

At which step does the server confirm the password matches?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the 'Password matches?' decision in the execution_table.

If the password method changed to 'scram-sha-256', what would change in the execution_table?

A Password Sent column would be empty

B Password Method column would show 'scram-sha-256'

C Connection Status would be 'rejected'

D No changes

💡 Hint

Focus on the 'Password Method' column in the execution_table.

Concept Snapshot

Password authentication in PostgreSQL:
- Client sends password on connect
- Server checks password using method (md5, scram-sha-256, etc.)
- Passwords are hashed for security
- Match allows connection; mismatch rejects
- Method set in pg_hba.conf or user creation

Full Transcript

When a client connects to PostgreSQL, the server requests a password. The client sends the password, usually hashed depending on the method. The server compares the received password with the stored one using the configured authentication method, such as md5 or scram-sha-256. If the password matches, the connection is allowed; otherwise, it is rejected. This process ensures secure authentication by never sending plain passwords over the network. The method used is configured in the server settings and can vary per user or connection type.