Password authentication methods in PostgreSQL - Mini Project: Build & Apply

Password Authentication Methods in PostgreSQL

📖 Scenario: You are setting up a PostgreSQL database for a small company. You need to create users with different password authentication methods to understand how PostgreSQL handles security.

🎯 Goal: Create three PostgreSQL users with different password storage methods corresponding to authentication methods: md5, scram-sha-256, and plain-text (off). This will help you learn how to configure and use password authentication in PostgreSQL.

📋 What You'll Learn

Create a user named user_md5 with password storage method md5 and password md5pass.

Create a user named user_scram with password storage method scram-sha-256 and password scrampass.

Create a user named user_plain with password storage method plain-text (off) and password plainpass.

Use the exact SQL commands to create these users with the specified storage methods.

💡 Why This Matters

🌍 Real World

Database administrators often need to manage user authentication methods to secure access to PostgreSQL databases.

💼 Career

Understanding password authentication methods is essential for roles like database administrator, backend developer, and security engineer.

1

Create user user_md5 with MD5 password authentication

Write SQL commands to set password_encryption to 'md5' and create a PostgreSQL user named user_md5 with password md5pass using the MD5 password storage method.

PostgreSQL

-- Set password_encryption to md5 and create user_md5 with MD5 password authentication
-- Your code here

Hint

Use SET password_encryption = 'md5'; then CREATE USER with WITH PASSWORD clause. This forces MD5 hashing of the password.

2

Create user user_scram with SCRAM-SHA-256 password authentication

Write SQL commands to set password_encryption to 'scram-sha-256' and create a PostgreSQL user named user_scram with password scrampass using the SCRAM-SHA-256 password storage method.

PostgreSQL

SET password_encryption = 'md5';
CREATE USER user_md5 WITH PASSWORD 'md5pass';
-- Set password_encryption to scram-sha-256 and create user_scram
-- Your code here

Hint

Use SET password_encryption = 'scram-sha-256'; before creating the user with password. This stores the SCRAM-SHA-256 hash.

3

Create user user_plain with plain text password authentication

Write SQL commands to set password_encryption to 'off' and create a PostgreSQL user named user_plain with password plainpass using plain text password storage.

PostgreSQL

SET password_encryption = 'md5';
CREATE USER user_md5 WITH PASSWORD 'md5pass';
SET password_encryption = 'scram-sha-256';
CREATE USER user_scram WITH PASSWORD 'scrampass';
-- Set password_encryption to off and create user_plain with plain text password
-- Your code here

Hint

Plain text passwords are not recommended but can be used by setting password_encryption = 'off' before creating the user. The password will be stored as plain text.

4

Verify the password storage methods

Write a SQL query to verify the different password storage methods for the three users by checking the rolpassword column.

PostgreSQL

SET password_encryption = 'md5';
CREATE USER user_md5 WITH PASSWORD 'md5pass';
SET password_encryption = 'scram-sha-256';
CREATE USER user_scram WITH PASSWORD 'scrampass';
SET password_encryption = 'off';
CREATE USER user_plain WITH PASSWORD 'plainpass';
-- Query to verify password storage
-- Your code here

Hint

Use SELECT rolname, rolpassword FROM pg_authid WHERE rolname LIKE 'user_%'; You should see 'md5...' for user_md5, 'SCRAM-SHA-256...' for user_scram, and plain text for user_plain.

Practice

1. Which password authentication method in PostgreSQL is considered more secure and recommended for use?

easy

A. scram-sha-256

B. md5

C. password

D. trust

Solution

1. Step 1: Understand common PostgreSQL password methods

   PostgreSQL supports several password authentication methods including md5 and scram-sha-256.

2. Step 2: Compare security levels

   SCRAM-SHA-256 is a newer, more secure method than MD5, which is older and less secure.

3. Final Answer:

   scram-sha-256 -> Option A

4. Quick Check:

   More secure method = scram-sha-256 [OK]

Hint: SCRAM is newer and stronger than MD5 for passwords [OK]

Common Mistakes:

• Confusing md5 as more secure than scram-sha-256
• Choosing 'password' which sends plain text
• Selecting 'trust' which requires no password

2. Which line correctly sets password authentication to SCRAM in the pg_hba.conf file?

easy

A. host all all 0.0.0.0/0 password

B. host all all 0.0.0.0/0 md5

C. host all all 0.0.0.0/0 scram-sha-256

D. host all all 0.0.0.0/0 trust

Solution

1. Step 1: Identify the correct authentication method syntax

   The pg_hba.conf file uses lines like 'host all all address method' to set authentication.

2. Step 2: Match method to SCRAM

   To use SCRAM, the method must be exactly 'scram-sha-256'.

3. Final Answer:

   host all all 0.0.0.0/0 scram-sha-256 -> Option C

4. Quick Check:

   SCRAM method line = host all all 0.0.0.0/0 scram-sha-256 [OK]

Hint: SCRAM method is 'scram-sha-256' exactly in pg_hba.conf [OK]

Common Mistakes:

• Using 'md5' instead of 'scram-sha-256' for SCRAM
• Confusing 'password' with SCRAM
• Omitting the IP address or using wrong format

3. Given this pg_hba.conf line: host all all 192.168.1.0/24 md5, what happens when a user connects from IP 192.168.1.15?

medium

A. The user must use SCRAM authentication.

B. The user connects without a password.

C. The connection is rejected automatically.

D. The user must provide a password hashed with MD5 to authenticate.

Solution

1. Step 1: Analyze the IP range and method

   The line applies to IPs in 192.168.1.0/24, which includes 192.168.1.15, and uses md5 authentication.

2. Step 2: Understand md5 authentication behavior

   MD5 requires the client to send an MD5-hashed password for authentication.

3. Final Answer:

   The user must provide a password hashed with MD5 to authenticate. -> Option D

4. Quick Check:

   IP in range + md5 method = MD5 password required [OK]

Hint: MD5 method means password hashed with MD5 is required [OK]

Common Mistakes:

• Assuming SCRAM is used instead of MD5
• Thinking no password is needed
• Believing connection is rejected without password

4. You set host all all 0.0.0.0/0 scram-sha-256 in pg_hba.conf but users still connect without password prompts. What is the likely cause?

medium

A. The scram-sha-256 method is misspelled

B. PostgreSQL was not reloaded after changing pg_hba.conf

C. Users have no passwords set in the database

D. The IP address range is incorrect

Solution

1. Step 1: Check if configuration changes are active

   Changes to pg_hba.conf require PostgreSQL reload to take effect.

2. Step 2: Identify why password prompts are missing

   If users connect without password prompts, likely the new method is not active due to missing reload.

3. Final Answer:

   PostgreSQL was not reloaded after changing pg_hba.conf -> Option B

4. Quick Check:

   Config changes need reload = missing reload causes issue [OK]

Hint: Always reload PostgreSQL after pg_hba.conf changes [OK]

Common Mistakes:

• Assuming misspelling causes no prompt instead of error
• Ignoring need to reload server
• Thinking IP range affects password prompt

5. You want to enforce SCRAM authentication only for users connecting from the local network (192.168.0.0/16) and allow password authentication (md5) for others. Which two lines in pg_hba.conf achieve this correctly?

hard

A. host all all 192.168.0.0/16 scram-sha-256
host all all 0.0.0.0/0 md5

B. host all all 0.0.0.0/0 scram-sha-256
host all all 192.168.0.0/16 md5

C. host all all 192.168.0.0/16 md5
host all all 0.0.0.0/0 scram-sha-256

D. host all all 0.0.0.0/0 trust
host all all 192.168.0.0/16 scram-sha-256

Solution

1. Step 1: Understand pg_hba.conf line order and matching

   PostgreSQL checks lines top to bottom and uses the first matching rule.

2. Step 2: Set SCRAM for local network first, then md5 for others

   Line 1: local network with scram-sha-256; Line 2: all others with md5.

3. Final Answer:

   host all all 192.168.0.0/16 scram-sha-256 host all all 0.0.0.0/0 md5 -> Option A

4. Quick Check:

   Specific local network first, then general others [OK]

Hint: Put specific IP range first, general last in pg_hba.conf [OK]

Common Mistakes:

• Reversing line order causing wrong method to apply
• Using 'trust' which disables password
• Assigning md5 to local network instead of SCRAM