Say I have table:
Fee
date : DATE
amount : INT
I can't store data in amount column in virgin way, because this is private information, so I need to encrypt it. Also I need to perform arithmetic operations on that encrypted column, like:
SELECT SUM(amount) FROM Fee;
What is the best way to do that? Thank you.
Why not use Postgres's built-in column-level security?
Code:
create table employees (
id int primary key,
name text not null,
salary decimal(10,2) not null
);
insert into employees values (1, 'Frank', 60000.00);
create or replace view employees_view as
select
id,
name,
case when has_column_privilege('employees', 'salary', 'select') then salary else null end as salary
from employees;
create role managers;
create role clerks;
grant select on employees_view to managers;
grant select on employees_view to clerks;
grant select (salary) on employees to managers;
set role managers;
select * from employees_view;
Result: 1, 'Frank', 60000.00
set role clerks;
select * from employees_view;
Result: 1, 'Frank', null
select sum( decrypt_int(amount, 'thekeydata') ) from ..., i.e. you must know the key to perform operations like a sum.