#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    User Authentication


    Hello,
    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:

    Code:
    CREATE USER foo WITH PASSWORD 'password'
    When I look at the properties of user 'foo', I can see its encrypted password is 'md52ded2b65ce11cfe94e88a881982a333e'. I can get this value by

    Code:
    select 'md5'|| md5('password' || 'foo')
    this query.

    But, when I try to verify username and password by

    Code:
    select usename, passwd from pg_user where usename='foo' and passwd = 'md5'||md5('password' || 'foo')
    no results return. Can somebody tell me what I am during wrong? Thanks...
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    Hi,

    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?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Hello,
    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...

IMN logo majestic logo threadwatch logo seochat tools logo