Column encryption with pgcrypto

[kmiyashiro]

I was looking into encrypting a sensitive column and read about the pgcrypto extension. I see pgcrypto is installed by default in supabase, but I was wondering how I would use it on my own tables or if it was something intended only for the auth table for now.

[ftonato]

Hey @kmiyashiro,

As you already know, Supabase allows the use of pgcrypto.

Although this is normally used for sensitive data (passwords), you can use it for whatever you want.

Below is an example of how to use it:

# Create table users
CREATE TABLE users (id SERIAL PRIMARY KEY,
                    email TEXT NOT NULL UNIQUE,
                    password TEXT NOT NULL);

# Insert a new user
INSERT INTO users (email, password)
VALUES ('kmiyashiro@mail.com', crypt('kmiyashiropassword', gen_salt('bf')));

# Fetch the user ID using his/her email and encrypted password
SELECT id
FROM users
WHERE email = 'kmiyashiro@mail.com'
  AND password = crypt('kmiyashiropassword', password);

# This will return one row with the correct user ID
# | id      |
# |---------|
# | 1       |
# | (1 row) |

Note: You can use this encryption for any field that you consider important.

I hope I've helped 🎸

[gcasesor]

i tried using pgp_sym_decrypt but got the same error --> ERROR: 42883: function pgp_sym_decrypt(text, unknown) does not exist.
Kindly advise.

[jakub-sekula]

I'm getting the same error here

[ivanbogdan-git]

Same error when using:
hashed_auth_code := encode(digest(id::TEXT || hash_salt, 'sha256'), 'hex');
function digest(text, unknown) does not exist.

Any update on this?

[ivanbogdan-git]

In my case, the solution was to remove the following line from the function:

SET search_path = public;

After digging deeper, I found out the reason this didn't work as expected was because pgcrypto was installed in the extensions schema (or namespace), not the public schema.

To check where pgcrypto was installed, I used the following script:

SELECT
    e.extname AS extension_name,
    n.nspname AS schema_name
FROM
    pg_extension e
JOIN
    pg_namespace n ON n.oid = e.extnamespace
WHERE
    e.extname = 'pgcrypto';

After confirming that pgcrypto was installed in the extensions schema, I updated the search path to include it:

SET search_path = public, extensions;

This resolved the issue by ensuring the function could access both the public schema and the extensions schema where pgcrypto was installed.

P.S. Someone somewhere mentioned that usign public.digest worked. But it didn't in my case.

[BeppeMarnell]

In my case, the solution was to remove the following line from the function:

SET search_path = public;

After digging deeper, I found out the reason this didn't work as expected was because pgcrypto was installed in the extensions schema (or namespace), not the public schema.

To check where pgcrypto was installed, I used the following script:

SELECT
    e.extname AS extension_name,
    n.nspname AS schema_name
FROM
    pg_extension e
JOIN
    pg_namespace n ON n.oid = e.extnamespace
WHERE
    e.extname = 'pgcrypto';

After confirming that pgcrypto was installed in the extensions schema, I updated the search path to include it:

SET search_path = public, extensions;

This resolved the issue by ensuring the function could access both the public schema and the extensions schema where pgcrypto was installed.

P.S. Someone somewhere mentioned that usign public.digest worked. But it didn't in my case.

Following up on this issue and this response:
make sure you have installed pgcrypt in the extension and then you can use the pgp_sym_encrypt/decrypt functions like this, just adding the extension namespace:

extensions.pgp_sym_encrypt(bla,bla,bla)
extensions.pgp_sym_decrypt(bla,bla,bla)