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

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.

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?