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

Hey Craig,

2010/10/27 Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>

> On 27/10/10 04:49, Dmitriy Igrishin wrote:
> > Hey Tony,
> >
> > 2010/10/27 Tony Cebzanov <tonyceb(at)andrew(dot)cmu(dot)edu
> > <mailto:tonyceb(at)andrew(dot)cmu(dot)edu>>
> >
> > On 10/23/10 11:01 AM, Craig Ringer wrote:
> > > Yep. As for not explicitly mentioning "lower" roles when granting a
> > > higher role (ie "admin" isn't also a "user") - role inheritance.
> >
> > I knew about role inheritance, I just didn't know about the
> > pg_has_role() function for determining if a user has a role. That's
> > helpful, but I really don't want to be hitting the database with a
> > pg_has_role() call for every time I want to check if a user should
> have
> > access to a certain page or function in my application.
> >
> > Why not? Performance? It's just one function call.
>
> It's potentially a fair bit more than that. It requires a new connection
> (tcp connection, backend startup, auth, etc) or borrowing one from a
> pool. If the pool is server side there's still a tcp connection with
> the associated latency. Then there's a round trip for the query and
> result. Processing the result. etc. It's not trivial, especially if your
> client and server aren't co-located.
>
This applies to any arbitrary SQL command. I don't see the problem here.
Caching the privileges on the client side - is a good idea, but there is a
perennial problem that I see very clearly - cache invalidation.

> Like you, I'd suggest using information_schema for the job.
>
> --
> Craig Ringer
>
> Tech-related writing: http://soapyfrogs.blogspot.com/
>

--
// Dmitriy.