Password authentication methods in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding PostgreSQL password authentication methods

Which PostgreSQL password authentication method stores passwords in a hashed format using MD5 and requires the client to send an MD5-hashed password for verification?

A password

B md5

C trust

D peer

💡 Hint

Think about which method hashes the password before sending it over the network.

The md5 method stores passwords hashed with MD5 and requires the client to send an MD5 hash of the password, providing better security than sending plain text.

❓ query_result

intermediate

Result of authentication method in pg_hba.conf

Given the following line in pg_hba.conf:

host all all 192.168.1.0/24 scram-sha-256

What does this configuration enforce for connections from the 192.168.1.0/24 network?

A Requires SCRAM-SHA-256 password authentication

B Allows connections without password

C Allows only local socket connections

D Requires password sent in clear text

💡 Hint

Look at the authentication method specified at the end of the line.

The scram-sha-256 method requires clients to authenticate using the SCRAM-SHA-256 password mechanism, which is more secure than MD5.

📝 Syntax

advanced

Identify the syntax error in pg_hba.conf entry

Which of the following pg_hba.conf lines contains a syntax error?

A host all all 10.0.0.0/8 md5

B local all all trust

C hostssl all all 0.0.0.0/0 scram-sha-256

D host all all 192.168.1.0/24 password md5

💡 Hint

Check the order and validity of authentication methods in the line.

Option D incorrectly combines two authentication methods password and md5. Only one method should be specified.

❓ optimization

advanced

Optimizing password authentication for security and performance

You want to configure PostgreSQL to use the most secure password authentication method that is widely supported by clients, while minimizing performance overhead. Which method should you choose?

A scram-sha-256

B password

C md5

D trust

💡 Hint

Consider modern security standards and client support.

scram-sha-256 is the most secure widely supported method, providing strong password hashing and challenge-response authentication with reasonable performance.

🔧 Debug

expert

Debugging failed password authentication with SCRAM-SHA-256

A PostgreSQL server is configured to use scram-sha-256 authentication in pg_hba.conf. A client attempts to connect but receives an authentication failure. Which of the following is the most likely cause?

A The <code>pg_hba.conf</code> file is missing the <code>hostssl</code> keyword

B The client is connecting over a local socket

C The user's password is stored using MD5 hashing, not SCRAM-SHA-256

D The server is running on an unsupported PostgreSQL version

💡 Hint

Think about password storage format compatibility with authentication method.

If the user's password is stored as an MD5 hash, SCRAM-SHA-256 authentication will fail because it requires passwords stored in SCRAM format.