3 ms·

> This approach might seem horrifyingly outside-the-box I mean, this is how we routinely built client-server apps 20-30 years back. Everything old is new again

> This approach might seem horrifyingly outside-the-box I mean, this is how we routinely built client-server apps 20-30 years back. Everything old is new again! One of the big reasons so many moved away from this approach was security. Giving end users direct access to the database (even if "obscured"), gives me the shivers!

mike_hearn 4y ago
One of the things that's changed in recent years is that free databases like PostgreSQL have far more security features. You can grant fine grained privileges on individual objects like queries (via views), individual table rows, stored procedures/functions and so on. In the demo app, I use a simple approach to security: users don't have permissions to directly access any tables. Read access is via views and could use row-level security (RLS), and write access is always via stored procedures. Stored procs can also act as queries, and they can be written in any supported extension language, so any access control and privacy policy is implementable. Still, security in two-tier apps is a nuanced topic. Some things get better and other things are worse. Some of the remaining things to solve are: 1. RDBMS engines tend to be written in C. They're good code and fairly trustworthy, but still, web servers tend to be written in memory safe languages and databases aren't. 2. DDoS defense companies have more options for protecting web apps (sending js challenges and captchas). If you have a mobile app then you're exposing raw APIs anyway, so this is maybe not a big change. 3. You have to set resource quotas to stop malicious users submitting expensive queries. Of course this is also a concern with web apps, just translating HTTP to PG doesn't make it impossible to spam servers with expensive queries. 4. Data can be protected with ACLs pretty well, but schemas not so much. So you could leak an upcoming feature via the appearance of new object names. Web apps sometimes have the same issue with leaking info about new features in their JS though, and the sky doesn't fall. For many app types the above concerns don't matter much. Any app that's internal or business-to-business for example. If you build auth around client certs then unauthorized users never get to connect at all. Also! Consider that this design also eliminates many kinds of security issue. The talk goes into this. For example, XSS and SQL injection are eliminated by design! Those have been two of the most common and destructive bug classes over time. Also, web servers do not exactly have a track record of being unhackable, especially as wrapping SQL with HTTP handlers introduces many new places where access checks might be forgotten or incorrectly implemented. A SQL GRANT statement is pretty transparent and the implementation is well tested compared to app specific logic that might have been written by one person and never properly tested. There's also other security-related benefits, for example, certificate expiry routinely causes massive and eye-bleedingly disruptive outages, but is eliminated as a problem entirely by this design. It works because code signatures are timestamped so don't break when the cert expires, and the db connection can be then be encrypted using a self-signed certificate that never expires. To rotate the key you roll out a new client.
- majkinetor 4y ago
  This is basically how its done today with tools like Postgrest.
RowanH 4y ago
Who remembers SQL in Delphi UI :)

mike_hearn 4y ago

One of the things that's changed in recent years is that free databases like PostgreSQL have far more security features. You can grant fine grained privileges on individual objects like queries (via views), individual table rows, stored procedures/functions and so on. In the demo app, I use a simple approach to security: users don't have permissions to directly access any tables. Read access is via views and could use row-level security (RLS), and write access is always via stored procedures. Stored procs can also act as queries, and they can be written in any supported extension language, so any access control and privacy policy is implementable. Still, security in two-tier apps is a nuanced topic. Some things get better and other things are worse. Some of the remaining things to solve are: 1. RDBMS engines tend to be written in C. They're good code and fairly trustworthy, but still, web servers tend to be written in memory safe languages and databases aren't. 2. DDoS defense companies have more options for protecting web apps (sending js challenges and captchas). If you have a mobile app then you're exposing raw APIs anyway, so this is maybe not a big change. 3. You have to set resource quotas to stop malicious users submitting expensive queries. Of course this is also a concern with web apps, just translating HTTP to PG doesn't make it impossible to spam servers with expensive queries. 4. Data can be protected with ACLs pretty well, but schemas not so much. So you could leak an upcoming feature via the appearance of new object names. Web apps sometimes have the same issue with leaking info about new features in their JS though, and the sky doesn't fall. For many app types the above concerns don't matter much. Any app that's internal or business-to-business for example. If you build auth around client certs then unauthorized users never get to connect at all. Also! Consider that this design also eliminates many kinds of security issue. The talk goes into this. For example, XSS and SQL injection are eliminated by design! Those have been two of the most common and destructive bug classes over time. Also, web servers do not exactly have a track record of being unhackable, especially as wrapping SQL with HTTP handlers introduces many new places where access checks might be forgotten or incorrectly implemented. A SQL GRANT statement is pretty transparent and the implementation is well tested compared to app specific logic that might have been written by one person and never properly tested. There's also other security-related benefits, for example, certificate expiry routinely causes massive and eye-bleedingly disruptive outages, but is eliminated as a problem entirely by this design. It works because code signatures are timestamped so don't break when the cert expires, and the db connection can be then be encrypted using a self-signed certificate that never expires. To rotate the key you roll out a new client.

majkinetor 4y ago
This is basically how its done today with tools like Postgrest.

RowanH 4y ago

Who remembers SQL in Delphi UI :)