1

I am trying to set my local postgresql so it does not have a password. I understand that this has to be done in the pg_hba.conf file and to acceess that file I have to be a postgres user. But to be a postgres user, I have to login with su postgres and enter the password that I don't have.

Any solution to this (I am on OSX)?

Improve this question

1 Answer 1

Reset to default
6

You're confusing several different concepts about the security model.

There is a postgres operating system user, which the PostgreSQL server runs as in order to isolate its data files and to limit damage in case of a security breach or application bug. PostgreSQL won't run as root for security. This user doesn't generally have a password, but you can change to it via the root account using sudo - you can sudo to this user with something like sudo -i -u postgres.

There is also a postgres database user, the default database superuser. This user doesn't generally have a password by default, but pg_hba.conf allows the postgres operating system user to connect as the postgres PostgreSQL user using peer authentication.

If you want you can change the configuration so that you use a password for the postgres database user, so you can psql -U postgres from any system user account:

  • ALTER USER postgres WITH ENCRYPTED PASSWORD 'blahblah';
  • Edit pg_hba.conf ("hba" is "host-based authentication") to use md5 authentication for local and host connections.
  • Re-start or re-load PostgreSQL

Similarly, if you want to allow any system user to connect as any database user without a password, you must modify pg_hba.conf and set trust as the authentication mode for local and host connection types. Please only use trust authentication for testing.

To learn more, see the client authentication chapter in the PostgreSQL documentation.

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

3 Comments

Thanks for the explanation. But when I do the psql -U postgres command, I am asked for a password...what can this be? (The only thing I really want is to get access to the pg_hba.conf file to alter it.)
@allegutta That suggests your pg_hba.conf is set to md5 authentication for local users. If you want to disable that you can set it to trust. To edit the file you'll need to become the postgres system user using sudo -u postgres -i. If your system user account has a password (to unlock your screen etc) it'll prompt you for that one. You don't edit pg_hba.conf via psql, but using operating system tools directly.
Yes, thanks for your help. I managed to alter my pg_hba.conf file! :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.