MD5 password issue

On Thu, 2009-01-15 at 18:05 +0100, Andreas Wenk wrote:

postgres=# SELECT rolname,rolpassword from pg_authid;
rolname | rolpassword
- -----------+-------------------------------------
postgres |
pgadmin | plaintext
odie | md5passswwwwooooorrrd

The user odie was created with:
CREATE ROLE odie LOGIN ENCRYPTED PASSWORD 'feedme';

The user pgadmin was created with:
$ createuser -a -d -P -N -U postgres pgadmin

Per the help. You need to pass -E to have it be an encrypted (md5 hash)
style password. What version of PostgreSQL is this as I recall all newer
versions do this by default.

Usage:
createuser [OPTION]... [ROLENAME]

Options:
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles
-l, --login role can login (default)
-L, --no-login role cannot login
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-c, --connection-limit=N connection limit for role (default: no
limit)
-P, --pwprompt assign a password to new role
-E, --encrypted encrypt stored password
-N, --unencrypted do not encrypt stored password
-e, --echo show the commands being sent to the server
--help show this help, then exit
--version output version information, then exit

Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to
create)
-W, --password force password prompt

If one of -s, -S, -d, -D, -r, -R and ROLENAME is not specified, you will
be prompted interactively.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Andreas Wenk <a.wenk@netzmeister-st-pauli.de> writes:

In pg_hba.conf we have:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.97.0/24 md5

Now the question: why is the user pgadmin able to connect to the database using pgAdmin
III from 192.168.97.30? That sould not be possible ... or am I wrong?

Why shouldn't it be possible? You've specifically allowed connections
from that IP range.

(If you're wondering why he didn't have to type his password,
it's likely because pgAdmin is getting it out of ~/.pgpass or
some private settings file.)

regards, tom lane

Hi Joshua

Joshua D. Drake schrieb:

On Thu, 2009-01-15 at 18:05 +0100, Andreas Wenk wrote:

postgres=# SELECT rolname,rolpassword from pg_authid;
rolname | rolpassword
- -----------+-------------------------------------
postgres |
pgadmin | plaintext
odie | md5passswwwwooooorrrd

The user odie was created with:
CREATE ROLE odie LOGIN ENCRYPTED PASSWORD 'feedme';

The user pgadmin was created with:
$ createuser -a -d -P -N -U postgres pgadmin

Per the help. You need to pass -E to have it be an encrypted (md5 hash)
style password.

Sure - I know .... we added -N so that the password is not encrypted

What version of PostgreSQL is this as I recall all newer

versions do this by default.

this was made with a 8.1 version ...

Usage:
createuser [OPTION]... [ROLENAME]

Options:
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles
-l, --login role can login (default)
-L, --no-login role cannot login
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-c, --connection-limit=N connection limit for role (default: no
limit)
-P, --pwprompt assign a password to new role
-E, --encrypted encrypt stored password
-N, --unencrypted do not encrypt stored password
-e, --echo show the commands being sent to the server
--help show this help, then exit
--version output version information, then exit

Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to
create)
-W, --password force password prompt

If one of -s, -S, -d, -D, -r, -R and ROLENAME is not specified, you will
be prompted interactively.

Joshua D. Drake

--

St.Pauli - Hamburg - Germany

Andreas Wenk

Hi Tom,

Tom Lane schrieb:

Andreas Wenk <a.wenk@netzmeister-st-pauli.de> writes:

In pg_hba.conf we have:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.97.0/24 md5

Now the question: why is the user pgadmin able to connect to the database using pgAdmin
III from 192.168.97.30? That sould not be possible ... or am I wrong?

Why shouldn't it be possible? You've specifically allowed connections
from that IP range.

Yes thats correct with the IP address range. Maybe I did not understand
the auth concept yet. I thought, that with METHOD set to md5, a md5
hashed password is required. The password is submitted with the PHP 5
pg_connect function - as plain text.

(If you're wondering why he didn't have to type his password,
it's likely because pgAdmin is getting it out of ~/.pgpass or
some private settings file.)

regards, tom lane

Also to Peter. It is like that - the pasword is stored in ~/.pgpass as
expected.

So maybe the better question is: what is the difference between METHOD
password and md5? As I assume now because of your answers, it has
nothing to do with either the password is md5 hashed or not?

Thanks to everybody!

cheers

Andy

--

St.Pauli - Hamburg - Germany

Andreas Wenk

Andreas Wenk wrote:

Yes thats correct with the IP address range. Maybe I did not understand
the auth concept yet. I thought, that with METHOD set to md5, a md5
hashed password is required. The password is submitted with the PHP 5
pg_connect function - as plain text.

It is specified to pg_connect as plain text, but it is sent over the
wire md5-hashed.

So maybe the better question is: what is the difference between METHOD
password and md5? As I assume now because of your answers, it has
nothing to do with either the password is md5 hashed or not?

The difference is what travels on the wire.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera schrieb:

Andreas Wenk wrote:

Yes thats correct with the IP address range. Maybe I did not understand
the auth concept yet. I thought, that with METHOD set to md5, a md5
hashed password is required. The password is submitted with the PHP 5
pg_connect function - as plain text.

It is specified to pg_connect as plain text, but it is sent over the
wire md5-hashed.

So maybe the better question is: what is the difference between METHOD
password and md5? As I assume now because of your answers, it has
nothing to do with either the password is md5 hashed or not?

The difference is what travels on the wire.

ok thanks - I think I got it now ... ;-)

Cheers

Andy

--

St.Pauli - Hamburg - Germany

Andreas Wenk