Hey Tony, 2010/10/22 Tony Cebzanov <tony...@andrew.cmu.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.
