Cockroach: sql: support crypt function 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?

https://github.com/cockroachdb/cockroach/issues/19368 has reference to pgcrypt. crypt is a part of the pgcrypto module.

[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: https://github.com/cockroachdb/cockroach/issues/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.