search

supabase / vault

Extension for storing encrypted secrets in the Vault
Apache License 2.0
163 stars 9 forks source link

Java JDBC connection ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid ciphertext #27

Open ryankazokas opened 1 year ago

ryankazokas commented 1 year ago

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

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 commented 10 months ago

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 commented 10 months ago

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 commented 10 months ago

Thanks for the clarification! Hope the fix comes soon!

bartoszpijet commented 9 months ago

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 commented 8 months ago

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 commented 5 months ago

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 commented 4 months ago

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:

ghost commented 4 months ago

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

ghost commented 4 months ago

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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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