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

Need a 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

Need a 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

Need a 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

Need a 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.