1

I just create a user and new database on my local machine.

sudo su - postgres -c "createuser gauthier"
sudo su - postgres -c "createdb local-ag-db"

Then:

$ sudo -u postgres psql
sudo -u postgres psql
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.

postgres=# grant all privileges on database "local-ag-db" to gauthier;
GRANT

I can access the database locally through a socket:

$ psql local-ag-db gauthier
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.

local-ag-db=> \conninfo
You are connected to database "local-ag-db" as user "gauthier" via socket in "/var/run/postgresql" at port "5432".

But I cannot access it through localhost:

$ psql -d local-ag-db -h localhost -U gauthier
Password for user gauthier: 
psql: fe_sendauth: no password supplied

My guess is that while createuser accepted to create a user without a password, logging in through the host requires identification that this user cannot provide.

This is the default content of /etc/postgresql/11/main/pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I have tried adding this at the end of that file, after reading the included documentation:

host   local-ag-db     gauthier        samehost                peer

but that did not work (after a restart sudo /etc/init.d/postgresql restart), it even broke my Unix socket access:

$ psql local-ag-db gauthier
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I also tried this as last row of my config, with the same result:

host    local-ag-db     gauthier        127.0.0.1/32            peer

My questions:

  • how do I allow my local user to access the database through localhost?
  • how do I edit my conf file so that I don't break everything? (Unix socket method stops working when I add a rule)

On Debian.

Improve this question
2
  • 2
    Use ALTER USER to add a password to role gauthier. Or if you want to live dangerously you could set the method to trust. Commented Feb 3, 2021 at 17:04
  • 1
    Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? <<-- answer these questions 1) is there a unix-domain socket? 2) is it listening ? Commented Feb 3, 2021 at 17:17

2 Answers 2

Reset to default
4

You will need to add a line like:

host    all             all             localhost            trust

As you have no explicit rule currently it is dropping through to another catch all rule and prompting for authentication. Adrian's comment above is saying the same and likewise I don't suggest using trust outside of testing your connectivity - you really should assign a password and enforce it.

Adding entries to the pg_hba.conf needs care as the PostgreSQL will find the first match:

The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

From https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Regarding

Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Looks like the restart may not have worked. Looks like no response rather than authentication failure.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, so I misunderstood how the config file was read. I now temporarily change the host/all/all/127.0.0.1/32 row to trust, restarted, and got the no password supplied again. Regarding the server restart, I don't get it. The exit value of the restart was 0, and the text output said ok.
So, I previously only changed the IPv4 version to trust, now that I've changed it for ::1/128 it works! Maybe you can mention changing both rows and I'll mark your answer as accepted? Also, I'm not sure if this is so bad to have trust here, no important data is going in the database, it's just for development (I've also restricted the trust to only this test database).
OK so I replicated and I had similar, it seems localhost and 127.0.0.1 are different in this context - if anyone can explain quite why, I'd be interested. I know 127.0.0.1 is a local address and hence will go through the interface card, where as localhost won't, but thats my limit here. When I use -h 127.0.0.1 it works for me To use -h localhost I have to add: host all all localhost trust So I learnt something, thank you. :-)
Edited my original answer to reflect previous comment.
Oh but I do have 127.0.0.1/32 and ::1/128 in the file, and login with -h localhost!
1

Your server is not running. It never came back up when you tried to restart it, because you broke your hba. If you look in the log file, you should something like:

LOG:  peer authentication is only supported on local sockets
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

Unbreak your pg_hba.conf and start the server again. Also, read your log files when you don't know what's going on. That is what they are there for.

You will need to choose a different method for authentication over localhost. ident is equivalent to peer, but it requires you to run the identd daemon on the server, and is not very secure for any addresses other than loopback ones.

Improve this answer

1 Comment

I naively thought that a return value of 0 on the restart command meant it was successful. As well as this reply: [ ok ] Restarting postgresql (via systemctl): postgresql.service. really doesn't make look like something was broken. show log_destination ; says stderr and that's it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.