April 20th, 2013, 02:59 PM
In my company each user has an separate login role. I am trying to write an PostgreSQL function to verify entered username and password against login role username and password. I create a user by using the query below:
When I look at the properties of user 'foo', I can see its encrypted password is 'md52ded2b65ce11cfe94e88a881982a333e'. I can get this value by
CREATE USER foo WITH PASSWORD 'password'
select 'md5'|| md5('password' || 'foo')
But, when I try to verify username and password by
no results return. Can somebody tell me what I am during wrong? Thanks...
select usename, passwd from pg_user where usename='foo' and passwd = 'md5'||md5('password' || 'foo')
April 20th, 2013, 04:28 PM
why on earth do you need to fumble with the internal PostgreSQL roles? Are you sure you know what you're doing?
The pg_user view does not have any passwords or password hashes. It's a view pointing to the (protected) pg_shadow and only contains a bunch of '*******' instead of the actual hashes. In modern PostgreSQL versions, pg_shadow itself is just a view meant to emulate the old system of users and groups. The actual data is in pg_authid.
But this table is obviously not meant to be readable by anyone but superusers. Giving your application access to it would create a gigantic security hole, especially since MD5 hashes are pretty much a joke nowadays.
To me, this whole approach sounds like a terrible idea. Why can't you use the built-in authentication system instead of trying to write some home-made reimplementation?
April 21st, 2013, 12:15 PM
thank you very much for the reply. Actually I am not good at DB issues. I was told that for security reasons each personnel has a DB role and they provide extra security by giving access rights for tables to those roles (actually they are moving from ORACLE to PostgreSQL and they are trying to manage the new DB exactly the same way they are doing the with old one). Even for the first connection to authenticate user, I need to use his user (role) name and password. So, is this impossible? I appreciate any kind of guidance. Thanks...