0

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?

4
  • 1
    Are you sure this pg_hba.conf is actually the one which is used? Run show hba_file; or select * from pg_settings where name='hba_file'; as the user who is member of pg_read_all_settings role, or as a superuser (postgres), it will show the path. The latter also will tell you the source of the configuration that instructs it to use that file instead of the default. Commented Oct 17, 2024 at 6:05
  • You are correct, the file I was working on was the the file that PostgreSQL was using. I was working on /var/lib/pgsql/17/data/pg_hba.conf, and PostgreSQL was reading from /var/lib/pgsql/data/pg_hba.conf. Thank you very much, mister Kipriyanov. I have a question, if you please. If PostgreSQL was reading from the second path, what's the first path for? (I have not ordered PostgreSQL to read from the second, nor did I create the 17/ directory.) Commented Oct 17, 2024 at 12:23
  • You didn't, Fedora maintainers did. Apparently, their views on the topic of configuration files location are different from PostgreSQL developers views, so they made their own amendments (either to the configuration file template, to the initdb script, to environment variables; there are multiple ways to do it). This does happen very often when using distribution packages. Commented Oct 18, 2024 at 6:27
  • @NikitaKipriyanov understood, thank you, mister Kipriyanov! Commented Oct 18, 2024 at 7:59

1 Answer 1

1

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.

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.