sql: support more functions from pgcrypto module

[robert-s-lee]

FEATURE REQUEST

https://vernemq.com/docs/configuration/db-auth.html

is there an equivalent crypt(x.password, x.salt) available?

#19368 has reference to pgcrypt. crypt is a part of the pgcrypto module.

---

Update on 2023-05-09: Here is a list showing the currently supported functions. We are working on adding support for the remaining ones.

• digest
• hmac
• crypt
• gen_salt
• encrypt
• decrypt
• encrypt_iv
• decrypt_iv
• gen_random_bytes
• gen_random_uuid

pgcrypto also has PGP functions which we may or may not add, since the security community is generally moving away from openpgp. These are also tracked in #73869.

• pgp_sym_encrypt
• pgp_pub_encrypt_bytea
• pgp_sym_decrypt
• pgp_sym_decrypt_bytea
• pgp_pub_encrypt
• pgp_pub_encrypt_bytea
• pgp_pub_decrypt
• pgp_pub_decrypt_bytea
• pgp_key_id
• armor
• dearmor
• pgp_armor_headers

Jira issue: CRDB-5904

Epic CRDB-10655

[awoods187]

Who requested this?

[mberhault]

It seems Bxxx did in the linked issue. It's a reasonable ask to make password storage easier (passwords from the end-user application, not ours). We'd have to gauge demand and figure out exactly which algorithms to support (only one out of the four supported by pgcrypto is acceptable for security purposes), as well as what else to support from pgcrypto.

[mberhault]

See also: #22878 for pgp functions from pgcrypto.

[dianasaur323]

Ok, apparently I'm supposed to take this on, so I'll do some research on this.

[cmnstmntmn]

@robert-s-lee did you manage to auth?

i set the password to null, but i'm getting this warning

2018-07-08 16:51:40.103 [warning] <0.483.0>@vmq_mqtt_fsm:check_user:555 can't authenticate client {[],<<"ctin">>} due to chain_exhausted

[knz]

@cmnstmntmn I have investigated the issue by looking at the source code of VerneMQ, which thankfully is open source.

The issue can be addressed either by

• providing the built-in functions crypt() and gen_salt() as initially identified,

• or by providing an additional vmq_diversity plugin to handle authentication using CockroachDB that you would use instead of auth_postgres

I am not yet certain which one is the easiest for us but I'll investigate.

[cmnstmntmn]

@knz thank you for your time and interest!

[cmnstmntmn]

a specific VerneMQ auth plugin would be great;
i do belive there's a win-win situation for both crdb and verne.

[knz]

@awoods187 can you pick this up for roadmapping?

[awoods187]

As discussed, we will handle this through Ops & Tools planning

[MrOffline77]

What is the state of pgcrypto with cockroachdb ?

[knz]

@MrOffline77 thank you for your interest in CockroachDB!

we are approaching this by looking at individual use cases and applications and providing answers on a per-case basis. It is unlikely that we will aim supporting the entirety of pgcrypto any time soon, because it has a very large scope and also includes functionality that is rarely used (if at all) by modern applications.

The proper next step for you here is to tell us more about your specific needs to see how we can help you.

[MrOffline77]

@knz Thank you !
As far as i know pgcrypto is used for encryption of tables / columns inside the database for customer sensitive data like credit card information and so on. Is this possible with cockroachdb "core" at the moment, or is this a enterprise license thing ?

[knz]

@MrOffline77 we provide a separate feature which is systematic on-disk encryption for all the data (not just table data: also metadata and user authentication details). This is an enterprise feature.

[MrOffline77]

Ok. Did I get you right that there is no alternative for a mysql query type like this:

INSERT INTO users (username, password) VALUES ('root', AES_ENCRYPT('somepassword', 'key12346123'));
SELECT AES_DECRYPT(password, 'key12346123') FROM users WHERE username = 'root';

I guess this is an interesting thing for other people too.

[knz]

This is a similar request as the one from a previous commenter. In this case we advise to run the AES encrypt and decrypt functions using a client-side library.

We understand this need well and will consider this for a future extension of CockroachDB.

Note however that the performance profile of such a facility is not ideal, and if performance is important to you you will want to run the encryption/decryption client-side in a streaming fashion, or use a storage encryption facility on the database side (which can also operate in streaming fashion).

[tim-o]

Zendesk ticket #3212 has been linked to this issue.

[polikeiji]

Hello!
What's the status of the pgcrypto module support? The issue was closed as completed, but has it been already supported by CockroachDB? I found #73869, and it looks not be finished, so I'm a little bit confused.

We're currently looking into CockroachDB and YugaByte DB as the distributed SQL for our internal projects. Some projects might be required column-based encryption at rest for specific data to follow our data privacy policy. Of course, we can encrypt them on the application side with some libraries, but I think the encryption approach using PGP_SYM_ENCRYPT, PGP_SYM_DECRYPT, PGP_PUB_ENCRYPT, and PGP_PUB_DECRYPT such as ones in the YugaByte DB document might be one of the attractive options for the engineers.

[ajwerner]

I don't think we meant to close this.

[dvasdekis]

This is also a hurdle for supporting Hasura's Metadata database, meaning that Hasura needs a separate Postgres database to work with Cockroach (given cockroach's lack of support for pg_crypto). Discussion here: https://discord.com/channels/407792526867693568/428469959530643466/1035501924541267968

[jordanlewis]

Hi @dvasdekis, that Discord link isn't accessible to me.

Can you please provide a list of the missing functionality that Hasura needs from pg_crypto?