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
