Java JDBC connection ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext

[ryankazokas]

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.

org.postgresql.util.PSQLException: ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext
  Where: PL/pgSQL function pgsodium.crypto_aead_det_decrypt(bytea,bytea,uuid,bytea) line 12 at RETURN
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2297)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
	at com.channelape.drivers.SupabaseDriver.givenSomeValuesWhenTestingThenExpectTest(SupabaseDriver.java:19)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)

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

String url = "jdbc:postgresql://db.supabaseinstanceabc.supabase.co:5432/postgres?user=postgres&password=xxxxxxxx";
		Connection conn = DriverManager.getConnection(url);
		Statement st = conn.createStatement();
		// Fails if i call directly or indirectly
		ResultSet rs = st.executeQuery("select * FROM vault.decrypted_secrets");
		while (rs.next()) {
			System.out.print("Column 1 returned ");
			System.out.println(rs.getString(1));
		}
		rs.close();
		st.close();

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

• OS: [mac]
• supabase cloud
• correto 8 (but also replicated in open jdk 11 and 17)
• Postgres JDBC Driver 42.6.0

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.

[bombillazo]

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.

[michelp]

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 TimeZone.setDefault(TimeZone.getTimeZone("UTC"));. We have a fix for this bug that will be included in pgsodium in the next release.

[bombillazo]

Thanks for the clarification! Hope the fix comes soon!

[bartoszpijet]

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 TimeZone.setDefault(TimeZone.getTimeZone("UTC"));. We have a fix for this bug that will be included in pgsodium in the next release.

Hi, is it same thing in issue #30? I'm having similar issue and can't figure it out

[bartoszpijet]

Hi, For anyone wondering. This issue occurs because pgsodium encryption key is not kept and is generated every time you restart postgres container. You just gotta keep pgsodium_root.key file persistant and that's it. For example you can create it in specified location and modify docker-compose.yml, here is an example (look at last line)

  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

[bombillazo]

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.

[ghost]

Edit: I believe the :Z flag (info) is needed at the end @bartoszpijet, my docker compose is treating ./volumes/db/pgsodium_root.key:/etc/postgresql-custom/pgsodium_root.key as an empty directory, whereas adding :Z at the end (./volumes/db/pgsodium_root.key:/etc/postgresql-custom/pgsodium_root.key:Z) solves the problem.

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 volumes directory.

#!/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}"
fi

For anyone coming accross this:

• based on https://github.com/supabase/vault/issues/27#issuecomment-1804197388:
  • In your docker compose file:

# ...
  db:
    # ...
    volumes:
      # ...
      # pgsodium decryption key
      - ./volumes/db/pgsodium_root.key:/etc/postgresql-custom/pgsodium_root.key:Z

[ghost]

I introduced a PR to fix this, as it really should be the default behaviour: https://github.com/supabase/supabase/pull/21855

[ghost]

This https://github.com/supabase/postgres/pull/901 is a better solution, its just a bit more involved, and it would allow the PR above (https://github.com/supabase/supabase/pull/21855).

[commandcenterio]

is there any follow up that can be posted? I am running into this issue after creating a function the retrieves a secret from the Vault, I am calling the function through rpc on a nextjs server function. This seems to work locally, but when pushing to staging and testing is when this error pops up. It is unclear how we can resolve this for a supabase-hosted environment.

[MoergJ]

Same problem here, when working locally (supabase start), I created a function to query a foreign wrapper table. That function works fine when executed directly using SQL, but throws the invalid ciphertext error, as soon as my NuxtJS application tries to call the rpc endpoint.

[MoergJ]

I found a workaround, which is re-adding the stripe API key (the wrapper I use atm) using the local Studio Web-UI.

[barrownicholas]

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 TimeZone.setDefault(TimeZone.getTimeZone("UTC"));. We have a fix for this bug that will be included in pgsodium in the next release.

This is the correct solution for the issue raised. Namely, before executing any JDBC actions, to use: TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

Solutions like

I found a workaround, which is re-adding the stripe API key (the wrapper I use atm) using the local Studio Web-UI.

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).