Re: Advice needed on application/database authentication/authorization/auditing model

Hey Tony,

2010/10/22 Tony Cebzanov <tonyceb(at)andrew(dot)cmu(dot)edu>

> Hi Dmitriy,
>
> On 10/21/10 4:21 PM, Dmitriy Igrishin wrote:
> > IMO, you are trying to reinvent the wheel. Although, you may do it just
> for
> > fun. :-)
>
> Surely I am, but I think it made sense at the time. It doesn't make as
> much sense now that I need to audit every insert/update/delete in the
> database.
>
> > Why not just create "groups" via CREATE ROLE User ... and grants this
> > roles to the "users" (created via CREATE USER or CREATE ROLE ... LOGIN)Â
> ?
>
> The reason I shied away from this initially was the overhead of having
> to maintain user info in two places (the pg_catalog schema for postgres
> users, and in my application schema, with the user's real name,
> application preferences, etc.) It also seemed like the role information
> wasn't very accessible in the system catalogs -- I had noticed that the
> pg_group view was deprecated, and the query to get group information out
> of the pg_auth_members and pg_roles tables started to look very ugly,
> when I could just do a quick "is the user an administrator" check via a
> boolean flag in my app user's table.
>
You table, e.g. "usr" and the systems table with roles will have 1:1
cardinality.
That's all. There is no redundancy and / or overhead. You just extends the
system table with columns you need and create implicitly 1:1 relation by
placing a column "rolename" with unique index in you "usr" table.

>
> With my new requirements for auditing, using the database's roles makes
> more sense, but I still see some problems with it, even if I can solve
> the connection pooling problem by using persistent connections as you
> suggest.
>
> For one thing, in this app, all higher permissions include the lower
> permissions -- all administrators are auditors and regular users, and
> all auditors are regular users. So, my normal instinct would be to set
> it up like this:
>
> GRANT g_user TO g_auditor WITH ADMIN OPTION;
> GRANT g_auditor TO g_admin WITH ADMIN OPTION;
>
> Then, in theory, I could grant administrators the g_admin group,
> auditors the g_auditor group, etc. and they could do all the things the
> lower groups can. BUT, in my app, to check for access to audit
> functions, I can't do a simple query to see if the user is in the
> "g_auditor" group, because administrators aren't explicitly granted this
> group -- they get those permissions implicitly, but how do I know this
> from my application? Is there some kind of query I can do to get back
> all the groups a role is a member of?
>

Please see
http://www.postgresql.org/docs/9.0/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

--
// Dmitriy.