Re: [GENERAL] getting all groups where a user belongs to

[Tom Lane]

=?iso-8859-2?Q?Keresztury_Bal=E1zs?=  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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] getting all groups where a user belongs to

[Johan Nel]

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




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general