4 ms·

At KotlinConf today I gave a talk on designing apps with two-tier architecture, where you implement your entire app without the web stack appearing anywhere at

At KotlinConf today I gave a talk on designing apps with two-tier architecture, where you implement your entire app without the web stack appearing anywhere at all. Instead you publish desktop and mobile apps that connect directly to an RDBMS like PostgreSQL via its native protocol, and use server extensions for any logic that is inconvenient to do with SQL. This approach might seem horrifyingly outside-the-box but has a lot of advantages, and some of the reasons we didn't do things this way historically have been solved in recent years. Because it was KotlinConf the demo uses PL/Java, which is pretty nice because there's such a healthy ecosystem of stuff based around JDBC and because deploying JVM stuff doesn't require any sort of cross-compilation. PL/Java also supports (for now) trusted extensions using sandboxing, although of course the sandbox can just get in the way and normally you trust your own server anyway so this is a double edged sword. The demo code can be found here (it's a prototype and nobody reviewed it yet so be gentle) https://github.com/hydraulic-software/bugzino https://github.com/hydraulic-software/bugzino I'll write up a blog post version of the talk, but for now I had to mention that DBaaS providers don't actually enable this sort of design because they like to wall off the full power of the RDBMS behind custom APIs. But in two-tier design you really lean into the database and use all of its features. So, it'd be nice if: a. database.dev were to support PL/Java extensions. b. Supabase were to allow direct connections, as the native DB protocol supports a lot of features that otherwise have to be sort of hacked on top of HTTP. Ultimately, HTTP is designed to fetch hypertext whereas the PG native protocol is designed to work with data, and that difference shines through in a bunch of ways.

nijave 4y ago
Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.
- mwcampbell 4y ago
  Mike has called this out as a weakness of PostgreSQL, and an advantage of Microsoft SQL Server and Oracle, before.
- mike_hearn 4y ago
  This is also discussed in the talk, which I hope will be on YouTube soon enough (it was live streamed). DB connections are heavier than web server connections, in particular in terms of memory (when using postgres, not sure about mysql). Multiplexers like pgbouncer can address this, but then you lose a bit of the deployment simplicity. Fortunately RAM is pretty cheap nowadays, you can configure the DB to disconnect idle connections, you can use multiplexers and you can make the client transparently reconnect when a connection is needed. So it turns into a RAM/user latency tradeoff. There are some benefits though. Native DB protocols will tend to stream results in binary, avoiding a JSON encoding that can expand the bandwidth requirements. Also you can get rid of the server side load balancing, because good db drivers know how to load balance amongst R/O replicas already. A lot of deployment complexity in web apps is driven by the fact that browsers handle errors by just throwing up an error page and stopping, so you need to avoid even tiny amounts of downtime. When you control the client you can do failover and recovery more gracefully, which in turn relaxes the availability constraints on the servers a bit.
  - nijave 4y ago
    It seems like this uses native Postgres security/roles which I think would prevent a connection pooler from working (unless you held open 1 connection per user) Ultimately it'd be nice if Postgres could keep a hot worker pool for connections instead of forking processes but afaik there's been plenty of debate over the years and no consensus I think high connection thrashing also eats up CPU and there's the issue of increasing load eats more RAM which reduces Postgres memory cache. We had a connection leak problem and clearing ~2600 PG connections saved something like 80-100GB of RAM
    - mike_hearn 4y ago
      I think you can use https://github.com/pgaudit/set_user https://github.com/pgaudit/set_user combined with some pgbouncer configuration to solve that, but agreed that postgres doesn't have perfect support for this scenario. It'd be better to have a proxy that is designed specifically for this use case that knows how to drive something like the set_user extension (and better if it were to be merged upstream).
saghm 4y ago
> This approach might seem horrifyingly outside-the-box but has a lot of advantages, and some of the reasons we didn't do things this way historically have been solved in recent years. From a development standpoint, I definitely think this seems easier! My concerns about this would be almost entirely about security. How does this approach avoid leaking credentials that people could use to access the database outside of the application? More generally, how does this approach deal with the increased attack surface for the database from exposing it to the open internet?
- mike_hearn 4y ago
  See my other post for a discussion of non-user security topics. Every app user has a database user, mapped 1:1, so there are no credentials to leak. The DB credentials are the user credentials. In the demo app repository I implement open signup with email confirmation using stored procedures/a server extension. It works by creating a guest user with a well known password, but it's locked down so that the only things it can do are run a couple of stored procedures. Those then send an email to confirm ownership of the address before creating a real DB user. Open work: don't use passwords, mint SSL client certificates during signup with the private key stored in the OS keystore. Now there's no cookies or passwords to steal! Also, OAuth integration. The guest user / open signup is certainly a weakness for the demo, which is public on the internet (a dedicated cluster though). It's locked down as much as possible but there are probably lots of ways to screw with the server even without being granted access to anything. And there are no rate limits on signup. It's really a very basic exploration.
  - conceptme 4y ago
    Connections are pretty expensive in PG how can you do pooling if every user connects directly?
    - giovannibonetti 4y ago
      Connection poolers like PgBouncer [1] (traditional) and Supabase's Supavisor [2] (new) come to mind. [1] https://www.pgbouncer.org/ https://www.pgbouncer.org/ [2] https://github.com/supabase/supavisor https://github.com/supabase/supavisor
GordonS 4y ago
> 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.
benatkin 4y ago
> This approach might seem horrifyingly outside-the-box but has a lot of advantages You don't need to worry, there are a lot of people who are into stored procedures. If there weren't, there wouldn't be people disagreeing with them, like the author of Rails: http://web.archive.org/web/20060418215514/http://www.loudthinking.com/arc/000516.html http://web.archive.org/web/20060418215514/http://www.loudthi... The slippery slope of liking stored procedures a whole lot means them taking over the rest of the middle of the stack, and that isn't a new idea. I like the idea of having instant access to the database, and microservices are my favored approach.
- mike_hearn 4y ago
  I think DHH's perspective makes a lot of sense from the perspective of a web framework author. If you're going to buy into Rails it makes sense to buy in all the way, and not try to split your app logic across two very different languages and execution environments. Naturally he wants everything to be in Rails and for the DB to be treated as dumb as possible, it is always the way for a platform to want to subsume and abstract its lower layers. In two-tier architecture there is no web framework, however, because there is no web server to begin with. All the UI logic runs client side and is written in the same language as the stored procedures. In this design there's nothing that's competing to be the place where app logic is implemented because you can't trust the client, so all trusted logic has to be in the database. In that case you buy into the RDBMS all the way, and really lean on its capabilities. The tension is resolved in a different direction.
asguy 4y ago
I had fun with a similar concept: just access the database from the browser using nginx/TLS/lua: https://github.com/alexanderguy/pgress https://github.com/alexanderguy/pgress If you're good with your authenticated users directly talking to the DB (which there are plenty of uses for), it's a great way to get your data into the browser.
refset 4y ago
Omnigres, "Postgres as a platform", is very much in the same vein: https://github.com/omnigres/omnigres https://github.com/omnigres/omnigres
- mike_hearn 4y ago
  That's cool. Seems they're focused on writing apps in SQL though.
  - yrashk 4y ago
    Omnigres author here. While it's definitely possible (and perhaps even advisable) to build many application in sql (especially if your traditional backend is mostly just slinging sql queries), Omnigres by no means limits users to that. Any other postgres PL can handle request. Support for proxy proxying requests to Postgres-managed containers is coming, too. The core idea here is less about using SQL or any other PL but increasing mechanical sympathy and decreasing integration, devops and network overhead of the traditional architecture. Feel free to ask questions or stop by our Discord!
jchmbrln 4y ago
This approach is very appealing, but when I put my SRE hat on my first concern is observability. Has anyone implemented tracing for Postgres with a span for each procedure call? When I put my dev hat on, I want the option of attaching a debugger and adding breakpoints. Anything there?
- mike_hearn 4y ago
  With PL/Java you can at least debug stored procedures, because the Java Debug Wire Protocol is a protocol so you can run it over the network. There are instructions for how to attach a debugger in the PL/Java docs. For debugging SQL remotely, I don't know what's available, if anything. Though if you only use SQL for short things, you can just run it and debug the query interactively I guess. Most databases have tools for logging query timings and doing auditing. PGAudit is one for Postgres.
spullara 4y ago
My first job in SF was with WebLogic and at the time their #1 product was a JDBC driver that let applets talk directly with databases proxied through a Java server, looked something like Applet<->JDBC<->HTTP<->Server<->JDBC<->Oracle (most of the time).

nijave 4y ago

Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.

mwcampbell 4y ago
Mike has called this out as a weakness of PostgreSQL, and an advantage of Microsoft SQL Server and Oracle, before.
mike_hearn 4y ago
This is also discussed in the talk, which I hope will be on YouTube soon enough (it was live streamed). DB connections are heavier than web server connections, in particular in terms of memory (when using postgres, not sure about mysql). Multiplexers like pgbouncer can address this, but then you lose a bit of the deployment simplicity. Fortunately RAM is pretty cheap nowadays, you can configure the DB to disconnect idle connections, you can use multiplexers and you can make the client transparently reconnect when a connection is needed. So it turns into a RAM/user latency tradeoff. There are some benefits though. Native DB protocols will tend to stream results in binary, avoiding a JSON encoding that can expand the bandwidth requirements. Also you can get rid of the server side load balancing, because good db drivers know how to load balance amongst R/O replicas already. A lot of deployment complexity in web apps is driven by the fact that browsers handle errors by just throwing up an error page and stopping, so you need to avoid even tiny amounts of downtime. When you control the client you can do failover and recovery more gracefully, which in turn relaxes the availability constraints on the servers a bit.
- nijave 4y ago
  It seems like this uses native Postgres security/roles which I think would prevent a connection pooler from working (unless you held open 1 connection per user) Ultimately it'd be nice if Postgres could keep a hot worker pool for connections instead of forking processes but afaik there's been plenty of debate over the years and no consensus I think high connection thrashing also eats up CPU and there's the issue of increasing load eats more RAM which reduces Postgres memory cache. We had a connection leak problem and clearing ~2600 PG connections saved something like 80-100GB of RAM
  - mike_hearn 4y ago
    I think you can use https://github.com/pgaudit/set_user https://github.com/pgaudit/set_user combined with some pgbouncer configuration to solve that, but agreed that postgres doesn't have perfect support for this scenario. It'd be better to have a proxy that is designed specifically for this use case that knows how to drive something like the set_user extension (and better if it were to be merged upstream).

saghm 4y ago

> This approach might seem horrifyingly outside-the-box but has a lot of advantages, and some of the reasons we didn't do things this way historically have been solved in recent years. From a development standpoint, I definitely think this seems easier! My concerns about this would be almost entirely about security. How does this approach avoid leaking credentials that people could use to access the database outside of the application? More generally, how does this approach deal with the increased attack surface for the database from exposing it to the open internet?

mike_hearn 4y ago
See my other post for a discussion of non-user security topics. Every app user has a database user, mapped 1:1, so there are no credentials to leak. The DB credentials are the user credentials. In the demo app repository I implement open signup with email confirmation using stored procedures/a server extension. It works by creating a guest user with a well known password, but it's locked down so that the only things it can do are run a couple of stored procedures. Those then send an email to confirm ownership of the address before creating a real DB user. Open work: don't use passwords, mint SSL client certificates during signup with the private key stored in the OS keystore. Now there's no cookies or passwords to steal! Also, OAuth integration. The guest user / open signup is certainly a weakness for the demo, which is public on the internet (a dedicated cluster though). It's locked down as much as possible but there are probably lots of ways to screw with the server even without being granted access to anything. And there are no rate limits on signup. It's really a very basic exploration.
- conceptme 4y ago
  Connections are pretty expensive in PG how can you do pooling if every user connects directly?
  - giovannibonetti 4y ago
    Connection poolers like PgBouncer [1] (traditional) and Supabase's Supavisor [2] (new) come to mind. [1] https://www.pgbouncer.org/ https://www.pgbouncer.org/ [2] https://github.com/supabase/supavisor https://github.com/supabase/supavisor

GordonS 4y ago

> 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.

benatkin 4y ago

> This approach might seem horrifyingly outside-the-box but has a lot of advantages You don't need to worry, there are a lot of people who are into stored procedures. If there weren't, there wouldn't be people disagreeing with them, like the author of Rails: http://web.archive.org/web/20060418215514/http://www.loudthinking.com/arc/000516.html http://web.archive.org/web/20060418215514/http://www.loudthi... The slippery slope of liking stored procedures a whole lot means them taking over the rest of the middle of the stack, and that isn't a new idea. I like the idea of having instant access to the database, and microservices are my favored approach.

mike_hearn 4y ago
I think DHH's perspective makes a lot of sense from the perspective of a web framework author. If you're going to buy into Rails it makes sense to buy in all the way, and not try to split your app logic across two very different languages and execution environments. Naturally he wants everything to be in Rails and for the DB to be treated as dumb as possible, it is always the way for a platform to want to subsume and abstract its lower layers. In two-tier architecture there is no web framework, however, because there is no web server to begin with. All the UI logic runs client side and is written in the same language as the stored procedures. In this design there's nothing that's competing to be the place where app logic is implemented because you can't trust the client, so all trusted logic has to be in the database. In that case you buy into the RDBMS all the way, and really lean on its capabilities. The tension is resolved in a different direction.

asguy 4y ago

I had fun with a similar concept: just access the database from the browser using nginx/TLS/lua: https://github.com/alexanderguy/pgress https://github.com/alexanderguy/pgress If you're good with your authenticated users directly talking to the DB (which there are plenty of uses for), it's a great way to get your data into the browser.

refset 4y ago

Omnigres, "Postgres as a platform", is very much in the same vein: https://github.com/omnigres/omnigres https://github.com/omnigres/omnigres

mike_hearn 4y ago
That's cool. Seems they're focused on writing apps in SQL though.
- yrashk 4y ago
  Omnigres author here. While it's definitely possible (and perhaps even advisable) to build many application in sql (especially if your traditional backend is mostly just slinging sql queries), Omnigres by no means limits users to that. Any other postgres PL can handle request. Support for proxy proxying requests to Postgres-managed containers is coming, too. The core idea here is less about using SQL or any other PL but increasing mechanical sympathy and decreasing integration, devops and network overhead of the traditional architecture. Feel free to ask questions or stop by our Discord!

jchmbrln 4y ago

This approach is very appealing, but when I put my SRE hat on my first concern is observability. Has anyone implemented tracing for Postgres with a span for each procedure call? When I put my dev hat on, I want the option of attaching a debugger and adding breakpoints. Anything there?

mike_hearn 4y ago
With PL/Java you can at least debug stored procedures, because the Java Debug Wire Protocol is a protocol so you can run it over the network. There are instructions for how to attach a debugger in the PL/Java docs. For debugging SQL remotely, I don't know what's available, if anything. Though if you only use SQL for short things, you can just run it and debug the query interactively I guess. Most databases have tools for logging query timings and doing auditing. PGAudit is one for Postgres.

spullara 4y ago

My first job in SF was with WebLogic and at the time their #1 product was a JDBC driver that let applets talk directly with databases proxied through a Java server, looked something like Applet<->JDBC<->HTTP<->Server<->JDBC<->Oracle (most of the time).