4 ms·

A good overview, but the most secure way is to not give any table-level access privileges and only allow access using SECURITY DEFINER stored procedures (the PG

by fmajid 6y ago

A good overview, but the most secure way is to not give any table-level access privileges and only allow access using SECURITY DEFINER stored procedures (the PG equivalent of the setuid bit). For instance if you have a table of users with login and hashed salted password, you could implement a check_password() procedure. If the app account is compromised, it would not have access to the password hashes or even be able to enumerate users.

ed25519FUUU 6y ago
Stored procedures are really not used enough. They’re incredibly useful, and not just for security. People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.
- doctor_eval 6y ago
  When used for business logic they also execute about 20x faster than the same logic encoded in a client, and in far fewer LOC. Getting rid of all those round-trips has a huge effect!
  - koolba 6y ago
    Running a slow hash operation that should take a non-negligible amount of CPU on your single core per process database server isn’t a great idea. That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
    - tpetry 6y ago
      You can do the slow pbkdf2 hash on your webserver and use the result for your stored procedure to check the password. If the password column is only visible to the stored procedure you have used the best features of both systems.
      - koolba 6y ago
        No you can’t. To do the hash you need the per user salt before you start hashing. Which would require reading from the DB.
        mvolfik 6y ago
        Then you can retrieve salt, do hash on server, and use the stored procedure to compare. More requests, but a tradeoff for security \o/
        tpetry 6y ago
        There is no reason you can‘t have a deterministic function which generates a hash for the user. A simple sha256(lower(email)) is equally secure as a complete random salt, the only requirement on a salt is to be unique.
- cooperaustinj 6y ago
  I agree with the password example. I don't agree with people updating queries willy-nilly. Updated queries should go through the same life-cycle of testing (etc) as code, meaning they can simply go out with continuous application deployments anyways.
- citrin_ru 6y ago
  I have an impression that many developers actively avoid stored procedures, trigger or any 'complex' database features because they don't fit well into a typical CI/CD pipeline which deals only with application code. It is quite possible to test stored procedures, it's just requires some additional work/infrastructure.
  - fmajid 6y ago
    It's mostly a familiarity and education problem, as well as the fact MySQL didn't have good support for this. Developers are exposed to procedural programming languages at university, but seldom to databases, let alone stored procedures. Unit tests can be written in SQL or PL/PGSQL and will typically run much faster than a CI pipeline.
throwawayboise 6y ago
I mean, it's likely you have some other stored procedure that does enumerate users, for use by app adminstrators. Ideally it doesn't return password hashes though, since they are really not useful for humans to look at. Also ideally, the admin stored procedures are not executable by the end-user app.
- vbezhenar 6y ago
  Usually application uses connection pool to connect with database server. I'm not sure if that pattern could be used, when different requests need different database roles.
  - megous 6y ago
    It's doable. You have a web role in DB that's used for public facing part of a website and then admin role that's used for backoffice part of the website. You can then separate these backends to different machines or at least to a differnet UNIX users, so that when someone breaks through via public facing backend, he doesn't immediately get access to everything, but still has to work for it a bit. It's not as simple, you still have to make sure public backend user can't access web logs, that may reveal session id of an backoffice admin account or other information useful for breaking in other parts of the website. Eventually, the attacker will gain access, but this is useful to slow him down enough, so there's a chance he'll give up, or you notice something suspicious. For example pgbouncer allows you to connect via multiple roles/credentials to a database. It's just about how you configure your pool.
    - fmajid 6y ago
      Not to mention that with the microservices fad you could have different server processes with different credentials in their connection pools for different operations.
hannihanni 6y ago
Lock down port-level access to the PostgreSQL database is important though

by fmajid 6y ago

ed25519FUUU 6y ago

Stored procedures are really not used enough. They’re incredibly useful, and not just for security. People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.

doctor_eval 6y ago
When used for business logic they also execute about 20x faster than the same logic encoded in a client, and in far fewer LOC. Getting rid of all those round-trips has a huge effect!
- koolba 6y ago
  Running a slow hash operation that should take a non-negligible amount of CPU on your single core per process database server isn’t a great idea. That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
  - tpetry 6y ago
    You can do the slow pbkdf2 hash on your webserver and use the result for your stored procedure to check the password. If the password column is only visible to the stored procedure you have used the best features of both systems.
    - koolba 6y ago
      No you can’t. To do the hash you need the per user salt before you start hashing. Which would require reading from the DB.
      - mvolfik 6y ago
        Then you can retrieve salt, do hash on server, and use the stored procedure to compare. More requests, but a tradeoff for security \o/
      - tpetry 6y ago
        There is no reason you can‘t have a deterministic function which generates a hash for the user. A simple sha256(lower(email)) is equally secure as a complete random salt, the only requirement on a salt is to be unique.
cooperaustinj 6y ago
I agree with the password example. I don't agree with people updating queries willy-nilly. Updated queries should go through the same life-cycle of testing (etc) as code, meaning they can simply go out with continuous application deployments anyways.
citrin_ru 6y ago
I have an impression that many developers actively avoid stored procedures, trigger or any 'complex' database features because they don't fit well into a typical CI/CD pipeline which deals only with application code. It is quite possible to test stored procedures, it's just requires some additional work/infrastructure.
- fmajid 6y ago
  It's mostly a familiarity and education problem, as well as the fact MySQL didn't have good support for this. Developers are exposed to procedural programming languages at university, but seldom to databases, let alone stored procedures. Unit tests can be written in SQL or PL/PGSQL and will typically run much faster than a CI pipeline.

throwawayboise 6y ago

I mean, it's likely you have some other stored procedure that does enumerate users, for use by app adminstrators. Ideally it doesn't return password hashes though, since they are really not useful for humans to look at. Also ideally, the admin stored procedures are not executable by the end-user app.

vbezhenar 6y ago
Usually application uses connection pool to connect with database server. I'm not sure if that pattern could be used, when different requests need different database roles.
- megous 6y ago
  It's doable. You have a web role in DB that's used for public facing part of a website and then admin role that's used for backoffice part of the website. You can then separate these backends to different machines or at least to a differnet UNIX users, so that when someone breaks through via public facing backend, he doesn't immediately get access to everything, but still has to work for it a bit. It's not as simple, you still have to make sure public backend user can't access web logs, that may reveal session id of an backoffice admin account or other information useful for breaking in other parts of the website. Eventually, the attacker will gain access, but this is useful to slow him down enough, so there's a chance he'll give up, or you notice something suspicious. For example pgbouncer allows you to connect via multiple roles/credentials to a database. It's just about how you configure your pool.
  - fmajid 6y ago
    Not to mention that with the microservices fad you could have different server processes with different credentials in their connection pools for different operations.

hannihanni 6y ago

Lock down port-level access to the PostgreSQL database is important though