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.

I have implemented row and column based access from my application.

I do'nt want to re-implement this is server-side since this is huge work and
PostgreSQL (and probably any other dbms) does not support this easily.

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

I'd call this row level acces. Yes, I need this since I hold different
documents in same table.

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

This does'nt solve my major issues: column and row level acces restriction.

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

I have implemented this already in my application.
I should be huge very PostgreSQL specific work, using rules for write access
etc.
I do'nt want to implement this.

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

I have singe tabel contianing invoices, orders etc.
Some users are allowd to see only invoice rows some users only order rows.

Some users are allowed to see purchase price column, some not.
Sme users can modify sales price column, some not.

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

Can yuo provide some link where I can read about this ?

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

Yes, I have opened 5432 port for direct internet access.

I do'nt wat to re-design the whole application by using application server
or web services in server side.
I have few knowledge of PostgreSQL server side languages required to
re-implement appli server in PostgreSql server side.
I do'nt want to use PostgreSQL as application server by adding views , rules
and triggers since this all is huge work.

This is why I'm looking for application level authentication for PostgreSQL.
In this case I can use my existing application without making changes.
I'm currently using password mangling (application applies secret conversion
to password) but looking for better solution.
like using public key certificates.

Andrus.