5 ms·

Additional security is absolutely necessary. The docs have table and field level permissions but that's not enough. A user should only be able to edit their own

Additional security is absolutely necessary. The docs have table and field level permissions but that's not enough. A user should only be able to edit their own row in the database, and their password should always be one-way encrypted, etc. Also don't want a user to be able to add themselves to a user group, say add themselves to the "Administrator" user group by inserting a table row. This framework may simply be too "thin" - there needs to be a place to code business logic and access control, neither of which map neatly to how RDMS treat users and security permissions. In which case might as well use a framework like Django or Rails or Bottle or Laravel or any other of the dozens of mature systems available.

dragonwriter 9y ago
> This framework may simply be too "thin" - there needs to be a place to code business logic and access control, neither of which map neatly to how RDMS treat users and security permissions. It's certainly to thin since it doesn't even leverage db-level RBAC, but, used fully, I think PostgreSQL roles and permissions are reasonably robust. (Particularly, all the specific examples, including row-level permissions, you point to are addressed by PostgreSQL roles and permissions.)
ruslan_talpa 9y ago
All authorisation requirements are solved easier and better at the db level. Since you have permissions by columns and row level security, it means you can define permissions down to a cell. As someone said, people just don't read the manual of their database past page 100 :) and use "root" when connection to the database from their code. https://blog.2ndquadrant.com/application-users-vs-row-level-security/ https://blog.2ndquadrant.com/application-users-vs-row-level-...
- virmundi 9y ago
  Does anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?
  - snuxoll 9y ago
    SET ROLE "user" The biggest issue with this is roles are identifiers, which means you can't use parameters for them.
    - virmundi 9y ago
      Can I do that inline with a statement? I don't think I'd mind outsourcing rights to the DB.
      - snuxoll 9y ago
        No, you'll need to call SET/RESET role as their own statements. The easiest way to do this in Java is to ensure RESET ROLE is called when checking a connection back into the pool, and calling SET ROLE in a servlet interceptor (probably the same one that handles authentication).
    - s_kilk 9y ago
      So, if I'm following this correctly, you'd need to start a new connection with the end-users credentials? Which would be slow and rule out connection pooling and other such niceties?
      - snuxoll 9y ago
        You can use connection pooling, the user you are connected with needs to be granted the role you are switching to (preferably with the NOINHERIT flag) - that's the purpose behind the SET ROLE and RESET ROLE statements.
- darksaints 9y ago
  Yeah, but users are a connection level concept, meaning you have to abandon your connection pooling system in order to use the db's user authorization and authentication system. Every new query is a new connection.
  - begriffs 9y ago
    The link Ruslan shared explains how to use row level security with application users as opposed to db users. Basically PostgREST translates cryptographically signed JWT claims from the client request into local SQL variables accessible by postgresql's "current_setting" function. The additional claims can identify the user beyond their db role. They can specify, for instance, the user's email address. Row level security policies can use current_setting to refer to the extra claims. So multiple users can share a db role and connection pooling works fine.
  - ruslan_talpa 9y ago
    > Every new query is a new connection > users are a connection level concept That is not true. There are users in the db that do not have login privileges. PostgREST connects in with only one user (authenticator), then for each authenticated request it does `set local role alice` within a transaction, where alice is a db user with no login privileges.
combatentropy 9y ago
A table starts out with zero rights, except to the user who made the table. Another user cannot even see its name. You might first make database views, and grant only those to users, not granting any user rights to a table. PostgreSQL lets you run an update to a view and then translates it to an update on the table. So reads and writes on a view would both work. PostgreSQL now has security by column and row. So you could do it that way too.
- ruslan_talpa 9y ago
  anyone with the privilege to login can see the names and relations of all the entities in the database.
  - dragonwriter 9y ago
    Yeah, but if you are using a wrapper similar to this, you can easily prevent that while still using the underlying DB security mechanisms.
    - ruslan_talpa 9y ago
      I'm not arguing using db for authorisation is bad, i was just pointing out the statement is incorrect (about not seeing other tables names). The fact that you can see the schema even without privileges is used by PostgREST and this weirdly contributes to security because the user with which it connects to the database (authenticator) does not need to have any rights, just the right to switch to other (specific) users.
  - combatentropy 9y ago
    You're right. I'm sorry! I remembered wrong. But that's only if you can first connect to the database with a general-purpose client program, like psql or one of the GUIs. By default Postgres allows connections only from localhost. So even if you had a user account, you couldn't connect. So how would something like this REST interface even work with accounts for each user? The REST program would have to be installed on the same server as Postgres, or you would have tell Postgres to allow network connections only from the server that the REST program is on. The REST program first connects with its own user account, say "resty". resty is a member of your account, say "jdoe" (users and groups are the same entity, and they're really called "roles", and one role can be a member of another role). If a role is member of another role, then it can become it, like this: set role jdoe; Now you been downgraded to the rights of jdoe. The REST program could pass through your username and password and relogin as you. Or it could trust that your web server authenticated the REMOTE_USER and just set itself to that variable.

dragonwriter 9y ago

> This framework may simply be too "thin" - there needs to be a place to code business logic and access control, neither of which map neatly to how RDMS treat users and security permissions. It's certainly to thin since it doesn't even leverage db-level RBAC, but, used fully, I think PostgreSQL roles and permissions are reasonably robust. (Particularly, all the specific examples, including row-level permissions, you point to are addressed by PostgreSQL roles and permissions.)

ruslan_talpa 9y ago

All authorisation requirements are solved easier and better at the db level. Since you have permissions by columns and row level security, it means you can define permissions down to a cell. As someone said, people just don't read the manual of their database past page 100 :) and use "root" when connection to the database from their code. https://blog.2ndquadrant.com/application-users-vs-row-level-security/ https://blog.2ndquadrant.com/application-users-vs-row-level-...

virmundi 9y ago
Does anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?
- snuxoll 9y ago
  SET ROLE "user" The biggest issue with this is roles are identifiers, which means you can't use parameters for them.
  - virmundi 9y ago
    Can I do that inline with a statement? I don't think I'd mind outsourcing rights to the DB.
    - snuxoll 9y ago
      No, you'll need to call SET/RESET role as their own statements. The easiest way to do this in Java is to ensure RESET ROLE is called when checking a connection back into the pool, and calling SET ROLE in a servlet interceptor (probably the same one that handles authentication).
  - s_kilk 9y ago
    So, if I'm following this correctly, you'd need to start a new connection with the end-users credentials? Which would be slow and rule out connection pooling and other such niceties?
    - snuxoll 9y ago
      You can use connection pooling, the user you are connected with needs to be granted the role you are switching to (preferably with the NOINHERIT flag) - that's the purpose behind the SET ROLE and RESET ROLE statements.
darksaints 9y ago
Yeah, but users are a connection level concept, meaning you have to abandon your connection pooling system in order to use the db's user authorization and authentication system. Every new query is a new connection.
- begriffs 9y ago
  The link Ruslan shared explains how to use row level security with application users as opposed to db users. Basically PostgREST translates cryptographically signed JWT claims from the client request into local SQL variables accessible by postgresql's "current_setting" function. The additional claims can identify the user beyond their db role. They can specify, for instance, the user's email address. Row level security policies can use current_setting to refer to the extra claims. So multiple users can share a db role and connection pooling works fine.
- ruslan_talpa 9y ago
  > Every new query is a new connection > users are a connection level concept That is not true. There are users in the db that do not have login privileges. PostgREST connects in with only one user (authenticator), then for each authenticated request it does `set local role alice` within a transaction, where alice is a db user with no login privileges.

combatentropy 9y ago

A table starts out with zero rights, except to the user who made the table. Another user cannot even see its name. You might first make database views, and grant only those to users, not granting any user rights to a table. PostgreSQL lets you run an update to a view and then translates it to an update on the table. So reads and writes on a view would both work. PostgreSQL now has security by column and row. So you could do it that way too.

ruslan_talpa 9y ago
anyone with the privilege to login can see the names and relations of all the entities in the database.
- dragonwriter 9y ago
  Yeah, but if you are using a wrapper similar to this, you can easily prevent that while still using the underlying DB security mechanisms.
  - ruslan_talpa 9y ago
    I'm not arguing using db for authorisation is bad, i was just pointing out the statement is incorrect (about not seeing other tables names). The fact that you can see the schema even without privileges is used by PostgREST and this weirdly contributes to security because the user with which it connects to the database (authenticator) does not need to have any rights, just the right to switch to other (specific) users.
- combatentropy 9y ago
  You're right. I'm sorry! I remembered wrong. But that's only if you can first connect to the database with a general-purpose client program, like psql or one of the GUIs. By default Postgres allows connections only from localhost. So even if you had a user account, you couldn't connect. So how would something like this REST interface even work with accounts for each user? The REST program would have to be installed on the same server as Postgres, or you would have tell Postgres to allow network connections only from the server that the REST program is on. The REST program first connects with its own user account, say "resty". resty is a member of your account, say "jdoe" (users and groups are the same entity, and they're really called "roles", and one role can be a member of another role). If a role is member of another role, then it can become it, like this: set role jdoe; Now you been downgraded to the rights of jdoe. The REST program could pass through your username and password and relogin as you. Or it could trust that your web server authenticated the REMOTE_USER and just set itself to that variable.