21–30 of 57 posts
I'd love to see plv8 added to this. Jerry S. has done an amazing job of wrangling the insane v8 build process into something that mostly works across platforms, but installation is still not particularly simple. Unfortunately, due to the problems with that build process, Ubuntu and other flavors dropped the prebuilt apt packages. Making plv8 a simple install would be a huge boost to the community.
From https://supabase.com/blog/dbdev > dbdev fills the same role for PostgreSQL as npm for JavaScript, pip for Python and cargo for Rust in that it enables publishing libraries and applications for repeatable deployment. We'll be releasing the tooling necessary for third-parties to publish pglets to the registry once we’ve collected some community feedback and incorporate any great new ideas. Our goal is to create an…
pgxn does not (yet) support the notion of TLEs, but it certainly could with minimal changes. We're currently in discussion with the pgxn maintainers about how that would look.
I'd love to see plv8 added to this. Jerry S. has done an amazing job of wrangling the insane v8 build process into something that mostly works across platforms, but installation is still not particularly simple. Unfortunately, due to the problems with that build process, Ubuntu and other flavors dropped the prebuilt apt packages. Making plv8 a simple install would be a huge boost to the community.
EDIT: I think I misunderstood your comment, plv8 itself is a compiled extension and thus cannot be a TLE, however TLEs can be written in plv8 once you have it installed. Sorry for the confusion!
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 h…
Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.
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 h…
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?
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 h…
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!
I'd love to see plv8 added to this. Jerry S. has done an amazing job of wrangling the insane v8 build process into something that mostly works across platforms, but installation is still not particularly simple. Unfortunately, due to the problems with that build process, Ubuntu and other flavors dropped the prebuilt apt packages. Making plv8 a simple install would be a huge boost to the community.
wow, thanks for the compliment! it hasn't always been easy, believe me.
I know that at one point the idea of using a non-v8 JavaScript engine was floating around, due in part to the V8 build complexity and the API breaking changes.
Is this still something that is up in the air?
I know that Bun decided to use JavaScriptCore as a recent example.
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 h…
> 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!
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.