Is there a way to store / use secrets, like an API...
# helpe
Erwin
02/27/2022, 12:07 PMIs there a way to store / use secrets, like an API token, within a Postgres function?
Use case: our users can upload and delete images. I need to check the authorizations of the user before deleting an image. Seems like the workflow that minimizes number of network calls would be:
1. Call Postgres function to check authorization
2. Delete in external storage using an HTTP call from the Postgres function
3. If successful, remove from the DB
We're using an external storage provider (for now 😉 ), so the HTTP call requires an API access token we don't want to expose. I don't know enough about Postgres to know if I can just define it inline in the function (as long as I don't commit the code), if there is an extension to store secrets or if it's just not doable
c
chipilov
02/27/2022, 1:03 PMI cannot speak to the possibility of using PG extension to store secrets, since I am not aware of such extensions.
chipilov
02/27/2022, 1:04 PMHowever, if you consider it enough NOT to check in the secret with your code, you can use a migrations library which supports placeholders/environment variable expansion within migration scripts and/or support for a secrets manager
chipilov
02/27/2022, 1:06 PMFlyWay is one such tool and that's how I keep secrets away from the repo. FlyWay basically supports 2 way of accessing those secrets and injecting them in the migration scripts at migration time:
1) Via an environment variable (not a CI/CD expert, but I am pretty sure GitHub/GitLab support setting secrets as env variable during build/migration actions)
2) Via secrets managers such as Google Secrets Manager, Aws Secrets Manager, Vault and Dapr (https://flywaydb.org/documentation/configuration/secretsManagement)
chipilov
02/27/2022, 1:07 PMOf course, this approach does NOT prevent a user/attacker who has access to the actual db instance to see the expanded secret within the function definition - however, my guess is that if you have someone with unauthirzed access to the db instance, it's probably game over any way (unless it is something very specific you grant to developers where they can only look at function definitions and NOT read or edit the data)
e
Erwin
02/27/2022, 3:11 PMThe security model we're going for is: what can an authenticated client do if they open their console & start using the Supabase JS client directly, assuming they have access to everything sent w/ the client app?
So if an authenticated client can't retrieve the definition of a Postgres function from RPC, I think we should be good with putting the secret in-line, right?
Erwin
02/27/2022, 3:13 PMThis is for a side project with friends, so security requirements are not high and I may just write the function in Supabase's online editor & keep the definition w/out the API token in the repo
Erwin
02/27/2022, 3:14 PMFlyway's solution (or a handwritten script in CD pipeline for simple cases) look good for more serious projects!
c
chipilov
02/27/2022, 3:51 PMYes, I think so