Hello there,
How do you secure a VIEW so that only certain users may use it?
Let's say you have a table that has a BYTEA column that holds (pgcrypto)
encrypted data; and you have a VIEW on the aforementioned table that returns
the decrypted contents of the BYTEA column.
How do you secure this VIEW so that only certain users may use it (to get
the decrypted data)?
For example:
1. Here is a simple table that will hold a pgcrypto encrypted BYTEA value:
CREATE TABLE crypto ( id SERIAL PRIMARY KEY, title VARCHAR(50), crypted_content BYTEA
);
2. Let's enter (and encrypt via pgcrypto) some rows of test data:
insert into crypto VALUES (1, 'test1', encrypt('11112222', 'password',
'aes') );
insert into crypto VALUES (2, 'test2', encrypt('22223333', 'password',
'aes') );
insert into crypto VALUES (3, 'test3', encrypt('33334444', 'password',
'aes') );
3. Let's SELECT directly from the table (note the encrypted values):
# select * from crypto;
id | title | crypted_content
----+-------+------------------------------------------------------------ 1 | test1 |
\026\206I93\327\315\376t\243\006~J\177{\301 2 | test2 | \257\212\260\321\217\274c\210d \361\225\212\327\317\206 3 |
test3| 6\345:\224dp\002\206<\007k\344\302\347V\214
(3 rows)
4. Let's create a VIEW that automatically decrypts the (pgcrypto encrypted)
BYTEA column, returning its decrypted contents as type TEXT:
CREATE VIEW crypto_view AS
SELECT id, title,
--Decrypt the BYTEA column and convert result to TEXT type: encode(decrypt(crypted_content,
decode('password','escape'::text),
'aes'::text), 'escape'::text) AS crypted_content FROM crypto;
5. Now let's select from this VIEW (note the decrypted values):
# select * from crypto_view;
id | title | crypted_content
----+-------+----------------- 1 | test1 | 11112222 2 | test2 | 22223333 3 | test3 | 33334444
(3 rows)
Pretty straight forward, right?
So how can I secure this nifty VIEW? If it can't be secured, then everyone
has access to the data by simply calling the VIEW.
By extention, if you have RULES on a VIEW, when you secure the VIEW, do the
RULES inherit their underlying VIEW's security rights as well?
Thank you,
Michael Moran