4 ms·

> if you concede to an attacker INSERT/UPDATE/SELECT (ie: if you have SQL Injection), even if you've locked down the rest of the database and minimized privileg

> if you concede to an attacker INSERT/UPDATE/SELECT (ie: if you have SQL Injection), even if you've locked down the rest of the database and minimized privileges, you're pretty much doomed. Well... no. If you lock down your db correctly, using the correct privileges for the users accessing the data, and using veil or rls, the sql executed on the db is just an other way of accessing the data you can see anyway through the webapp. The webapp becomes just a nice frontend so the user does not need to execute sql directly, and if there is an sql injection vulnerability the attacker would still be able to only see the data he has the right to see anyway. He is just accessing it with sql instead of the webapp.

tptacek 13y ago
To attempt this security model, which can work if everything in the universe goes right for you, your application needs to be designed for it from the very beginning. Virtually nobody does this. If your application wasn't designed from the start to make its database connection "just another view onto data users would have access to anyways" (paraphrasing), locking down the database is closer to make-work than it is to critical security measures.
- y0ghur7_xxx 13y ago
  > To attempt this security model, which can work if everything in the universe goes right for you Almost all webapps we write, we write them this way. (on oracle, not postgres, but that makes no difference). > your application needs to be designed for it from the very beginning. That's true. As in "you can not add security later on".
  - tptacek 13y ago
    No, that's not what I said. I didn't say you "can't add security later on". I said that you can't rely on database access control configuration to protect an application that hasn't been designed from scratch to do that. Most applications don't rely on database access control; they rely on the application server to protect the database.
    - einhverfr 13y ago
      Just because the application assumes it is trusted by the database doesn't mean the database has to trust the application.
    - ako 13y ago
      15 years ago, in client-server Era, relying on database Access control was the default. It's a shame that all that knowledge has mostly been forgotten, and most programmers don't actually know how to use a database.
  - wila 13y ago
    That works as long as the database server is kept up to date with security patches. Quite often in production running a security patch is not high priority. I guess it all depends on using this feature as an additional layer of protection or using it as the only layer of protection.
- einhverfr 13y ago
  Actually in LedgerSMB we retrofitted such a security model onto the application (in 1.3) because it was the only real hard security model we could retrofit onto the application. A security model which follows these processes merely has a database that doesn't trust the application. This has some significant costs and tradeoffs associated (you can't do connection pooling).
- ris 13y ago
  So each user of your webapp has a respective database user? Otherwise I don't know how you would be able to make the permissions fine-grained enough to be able to make these claims.
  - einhverfr 13y ago
    That's exactly what we do in LedgerSMB. More to the point, the db user credentials are application login credentials so the application has the db permissions of the user. No password? The app can't access any data. Now this has some significant tradeoffs. It is very much a good decision for an ERP app. However for customer portals we are looking at a different approach, namely: 1. A portal user account in the db with very restricted permissions (and no direct access to tables) 2. A portal back-end user account in the db with limited access to tables and which owns most of the security definer functions the portal uses. I see no reason why we might need dynamic SQL in such functions, so no avenues for SQL injection unless you can exploit a trigger or something. For fine-grained permissions, the functions could do additional permission checks based on an auth token. This should effectively prevent SQL Injection from being particularly useful in this context.
    - krallin 13y ago
      Under this model, how do you store user (database) credentials?
      - einhverfr 13y ago
        The application logs into the db with the credentials the user supplies, so the problem is not our problem anymore. This means, btw, that you can hook up LedgerSMB to use LDAP Auth simply by changing PostgreSQL's LDAP settings. Here's an example howto, using LDAP via Samba (I would prefer Kerberos, but this demonstrates what people can do with such a model): http://cocnm.computerisms.ca/index.php/Install_Ledgersmb http://cocnm.computerisms.ca/index.php/Install_Ledgersmb Now, again, this is great for an internal business tool. It may not be what you want for a high-scaling public web app. We use a different model for customer portals (with security again encapsulated in the db, but through a different way). It's worth noting btw that PostgreSQL allows you to specify auth method by connecting host and requesting user/database, so you have a high degree of control over what sorts of connections can be enabled or disabled in such an environment in the db backend.
dragonwriter 13y ago
That's true, but unfortunately for the state of application security today, most applications developers today treat the DB as a stupid storage box, despite the fact that more effort has been put into the security model of almost any RDBMS they might be using than will be put into the whole of most applications using it, which means in terms of access control, most applications end up reinventing the wheel, badly.

tptacek 13y ago

To attempt this security model, which can work if everything in the universe goes right for you, your application needs to be designed for it from the very beginning. Virtually nobody does this. If your application wasn't designed from the start to make its database connection "just another view onto data users would have access to anyways" (paraphrasing), locking down the database is closer to make-work than it is to critical security measures.

y0ghur7_xxx 13y ago
> To attempt this security model, which can work if everything in the universe goes right for you Almost all webapps we write, we write them this way. (on oracle, not postgres, but that makes no difference). > your application needs to be designed for it from the very beginning. That's true. As in "you can not add security later on".
- tptacek 13y ago
  No, that's not what I said. I didn't say you "can't add security later on". I said that you can't rely on database access control configuration to protect an application that hasn't been designed from scratch to do that. Most applications don't rely on database access control; they rely on the application server to protect the database.
  - einhverfr 13y ago
    Just because the application assumes it is trusted by the database doesn't mean the database has to trust the application.
  - ako 13y ago
    15 years ago, in client-server Era, relying on database Access control was the default. It's a shame that all that knowledge has mostly been forgotten, and most programmers don't actually know how to use a database.
- wila 13y ago
  That works as long as the database server is kept up to date with security patches. Quite often in production running a security patch is not high priority. I guess it all depends on using this feature as an additional layer of protection or using it as the only layer of protection.
einhverfr 13y ago
Actually in LedgerSMB we retrofitted such a security model onto the application (in 1.3) because it was the only real hard security model we could retrofit onto the application. A security model which follows these processes merely has a database that doesn't trust the application. This has some significant costs and tradeoffs associated (you can't do connection pooling).
ris 13y ago
So each user of your webapp has a respective database user? Otherwise I don't know how you would be able to make the permissions fine-grained enough to be able to make these claims.
- einhverfr 13y ago
  That's exactly what we do in LedgerSMB. More to the point, the db user credentials are application login credentials so the application has the db permissions of the user. No password? The app can't access any data. Now this has some significant tradeoffs. It is very much a good decision for an ERP app. However for customer portals we are looking at a different approach, namely: 1. A portal user account in the db with very restricted permissions (and no direct access to tables) 2. A portal back-end user account in the db with limited access to tables and which owns most of the security definer functions the portal uses. I see no reason why we might need dynamic SQL in such functions, so no avenues for SQL injection unless you can exploit a trigger or something. For fine-grained permissions, the functions could do additional permission checks based on an auth token. This should effectively prevent SQL Injection from being particularly useful in this context.
  - krallin 13y ago
    Under this model, how do you store user (database) credentials?
    - einhverfr 13y ago
      The application logs into the db with the credentials the user supplies, so the problem is not our problem anymore. This means, btw, that you can hook up LedgerSMB to use LDAP Auth simply by changing PostgreSQL's LDAP settings. Here's an example howto, using LDAP via Samba (I would prefer Kerberos, but this demonstrates what people can do with such a model): http://cocnm.computerisms.ca/index.php/Install_Ledgersmb http://cocnm.computerisms.ca/index.php/Install_Ledgersmb Now, again, this is great for an internal business tool. It may not be what you want for a high-scaling public web app. We use a different model for customer portals (with security again encapsulated in the db, but through a different way). It's worth noting btw that PostgreSQL allows you to specify auth method by connecting host and requesting user/database, so you have a high degree of control over what sorts of connections can be enabled or disabled in such an environment in the db backend.

dragonwriter 13y ago

That's true, but unfortunately for the state of application security today, most applications developers today treat the DB as a stupid storage box, despite the fact that more effort has been put into the security model of almost any RDBMS they might be using than will be put into the whole of most applications using it, which means in terms of access control, most applications end up reinventing the wheel, badly.