1

I have installed postgresql version 9.3.14 on High Sierra via HomeBrew. Upon trying to access the server via psql, I was being prompted for a password for the user postgres. Using postgres as the password got me into the database, but I was confused, as I have never been prompted for a password for the default user before. I looked around online on how to circumvent this, and I tried the solution suggested by lalligood on remove password requirement for user postgres which was to run alter user postgres password null; and now I cannot access psql. Trying postgres, null or omitting a password does not get me in, and I do not know what to do.

NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
K Pekosh
  • 111
  • 1
  • 3

1 Answers1

1

Check your PostgreSQL pg_hba.conf. It should have lines like this copied directly from the docs.

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

You don't have to keep these. But it's normal to have these so you can connect locally without having to worry about passwords using your local system authentication methods. You also don't need both of these. If you only ever connect over sockets, feel free to nuke the one with tcp. This will allow you to log in with any locally authenticated user.

So you can log in as PostgreSQL with,

sudo -u postgresql psql -d myDatabase

or using tcp

sudo -u postgresql psql -d myDatabase -h 127.0.0.1
NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
  • I see the following in my `pg_hba.conf` file: `# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust` – K Pekosh Jan 22 '18 at 19:07
  • @KPekosh that's fine so you can log in if the user your authenticating with in the database is also the user you're logged in with locally. If you want to log in with the `postgres` user, simply become the `postgres` user with `sudo` that user exists in your system (or should on *nix anyway) – NO WAR WITH RUSSIA Jan 22 '18 at 19:08
  • I tried the `sudo -u postgresql psql -d myDatabase` suggested and I get a Permission denied message. If I try `sudo su - postgres`, I'm logged in as postgres, but I'm not sure what to do from there. – K Pekosh Jan 22 '18 at 19:11
  • @KPekosh just try running `psql -d myDatabase` if you have `trust` it shouldn't ask you for a password. If it does did you just add that line or uncomment it? If so you need to restart the server. If not, are you connecting to the right instance. Do you have two on your machine? Is the connection not working or the login? – NO WAR WITH RUSSIA Jan 22 '18 at 19:23