How to get past "query returned no rows" in Postgres?

Question

I have a project on Supabase and I've wanted to implement TCE to encrypt a certain column.

I am running into errors when trying to insert into my table which has an encrypted column.

Here is my table definition:

create table
  public.user_databases_tce (
    id bigint generated by default as identity,
    created_at timestamp with time zone null default now(),
    database_string text null,
    user_id text null default requesting_user_id (),
    uuid uuid not null,
    show_sample boolean null default false,
    constraint user_databases_pkey_tce primary key (id, uuid),
    constraint user_databases_uuid_key_tce unique (uuid),
    constraint user_databases_uuid_fkey foreign key (uuid) references user_schemas (uuid) on delete cascade
  ) tablespace pg_default;

create trigger user_databases_tce_encrypt_secret_trigger_database_string before insert
or
update of database_string on user_databases_tce for each row
execute function user_databases_tce_encrypt_secret_database_string ();

And here is how I activated column encryption for the database_string column:

SECURITY LABEL FOR pgsodium
    ON COLUMN user_databases.database_string
    IS 'ENCRYPT WITH KEY COLUMN uuid';

Now, when I try to insert in the table using the JavaScript Supabase client or even the Supabase dashboard GUI, I get this error:

{
    "error": {
        "code": "P0002",
        "details": null,
        "hint": null,
        "message": "query returned no rows"
    }
}

I don't know if I'm doing something wrong or if I need to configure some type of policy.

I'd appreciate your guys' help.

Answer 1

first thing is that you have a trigger in your table definition, but don't explain what it does. You don't need to explicitly create triggers to use pgsodium's TCE, the encryption trigger is created automatically.

Second is that you are using the uuid column as the key id column, but not providing a default, nor do you show the insert statement you are using, so it's hard to tell what you are putting into that column. It must be a valid key in the pgsodium.key table.

Third, INSERT statements do not return rows (unless you add RETURNING * to the statement). So not returning rows is normal. Are you doing a SELECT and not seeing any rows?

Please review the column encryption docs for a complete example on how to use TCE from both the Supabase dashboard and SQL.