40

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres):

postgres=# select usename, passwd is null from pg_shadow;
 usename  | ?column? 
----------+----------
 postgres | t
(1 row)

... and one is advised to set it with:

alter role postgres password '<<very-secret>>';

(and then update the pg_hba.conf file accordingly)

My question is: what is the SQL to use to revert back to the previous situation when no password was needed for user postgres.

In general, how can I remove the password requirement for any role? I am not asking how to change the password but rather how to remove the password requirement (null passwd column in table pg_shadow).

51

Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird.

pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same username in the operating system as in PostgreSQL, use peer (UNIX, only for local connections) or sspi (Windows).

If there's a password set, but pg_hba.conf doesn't tell PostgreSQL to ask for it, the password is ignored.

If pg_hba.conf tells PostgreSQL to ask for a password but there's none set, then all login attempts will fail no matter what password is supplied.

10

The postgres user by default has no password. To remove a user password (in this case for the postgres user/role):

alter role postgres password null;

We also need to set authentication to trust in pg_hba.conf - view details [https://dba.stackexchange.com/a/19657/52550]

  • 3
    now i cant acces in psql – Roberth Solís Dec 3 '17 at 0:19
  • 3
    Yeah, now I'm asked for a password and can't login... – Amalgovinus Dec 29 '17 at 0:07
  • 1
    this command bans me from postgres - same as @Amalgovinus said. – Nam G VU Feb 6 '18 at 14:12
  • If you can't access because of the error psql: fe_sendauth: no password supplied, then edit your C:\Program Files\PostgreSQL\10\data\pg_hba.conf and replace md5 with trust next to 127.0.0.1/32 under IPv4 local connections and next to ::1/128 under IPv6 local connections – Marco Lackovic Aug 8 at 8:27
1

This worked for me:

alter role postgres password '';

  • 4
    When I do this in postgres 10, I get the error "NOTICE: empty string is not a valid password, clearing password". But then it changes my password anyway and I can't login with anything! WTF!! – Amalgovinus Dec 29 '17 at 0:23
  • @Amalgovinus see my comment to lalligood answer – Marco Lackovic Aug 8 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.