Home / server / Questions / 1166699
Accepted
GuteShel
Asked: 2024-10-17 13:42:05 +0800 CST

Why is PostgreSQL not prompting for password?

  • 772

In PostgreSQL, I have the following configuration in pg_hba.conf:

local   all     sec_eng                 scram-sha-256
host    all     sec_eng                 scram-sha-256
hostssl all     app_server  192.168.1.0/32      scram-sha-256   clientcert=verify-full

There are no other configuration lines.

When I try to connect to PostgreSQL using sudo psql -U sec_eng, the client authenticates me as a peer, and does not prompt me for any password, and therefore the authentication fails.

If I try sudo psql -U sec_eng -h localhost I am authenticated as Ident, am not prompted for a password, and therefore the authentication fails.

What am I doing wrong here? How can I connect to PostgreSQL through a Unix Socket, using my username and a password?

postgresql

1 Answers

  1. Best Answer

    It is, indeed, the pg_hba.conf file which controls the Postgres authentication, but what's is uncertain is where it is located in a particular installation. By default it's a data directory, but that can be changed in a various ways.

    You can obtain the current value (full path to the file) as a hba_file configuration parameter, if you can connect to the server. For that, authenticate as a superuser (postgres) or as any user who is a member of pg_read_all_settings role, and issue

    SHOW hba_file;

    or

    \x
SELECT * FROM pg_settings WHERE name='hba_file';

    (\x enables extended output, so it'll show it in a columnar fashion, easier to read in this case). See the description of the pg_settings view to understand the output.

    • 1