Seeking design recommendations for database server - client application

Question

I am developing a file management system with a PostgreSQL database back end. I am writing the client side using Qt library that will connect and query the database server.

The file management system by default restricts access to top-level directories to all users, unless a specific user has access to a specific top-level directory. Access can be given by anyone to anyone, as long as the user giving access already has access him/herself. This is accomplished by maintaining a table in the database with 2 foreign keys: userID_fk and directoryID_fk. If a record exists in this table with a particular user having access to a particular directory, than the directory is displayed on the user's screen. Otherwise, the directory is not visible to the user.

This design presents a challenge: The user is already able to connect to the database, and is a legitimate user as far as the database server is concerned, so it's easy for a user who does not have access to some directory to give themselves access by simply connecting to the database server not using my client application, and simply adding a record in the table with the directory id and their own user id. I cannot allow this, however.

What I want to achieve is giving all database users full access to the database server but only via my own client application and not outside of it.

I have thought of several approaches to solving this challenge but I am seeking your recommendations as to the best architecture to prevent users from accessing the database server outside of my client application.

Possible design approaches I thought of:

1. Write my own server that will maintain a list of users that are allowed to connect to a database server(s). New database users will only be created using this "server" in such a way that the user's password consists of 2 parts - first part the actual user provided password, and the second part a randomly generated string a copy of which is stored on the "server". When a user logs into my "server", the server will give the second part of the password to the client, and the user will provide the first part of the password. The two passwords will be concatenated and used to log into the database server. This achieves the intended objective because the user's database password is different from what the user knows it to be and therefore cannot log into the database server without first querying my "server" for the second part of the password.

This design however, is cumbersome to the user - the user would have to provide a password to connect to my "server" and then a second password to connect to the database server. And in my particular case, the data on the database server is also encrypted, which means the user would have to provide a third password to access a database. This is a little much for an average user I think.

2. An alternative design is to write my own server that provides all functionality for the client. So the client only ever queries my "server" and my "server" then queries the real database server. This presents a cleaner approach from the client perspective as only 2 passwords need to be provided, but this is much more difficult to program and I would be re-inventing the wheel as the database server already has excellent multi-user concurrent access that is very fast and reliable. My server would add additional complexity and likely slow down the speed significantly, and therefore may not be a practical approach.

Beyond these 2 approaches, I have not been able to think of a better design. I would really appreciate your suggestions, including your thoughts on my proposed designs. Many thanks.

Answer 1

We had a very similar problem under MS SQL Server and solved it by providing an "API" at the level of stored procedures. Hopefully, you'll be able to do something similar under PostgreSQL.

The idea is to forbid all clients from querying the database directly. Instead, you only grant "execute" permission on the "public" stored procedures. "Private" procedures (i.e. implementation details) and tables are completely inaccessible to external clients.

Together, public and private procedures implement row-level security and any other business logic needed. Since all clients are "funneled" through this API, nobody can circumvent row-level security or any of the business rules. This includes any future applications that may be implemented to "talk" to the existing database.

In our case, we had 1:1 mapping between database users and our "application users", and simply let the DBMS authenticate users during log-in (which also has an added benefit of integrating with Active Directory, which was important in our case). Stored procedures would then use current database user name to implement row-level security.

You should also consider security caching. In our case, we have a very complex security mechanism that supports permission inheritance (from parent to child objects) and workflows, which makes it expensive to calculate - so we implemented a security cache that gets "lazily" updated and prevents repeated security calculations. The security cache is completely hidden behind stored procedures and transparent to clients. Depending on complexity of your security mechanism, you may consider whether some kind of security caching would be a good idea performance-wise...

Answer 2

I think you'd be better off using Postgres's built-in security system, instead of writing your own.

Each user gets their own username/password (or you could use SSL certs or Kerberos)

Postgres supports users and roles, and access control on tables, schemas etc, as well as column security.

Currently, you need to mimic row security with an "owner" column in a table, and views that prevent users from seeing rows they should not. You'll need to figure out "sharing", perhaps with an ACL array column (of allowed roles), or using a related table.

9.3 has security_barrier views, which prevent some snooping. in 9.4, they are updateable. 9.4 also supports WITH CHECK OPTION views, which would be useful here. 9.4 should be out later this week.

See http://veil.projects.pgfoundry.org/curdocs/main.html as well.

Basic example:

create schema data;

create table data.files (
  file_id int primary key,
  name text not null,
  owner name not null default current_user, --use a trigger in real world
  acl name[] null
);

create view files as 
  select
    *
  from data.files
  where 
    owner = current_user
    or acl && array[current_user]; --&& is array overlap operator

insert into data.files values (1, 'test', 'admin', '{CSLover}');

You'll need to add some other stuff obviously.