Thread: MD5 password issue
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi everybody, I posted this allready to the ADMIN list but recieved no reply (what is for sure ok in a way ;-) ). So I thought I'll give it a try here. Sorry for any inconvenience. We are trying to understand an issue concerning the md5 password encryption. The situation is as follows. 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 in pg_authid we get: 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 The -N parameter forces not to encrypt the password - what we can see as a result in pg_authid (if this makes sense or not is another question ;-) ). 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? Thanks for any advice Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJb2y+Va7znmSP9AwRAmGlAKCaingauIMGRvIqAqMBVdiBfhkoXwCeM1kR M/fZSYeJKq9tMe791MhN2J8= =V7hS -----END PGP SIGNATURE-----
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