Re: Roles with empty password (probably bug in libpq and in psql as well).

2012/7/24 Guillaume Lelarge <guillaume@lelarge.info>

On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge <guillaume@lelarge.info>
>         On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote:
>         > Hey all,
>         >
>         > According to
>         http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
>         >
>         > A query:
>         > ALTER ROLE davide WITH PASSWORD NULL;
>         > removes a role's password.
>         >
>         > But it's impossible to pass empty (NULL) password to the
>         backend
>         > by using libpq, because connectOptions2() defined the
>         fe-connect.c
>         > reads a password from the ~/.pgpass even when a password
>         > specified as an empty string literal ("").
>         >
>         > Also, when connecting to the server via psql(1) by using a
>         role
>         > with removed password psql exists with status 2 and prints
>         the error
>         > message:
>         > psql: fe_sendauth: no password supplied
>         >
>
>
>         Yes, and? I don't see how this could be a bug. If your
>         authentication
>         method asks for a password, you need to have one.
> Yes, I need. I just want to have empty password ("").
>
>         If you have resetted
>         it, well, you shouldn't have. Or you really want that your
>         users could
>         connect without a password, and then you need to change your
>         authentication method with trust. But no-one will encourage
>         you to do
>         that.
> Why I need to change an auth. method? If I've used a \password command
> in psql(1) and specified an empty password for my role I need to ask
> a database admin to change an auth. method? :-) Cool!
> Please note, psql(1) allow to do it as well as SQL - too.
>

If your admin sets PostgreSQL so that a password needs to be given while
trying to connect, a "simple user" shouldn't be able to bypass that by
setting no password for his role.

So, yes, if you want to be able to not use a password, you need to
change your authentification method.

dmitigr=> CREATE USER test ENCRYPTED PASSWORD 'test';
CREATE ROLE
dmitigr=> \c dmitigr test
Password for user test:
You are now connected to database "dmitigr" as user "test".
dmitigr=> \password
Enter new password:
Enter it again:

Now the user "test" will not be able to connect to the server.
This behaviour is incorrect.