Manage secrets and query third-party APIs from Postgres

today we're looking at managing Secrets like for example API keys in postes using superbase Vault now superbase vault is a postgress extension and um also we have built a UI for it in the superbase dashboard making it safe and easy to store encrypted Secrets now this is the important part here so the secrets are always stored encrypted on disk they are never available decrypted and then this is a really neat way so the decryption works is they are only decrypted on demand using a postgress view now if you're interested to dive more into kind of the details on of it there's a deep dive in the docs I'll link it below as well so um there's more in terms of like how the encryption keys are stored and managed and it really is done in a way that even if someone were to gain access to a dump of your database they would not be able to decrypt um the secrets that are stored in there so secrets are always stored in an encrypted way now the way to access this in your um superbase dashboard is in the project settings you can here go to the Vault um basa and you can for example use the UI here to add a new secret so we call it stripe here will'll store our stripe secret key so this is our stripe Secret API key um and we can then store the secret value now one thing we'll do here um which is quite neat we we combine this with um the uh superbase wrappers so the wrappers for the stripe API allow us to actually query our stripe account uh through postgress using SQL so this is pretty cool this is um the test account behind our popular subscription payment starter um and so what we can do is here we can go to the developer section and we can now let's do this where we'll actually create a restricted key we'll call this um super base fault demo and then what we want to do is we want to be able to um read the um balances so let's just do this for this demo here we can um if we go into the the docs here we can see there's a bunch of things that are um enabled for the rappers so for example for customers you can also um you know insert update delete customers um but we'll just do the balances for now so what we can do is we can just enable uh read access for our balances for this key uh and then we're going to say create key and so uh super base VA demo again um this is a a secret key so we need to store it securely and we shouldn't show it to anyone so don't do what I'm doing here and then we can go back to our um superbase dashboard and we can paste in um our secret value here and then we say okay we just want to um you know hand let superbase handle the whole encryption key management default um uh encryption key let's create that and we can see now that um we created this encryption key here um and then we created our secret as well uh we can here get our uh key ID so if we look into the Vault documentation we can then see so yeah in the background you know we can also do all of this in SQL if we want to um so for example as a vault create Secret method um that we can call or we can just insert into vault. secrets so now if we um check this out we can do this in the table editor uh we can see that we'll have a schema here that's fault um and then we have our um Secrets here so you can see our um key ID this is our um secret key ID and then this is our secret um and so here you can see our secret is uh encrypted and then here is the decrypted secrets um View and so on demand then we get the uh decrypted secret here in our um dashboard and so this is never stored on disk this is basically generated as we're accessing the um the view here okay great and so now we can combine this with um superbase uh rappers and rappers is pretty cool here so this actually allows us to create um it's called it's a concept called foreign data wrapper um originally designed to access kind of multiple postgress instances um but here what we can do is we've we've developed this framework where we can actually use SQL um to create kind of foreign um data tables uh right Within you know our superbase dashboard and then actually in the background what happens is when we're accessing those tables um we we actually making an API request from postgress to stripe using our API key stored in the vault okay so the first thing we need to do is we need to um create our uh reers extension if that doesn't exists already so we'll go to our um SQL editor we can all do this here here um so we'll need to create our extension if it um doesn't exist already and then we need to create uh a foreign data wrapper um here called stripe wrapper and then we have a handle here Handler here and the validator so this is kind of part of um how this is implemented under the hood and so we can go ahead and run this here so now we have this um you know uh enabled our for foreign data reper for stripe and so now what we can do is we um so this piece we already have done this we have created our um Vault secrets and so now connecting to stripe we can do this uh we basically create this uh stripe server uh and the API um base URL is already kind of the default URL um so what we can do here is we can um leave this out you know default is already in there and then we just need our API key ID so if you uh remember correctly here in our project settings uh in the vault we have this is um our API uh key ID and we can go back to the SQL editor and we can put in here our key ID so as you can see see you know we're not um ever using our um actual you know API key uh but rather we're storing it in the vault and only you know when we're um actually making the API request so stripe we're then decrypting the value on the Fly and making the API request um I believe sorry you let's run this okay and so now we have our stripe server enabled and so now what we can do is we we can um create a foreign data table for our um balance for example so let's go here um so the way this works we're basically creating a foreign table uh called stripe balance and then we're creating it from the server called stripe server and we're passing the options where the object is balance so that's how we create kind of that foreign um data table so paste that in give that a run uh and yeah we one thing we forgot we haven't uh created the schema yet so if we go back here you know obviously we can choose any kind of schema we want but yeah I think it makes sense here to have it on um kind of a schema called stripe okay let's run that and so now you can see we have a new schema stripe and then we have uh a stripe balance table so if we go to the table editor we can go go to uh now our new stripe uh and you can see here we have a foreign table we can look at the balance so here we have like um an amount uh that's um available and a pending amount and so that adds up yeah if we look at the balance to 3754 31 um that works out now let's say we have a big fish customer and they sign up for our $400 now let's add this bypass the pending balance and add it straight to so we can use this test card here and subscribe and now um we convert the amount so the $400 to 52172 so if we go to our balances we now um yeah so should see four 22 42558 and so if we go back here we can now see that um as the dashboard is refreshing uh it's updating the available amount um the pending amount and so what we can do as well we can do this in um the the um you know SQL so we can select everything from uh stripe. balance so if we run this we now see the updated amount and and then also what we can do is you know in um Group by currency so let's select the sum of the amounts um as well as the currency and group by currency and now we can see okay the sum is four 2 5 5 and 18 cents so here 4255 and 18 cents so you can see with um super base Vault and the foreign data wrappers you can get um kind of access to real time um data information on your stripe account um simply using uh SQL which is really really cool um as well there is a bunch of other um foreign data rappers you can use these um you know to get data out from Air table awss 3 bigquery click house Firebase actually lock flare as well um so these are really really handy and uh I hope this was helpful for you and would love to hear kind of what other foreign data rappers you would find helpful and you know as always let us know in the comments below what you want to learn next and I'll see you then [Music] bye-bye

Channel: Supabase

Views: 1,743

Keywords: supabase tutorial, best firebase alternative, open source database

Id: 8VRbpx8Lxtg

Length: 11min 56sec (716 seconds)

Published: Thu Mar 14 2024