Enter the terms you wish to search for.

Client Row Access Control In PostgreSQL

October 08, 2018

Usually, the database administrator controls who can access database data. However, it is possible for clients to completely control who can access data they add to the database, with the help of openssl.

 

First, let's create rsa keys for three users from the command line. We first create an rsa public/private key pair for each user in their home subdirectory and then make a copy of their rsa public key in the shared directory /u/postgres/keys:

 

 

# # must be run as the root user
# cd /u/postgres/keys
# for USER in user1 user2 user3
> do    mkdir ~"$USER"/.pgkey
>       chown -R "$USER" ~"$USER"/.pgkey
>       chmod 0700 ~"$USER"/.pgkey
>       openssl genpkey -algorithm RSA -out ~"$USER"/.pgkey/rsa.key
>       chmod 0600 ~"$USER"/.pgkey/*
>       openssl pkey -in ~"$USER"/.pgkey/rsa.key -pubout -out "$USER".pub
> done

 

 

A more sophisticated setup would include creating a certificate authority and signing certificates for each user using the created keys. This allows the certificate authority to prove that the public keys belong to the specified users.

 

With this in place, it is now possible to encrypt data on the client using a public key that can only be decrypted by someone with access to the matching private key. Here is an example for user user1:

 

 

# echo test4 | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user1.pub
> openssl pkeyutl -decrypt -inkey ~"$USER"/.pgkey/rsa.key
test4

 

 

This encrypts text with user1's public key, then decrypts it with their private key.

Now, let's create a table to hold the encrypted data and add some encrypted data:

 

 

CREATE TABLE survey1 (id SERIAL, username NAME, result BYTEA);
 
\set enc `echo secret_message | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user1.pub | xxd -p | tr -d '\n'`
INSERT INTO survey1 VALUES (DEFAULT, 'user1', decode(:'enc', 'hex'));
 
-- save data for the other two users
\set enc `echo secret_message | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user2.pub | xxd -p | tr -d '\n'`
INSERT INTO survey1 VALUES (lastval(), 'user2', decode(:'enc', 'hex'));
 
\set enc `echo secret_message | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user3.pub | xxd -p | tr -d '\n'`
INSERT INTO survey1 VALUES (lastval(), 'user3', decode(:'enc', 'hex'));

 

 

We could have placed all the user-encrypted data in the same row using a bytea array:

 

CREATE TABLE survey2 (id SERIAL, username NAME[], result BYTEA[]);
 
\set enc1 `echo secret_message | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user1.pub | xxd -p | tr -d '\n'`
\set enc2 `echo secret_message | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user2.pub | xxd -p | tr -d '\n'`
\set enc3 `echo secret_message | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user3.pub | xxd -p | tr -d '\n'`
 
INSERT INTO survey2 VALUES (
        DEFAULT,
        '{user1, user2, user3}',
        ARRAY[decode(:'enc1', 'hex'), decode(:'enc2', 'hex'), decode(:'enc3', 'hex')]::bytea[]);

 

 

We could have stored the encrypted value only once using a random password and encrypted the password using each user's public key and stored those:

 

 

CREATE TABLE survey3 (id SERIAL, result BYTEA, username NAME[], keys BYTEA[]);
 
\set key `openssl rand -hex 32`
\set enc `echo secret_message | openssl enc -aes-256-cbc -pass pass\::key | xxd -p | tr -d '\n'`
 
\set enc1 `echo :'key' | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user1.pub | xxd -p | tr -d '\n'`
\set enc2 `echo :'key' | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user2.pub | xxd -p | tr -d '\n'`
\set enc3 `echo :'key' | openssl pkeyutl -encrypt -pubin -inkey /u/postgres/keys/user3.pub | xxd -p | tr -d '\n'`
 
INSERT INTO survey3 VALUES (
        DEFAULT,
        :'enc',
        '{user1, user2, user3}',
        ARRAY[decode(:'enc1', 'hex'), decode(:'enc2', 'hex'), decode(:'enc3', 'hex')]::bytea[]);

 

 

To decrypt data using the first schema (one user per row, no arrays), user1 would do:

 

 

SELECT * FROM survey1 WHERE username = 'user1';
 id | username |    result
----+----------+-------------
  1 | user1    | \x80c9d0...
 
-- set psql variables to match output columns
\gset
 
-- 'cut' removes \x
\set decrypt `echo :'result' | cut -c3- | xxd -p -revert | openssl pkeyutl -decrypt -inkey ~user1/.pgkey/rsa.key`
 
SELECT :'decrypt';
    ?column?
----------------
 secret_message

 

 

The process would be similar for survey2 and survey3. Of course, this does not prevent database administrators from removing data from the database, and because public keys are used to encrypt, they could add data too. A message authentication code (mac) would prevent this.

 

 (Article originally published in Bruce's personal blog - September 5, 2018, @ https://momjian.us/main/blogs/pgblog/2018.html#September_5_2018)

Share this