CODEWITHSUNDEEP
javapostgresqljdbc
Maciej Kubiak
Maciej Kubiak

Reputation: 551

PostgreSQL application access

I'm using JDBC to connect to a PostgreSQL database. We are trying to block access to the database for the users themselves; instead they should be forced to use our frontend. We blocked access to any table, and gave only procedures, which do all the work for users, still not giving them any opportunity to access data directly. We tried to block access to schema pg_catalog, which limits users to procedures we created, but it seems that this access is needed for JDBC to call any procedure.

Anyway, the question is either how to use JDBC without access to pg_catalog, or how to authorize only connections made by application, not user.

Upvotes: 1

Views: 245

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

The DBMS is being presented with a Catch-22 situation:

  • When a user runs a specific JDBC program to access the database, let it do its stuff.
  • When a user runs any other JDBC program to access the database, do not let it do its stuff.

How can the DBMS tell the difference between the two programs? As far as it is concerned, they are both clients that are using the correct protocol to communicate with the DBMS, and have identified themselves as a legitimate user of the database.

To make it work, you have to find a non-subvertible way to distinguish between the two applications. That is not trivial - to say the least.

There are kludges, but there isn't a clean solution. It is a generic problem that any DBMS faces when the problem is presented as in the question.

Upvotes: 1

Eelke
Eelke

Reputation: 22063

There is no fool proof way but the simplest is to use a username and password for the connection that you do not give to your users. Store the password in an encrypted configuration file. Ofcourse the encryption key can be retrieved from the application by a smart person.

For a really save system it would probably be best to put a service in front of the database that handles all security and provides a high level API to access the data and let the client connect to this.

Upvotes: 2

martin
martin

Reputation: 980

Well, just don't give your users an account on your postgresql database and create only an postgresql account for your application.

Upvotes: 0

Related Questions