DEV Community

Ayin Kim
Ayin Kim

Posted on

Will I be safe to edit pg_hba.conf METHOD?

#postgres #crashnote

Background
All I wanted to do with pgadmin 4 and DBeaver were obvious: create a new server/DB.

But when I fill the password form in process, it kept throwing an error message.

FATAL: password authentication failed for user "postgres" (postgresql 11 with pgAdmin 4)

Unfortunately, the error message was shown with alien language(literally, with symbolic charcater. This might be due to different OS language setting). So I had to guess.

This must be authentication or password thing to consider. I even doubt my antivirus software, bitdefender. And reinstalled postgresql, pgadmin, even DBeaver itself.

The problem persists even on 'safe mode' on Windows 11.

Debugging
And found a clue here.
Changing the setting of postgresql must be the solution.

Although the answer stated that 'once you changed that password you wish to create or change the DB, you need to revert it to initial Method.' But this only revived the error message I encountered.

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
Enter fullscreen mode Exit fullscreen mode

Leaving METHOD as trust will not cause any problem if this is not your production purpose. But if you do use your DB on production level, then you must find alternative to figure out this authentication method problem.

So I searched bit more,** [here]**(https://www.postgresql.org/docs/current/auth-pg-hba-conf.html).

Tip

To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it's usually easier to control this by granting/revoking CONNECT privilege than to put the rules in pg_hba.conf entries.
Enter fullscreen mode Exit fullscreen mode

Go to that link and see Example 21.1. Example pg_hba.conf Entries. That's what the document stated.

Let's break down. According to ChatGPT:


- Unix Domain Socket Connections: The line local all all scram-sha-256 means that all local Unix domain socket connections (not applicable on Windows) will use scram-sha-256 authentication. This requires a password encrypted with scram-sha-256.
- IPv4 Local Connections: The line host all all 127.0.0.1/32 trust means that all IPv4 connections from the localhost (127.0.0.1) will be allowed without a password (using the trust method).
- IPv6 Local Connections: The line host all all ::1/128 trust has the same effect as the IPv4 line but applies to IPv6 connections from the localhost.
- Replication Connections: The replication lines are set to use scram-sha-256, meaning that replication connections will require a password encrypted with scram-sha-256.
- With this configuration:
Enter fullscreen mode Exit fullscreen mode

So, with this configuration of pg_hba.conf:

  • Local connections (from the same machine where PostgreSQL is running) will be able to connect without a password.
  • Any connections from other machines will not be allowed, as there are no host lines for other addresses.
  • Replication connections will require a scram-sha-256 encrypted password.

Conclusion
Only modify METHOD to trust for the local connections.

*Disclaimer: *
Just in case, I recommend you consult with a cyber security expert if you are applying this to your production one.

Top comments (0)