Bird
Raised Fist0
PostgreSQLquery~5 mins

Password authentication methods in PostgreSQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Introduction

Password authentication methods help keep your database safe by checking if users provide the right password before they can connect.

When you want to control who can access your database.
When you need to make sure only authorized users can run queries.
When setting up a new database user with a password.
When changing how users log in to improve security.
When troubleshooting login problems related to passwords.
Syntax
PostgreSQL
METHOD = 'password' | 'md5' | 'scram-sha-256' | 'peer' | 'ident' | 'trust' | ...

This syntax is used in the pg_hba.conf file to set authentication methods.

Common password methods are password, md5, and scram-sha-256.

Examples
Require MD5 hashed password for all users connecting from any IP address.
PostgreSQL
host all all 0.0.0.0/0 md5
Require SCRAM-SHA-256 password authentication for users from the local network.
PostgreSQL
host all all 192.168.1.0/24 scram-sha-256
Use peer authentication for local connections (checks OS user name).
PostgreSQL
local all all peer
Sample Program

This example sets the password authentication method to SCRAM-SHA-256 for all users connecting from any IP address. After editing pg_hba.conf, you reload the configuration to apply changes.

PostgreSQL
# In pg_hba.conf file
host all all 0.0.0.0/0 scram-sha-256

-- Then reload PostgreSQL configuration
SELECT pg_reload_conf();
OutputSuccess
Important Notes

MD5 is older and less secure than SCRAM-SHA-256, which is recommended for new setups.

Always reload or restart PostgreSQL after changing authentication methods in pg_hba.conf.

Using trust means no password is needed, which is unsafe for production.

Summary

Password authentication methods protect your database by requiring users to prove their identity.

Common methods include md5 and scram-sha-256, with SCRAM being more secure.

Set these methods in pg_hba.conf and reload PostgreSQL to apply changes.

Practice

(1/5)
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