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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
