Re: Hot to restrict access to subset of data

On Fri, Jul 01, 2005 at 09:43:34PM +0300, Andrus wrote:
>
> My application connects to Postgres always as superuser, using user name
> postgres.
> Postgres server as only one user.

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.

> Actual users names of users who can access data are stored in special table.
> Since only my application knows the super-user password, the users can only
> access data
> throught my application. My application implements desired level of security
> by allowing only pre-defined queries to be run by particular user.
>
> Is this approach secure and better ?

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.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/