Skip site navigation (1) Skip section navigation (2)

Re: How to allow users to log on only from my application

From: Furface <furface(at)omnicode(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to allow users to log on only from my application
Date: 2007-01-30 02:01:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Doesn't pg_hba.conf just deal with user connections?  If you denied via 
pg_hba.conf, wouldn't you also deny access for the application?  Can 
pg_hba.conf authenticate based on a per application basis?  I wasn't 
aware of anything like that.  I'm not an expert on this, so I could be 

This is similar to my problem discussed in my question about the maximum 
number of users.  What I would do is create a user group that isn't 
given access to the privs table first of all.  Fine grained access is a 
much trickier problem, though.  Think about triggers and database 
procedures to stop people from messing with data at a row level.  You 
also need to think about issues like referential integrity at the 
database level instead of having your application enforce it.

What are the types of things you want to protect against?  Here are the 
things I'm working on.

1. Per user access.  For instance in a timesheet file, users should only 
be able to access/update data on their own timesheets, and only if the 
timesheets have not been approved by an administrator.  This is typical 
row level authentication, I think, where individual users can only deal 
with data that relates to them individually and only under certain 

2. Restricting certain tables to certain users.  Well that's easy.  You 
just use the "grant" command.

3. Restricting certain columns of certain tables to certain users.  This 
would be something like an "approved" or "active" column where only 
administrators can set these values.  This would have to be done with 
triggers and procedures.

4. Read only for certain users.  Again, you can user the "grant" command 
to grant privs to only one user.

I'll forward what I develop to anybody who's interested when I finish it up.

In a certain sense, the proxy app I described and then admitted was 
incorrect in another thread solves this problem much more simply.  
You're defining your security at an application level, which is much 
simpler and probably less error prone, than writing a slue of triggers 
and procedures in sql.  I have to admit I still go back and forth on 
this issue, although I understand the reasons for keeping it all on a 
single, proven, fast, robust server.  Still, exposing even a minor 
subset of raw sql access to a database can be potentially very 
dangerous.  To be honest, at this point I wouldn't even consider 
non-verified (meaning I have a real name, credit cart, etc) Internet 
access directly to my database server.

Just some thoughts. 

Raymond O'Donnell wrote:
> Andrus wrote:
>> My application implements field and row level security.
>> I have custom table of users where user privileges are described.
>> However user can login directly to database using pgAdmin. This bypasses
>> the security.
>> How to allow users to login only from my application ?
>> I think I must create server-side pgsql procedure for login validation.
> What role are your users using to login via PgAdmin? Why not simply 
> deny them access in pg_hba.conf?
> Ray.
> ---------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod(at)iol(dot)ie
> ---------------------------------------------------------------
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

In response to


pgsql-general by date

Next:From: Christopher BrowneDate: 2007-01-30 02:04:17
Subject: Re: PG Email Client
Previous:From: Ron PetersonDate: 2007-01-30 01:57:34
Subject: programming 'like' operator

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group