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.