[GENERAL] 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 DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall 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 | md5passsorrrd 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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 | md5passsorrrd 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-superuserrole will not be superuser -d, --createdbrole 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-loginrole 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, --pwpromptassign a password to new role -E, --encrypted encrypt stored password -N, --unencrypted do not encrypt stored password -e, --echoshow the commands being sent to the server --helpshow 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, --passwordforce 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: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
Andreas Wenk a.w...@netzmeister-st-pauli.de writes: In pg_hba.conf we have: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
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 | md5passsorrrd 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-superuserrole will not be superuser -d, --createdbrole 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-loginrole 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, --pwpromptassign a password to new role -E, --encrypted encrypt stored password -N, --unencrypted do not encrypt stored password -e, --echoshow the commands being sent to the server --helpshow 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, --passwordforce 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
Hi Tom, Tom Lane schrieb: Andreas Wenk a.w...@netzmeister-st-pauli.de writes: In pg_hba.conf we have: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostall 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MD5 password issue
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
