7 ms·

Dbdev – A database package manager for PostgreSQL trusted language extensions

156 points by samwillis 4y ago

deleted 4y ago
[deleted]
noob4life 4y ago
How does `index_advisor` work?
- oliverrice 4y ago
  index_advisor is a project that almost made the cut for LW7. We decided to launch it as a pgtle instead to get some feedback before we (spoiler) probably release it in LW8 It uses the hypopg package to very quickly generate an index for each column referenced in the query and then re-run's an explain plan to measure the estimated "total cost". If the expected "total_cost" reduces, we add the indexes to the list of recommendations and continue testing the remaining columns. Its currently limited to single column indexes only but in a later update we'll enhance that to do more comprehensive searches with multiple columns in a single index
  - claytongulick 4y ago
    Cool idea for an extension! I had a whole set of comments about concerns about doing this to a large table in prod, and statistics and such - then I read about hypopg. What a neat package, learned something new today.
foreigner 4y ago
Can I use this on AWS RDS?
- paulddraper 4y ago
  No. RDS has a strict list of approved extensions. (Since AWS is on the hook for the system stability, security, replication, etc.)
  - supamichelp 4y ago
    RDS for Postgres 14+ does come with pg_tle, but it does not come with pgsql-http, so you cannot use the dbdev installer. But, you can still install TLEs, you just have to do so "by hand" by calling `pgtle.install_extension()` yourself, probably from a migration script depending on your tooling. See the RDS docs here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...
  - samwillis 4y ago
    Partially correct, this doesn't work with RDS as you can't install pgsql-http (a requirement for automatic install). However all the extensions listed are "Trusted Language Extensions for PostgreSQL" which was developed by AWS to enable installing extensions written in "Safe" languages. So I believe that all the extensions listed could be installed manually. https://github.com/aws/pg_tle https://github.com/aws/pg_tle https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...
    - paulddraper 4y ago
      Oh cool!
- oliverrice 4y ago
  RDS does have the underlying native extension that is required to install user defined packages (pg_tle) from database.dev. It doesn't currently have support for the extension that is necessary to make the in-database client work to install packages directly from the registry. That's great feedback though: We'll add a new page to the website for each package that provides the snippet you can run on an RDS instance to load the extension and then run `create extension`. We're also in talks with RDS team and experimenting with a few options that would allow users to query the registry directly. The most promising one we have found so far is using AWS Lambda with RDS's aws_lambda extension to do that http part, and pass the contents back to the database. Still a WIP though!
nextaccountic 4y ago
From https://supabase.com/blog/dbdev 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 open ecosystem for packaging and discovering SQL. How does https://pgxn.org/ https://pgxn.org/ fit in this ecosystem?
- supamichelp 4y ago
  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.
  - ccleve 4y ago
    TLEs = trusted language extensions?
    - deleted 4y ago
      [deleted]
    - supamichelp 4y ago
      Yes.
koolba 4y ago
Is this like pgxn but specific to trusted languages so presumably less installation issues?
- oliverrice 4y ago
  yeah, exactly. pgxn also houses native extensions, some of which may be TLE compatible. database.dev is trusted language extension specific We've reached out to David, who runs pgxn to see how we might collaborate and allow pgxn to be used as another registry that users can install TLEs from
mmmeff 4y ago
What's stopping me from adding an extension to this repository that adds something valuable while simultaneously exfiltrating entire databases back to me? It's a cool idea, but I won't be the first to try it. You're placing an awful lot of trust in these modules.
- michelpp 4y ago
  Like any package or plugin for any language or framework, you are responsible for understanding what you are installing. Since TLEs do not come with any compiled code, this is relatively easy for you to verify yourself with code inspection. We expect a lot of DBAs to say "no way" to something like TLEs, that's fine, for similar reasons people don't use pip, npm, or cpan. You make your own risk/benefit analysis.
- thewataccount 4y ago
  Nothing? It's a classic supply chain attack. The same goes for pypi, npm, crates.io, docker.com, github, chrome extensions, etc. Generally it comes down to trusted authors/package names. Many people trust `requests` from pypi. That being said even this doesn't prevent the package to be sold, or hacked, or from the owner later adding things, or a 3rd party sneaking something in via a PR. Some like docker (and technically most git based ones because of md5) can have specific tags changed at any time, so you could later add a malicious package to version 3.0.4 or whatever. Many package managers (mostly linux ones) include key-signing, some package managers also have built-in scanners. Preferably you proxy/cache versions you need, and have a way to audit version upgrades - but in reality it's a lot of work to do properly. Recent example of `pytorch` nightly on pypi accidentally having a dependancy named `torchtriton` that I think was name squatting? Anyway installing the pytorch nightly between December 25th to 30th would result in your home directory being uploaded, etc. https://www.bleepingcomputer.com/news/security/pytorch-discloses-malicious-dependency-chain-compromise-over-holidays/ https://www.bleepingcomputer.com/news/security/pytorch-discl... EDIT: That said if anyone from supabase reads this - I'd love to see GPG signing, version hashing/integrity verification, 2FA if it's not their already, and I know this one is a lot of extra work - but any type of automated even cursory glance for malicious code.
mike_hearn 4y ago
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
claytongulick 4y ago
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.
- jerrysievert 4y ago
  wow, thanks for the compliment! it hasn't always been easy, believe me.
  - LunaSea 4y ago
    Thank you for the amazing work indeed! 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.
    - jerrysievert 4y ago
      I have been slowly but surely working on pljs in my spare time. it is nowhere near feature parity with plv8, but I hope to get it closer with more time. currently missing: * query execution/cursors * window functions * sub transactions it is based on quickjs, is very fast/easy to compile, and while in long-running functions it is slower than v8, it has a very fast startup time that negates that for shorter functions. but, it's not ready for prime time yet.
  - mike_hearn 4y ago
    That's interesting - could you describe what makes the build and install so hard? I can't think of any obvious reason why it should be harder than PL/Java. V8 is pretty widely used!
    - 4y ago
bashinator 4y ago
You talking with the PostGIS folks?
- oliverrice 4y ago
  PostGIS is a native extension mostly written in C. Since there is no C trusted language it would be hard to fit PostGIS into the TLE paradigm Its less of a problem for well known and trusted extensions like PostGIS though because it comes pre-installed on most hosted providers (Supabase, RDS, etc)

156 points by samwillis 4y ago

deleted 4y ago

[deleted]

noob4life 4y ago

How does `index_advisor` work?

oliverrice 4y ago
index_advisor is a project that almost made the cut for LW7. We decided to launch it as a pgtle instead to get some feedback before we (spoiler) probably release it in LW8 It uses the hypopg package to very quickly generate an index for each column referenced in the query and then re-run's an explain plan to measure the estimated "total cost". If the expected "total_cost" reduces, we add the indexes to the list of recommendations and continue testing the remaining columns. Its currently limited to single column indexes only but in a later update we'll enhance that to do more comprehensive searches with multiple columns in a single index
- claytongulick 4y ago
  Cool idea for an extension! I had a whole set of comments about concerns about doing this to a large table in prod, and statistics and such - then I read about hypopg. What a neat package, learned something new today.

foreigner 4y ago

Can I use this on AWS RDS?

paulddraper 4y ago
No. RDS has a strict list of approved extensions. (Since AWS is on the hook for the system stability, security, replication, etc.)
- supamichelp 4y ago
  RDS for Postgres 14+ does come with pg_tle, but it does not come with pgsql-http, so you cannot use the dbdev installer. But, you can still install TLEs, you just have to do so "by hand" by calling `pgtle.install_extension()` yourself, probably from a migration script depending on your tooling. See the RDS docs here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...
- samwillis 4y ago
  Partially correct, this doesn't work with RDS as you can't install pgsql-http (a requirement for automatic install). However all the extensions listed are "Trusted Language Extensions for PostgreSQL" which was developed by AWS to enable installing extensions written in "Safe" languages. So I believe that all the extensions listed could be installed manually. https://github.com/aws/pg_tle https://github.com/aws/pg_tle https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...
  - paulddraper 4y ago
    Oh cool!
oliverrice 4y ago
RDS does have the underlying native extension that is required to install user defined packages (pg_tle) from database.dev. It doesn't currently have support for the extension that is necessary to make the in-database client work to install packages directly from the registry. That's great feedback though: We'll add a new page to the website for each package that provides the snippet you can run on an RDS instance to load the extension and then run `create extension`. We're also in talks with RDS team and experimenting with a few options that would allow users to query the registry directly. The most promising one we have found so far is using AWS Lambda with RDS's aws_lambda extension to do that http part, and pass the contents back to the database. Still a WIP though!

nextaccountic 4y ago

From https://supabase.com/blog/dbdev 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 open ecosystem for packaging and discovering SQL. How does https://pgxn.org/ https://pgxn.org/ fit in this ecosystem?

supamichelp 4y ago
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.
- ccleve 4y ago
  TLEs = trusted language extensions?
  - deleted 4y ago
    [deleted]
  - supamichelp 4y ago
    Yes.

koolba 4y ago

Is this like pgxn but specific to trusted languages so presumably less installation issues?

oliverrice 4y ago
yeah, exactly. pgxn also houses native extensions, some of which may be TLE compatible. database.dev is trusted language extension specific We've reached out to David, who runs pgxn to see how we might collaborate and allow pgxn to be used as another registry that users can install TLEs from

mmmeff 4y ago

What's stopping me from adding an extension to this repository that adds something valuable while simultaneously exfiltrating entire databases back to me? It's a cool idea, but I won't be the first to try it. You're placing an awful lot of trust in these modules.

michelpp 4y ago
Like any package or plugin for any language or framework, you are responsible for understanding what you are installing. Since TLEs do not come with any compiled code, this is relatively easy for you to verify yourself with code inspection. We expect a lot of DBAs to say "no way" to something like TLEs, that's fine, for similar reasons people don't use pip, npm, or cpan. You make your own risk/benefit analysis.
thewataccount 4y ago
Nothing? It's a classic supply chain attack. The same goes for pypi, npm, crates.io, docker.com, github, chrome extensions, etc. Generally it comes down to trusted authors/package names. Many people trust `requests` from pypi. That being said even this doesn't prevent the package to be sold, or hacked, or from the owner later adding things, or a 3rd party sneaking something in via a PR. Some like docker (and technically most git based ones because of md5) can have specific tags changed at any time, so you could later add a malicious package to version 3.0.4 or whatever. Many package managers (mostly linux ones) include key-signing, some package managers also have built-in scanners. Preferably you proxy/cache versions you need, and have a way to audit version upgrades - but in reality it's a lot of work to do properly. Recent example of `pytorch` nightly on pypi accidentally having a dependancy named `torchtriton` that I think was name squatting? Anyway installing the pytorch nightly between December 25th to 30th would result in your home directory being uploaded, etc. https://www.bleepingcomputer.com/news/security/pytorch-discloses-malicious-dependency-chain-compromise-over-holidays/ https://www.bleepingcomputer.com/news/security/pytorch-discl... EDIT: That said if anyone from supabase reads this - I'd love to see GPG signing, version hashing/integrity verification, 2FA if it's not their already, and I know this one is a lot of extra work - but any type of automated even cursory glance for malicious code.

mike_hearn 4y ago

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

claytongulick 4y ago

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.

jerrysievert 4y ago
wow, thanks for the compliment! it hasn't always been easy, believe me.
- LunaSea 4y ago
  Thank you for the amazing work indeed! 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.
  - jerrysievert 4y ago
    I have been slowly but surely working on pljs in my spare time. it is nowhere near feature parity with plv8, but I hope to get it closer with more time. currently missing: * query execution/cursors * window functions * sub transactions it is based on quickjs, is very fast/easy to compile, and while in long-running functions it is slower than v8, it has a very fast startup time that negates that for shorter functions. but, it's not ready for prime time yet.
- mike_hearn 4y ago
  That's interesting - could you describe what makes the build and install so hard? I can't think of any obvious reason why it should be harder than PL/Java. V8 is pretty widely used!
  - 4y ago

bashinator 4y ago

You talking with the PostGIS folks?

oliverrice 4y ago
PostGIS is a native extension mostly written in C. Since there is no C trusted language it would be hard to fit PostGIS into the TLE paradigm Its less of a problem for well known and trusted extensions like PostGIS though because it comes pre-installed on most hosted providers (Supabase, RDS, etc)