Обсуждение: Anyone tried storing software users as database users?
If so then how did you handle permissions, errors, connection pooling? Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you maynot copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the senderby reply email. Please advise immediately if you or your employer does not consent to Internet email for messages ofthis kind. Opinions, conclusions and other information in this message that do not relate to the official business of myfirm shall be understood as neither given nor endorsed by it.
Στις Tuesday 28 April 2009 15:27:58 ο/η Paul Rogers έγραψε: > If so then how did you handle permissions, errors, connection pooling? > In jboss for instance you could do that using org.jboss.security.auth.spi.DatabaseServerLoginModule in conf/login-config.xml One issue might be the quering of pg_shadow, also another issue would be to ensure if your user md5 hashed password really matches the one stored in the db (getting rid of leading "md5" prefix first ofcourse) Having said that, i haven't done it with the postgresql sys tables tho. Anyway, why do you want to combine database users and app users? Normally the app accesses the database as one db user. Most app descriptors are static xml (at least in J2EE), meaning you cant set up your connection pools dynamically with whoever user tries to login. If you manage to do it, however, there is great advantage in what you are trying to do. Both application *and* database logs will log according to the same user! So if for example one db process eats up 100% of the CPU you will know exactly who did what. > > Privileged/Confidential Information may be contained in this message. > > If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), youmay not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the senderby reply email. Please advise immediately if you or your employer does not consent to Internet email for messages ofthis kind. Opinions, conclusions and other information in this message that do not relate to the official business of myfirm shall be understood as neither given nor endorsed by it. > > -- Achilleas Mantzios
Paul Rogers schrieb: > If so then how did you handle permissions, errors, connection pooling? Hey there! I'm currently working on an .NET application with user- and privilege-management via database. Privileges and access to frontend modules (in the application), tables, functions etc. (in db) is controled via group-membership in the database backend by querying pg_groups. User-creation etc. (say "superuser-tasks" ) is done with functions that have definer-security-access (access to those controlled via user groups) Errorhandling is done by evaluating db-error messages in try-catch-clauses. There's no special need of any special conn-pooling-functions in my application. The application is coded in vb.net, using npgsql with Postgres 8.2 in background. Need to know more? --> Just ask. Greetings, H.P. Ertz >[...]
[Sent again as it did not seem to come through] 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
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