Use PostgreSQL users for all application users

Question

Up to now I always used one database user, and handled permission checking, and corresponding filtering in my application.

This pattern has the drawback that the programming language which was chosen when the application "was born" is mandatory.

All the permission checking stuff gets handled in this programming language.

Since I currently start an application from scratch I re-think this pattern.

Why not use a corresponding PG-user for every user which exists in the application?

So authentication still happens in my application, but as soon as I know that Bob is Bob (and not somebody else) I use SET_ROLE so that the following SQL statements get executed by the PG-user Bob.

Question: Why not use one PostgreSQL users for every application users?

Background: If I do the permission handling in PG (Row security policies), then I can give the users (readonly) SQL access. Hint: My users are scientist, and I think they able to learn SQL.

I guess this kind of application development is unusual. I googled but could not find talks or article. Please leave a comment if you know a article/talk about this topic.

Answer 1

Indeed, why not? Even if it is unusual, there is nothing wrong with managing your application users with database roles. As you say, it would make using row level security straightforward. Here are two potential problems to be aware of:

• You won't be able to use a connection pool effectively, since connections can only be reused for the same database user.

• Make sure to handle object permissions and policies via group roles. If you directly grant permissions to the end user, creating new users and dropping old ones becomes unnecessarily complicated.

Answer 2

Hint: My users are scientist, and I think they able to learn SQL

I don't understand the relevance of this. If they are going through an app server, why does it matter if they can learn SQL? And if you are planning on intentionally allowing them to do SQL injections, then you need to know that any rights 'given away' by set role can be reacquired by reset role;. And that might the answer to your question--if a chain is only as strong as its weakest length, adding more links doesn't make it stronger.