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

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Dmitriy Igrishin
Sent: Tuesday, July 24, 2012 10:00 AM
To: Guillaume Lelarge
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

2012/7/24 Dmitriy Igrishin <dmitigr(at)gmail(dot)com>

2012/7/24 Guillaume Lelarge <guillaume(at)lelarge(dot)info>
On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge <guillaume(at)lelarge(dot)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.

Full version :-)
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=> ALTER ROLE test PASSWORD '';
ALTER ROLE
dmitigr=> \c dmitigr test
FATAL: password authentication failed for user "test"
Previous connection kept

It's an incorrect behaviour because it's a user's decision
what a password to have - empty or not.
I'm dubious that the user of some WEB site should contact
to the site admin to ask him to change the auth. method
because the user sets his password to NULL :-).
On the other hand, it's a developer's decision to allow
empty passwords or not to allow them in the software.
--
// Dmitriy.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

It is reasonable that the system administrator can institute a password policy regarding whether the empty-string/NULL (i.e., no password) is allowable regardless of whether the user wants it or not. That said if the system is going to choke when a password is removed then the system should just not allow the user to remove the password in the first place -unless you really want the user to be able to disable their account themselves. Even if you do it would make sense to prompt the user to confirm that they mean to disable their account by removing the password. This seems like a psql oversight. The ALTER ROLE aspect would ideally have an explicit "NO PASSWORD" and then enforce non-empty/non-null when a password is actually present.

My .02

David J.