Search Postgresql Archives

Re: Hot to restrict access to subset of data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux