PostgreSQL Limit Who Can Execute Explain Plans

If you want the bloody details keep reading otherwise all I'm asking is if you can limit what users are able to run the EXPLAIN command on their SQL statements, something like GRANT EXPLAIN TO billy would be nice, but doesn't seem to exist.

My goal is to encrypt data in a column, index the encrypted column because it can be used on searches, and keep the encryption key hidden from all end users(can live with sys admins or DBA seeing it). What are some ways to do this in PostgreSQL that would allow this? And if there isn't a way to do all of this what things do you have in place to try to get there? My current approach listed below almost gets me there, but when and explain plan is ran on the query the encryption key is exposed. Which is why I wonder if you can restrict access from users being able to use explain.

So here is a walk through of a little setup to demonstrate what I'm talking about. Say I have some data encrypted in a column in a database table, this column will be searched on via the gui front-end screens or via pg_admin ad-hoc queries.

CREATE TABLE test(id serial,my_data TEXT);

--Fill table with enough data to need an index.
DO $$

DECLARE counter INTEGER := 0;
BEGIN
    WHILE(counter < 1000)
    LOOP
        EXECUTE 'INSERT INTO test(my_data)
                SELECT pgp_sym_encrypt(''avalue' || CAST(counter AS TEXT) || '''' || ', ''apasswordwithsomeentropy'',''compress-algo=1, cipher-algo=aes256'');';
        counter := counter + 1;
    END LOOP;
END$$;

ANALYZE test;

CREATE INDEX index1
  ON test
  USING btree
  (my_data);

This select will still have to do a full table scan, which I want to avoid.

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy') = 'avalue114';

"Seq Scan on test  (cost=0.00..665.00 rows=61 width=24开发者_运维知识库6)"
"  Filter: (pgp_sym_decrypt((my_data)::bytea, 'apasswordwithsomeentropy'::text) = 'avalue114'::text)"

So what if I make use of a function index to speed this query up. I use the get_password function to not have the index created with the password hard code in it. Remember I don't want users looking at index defs and seeing the password. Assume the user can't select from password or execute get_password(), only a single account with extra privileges can. And the get_password() function is only used from in the function index. So my understanding is the user doesn't need execute rights to the function?

CREATE TABLE password
(
  password_id serial NOT NULL,
  password_value text
);

INSERT INTO password(password_value)
SELECT 'apasswordwithsomeentropy';
from get_password();

CREATE FUNCTION get_password() RETURNS TEXT
    AS 'select password_value 
   from password 
  where password_id = 1'
    LANGUAGE SQL
    IMMUTABLE;

CREATE INDEX index2 ON test (pgp_sym_decrypt(cast(my_data as bytea),get_password()));

Now when I run the select the database uses index2 and I get the results back nice and fast as I wanted. The problem is index2 shows the password in plane text when I execute an explain plan on the select query.

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy') = 'avalue114';


"Bitmap Heap Scan on test  (cost=4.73..171.49 rows=61 width=246)"
"  Recheck Cond: (pgp_sym_decrypt((my_data)::bytea, 'apasswordwithsomeentropy'::text) = 'avalue114'::text)"
"  ->  Bitmap Index Scan on index2  (cost=0.00..4.72 rows=61 width=0)"
"        Index Cond: (pgp_sym_decrypt((my_data)::bytea, 'apasswordwithsomeentropy'::text) = 'avalue114'::text)"

The only thing I can think of is to create web services to the database that would keep a user from directly interacting with the database. But being the app is in house it would be nice if some users could still make use of pg_admin on occasion, but not see everything such as explain plans. These users would not know what an explain plan and won't ever use them.

I keep coming back to creating a web service that wraps all the queries they need to run in functions that are invoked via the web service, but that takes extra time to get released in a formal development environment and takes away the ad-hoc query approach and adds another layer to maintain. Any ideas? Thanks

---

On a side note you might want to change

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy') = 'avalue114';

into

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_encrypt(cast('avalue114' as bytea),'apasswordwithsomeentropy') = my_data;

This way postgres does not need to decrypt every my_data for your comparison. If pgp_sym_encrypt is deterministic and can be cached(not sure if postgres can handle this), your target data is encrypted once and only matching columns will be decrypted.

We have changed the where condition so plan may change, can you please try and send the new plan?