Is there a way to get the project reference id or url from within Postgres?

[whollacsek]

I have a use case where I call Edge function from Postgres but having to change the url manually for each environment is not faisable. Is there a solution to this?

[markszente]

Let's take this one step further: how do you store, say, API keys to make sure the request is coming from Postgres, and not some cheeky user?

Here's what I came up with:

Use ALTER SYSTEM to set your own values. For local instances, it can be done in seed.sql, for dev/staging/production, you would need to run the script in each environment. Then, in your Postgres function, just read the value of your config using current_setting, and use it as URL, API key, and so on.

[SparkInChief]

Your general approach is valid and matches what I was thinking, but when I try to run the script on dev/staging¹ as the postgres superuser in a pro account, I get the error

ERROR:  42501: permission denied to set parameter "my.project_url"

However, since Vault is now available in Beta, I tried storing the project url as a "secret" and voilà—a persisted value that is also easy to define in seed or migration scripts. I wasn't thinking of the project url as a secret, but since storing it in Vault I've started thinking of it and other persisted values in terms of their sensitivity.

Vault is an even better choice for your other use case of API keys: where a system parameter can, by nature, be retrieved by any process or query executed by any user, I can apply granular permissions to the vault.decrypted_secrets view to ensure that only authorized users/roles can access the plaintext values—from public settings to admin-eyes-only keys and credentials.

This does seem like overkill for some persisted values, and I may simply add a public table/view for parameters that aren't sensitive. I'd love to hear how you ended up using ALTER SYSTEM for your parameters—or whether you went with a different approach.

Footnotes

1. No "production" yet, as I'm still in the exploration phase 😉 ↩

[SparkInChief]

@whollacsek — how did you end up solving this? I couldn't get markszente's ALTER SYSTEM approach to work, but as noted in my comment above I've had some success with Vault.

Happy to consider other solutions if you found any!

[whollacsek]

I used the Vault approach at the end but it needs ugly string concatenations when defining triggers.

…

On Sat, 3 Aug 2024 at 21:22, SparkInChief ***@***.***> wrote: @whollacsek <https://github.com/whollacsek> — how did you end up solving this? I couldn't get markszente's ALTER SYSTEM approach to work, but as noted in my comment <#12639 (reply in thread)> above I've had some success with Vault. Happy to consider other solutions if you found any! — Reply to this email directly, view it on GitHub <#12639 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAGJQUAFP3RHPY2WKHSDDTTZPUUZHAVCNFSM6AAAAABL6EVPGGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAMRTGIYDMNQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>