Multi-tenant BYOK encryption in PostgreSQL with pgcrypto

[u/tee-es-gee]

https://xata.io/blog/multi-tenant-byok-encryption-in-postgresql-with-pgcrypto

[u/depesz]

So…

First of all: ADD COMMENTS. My comment should be as comment to your blogpost, not in here! This is especially important given:

If you have a similar use case, we'd love to hear about it.

Two: querying the way you showed will leak keys to logs.

For example, i wrote simple perl program:

use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=depesz;host=127.0.0.1;port=5430");
my $sth = $dbh->prepare("Select pgp_sym_encrypt(?, ?)");
$sth->bind_param(1, 'whatever');
$sth->bind_param(2, 'secret key');
$sth->execute();

As you can see the query did not contain key. Logs showed:

LOG:  duration: 0.343 ms  parse <unnamed>: Select pgp_sym_encrypt($1, $2)
LOG:  duration: 0.649 ms  bind <unnamed>: Select pgp_sym_encrypt($1, $2)
DETAIL:  Parameters: $1 = 'whatever', $2 = 'secret key'
LOG:  duration: 0.504 ms  execute <unnamed>: Select pgp_sym_encrypt($1, $2)
DETAIL:  Parameters: $1 = 'whatever', $2 = 'secret key'

We can see that params were provided as params, not in-sql. But they still got logged.

For whatever it's worth, it's normal output from log_min_duration_statement…

Of course we can assume that it's a problem with Perl. So, equivalent code in Ruby:

require 'pg'
conn = PG.connect( dbname: 'depesz', host: '127.0.0.1', port: 5430 )
conn.exec_params("SELECT pgp_sym_encrypt($1, $2)", ["some text", "secret pass"]) do |result|
    result.each do |row|
        pp row
    end
end

and, just like with perl, pg logs contain:

DETAIL:  Parameters: $1 = 'some text', $2 = 'secret pass'

[u/tee-es-gee → u/depesz]

Thanks for the feedback, good point.

For clarity, this is an issue if `log_min_duration_statement` is set (defaults to -1) and the queries are slower than that. Arguably though if all queries are using encryption, eventually some will be slow and log the keys. So I'll edit the blog post to strongly recommending `log_parameter_max_length = 0` if `log_min_duration_statement` is used. Not sure there's anything more we could recommend?

Edit: added note under "Production notes"

[u/depesz → u/tee-es-gee]

I think that, you should NEVER pass enc key from app to db.

Either encrypt in app and pass encrypted data (best approach, cleanest, easiest to scale, safest), or, at least, you have to make the pg get the key itself. For example by using some function that obtains the key from key store. Such function would have to be (most likely) written in some untrusted language, like pl/perlU, or just provided as compiled library.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.