Password authentication methods in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to set the password authentication method to md5 in the pg_hba.conf file.

PostgreSQL

host all all 0.0.0.0/0 [1]

Drag options to blanks, or click blank then click option'

Atrust

Bpeer

Cmd5

Dident

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'trust' which allows connections without password.

Using 'peer' which uses OS user authentication.

Using 'ident' which relies on client IP identification.

The md5 method requires users to provide a password hashed with MD5. This line sets password authentication for all users and databases.

2fill in blank

medium

Complete the SQL command to change a user's password using md5 encryption.

PostgreSQL

ALTER USER alice WITH PASSWORD '[1]';

Drag options to blanks, or click blank then click option'

Amd5password123

Bmd5c4ca4238a0b923820dcc509a6f75849balice

Cmd5c4ca4238a0b923820dcc509a6f75849b

Dpassword123

Attempts:

3 left

💡 Hint

Common Mistakes

Providing the hashed password manually.

Including the username in the hashed password string.

Omitting quotes around the password.

When setting a password in PostgreSQL, you provide the plain password string. PostgreSQL handles the MD5 hashing internally.

3fill in blank

hard

Fix the error in the pg_hba.conf line to require password authentication using scram-sha-256.

PostgreSQL

host all all 192.168.1.0/24 [1]

Drag options to blanks, or click blank then click option'

Ascram-sha-256

Btrust

Cpassword

Dmd5

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'password' which sends clear-text password.

Using 'trust' which disables password authentication.

Using 'md5' which is a different (weaker) password method.

The scram-sha-256 authentication method provides secure password authentication using the SCRAM-SHA-256 protocol.

4fill in blank

hard

Fill both blanks to complete the SQL command that creates a user with SCRAM password authentication.

PostgreSQL

CREATE USER bob WITH [1] '[2]';

Drag options to blanks, or click blank then click option'

APASSWORD

BENCRYPTED PASSWORD

Cpassword123

Dscram-sha-256

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ENCRYPTED PASSWORD' which is invalid syntax.

Providing the hashed password instead of plain text.

Omitting quotes around the password.

To create a user with a password for use with SCRAM authentication, use PASSWORD followed by the plain password string after setting password_encryption = scram-sha-256 in postgresql.conf. PostgreSQL will handle the SCRAM hashing.

5fill in blank

hard

Fill all three blanks to complete the pg_hba.conf line that allows local connections with SCRAM authentication.

PostgreSQL

local [1] [2] [3]

Drag options to blanks, or click blank then click option'

Aall

Bscram-sha-256

Cpassword

Dpeer

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'peer' which uses OS user authentication.

Using 'password' which transmits clear-text password.

Using 'trust' which disables password authentication.

This line allows all users to connect locally using SCRAM-SHA-256 authentication: local all all scram-sha-256.

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