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

On 27/10/10 04:49, Dmitriy Igrishin wrote:
> Hey Tony,
>
> 2010/10/27 Tony Cebzanov <tonyceb@andrew.cmu.edu
> <mailto:tonyceb@andrew.cmu.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.

Like you, I'd suggest using information_schema for the job.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/