Re: Hot to restrict access to subset of data - Mailing list pgsql-general

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