Java JDBC connection ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext #27
Comments
|
We are hitting this issue as well when one of our triggers looks for a secret in the vault from the decrypted_secrets view. But in our case, we're not using a Java client, the trigger is coming from an insert from an RPC which in turn is called with the supabase client via the .rpc() function. This is the second time I attempt to use vault after various versions of the CLI updated, still not possible to get it to work consistently. |
|
This issue is due to JDBC defaulting the session timezone to the local timezone of the computer running the client, which, if different from the session timezone used to encrypt the data, causes the associated timestamp columns that are checked by the encryption signature to be rendered in a different timezone and thus the signature check fails. The workaround is to ensure that the decrypting session uses the same session timezone as the encrypting session, which we recommend always be UTC. In JDBC you can change this with |
|
Thanks for the clarification! Hope the fix comes soon! |
Hi, is it same thing in issue #30? I'm having similar issue and can't figure it out |
|
Hi, db:
container_name: supabase-db
image: supabase/postgres:15.1.0.117
healthcheck:
test: pg_isready -U postgres -h localhost
interval: 5s
timeout: 5s
retries: 10
depends_on:
vector:
condition: service_healthy
command:
- postgres
- -c
- config_file=/etc/postgresql/postgresql.conf
- -c
- log_min_messages=fatal # prevents Realtime polling queries from appearing in logs
restart: unless-stopped
ports:
# Pass down internal port because it's set dynamically by other services
- ${POSTGRES_PORT}:${POSTGRES_PORT}
environment:
POSTGRES_HOST: /var/run/postgresql
PGPORT: ${POSTGRES_PORT}
POSTGRES_PORT: ${POSTGRES_PORT}
PGPASSWORD: ${POSTGRES_PASSWORD}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
PGDATABASE: ${POSTGRES_DB}
POSTGRES_DB: ${POSTGRES_DB}
volumes:
- ./volumes/db/realtime.sql:/docker-entrypoint-initdb.d/migrations/99-realtime.sql:Z
# Must be superuser to create event trigger
- ./volumes/db/webhooks.sql:/docker-entrypoint-initdb.d/init-scripts/98-webhooks.sql:Z
# Must be superuser to alter reserved role
- ./volumes/db/roles.sql:/docker-entrypoint-initdb.d/init-scripts/99-roles.sql:Z
# PGDATA directory is persisted between restarts
- ./volumes/db/data:/var/lib/postgresql/data:Z
# Changes required for Analytics support
- ./volumes/db/logs.sql:/docker-entrypoint-initdb.d/migrations/99-logs.sql:Z
# Custom postgres config
- ./volumes/db/custom_postgres.conf:/etc/postgresql/postgresql.conf:Z
# pgsodium decryption key
- ./volumes/db/pgsodium_root.key:/etc/postgresql-custom/pgsodium_root.key |
|
Any update? It's been a really bad experience for us to use the Supabase vault reliably. We constantly get permission or decrypt errors when testing the code, resetting/reseeding our local db for development, or deploying our code from local to remote. |
|
Edit: I believe the Edit 2: it also looks like this will fail if the key does not exist on first start up... I reccomend running this (below) before your first (and every subsequent) start to generate a key (it will skip if the file exists). It should be run at the top level, just outside the #!/bin/bash
set -euo pipefail
KEY_FILE=./volumes/db/pgsodium_root.key
if [[ ! -f "${KEY_FILE}" ]]; then
head -c 32 /dev/urandom | od -A n -t x1 | tr -d ' \n' > "${KEY_FILE}"
fiFor anyone coming accross this:
# ...
db:
# ...
volumes:
# ...
# pgsodium decryption key
- ./volumes/db/pgsodium_root.key:/etc/postgresql-custom/pgsodium_root.key:Z |
ghost
mentioned this issue
|
I introduced a PR to fix this, as it really should be the default behaviour: supabase/supabase#21855 |
ghost
mentioned this issue
|
This supabase/postgres#901 is a better solution, its just a bit more involved, and it would allow the PR above (supabase/supabase#21855). |
|
is there any follow up that can be posted? |
|
Same problem here, when working locally ( |
|
I found a workaround, which is re-adding the stripe API key (the wrapper I use atm) using the local Studio Web-UI. |
This is the correct solution for the issue raised. Namely, before executing any JDBC actions, to use: Solutions like
are probably only working because then the timezones will match (timezone used in browser is local timezone, not UTC, and thus succeeds). As of today, supabase CLI (and the docker compose examples) make the pgsodium key persistent, so this really boils down to a JDBC error (as this specific issue is for). |
Bug report
Describe the bug
I am connecting via jdbc and making a call to insert into a table in our supabase database. On that table we have a trigger that is pulling from vault to do some downstream work after insert/update. While executing the part of the trigger that is accessing vault.decrypted_values i am seeing the following error be thrown by supabase.
Postgres is throwing the error so i'm thinking it might be something that the java connection is doing.
To Reproduce
I narrowed it down to something with vault even though above is calling somehting just associated with vault.
A basic test replicates the issue below
Expected behavior
We are making the same call in a js client as well as executing the query right in supabase console i have no issues. I'd expect the same from jdbc.
Screenshots
System information
Additional context
Like i mentioned above this is working on other connections we are making in javascript and psql, so it appears to be something specific the jdbc driver is doing. It's worth mentioning that client_encoding is utf8 on the instance as well as being set on the client itself.
The text was updated successfully, but these errors were encountered: