Обсуждение: Roles with empty password (probably bug in libpq and in psql as well).

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


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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


2012/7/24 Dmitriy Igrishin <dmitigr@gmail.com>

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.

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
chokewhen 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
senseto prompt the user to confirm that they mean to disable their account by removing the password.  This seems like a
psqloversight.  The ALTER ROLE aspect would ideally have an explicit "NO PASSWORD" and then enforce non-empty/non-null
whena password is actually present. 

My .02

David J.

On 07/24/2012 05:41 AM, 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.

http://www.postgresql.org/docs/9.2/static/sql-createrole.html
PASSWORD password
Sets the role's password. (A password is only of use for roles having
the LOGIN attribute, but you can nonetheless define one for roles
without it.) If you do not plan to use password authentication you can
omit this option. If no password is specified, the password will be set
to null and password authentication will always fail for that user. A
null password can optionally be written explicitly as PASSWORD NULL.

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

I do not see much traction in the argument no password == password. I do
see where a warning that you are losing the ability to login would be nice.


>
> Thanks.
>
> --
> // Dmitriy.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Dmitriy Igrishin <dmitigr@gmail.com> writes:
> But it's impossible to pass empty (NULL) password to the backend

Please note that empty and null are not the same thing...

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

I rather doubt that we'll change this, because it seems more likely
to break applications that rely on that behavior than to do anything
useful.  Surely nobody in their right mind uses an empty password.

(If anything, I'd be more inclined to make the backend treat an empty
password as an error than to try to make libpq safe for the case.
Even if we did change libpq, there are probably issues with empty
passwords in jdbc and who knows how many other places.)

            regards, tom lane

Dmitriy Igrishin <dmitigr@gmail.com> writes:
> 2012/7/24 Tom Lane <tgl@sss.pgh.pa.us>
>> Please note that empty and null are not the same thing...

> Yes, I know. But why the ALTER ROLE treats '' as NULL and
> as the result all of values of pg_catalog.pg_authid.rolpassword are always
> NULL even when the password in ALTER ROLE was specified as ''? :-)

It does not do that for me.  What PG version are you testing?

            regards, tom lane