Re: getting all groups where a user belongs to

Hi Balázs,

Depending the PG Version (pre 8.4) have a look at connectby() in tablefunc.

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos',
'row2', 0, '~')
  AS t(keyid text, parent_keyid text, level int, branch text, pos int);
  keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
  row2  |              |     0 | row2                |   1
  row5  | row2         |     1 | row2~row5           |   2
  row9  | row5         |     2 | row2~row5~row9      |   3
  row4  | row2         |     1 | row2~row4           |   4
  row6  | row4         |     2 | row2~row4~row6      |   5
  row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

Regards,

Johan Nel
Pretoria, South Africa.

Keresztury Balázs wrote:
> hi,
>
> I'm currently developing a business software, and I faced a problem just a
> few days ago.
>
> My users are currently using their own credentials for logging in to
> PostgreSQL server (this makes auditing, logging a lot easier). There are
> several groups, and the groups can inherit their parents' rights. I would
> like to control the access to several functions based on these groups not
> only inside the DB, but also inside the application layer.
>
> For example: there is a Service user, called Joe, who belongs to the group
> called SER. There is an other one, Kim, who's an administrator (ADM), which
> inherits rights from both SER and CEO. My problem is that I need a query,
> which returns _all_ the group names which Kim belongs to.
> I already find a solution to get the direct parents of a role, but I'd like
> to have all of them to use it for access control.
>
> My query so far:
>
> CREATE OR REPLACE VIEW "felhasznalo"."jogosultsag" (
>     felhasznalo_id,
>     szerep_id)
> AS
>  SELECT pr.rolname AS felhasznalo_id,
>        pr2.rolname AS szerep_id
>  FROM pg_roles pr
>       JOIN pg_auth_members pam ON pr.oid = pam.member
>       JOIN pg_roles pr2 ON pam.roleid = pr2.oid;
>
> Is there any solution for this? Or maybe a best practice to somehow
> integrate DBA and application security?
>
> Thank you!
> Balazs
>
>