PostgreSQL: Remove password requirement for user postgres

linuxpostgresqlpostgresql-9.1

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).

Best Answer

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.

Related Question