Re: Getting the type Oid in a CREATE TYPE output function

On 10/16/06, Weslee Bilodeau <weslee(dot)bilodeau(at)hypermediasystems(dot)com> wrote:
> Marko Kreen wrote:
> > The PGP functions happen to do it already - pgp_key_id().
>
> Actually, Tom helped me realize I made a mistake, which I'm following
> his suggestion. Not tying keys to OIDs which change when backup/restored.

Yeah, tying to oids is bad, you should link to names,
preferably schema-qualified. Anyway, that was just off-hand
suggestion.

> [ snip nice description ]

> I'm not sure if anyone else needs something like it, but it allows us to
> transparently encrypt data directly in the tables. Minimum application
> changes ('select enc_key' at connection) - the main requirement when
> working on legacy code that needs to match todays security polices quickly.

Some want row-level access control, then your scheme would not be enough.

Maybe it would be better to avoid combining the keys, instead have
hidden key in database and several user keys that grant access to that
key, thus you can revoke access from only some users.

But one thing I suggest strongly - use PGP encryption instead
of old encrypt()/decrypt(). PGP hides the data much better,
espacially in case of lot of small data with same key.

--
marko