Thread: getting all groups where a user belongs to

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

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

=?iso-8859-2?Q?Keresztury_Bal=E1zs?= <balazs@gaslightmusic.hu> writes:
> 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.

You could do something like

select rolname from pg_roles where pg_has_role('Kim', rolname, 'USAGE');

but this isn't going to be terribly efficient if you do it over and
over.  You should probably think twice about whether you really need
that data in explicit form on the client side.

            regards, tom lane