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
>
>