On Sun, 2005-03-07 at 23:14 +0300, Andrus Moor wrote: > > Does the application really need superuser privileges or is that > > just a convenience? It's usually a good idea to follow the "Principle > > of Least Privilege" -- do some searches on that phrase to learn > > more about it and the rationale for following it. > > > Whether this approach is "secure and better" depends on the application > > requirements, the threat model, how well the application is written, > > etc. As Bruno pointed out, if users have enough access to the > > system that they could discover the account name and password, then > > they could easily bypass the application's security. Another > > potential problem is SQL injection: if the application isn't careful > > with how it handles user input, then specially-crafted data could > > result in the pre-defined queries doing more than intended. You'll > > have to evaluate the risks and benefits of the various approaches > > in the context of your own environment; there's no universal "this > > way is better" answer. > > My application is general purpose accounting and sales application. If > database does not exists, it prompts user and creates new database containig > some hundreds of tables and > upsizes local data to database. > Each database can have a lot of schemas. Each schema represents a single > company. All those schemas have exactly the same tables, each schema > contains 80 tables. > In public schema I store tables common for all companies (60 tables). > > So I seems that my application needs to be run with super-user privileges in > Postgres. > > Andrus I am quite sure that you can use a non super-user account and still work with different schemas.
First thing I would do in your case is determine who should have access to PgAdmin, and create restricted-users for each of them. Next I would remove all privileges, then specifically grant access to the action required on any specific table to your application. If your application needs more privileges under special circumstances then have a higher privileged user defined to allow those changes. I have some applications that have a couple of user levels defined. I have also built a php interface for one customer that used postgresql to store user accounts and session information. It should be possible to extend that type of system to use the authenticated user as the application user, but depending on how many users simultaneously connect, you may run into a problem due to too many open connections. If you don't use a separate PG user for each user, you can use views as stipulated by others. The program I wrote used a hierarchal access system and each record had a userid and privilege level associated with it. In that system users were stored in a hierarchal lookup table using id's and the specific information for the user was held in a contact table, so that a real person could belong to more than one organization without having to be redefined. The privilege was basically ; private, supervisor, peer, subordinates and public. The permission levels a user was allowed to access and assign were defined in the hierarchal lookup table. I hope that helps. I have another similar but much larger project I have been mulling over, that will require this same kind of granularity, and due to privacy concerns, I will need to use all the tricks I have used before and maybe even some new ones. Good Luck ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
