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

[Dmitriy Igrishin]

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

Thanks.

--
// Dmitriy.

[Guillaume Lelarge-3]

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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Dmitriy Igrishin]

Hey Guillaume,

2012/7/24 Guillaume Lelarge <[hidden email]>

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.

--
// Dmitriy.

[Guillaume Lelarge-3]

On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:

> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge <[hidden email]>
>         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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Dmitriy Igrishin]

2012/7/24 Guillaume Lelarge <[hidden email]>

On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge <[hidden email]>
>         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.

--
// Dmitriy.

[Dmitriy Igrishin]

2012/7/24 Dmitriy Igrishin <[hidden email]>

2012/7/24 Guillaume Lelarge <[hidden email]>

On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:
> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge <[hidden email]>
>         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.

[David Johnston]

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


2012/7/24 Dmitriy Igrishin <[hidden email]>

2012/7/24 Guillaume Lelarge <[hidden email]>
On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote:

> Hey Guillaume,
>
> 2012/7/24 Guillaume Lelarge <[hidden email]>
>         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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Adrian Klaver-3]

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
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Tom Lane-2]

Dmitriy Igrishin <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Dmitriy Igrishin]

2012/7/24 Tom Lane <[hidden email]>

Dmitriy Igrishin <[hidden email]> writes:
> But it's impossible to pass empty (NULL) password to the backend

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 ''? :-)
That is the reason why I've considered empty and NULL as 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.)

Then it should be at least documented in the libpq's documentation that
explicitly specified empty password will force libpq to get it from the file?

                        regards, tom lane

--
// Dmitriy.

[Tom Lane-2]

Dmitriy Igrishin <[hidden email]> writes:
> 2012/7/24 Tom Lane <[hidden email]>
>> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Dmitriy Igrishin]

2012/7/24 Tom Lane <[hidden email]>

Dmitriy Igrishin <[hidden email]> writes:

> 2012/7/24 Tom Lane <[hidden email]>
>> 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?

Oops, I am sorry, Tom. It doesn't on my 9.2beta2.
Perhaps, it's too hot in Russia now...
But maybe it's worth it to add a parameter key word to libpq,
e.g. "passwd" which's empty value will be treated as an
empty password?

--
// Dmitriy.