Postgres中的用户权限

I need to implement access control in my application. I need to control which columns of which row can be edited by who.

Examples of a rule may be:

  • User can edit only her own rows (and also insert rown only for her) of TableA, which is linked to her through TableB (TableB contains foreign keys of both User and TableA).
  • User can edit just some parts of her profile, while admin can update other parts.

Instead of asking a DB to give my application all the information needed to evaluate the possibility of change in the application code, I am currently thinking about trigger based approach for inserts, updates and deletes. In this scenario a triggers, which would fail when operation is not permitted, would be hooked to controlled tables.

  • I will tell Postgres which user is working right now using local configuration.
  • Then I simply run the operation while expecting the possibility of failure.

Assuming I am able to evaluate all the permissions inside the Postgres DB, I see a few positives:

  • I would not need to ask the DB before the operation if it can be done.
  • I would not litter the application with permissions evaluation.
  • I can run updates from different parts of application even without using proper models while maintaining the security.

My questions are:

  • Can this become a bottleneck when scaling up? (It is a pretty traditional web-based PHP app, so I am expecting about a 100-times more selects than updates. But this would shift evaluation from web server which could be more easily replicated then DB server.)
  • Is there some better well-known design practice to implement this kind of permissions I have in mind.

there is a possibility that after scaling up your DB will be full of triggers which would be difficult to edit or change something. E.g. if you add extra column, you should edit all procedures related to table.

If I've got your question, you're trying to control web application users.

I think better way is to implement restrictions on a PHP side. For instance to create role, which contain array of possible to edit columns. And if this is 'admin', his/her list would contain all columns. Then dynamically create the form according to role configuration, in order to let user see, what he/she can edit.