4 ms·

> Theoretically postgres already has a robust layer of security and validation; although it may not be used for the typical web app. What Postgres has in terms

> Theoretically postgres already has a robust layer of security and validation; although it may not be used for the typical web app. What Postgres has in terms of validation is essentially a not terribly sophisticated type system, plus stored procedures in PL/pgSQL, Perl, Python or Tcl. These are frankly a pain to manage. In terms of security, well... outside of stored procedures, there is no special way of restricting a given connection/user to only rows with the right foreign key in a table. On the other hand, many web framework have some sort of authorization framework.

AlterEgo20 12y ago
There are `security_barrier` VIEWs. They give a "way of restricting a given connection/user to only rows with the right foreign key in a table". Still you have to create them all (not a simple task for complex DB scheme).
- mercurial 12y ago
  That'd make in a very simple case (an app with a user_profile table) one view per user? This doesn't sound particularly appealing...
  - AlterEgo20 12y ago
    "one view per user" - you can have a single VIEW, that filters rows based on user name ("owner_user_name" column in the base table). Or use some kind of mapping table for "user_name" <-> "table PK id". It gets more complicated with inherited user roles, but still manageable.
imanaccount247 12y ago
They are not a pain to manage at all, and what is so lacking with the type system? >In terms of security, well... outside of stored procedures, there is no special way of restricting a given connection/user to only rows with the right foreign key in a table "If I specifically exclude the way to do this, there is no way to do this"? Also views work fine.
7952 12y ago
It is never entirely obvious where this kind of user/row level security should be placed. Personally I don't like using frameworks with complicated authorisation systems that I don't understand. I don't think it makes sense to use that kind of system for a hand written SQL heavy project which is what this API is undoubtly aimed at. I would rather integrate user validation into normal queries, or have it handeled at the edge with an application firewall along with other kinds of input validation. You could always do something like this... INSERT INTO test select %(inputdata) where %(cookie) = hmac(%(userid), 'serverkey','sha256' ) and char_length(%(inputdata)) < 1000

AlterEgo20 12y ago

There are `security_barrier` VIEWs. They give a "way of restricting a given connection/user to only rows with the right foreign key in a table". Still you have to create them all (not a simple task for complex DB scheme).

mercurial 12y ago
That'd make in a very simple case (an app with a user_profile table) one view per user? This doesn't sound particularly appealing...
- AlterEgo20 12y ago
  "one view per user" - you can have a single VIEW, that filters rows based on user name ("owner_user_name" column in the base table). Or use some kind of mapping table for "user_name" <-> "table PK id". It gets more complicated with inherited user roles, but still manageable.

imanaccount247 12y ago

They are not a pain to manage at all, and what is so lacking with the type system? >In terms of security, well... outside of stored procedures, there is no special way of restricting a given connection/user to only rows with the right foreign key in a table "If I specifically exclude the way to do this, there is no way to do this"? Also views work fine.

7952 12y ago

It is never entirely obvious where this kind of user/row level security should be placed. Personally I don't like using frameworks with complicated authorisation systems that I don't understand. I don't think it makes sense to use that kind of system for a hand written SQL heavy project which is what this API is undoubtly aimed at. I would rather integrate user validation into normal queries, or have it handeled at the edge with an application firewall along with other kinds of input validation. You could always do something like this... INSERT INTO test select %(inputdata) where %(cookie) = hmac(%(userid), 'serverkey','sha256' ) and char_length(%(inputdata)) < 1000