PostgreSQL: Remove password requirement for user postgres

Question

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

Answer 1

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.

Answer 2

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

Answer 3

As of 2020-11-22, using PostgreSQL 13 on ubuntu 18, i stumbled over the answers given by @lalligood (and @thouliha - meanwhile deleted), locking myself user postgres passwordless out.

The following assumes that the login is done using the psql (wrapper-) program on the computer on which the PostgreSQL-server is running.

After reading the PostgreSQL 13.0 Documentation, chapter 20 (Client Authentication), i wondered why to change the ...127.0.0.1/32.... and ...::1/128... lines to trust authentication. The docs say clear: the first line in pg_hba.conf that fits is taken and all further lines are ignored. So it should be sufficient to just use the (default) first line

local   all     postgres    peer

This line is related to a (unix-) socket connection (from psql) to the PostgreSQL server. The unmentioned "problem" is that the psql program uses by default a TCP connection (even) to localhost.

To prove this, just comment out the ...127.0.0.1/32.... and ...::1/128... lines in pg_hba.conf and make the PostgreSQL server reread the modified lines.

How to do, from the docs:

If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload, calling the SQL function pg_reload_conf(), or using kill -HUP) to make it re-read the file.

You may also "brutally" issue

 systemctl restart postgresql 

if a db-restart does not matter.

Then, trying to connect using the psql program an error message will occur indicating that psql tried to connect via TCP.

Once using psql to connect via unix-socket the first line in pg_hba.conf works and (super-) user postgres may set his password to null or any string and still connect without beeing asked for password.

To connect via socket:

psql -h /var/run/postgresql -p 5432

or

export PGPORT=5432; psql -h /var/run/postgresql

and further on in the console session just use

psql -h /var/run/postgresql

see:

man psql

especially for the "-h" ond "-p" options.

Citation from my console, password was not required nor asked (sys lang is german):

postgres@hostname:/tmp$ psql -h /var/run/postgresql -p 5432

psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1))

Geben Sie »help« für Hilfe ein.

postgres=# alter role postgres password null;

ALTER ROLE

postgres=# \q

postgres@hostname:/tmp$ psql -h /var/run/postgresql -p 5432

psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1))

Geben Sie »help« für Hilfe ein.

postgres=# alter role postgres password 'secret';

ALTER ROLE

postgres=# \q

postgres@hostname:/tmp$ psql -h /var/run/postgresql -p 5432

psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1))

Geben Sie »help« für Hilfe ein.

Addendum:

@a_horse_with_no_name posted a comment citing from man psql:

If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets.

Thank you for the hint, that is correct, but is directly followed by the relativating

The default port number is determined at compile time. Since the database server uses the same default, you will not have to specify the port in most cases.

This should result in the need to specify host or socket-dir (-h argument) when running the db-server on another than the standard port 5432.

I must confess i cheated in "Citation from my console" above as far as "hostname" is not the real hostname of my computer and 5432 is not the port my PostgreSQL 13 is serving at. Did it for reasons of privacy and simplicity. In fact, i have a PostgreSQL 9.6 running on port 5432 and the 13-version running on another port simultaneously.

For a test i disabled the ...127.0.0.1/32.... and ...::1/128... lines in pg_hba.conf of both, 9.6 and 13 so that only sockect connections will be possible.

Anyway, following the comment of @a_horse_with_no_name a simple

psql

should connect via socket to my PostgreSQL 9.6 server. But it does not! It does even not when i issue

psql -p 5432

The same result if the server version 13 is disabled so that only one single db-server (9.6) on standard port 5432 is running.

Nor it does when i try

psql -p [port of my version 13 db-server]

with running server 13 version.

In all that cases psql tries to connect not via socket, but via TCP. Have i overseen something? At least my solution, specifying the socket dir, should work anyway.

On Ubuntu 18.04, providing unix-sockets, it seems that the -h argument and its parameter, specifying port or socket-dir is needed.

By the way, neither the PGHOST nor the PGPORT environment variables were set, but undefined.

If anyone knows better, please comment.

Answer 4

If you do not tell PSQL what user name nor database to use when connecting it uses the system $USER to identify the user logging in and attaches to the database with that name. It needs both user and the database to make the connection properly. This is out of section 1.4 of the PostgreSQL documentation.

If you do not supply the database name then it will default to your user account name.

AFAIK if there is no database with that name to connect to it will fail (and it did when I tested it that way). If a database exists with a name that matches $USER then it will connect to that database with "psql" and no database specified (again I tested to be sure).