michelpp

> Is Vault something that can handle this without getting into my app code? Basically, if i gave a someone root access to my supabase instance is that encrypted data safe?

The answer is slightly offset from your question, so let me start by pointing out that the Vault is about Encrypted Data At Rest. This is mentioned in the docs and in the blog and video, but it's something that I like to always mention first in discussions. The main purpose of the Vault is to store your data encrypted, so that it's encrypted on disk, and in backups. In SQL the decrypted secrets are available to you, because that's where you are using them and encrypted data must be decrypted to be useful.

If someone roots access to your database, then yes they can access the decrypted secrets through the view. This is by design, the secrets must be decrypted to be useful in query code. This risk is similar to someone rooting your application code, they will see decrypted secrets via your environment key, so no it won't protect you against anyone rooting processes in your stack that need useful access to secrets and it's not meant to. Like all security you must take a layered approach, the Vault is just one storage level layer strategy.

One big difference from the env var approach though is that the key Supabase uses to encrypt your secrets with the Vault is stored outside the database, it is inaccessible to SQL, which is an enhancement over sticking the raw key into an environment variable or a table that is accessible to your application. Instead of revealing the raw key, pgsodium has a feature called [Server Key Management](https://github.com/michelp/pgsodium#server-key-management) where you do not have access to the raw key, but instead reference keys by an key identifier. It is safe to store this identifier alongside the data it encrypts. The raw key itself is never stored. I'm very intentionally overusing the word "store" here, because that's specifically the layer of security that the Vault provides.

Vault and pgsodium dev here at supabase, we're pretty excited about the Vault, and this is just the beginning of some of the advanced encryption functions that libsodium provides that we want to bring into the Postgres ecosystem.

Happy to answer any questions here about how the Vault works now, and always excited to see use cases and suggestions for features from the community. libsodium is a big API, and pgsodium exposes most of it (about 110 functions so far, a few functions don't make sense in SQL) so there is a lot of possibilities for new ideas and projects straight into SQL without having to learn the low level C details of using the sodium library directly.

Yes, multiplying graph adjacency matrices over a min-tropical semiring produces shortest paths. APL supports this syntactically, but how does it function in the real world? What if the matrices are extremely sparse, like almost all real world graphs are? Syntax is the easy the problem. The hard problem is having very sparse graphs with billions of nodes and many billions of edges. How is a square matrix going to deal with that? Sparse and hypersparse graph computing is hard, which is not syntactically relevant.

Python does not have this terse a syntax, but it gets very close with multiple bindings to the SuiteSparse GraphBLAS Hypersparse graph processing libraries, which intrinsically supports sparse graphs and a very large number of operators, including the tropical min/max ones. Suitesparse doesn't provide syntax (that's the easy part), it provides the hard part, sparse and hypersparse data structures and very complex dynamic, parallel graph processing optimizations including JIT compilation of GPU accelerated operations.

Here's a notebook that shows your shortest path example using Python and the GraphBLAS. While it's a trivial example, it can scale the the largest graphs possible to use today using SuiteSparse:

https://github.com/Graphegon/pygraphblas/blob/main/demo/Intr...

NB: I should clarify I'm not advocating for Python's syntax in particular vs APL, I'm saying that syntax is not the hard part of any particular problem, especially when it comes to sparse graphs. There are also other binding to GraphBLAS like Julia that are quite nice. I do appreciate that APL does elgantly permit the notion of graph operations with linear algebra.

Another signcryption scheme as described in the article is also implemented by the libsodium author as an extension:

https://github.com/jedisct1/libsodium-signcryption

It's unclear from the article if this is the same implementation age uses.

Signcryption schemes are also a good candidate algorithm for replacing JWTs and PASETO as they suffer from no algorithm confusion, and don't need what PASETO calls "Algorithm Lucidity" and serve both plaintext authentication, authenticated encryption, sender receiver verification, and shared key generation that can be used for unlimited encrypted streaming, for example with libsodium's crypto_secretstream API.

https://doc.libsodium.org/secret-key_cryptography/secretstre...

https://github.com/paseto-standard/paseto-spec/blob/master/d...

> But I think it would help to understand if Supabase is fully managing key backup and recovery internally, how exactly is that working?

Supabase persists and protects your key and we will provide API and CLI access to retrieve it securely. This is a pre-release so we haven't worked out all the use cases yet but those are the basics for MVP.

> 1) Being able to store your database backups in less trusted locations,

Yes. Using Transparent Column Encryption you control on a column by column basis how your data is stored encrypted so you have more fine grained control over your data.

> 2) actually keeping the secret data secret, which amounts to keeping that encryption key secured at a much higher level than the database backup itself.

Yep, we don't have all the answers there, keeping the root key out of SQL is a big one. Maybe requiring MFA to access the key even with the API, there are a lot of possibilities. Thanks for your feedback these are all going into my notes for an upcoming release.

The Vault will not prevent someone who has login access to your database and the right grants (or superuser) from decrypting the data. If someone is in this position they are fully compromised and the Vault is not protection against that (nor is anything else really).

In particular if an attacker has a postgres superuser login they can essentially asct as the OS process owner, and could possibly get around the process hardening we already employ to reduce that risk, but again Vault is not designed to protect against a full superuser exploit. You must carefully guard database login access.

However, the secret data that is stored on disk, in WAL logs, and in database dumps is encrypted. This way you are ensured that your secrets are encrypted at rest. The Vault also provides using standard Postgres privilege access control (via GRANT/REVOKE) to control access to the decrypted data.

> Hmm... I feel like secrets are the one thing I don't want to be in Postgres... because I want to store my Postgres credentials in the secrets vault! And I certainly don't want to have to update the configuration for every service which accesses my secrets vault every time I upgrade my Postgres database (and the access URL changes).

Password storage is a somewhat different problem, if you're checking passwords, you just need to know it's authentic, not the actual password itself, so it's common to use hashing and salting techniques for this (pgsodium exposes all of the libsodium password and short hashing functions if you want to dig further) your best bet here is to use SASL with SCRAM auth for postgres

https://www.postgresql.org/docs/current/sasl-authentication....

Secret storage is more about encrypting and authenticating data that is useful for you to know the value of. For example you need the actual credit card number to process a payment (waves hand, this is a broad subject, and some payment flows do not require the knowledge of CCN) but you want to make sure that number is stored encrypted on disk and in database dumps. That's the use case the vault is hitting.

We also have some upcoming support for external keys that are stored encrypted, so for example you can store your Stripe webhook signing key encrypted in pgsodium and reference it by key id that can be passed to `pgsodium.crypto_auth_hmacsha256_verify()` to validate a webhook callback instead of the raw key itself.

The Supabase Vault is encryption at rest, the column is stored encrypted in the database, WAL streams and backup dumps. This is usually more efficient than dealing with full disk encryption, and it allows you to control who sees decrypted data on a role-by-role basis using normal Postgres security GRANTs.

With Full Disk Encryption you also only get encryption to that one disk, if you are doing WAL shipping, the disk you are storing the db on may be encrypted, but the WAL files you ship will not be, so you have to make sure those files are encrypted through a full chain-of-custody. With the Vault the data starts off encrypted before going into the WAL stream. Downstream consumers would need to also acquire the hidden root key to decrypt it. We're working on making that process seamless but also secure.

If you give a database client access to the decrypted secrets, then they have them. What the client will not have access to is the hidden root key that is not accessible to SQL that pgsodium uses to encrypt and decrypt data.

We are considering running the Vault in Trusted Execution Environments (TEE) that are similar to encrypted VMs, where the memory traffic to the cpu is encrypted until it hits the processor. We're still investigating this possibility but it would make for a more secure cloud environment for sure. Of course AWS charges quite a premium for them!

> Namely what will happen when you first restore some data into a new Postgres instance which booted with its own randomly generated root key (the wrong key) and then how you are supposed to patch in the correct key and be able to start reading secrets again?

We restore you're original key into new projects. There is also WIP on accessing the key through the API and CLI.

> Also, how does the decrypted view look if you try to read it with the wrong key loaded?

The decryption will fail (pgsodium will thrown an error).

> Do you have to worry about a race condition where you boot an instance with some encrypted data but forget to put the key file in place, and then end up with a new random key, saving some new data, and now you have a mix of rows encrypted with two different keys? Or will the whole subsystem block if there’s data stored that can’t be decrypted with the resident key?

There's no race in the system, your key is put in place by us before the server boots.

Thanks for the feedback! I'll put some more thought into your question about authenticating a key is the original before you use it.

Hi! article author and Supabase employee here. We're excited to preview the Vault secret storage we've been working on. Please drop any comments or questions you may have here and I'll be happy to answer them!

He's played the comical part of slick startup investor "Freak Masterstroke" in the new mst3k series:

https://mst3k.fandom.com/wiki/Freak_Masterstroke

"Where's my second-screen experience!"

Old android phones make great dashcams. Most of them have automatic gps address lookup, g-sensor shock detection, far better cameras and more storage than most dedicated devices. There's several dashcam apps on the play store.

PBS Space Time has done a couple of episodes on Stationary Action and Noether's Theorem that are approachable by those not yet already exposed to Langrangians:

https://www.youtube.com/watch?v=Q_CQDSlmboA

https://www.youtube.com/watch?v=04ERSb06dOg

Sabine Hossenfelder also has a good episode on it:

https://www.youtube.com/watch?v=A0da8TEeaeE

double entendre: "A phrase that has two meanings, especially where one is innocent and literal, the other risqué, bawdy, or ironic; an innuendo."

Postgres does support jsonpath syntax which covers some of those cases:

https://www.postgresql.org/docs/current/datatype-json.html#D...

It seems like an obvious next step for Postgres support would be to make PRQL a stored Procedure Language.

create function foo() returns bar as language prql $$<prql code here>$$;

The "Zope" interface model was proposed 21 years ago but not accepted:

https://peps.python.org/pep-0245/

Real buildings must take into account material costs, weight bearing, soil conditions, thermal cycling through various paths, and human and seismic induced dynamic loads. The larger the buildings get, the exponentially more compute is needed to solve these problems.

“Anyone can build a bridge that stands, but it takes an engineer to build a bridge that barely stands.”

Awesome I'm gonna check this out for my bundle!

This is also the basis for the postgresql-wheel package, a Python wheel that contains an entire local PostgreSQL binary installation:

https://github.com/michelp/postgresql-wheel

Out of sheer laziness I used CFFI to encapsulate the build, even though there is no dynamic linking involved. There's probably a simpler way but this approach has worked very well for disposable database tests.

I'm not posing an opinion either way, but what the article says is that vitamin D doesn't cause either good or bad health, it's only a marker for sun exposure which does cause good health.

"These rebels argue that what made the people with high vitamin D levels so healthy was not the vitamin itself. That was just a marker. Their vitamin D levels were high because they were getting plenty of exposure to the thing that was really responsible for their good health—that big orange ball shining down from above."

Yes, that article has come up a few times:

https://hn.algolia.com/?q=sunscreen+margerine

The article presents evidence that Vitamin D doesn't cause good health, but instead is a biomarker of good health caused by sun exposure and thus, supplementation does nothing for most people.

> I've never worked at Zope. Must be my doppelganger.

Same last name and first initial as your handle so I took a guess. :)

> I was referring to the ability to assign a user to one or more groups and then set, at the group level, access to a row or column. When I implemented this, I avoided using PG roles b/c I was unsure how this play safely with future changes to the hosted DB.

This can be done with Postgres' built-in role system. You can assign a "group role" to as many roles as you want (which in turn, can also be groups, or not) into a hierarchy of roles which can be used in any GRANT statement or RLS policy. Postgres used to have `CREATE USER ...` and `CREATE GROUP ...` but they are now completely subsumed by the "new" role system. Both statements still work and map to equivalent `CREATE ROLE ...` statements.

I work at Supabase on database security so I can speak to some of these.

> - As others have said, default security is way too permissive. They should lock everything down by default. At some point, this is going to cause major problems for some company that decides to build off Supabase.

We agree and are internally actively discussing this very subject.

> - Although they have row-level security, RBAC is completely missing. It's not hard to implement yourself through stored procedures and triggers (they have an example repo to copy from), but I don't see a lot of junior devs doing this.

We don't "have" row level security per se, that is a native feature of Postgres we expose, and the customer is free to use or not. RBAC is a very broad term, and broadly speaking Postgres has roles and privileges that do access control. Did you have something more specific in mind?

> - Stored procedures are more likely to be utilized in the Supabase paradigm, but I haven't felt like they have done much to address the inherent weakness and common critiques of using sprocs instead of application level functions.

Weaknesses, critiques and strengths. There are tradeoffs and some of this gets down to a matter of opinion. Mine is admittedly radical in this case. We recently released Edge functions which are great, more tools in the toolbox, including stored procedures.

Did you used to work on Zope? You have a familiar handle...

Disclaimer: I work at Supabase on database security.

The link you posted is a great guide to sensible security practices with Supabase. It's worth noting that the feature highlighted, Row Level Security (RLS) is a native Postgres feature and not Supabase specific in any way.

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

One immediate benefit of using native security is that it applies to all types of database clients and tools uniformly. we're not blocked in the future adding more integrations. And it works both ways, if you are using RLS in an existing postgres deployment you can migrate to Supabase and your existing trusted policies will come with you.

It also means that we can avoid creating a whole class of security bugs by starting from scratch, and take advantage of the millions of collected human-hours of work that already exists. Security is hard because it is adversarial and it is hard to reproduce or even conceptualize a truly threatening environment without experience.

One of our guiding principals is to stand on the shoulders of the core developers and not implement our own core security model, but to use the native one as a foundation for even more powerful security concepts to come. We're actively discussing a lot of ways to make this even easier and safer for customers.

Supabase recently shipped with graphql support that is very nice, it maps the query language to the relational schema in a sensible way. Without direct experience I understand this is similar to how Hasura maps it.

As for your preference for Hasura security vs Postgres' native row level security, of course your preference is entirely valid and may also be the best fit for your needs, but consider that RLS works for all database clients, not just Hasura, so if you have a heterogeneous db client environment and you want to enforce one central policy, RLS can do that but a Hasura specific policy checker can't.

Disclaimer: I work at Supabase but not directly on the GraphQL support.

> In high school and later on in rock/metal bands, I never actually used it. It was far more common to use guitar tablature

I know you know this, but I'd like to point out for others that Tab is specific to the guitar family (basses, ukulele, etc) it does not work for any other kind of instrument.

I am not very familiar with this tool, and the documentation doesn't mention this use case but it does look like you can pass specific schema(s) or exclude them:

https://github.com/djrobstep/migra/blob/master/migra/migra.p...

It looks like the schema do have to have the same name between the databases, which I think is sensible but again I could be wrong. This is a darker corner of the postgres tooling world that could definitely use some light, so good on the migra devs to trying this out. If you do get it working, consider sending them a PR with some documentation on your use case!