Paul,
Yes, all of our software works this way - that way it fits in nicely with an
organization's internal systems and permissions: they might have their access
mechanisms tied into other systems (LDAP, Kerberos or whatever). If you don't
use this method, then you have to roll your own system (more work, less
secure) and also add your own user management, utilities, etc. You can imagine
how popular that will make your application in a large installation with
hundreds of users.
If you only use one connection for your software, you are, in effect, giving
out the equivalent of a root password for your database system, or storing it
in a breachable text-file, or whatever. An error will also mean that the user
will have the permissions of the single connection (which are likely to be too
great).
Using the built-in user permissions gives you the advantage that, if you make
an error in your application, the underlying postgresql mechanisms can help
prevent catastrophic security breaches. Like Hans, we use group membership and
query the system tables, etc.
You do not need to query pg_shadow, or jiggle with MD5 or the like - just try
to establish a connection with the user-supplied user-name and password: if
you can connect, then scan for group membership, etc. and bail out if group
membership is incorrect. Alternatively (or in addition) after connection, you
can try to select from an application table and if that fails because of an
access permission, bail out.
We catch errors via exceptions (nice and easy and reduces the complexity of
the software).
You can speed access by (say) saving the connection details (name, password,
etc.) for a user in a dictionary (in Python) and scanning that for previously
established connection details for the user - this is most useful for web-
based applications, where you only have a cookie ID to tie the session into a
user's connection.
As Achilleas stated, connecting via the user's account gives you full details
for logging, etc.
RJL
On Tuesday 28 April 2009 13:27:58 Paul Rogers wrote:
> If so then how did you handle permissions, errors, connection pooling?
>
--
Robert Ladyman
File-Away Limited, 32 Church Street, Newtyle
Perthshire, PH12 8TZ SCOTLAND
Registered in Scotland, Company Number SC222086
Tel: +44 (0) 1828 898 158
Mobile: +44 (0) 7732 771 649
http://www.file-away.co.uk
