Thread: Role Hierarchy

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

    Join Date
    Jun 2009
    Posts
    2
    Rep Power
    0

    Role Hierarchy


    Hi,
    I am using PostgreSQL 8.1 . I am trying to print all the roles in a hierarchy. For example

    r1
    |
    r2 r3
    | |
    -------
    |
    r4
    |
    r5

    Here r5 is member of r4 , r4 is member of r2 and r3 and r2 is member of r1. Now in a query if i give role name as 'r5' it has to display the entire hierarchy. For this I am trying with the following query but it just printing the immediate hierarchy only.
    The query is as following :

    select pr.rolname from pg_roles pr
    LEFT JOIN pg_auth_members pam
    ON(pr.oid=pam.roleid)
    LEFT JOIN pg_roles pr1
    ON (pam.member=pr1.oid)
    WHERE pr1.rolname='r4';

    I need a solution for this problem.

    Regards,
    Prakash.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Location
    Québec city
    Posts
    13
    Rep Power
    0
    Hi Prakash, did you ever find the solution for that ?

    I'm having the same problem to solve, so I'd be interested to know the answer.

    Thanks,

    Sebastien


    Originally Posted by prakashgula
    Hi,
    I am using PostgreSQL 8.1 . I am trying to print all the roles in a hierarchy. For example

    r1
    |
    r2 r3
    | |
    -------
    |
    r4
    |
    r5

    Here r5 is member of r4 , r4 is member of r2 and r3 and r2 is member of r1. Now in a query if i give role name as 'r5' it has to display the entire hierarchy. For this I am trying with the following query but it just printing the immediate hierarchy only.
    The query is as following :

    select pr.rolname from pg_roles pr
    LEFT JOIN pg_auth_members pam
    ON(pr.oid=pam.roleid)
    LEFT JOIN pg_roles pr1
    ON (pam.member=pr1.oid)
    WHERE pr1.rolname='r4';

    I need a solution for this problem.

    Regards,
    Prakash.

IMN logo majestic logo threadwatch logo seochat tools logo