Thread: Postgresql + digital signature

I'm working in a project which is using postgres (great database!, I love it)

We're in a stage where I need to implement a mechanism to prevent the data modification.

I'm thinking on 'Digital Signatures' (maybe RSA) in each row. If there's a modification, the signature doesn't verify.


However before start I need your help to know:

Is there in postgres something functionality like this?
Does any know if there's something similar another database system?


thanks in advance!



Luis Alberto Pérez Paz

On 1/23/08, Luis Alberto Pérez Paz <midriasis@gmail.com> wrote:
> I'm working in a project which is using postgres (great database!, I love
> it)
>
> We're in a stage where I need to implement a mechanism to prevent the data
> modification.
>
> I'm thinking on 'Digital Signatures' (maybe RSA) in each row. If there's a
> modification, the signature doesn't verify.
>
>
> However before start I need your help to know:
>
> Is there in postgres something functionality like this?
> Does any know if there's something similar another database system?

There is hmac() in pgcrypto, basically digest() with key.
It should be enough if you are ok with symmeric keys.

For public keys there is also pgp_pub_encrypt/decrypt but not
sign/verify.  You emulate them with digest() + pub_encrypt,
but that would be ugly, you are better off doing proper
sign/verity in client.

Another path would be to look for PLs that have module for
sign+verify - I'd guess that both plpythonu and plperlu
should have those.

--
marko

> We're in a stage where I need to implement a mechanism to prevent the
> data modification.
>
> I'm thinking on 'Digital Signatures' (maybe RSA) in each row. If
> there's a modification, the signature doesn't verify.
Like all such solutions, the key (lame pun intended) is how to do you
manage the keys?  Obviously, when the digitally signed data is inserted,
the private key must be accessible.  If you then do an update and also
have access to the keys, then new digitally signed data would be there.

Is there no way for your application to ensure that once data is
inserted, it cannot be changed?

You can also grant database access with just SELECT,INSERT permissions
so that an UPDATE and DELETE are not allowed.

We store lots of digitally signed data as BLOBs in PG, but control this
at the application level since it's the one that has access to the
private key, and our application has no UPDATE/DELETE calls.

Good luck,
David

--
paz, amor y comprensión
       (1967-1994)

On 1/23/08, Luis Alberto Pérez Paz <midriasis@gmail.com> wrote:
> Very interesting point of view.
> Yes, you're right about the manage key problem.
>
> The grant database access looks like a real solution.

Eh, for some reason I imagined you have have some good reason
why simple solutions are not enough...


Btw, if you try to simply rrestrict access to your data, one good
way for that is to make all data access and modification go via
SECURITY DEFINER functions, so that user have no access to
underlying data tables.

This gives both more flexible access handling than simple GRANTs
can give you and also give ability to do smooth schema upgrades
without applications noticing.

--
marko

--
paz, amor y comprensión
       (1967-1994)