0

I'm trying to get PostgreSQL setup on a new (Linux Mint) computer. Everything works great until I try to access the database. When I do, as my own user, I get the following (expected) error:

$ psql 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "me" does not exist

That makes sense, because on Linux there's a separate postgres user with DB access. I tried using them instead:

$ sudo su postgres
[sudo] password for me:
$ psql
Password for user postgres:  psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

At this point I became confused: the postgres user is supposed to have password-less access to all PostgreSQL databases. Furthermore, I tried setting my pg_hba.conf file to allow all users local access:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

However, even after I restarted PostgreSQL (with /etc/init.d/postgresql reload) I still was prompted for a database password for user postgres.

Can anyone help me understand:

  1. Why am I getting prompted for a password when I try to access my database as the postgres user (on a fresh install)?
  2. Why do I still get prompted even after setting IPv4/IPv6 local connections to trust?
  3. How can I fix my postgres user to be like other Linux boxes (ie. not have any database password, but still be able to access all DBs)
Improve this question
5
  • FATAL: role "me" does not exist You're using the wrong user. Be specific in your commands for psql, something like this: psql -p 5432 -h localhost -U postgres -d your_database_name This command uses the superuser "postgres" and not your default user "me" Commented Apr 8 at 18:34
  • See also postgresql.org/docs/current/app-psql.html Commented Apr 8 at 18:36
  • Thank you, but I do understand that my me user can't access: that's expected (please read the full question). What I don't understand is why the postgres user can't access. Also, just to clarify, when I'm logged in as the (system) user postgres, I shouldn't need -U postgres (because the database user is inferred to be the system user). Commented Apr 8 at 18:45
  • 1
    That's because of socket. You didn't set local in your configuration. Add this to pg_hba.conf or use a TCP/IP connection like my example. Commented Apr 8 at 18:52
  • I see, thank you! I was editing the "IPv4/6 local connections" thinking they were ... well, local connections. However, that was (I guess?) just confusing wording, and when I edited the "local is for Unix domain socket connections only" line everything worked. That answers everything (except why the box didn't start out this way, but I guess that's just a Linux Mint config decision) ... could you please provide things in an official answer, so that I can accept it? Commented Apr 8 at 19:58

1 Answer 1

Reset to default
1

You changed the lines for TCP connections from localhost, but you didn't change the line for local Unix socket connections. That should look like

local  all  all  trust
Improve this answer

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.