From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Dmitriy Igrishin Sent: Tuesday, July 24, 2012 10:00 AM To: Guillaume Lelarge Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Roles with empty password (probably bug in libpq and in psql as well). 2012/7/24 Dmitriy Igrishin <dmitigr@xxxxxxxxx> 2012/7/24 Guillaume Lelarge <guillaume@xxxxxxxxxxxx> On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: > Hey Guillaume, > > 2012/7/24 Guillaume Lelarge <guillaume@xxxxxxxxxxxx> > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
