supabase-js: Strings Upserted Into An Encrypted Text Column Cannot Be Decrypted
Bug report
- I confirm this is a bug with Supabase, not with my own application.
- I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
When a string is inserted into an encrypted column via the API, the decrypted copy of that column does not display the original value.
To Reproduce
Steps to reproduce the behavior, please provide code snippets or a repository:
const { team, user, bot } = installation;
const customerId = cusId;
const customerData = {
id: customerId,
ref: team.name,
bot_token: bot.token,
installed_by_user: user.id,
installed_by_team: team.id,
};
const conflictField = 'id';
await upsertData('customers', customerData, conflictField, supabase);
async function upsertData(table, data, conflictField, supabase) {
const { data: result, error } = await supabase
.from(table)
.upsert(data, { onConflict: conflictField });
if (error) {
throw new Error('Error upserting data: ', error);
} else {
return result;
}
}
Expected behavior
The content that was originally inserted into the encrypted column will be returned when the column is decrypted.
System information
- OS: macOS
- Version of supabase-js: 2.24.0
- Version of Node.js: 18.16
Note
I first asked about this on Discord here.
About this issue
- Original URL
- State: closed
- Created a year ago
- Reactions: 4
- Comments: 33 (2 by maintainers)
Hi @AlexIsMaking,
We’ve found the issue is an intersection of two problems, the first is that PostgREST (correctly IMO) generates the ON CONFLICT query using the
EXCLUDED.*pseudo-row to set columns from the row that conflicted (ie the data to be updated). In this case, the excluded column contains the encrypted data, not the plaintext (that plaintext isn’t included in any column of the excluded row, that comes from the view) so it ends up being double encrypted.The view does have both columns, but the second issue is that you cannot usefully
INSERT ... ON CONFLICTto a view, because a conflict clause requires a unique constraint violation, and views do not have constraints. So even if problem 1 didn’t exist problem 2 would prevent it.The only workaround we can think of for the moment is to use and RPC function to do the upsert, joining the table and the view and doing
INSERT ... ON CONFLICT (id) DO UPDATE SET ..., t.secret = v.decrypted_secretand this will decrypt the column using the view, then reencrypt it using the table trigger. I’ll try and come up with a working example for you and drop it here in a comment.@michelp have you had a chance to look further into this at all? It’d be great to be able to use the encrypted columns to store sensitive information.
Yep I’m inserting the “same” row, of course in SQL the “sameness” of a row in this case is whether or not it satisfies a uniqueness constraint or not, so I’m inserting the same
idvalue, and thus violating the constraint, and consequently theON CONFLICTclause is evaluated.This might be something to do with PostgREST after all then, since I can’t reproduce what you’re describing in straight SQL. I’ll investigate further.
I’ll also add a helper function that does that security label join mess so it’s easier to diagnose.
Ah good point that is confusingly worded, I’ll update the docs, thanks!
looks like the
updateworkaround is no longer workingNo, I’m making two api requests now ha
@steve-chavez same here, I used the dashboard to create a new column on an existing table with TCE
@steve-chavez yes it’s using TCE, I’ve created the table and columns from the dashboard.
It looks like
updateworks, but upsert does not work. Might need to upsert first and then update.I am seeing the same issue. It looks like strings are being encrypted twice when using supabase-js.