Thread: How to configure PostgreSQl for low-profile users
Dear Sirs, I would like to rent a my application to a number of customer, each with a dedicated database (and perhaps a dedicated username). The database will be installed on 'public IP' machine and will be accessed by internet on standard port 5432 and using ODBC driver from several clients (whith the client part of the procedure installed on each machine). My questions are: It is possible to configure the security policy so that the simple users (the customer, in this case) can only read, write, update end delete data to the dedicated database AND NOTHING ELSE, particularly: - I would like to create each database with a different (customer) username (only one per database, in addition to the standard user postgres) - The user (customer): ---- cannot change his own username and the password ---- cannot backup the database ---- cannot read (the text of) the stored procedures, but execute only ---- cannot know the 'existence' of the other databases Thank you in advance for your appreciated reply. Domenico Formenton
On Wed, Mar 18, 2009 at 09:15:31PM +0100, dfx wrote: > It is possible to configure the security policy so that the simple users > (the customer, in this case) can only read, write, update end delete data to > the dedicated database AND NOTHING ELSE, particularly: I assume you're implying a caveat saying "modulo bugs" in there? > - I would like to create each database with a different (customer) username > (only one per database, in addition to the standard user postgres) OK > - The user (customer): > ---- cannot change his own username and the password Users aren't, by default, allowed to change their name in PG. They can of course change their password, why would you want to stop this?? > ---- cannot backup the database Well, anyone that can read a table can backup the tables they can read. Could you define this a bit better? > ---- cannot read (the text of) the stored procedures, but execute only I believe this can be tied down, yes. > ---- cannot know the 'existence' of the other databases I think this is a little trickier to arrange, why would it matter? -- Sam http://samason.me.uk/
In response to "dfx" <dfx@dfx.it>: > Dear Sirs, > > I would like to rent a my application to a number of customer, each with a > dedicated database (and perhaps a dedicated username). > > The database will be installed on 'public IP' machine and will be accessed > by internet on standard port 5432 and using ODBC driver from several clients > (whith the client part of the procedure installed on each machine). > > My questions are: > > It is possible to configure the security policy so that the simple users > (the customer, in this case) can only read, write, update end delete data to > the dedicated database AND NOTHING ELSE, particularly: > > - I would like to create each database with a different (customer) username > (only one per database, in addition to the standard user postgres) > - The user (customer): > ---- cannot change his own username and the password > ---- cannot backup the database > ---- cannot read (the text of) the stored procedures, but execute only > ---- cannot know the 'existence' of the other databases A lot of these aren't supported (the "existence" thing, in particular) If you really need to prevent users from knowing about each other, you'll probably be better off using a virtual machine infrastructure to give each client a dedicated DB system. FreeBSD jails are particularly useful for this because of how lightweight they are. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/