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

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
wrong.

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
circumstances.

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
>