On Dec 20, 2007 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> > is a bad idea.
>
> So if you want something other than endless arguments to happen,
> come up with a nice key-management design for encrypted function
> bodies.

Maybe a key management solution isn't required. If, instead of
strictly wrapping a language with an encryption layer, we provide
hooks (actors) that have the ability to operate on the function body
when it arrives and leaves pg_proc, we may sidestep the key problem
(leaving it to the user) and open up the doors to new functionality at
the same time.

The actor is basically a callback taking the function source code (as
text) and returning text for storage in pg_proc. Perhaps some other
house keeping variables such as function name, etc. are passed to the
actor as parameters as well. The actor operates on the function body
going into pg_proc (input actors) and going out (output actors). In
either case, the function 'body' is modified if necessary, and may
raise an error.

The validator can be considered an actor that doesn't modify the body.
Ideally, the actors can be written in any pl language. Naturally,
dealing with actors is for the superuser. So, I'm suggesting to
extend the validator concept, opening it up to the user, giving it
more power, and the ability to operate in both directions. The actor
will feel a lot like a trigger function.

Now, everything is left to the user...by adding an 'encryption' actor
to the language (trivial with pg_crypto), the user can broadly encrypt
in a manner of their choosing. A clever user might write an actor to
encrypt a subset of functions in a language, or register the same
language twice with different actors. Since the actor can call out to
other functions, we don't limit to a particular key management
strategy.

Another nice thing is we may solve a problem that's been bothering me
for years, namely that 'CREATE FUNCTION' takes a string literal and
not a string returning expression. This is pretty limiting...there
are a broad range of reasons why I might want to modify the code
before it hits pg_proc. For example, with an actor I can now feed the
data into the C preprocessor without giving up the ability of pasting
the function body directly into psql.

This isn't a fully developed idea, and I'm glossing over several areas
(for example, syntax to modify actors), and I'm not sure if it's a
good idea in principle...I might be missing an obvious reason why this
won't work. OTOH, it seems like a really neat way to introduce
encryption.

comments? is it worth going down this road?

merlin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> On 21/12/2007, Merlin Moncure <mmoncure@gmail.com> wrote:
>> ... The real issue as I see it is where to
>> keep the key. How did you handle that?

> Simply. I use for password some random plpgsql message text and
> compile it. I though about GUC, and about storing password in
> postgresql.conf. It's equal to protection level. We cannot protect
> code on 100%. If you have admin or superuser account and if you know
> some internal, you can simply get code.

Yeah. There is no defense against someone who is prepared to go in
there with a debugger and pull the post-decryption code out of memory.
So what we need to think about is what sorts of threats we *can* or
should defend against. A couple of goals that seem like they might
be reasonable are:

* Even a superuser can't get the code at the SQL level, ie, it's
secure if you rule out debugger-level attacks. (For example, this
might prevent someone who had remotely breached the superuser account
from getting the code.)

* Code not available if you just look at what's on-disk, ie, you can't
get it by stealing a backup tape.

Any other threats we could consider defending against?

BTW, this thread definitely doesn't belong on -performance anymore.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, Dec 21, 2007 at 12:09:28AM -0500, Merlin Moncure wrote:
> Maybe a key management solution isn't required. If, instead of
> strictly wrapping a language with an encryption layer, we provide
> hooks (actors) that have the ability to operate on the function body
> when it arrives and leaves pg_proc, we may sidestep the key problem
> (leaving it to the user) and open up the doors to new functionality at
> the same time.

I like this idea much better, because the same basic mechanism can be used
for more than one thing, and it doesn't build in a system that is
fundamentally weak. Of course, you _can_ build a weak system this way, but
there's an important difference between building a fundamentally weak system
and making weak systems possible.

A


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Fri, Dec 21, 2007 at 12:09:28AM -0500, Merlin Moncure wrote:
>> Maybe a key management solution isn't required.

> I like this idea much better, because the same basic mechanism can be used
> for more than one thing, and it doesn't build in a system that is
> fundamentally weak. Of course, you _can_ build a weak system this way, but
> there's an important difference between building a fundamentally weak system
> and making weak systems possible.

I find myself unconvinced by this argument. The main problem is: how
do we know that it's possible to build a strong system atop this
mechanism? Just leaving it to non-security-savvy users seems to me
to be a great way to guarantee a lot of weak systems in the field.
ISTM our minimum responsibility would be to design and document how
to build a strong protection system using the feature ... and at that
point why not build it in?

I've certainly got no objection to making a mechanism that can be used
for more than one purpose; but not offering a complete security solution
is abdicating our responsibility.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Dec 21, 2007 11:48 AM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> On Fri, Dec 21, 2007 at 12:40:05AM -0500, Tom Lane wrote:
>
> > whether there is a useful policy for it to implement. Andrew Sullivan
> > argued upthread that we cannot get anywhere with both keys and encrypted
> > function bodies stored in the same database (I hope that's an adequate
> > summary of his point).
>
> It is. I'm not a security expert, but I've been spending some time
> listening to some of them lately. The fundamental problem with a system
> that stores the keys online in the same repository is not just its potential
> for compromise, but its brittle failure mode: once the key is recovered,
> you're hosed. And there's no outside check of key validity, which means
> attackers have a nicely-contained target to hit.
>
> > I'm not convinced that he's right, but that has to be the first issue we
> > think about. The whole thing is a dead end if there's no way to do
> > meaningful encryption --- punting an insoluble problem to the user doesn't
> > make it better.
>
> Well, one thing you could do with the proposal is build a PKCS#11 actor,
> that could talk to an HSM. Not everyone needs HSMs, of course, but they do
> make online key storage much less risky (because correctly designed ones
> make key recovery practically impossible). So the mechanism can be made
> effectively secure even for very strong cryptographic uses.

ISTM the main issue is how exactly the authenticated user interacts
with the actor to give it the information it needs to get the real
key. This is significant because we don't want to be boxed into an
actor implementation that doesn't allow that interaction. If simply
calling out via a function is enough (which, to be perfectly honest, I
don't know), then we can implement the actor system and let actor
implementations spring to life in contrib, pgfoundry, etc. as the
community presents them.

merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Merlin Moncure" <mmoncure@gmail.com> writes:
> ISTM the main issue is how exactly the authenticated user interacts
> with the actor to give it the information it needs to get the real
> key. This is significant because we don't want to be boxed into an
> actor implementation that doesn't allow that interaction.

We don't? What purpose would such a setup serve? I would think
that for the applications we have in mind, the *last* thing you
want is for the end user to hold the key. The whole point of this
is to keep him from seeing the function source code, remember?

Andrew's suggestion of an outside-the-database key server is
apropos, but I think it would end up being a situation where
the key server is under the control of whoever wrote the function
and wants to guard it against the end user. The key server would
want some kind of authentication token but I think that could
perfectly well be an ID for the database server, not the individual
end user. There's no need for anything as awkward as an interactive
sign-on, AFAICS.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, Dec 21, 2007 at 01:57:44PM -0500, Tom Lane wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > ISTM the main issue is how exactly the authenticated user interacts
> > with the actor to give it the information it needs to get the real
> > key. This is significant because we don't want to be boxed into an
> > actor implementation that doesn't allow that interaction.
>
> We don't? What purpose would such a setup serve? I would think
> that for the applications we have in mind, the *last* thing you
> want is for the end user to hold the key. The whole point of this
> is to keep him from seeing the function source code, remember?

Hmm; this may be exactly part of the problem, though. It seems there are
two possible cases in play:

1. Protect the content in the database (in this case, function bodies)
from _all_ users on a given server. This is a case where you want to
protect (say) your function body from your users, because you have a
closed-source application.

2. Protect the content of a field from _some_ users on a given system,
based on the permissions they hold. This is roughly analagous to others not
being able to look in the table I created, because I haven't GRANTed them
permission.

(2) is really a case for column-level access controls, I guess. But if
we're trying to solve this problem too, then user passwords or something
make sense.

A


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> Hmm; this may be exactly part of the problem, though. It seems there are
> two possible cases in play:

> 1. Protect the content in the database (in this case, function bodies)
> from _all_ users on a given server. This is a case where you want to
> protect (say) your function body from your users, because you have a
> closed-source application.

> 2. Protect the content of a field from _some_ users on a given system,
> based on the permissions they hold. This is roughly analagous to others not
> being able to look in the table I created, because I haven't GRANTed them
> permission.

I would argue that (2) is reasonably well served today by setting up
separate databases for separate users. The people who are complaining
seem to want to send out a set of functions into a hostile environment,
which is surely case (1).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, Dec 21, 2007 at 04:19:51PM -0500, Tom Lane wrote:
> > 2. Protect the content of a field from _some_ users on a given system,
>
> I would argue that (2) is reasonably well served today by setting up
> separate databases for separate users.

I thought actually this was one of the use-cases we were hearing. Different
people using the same database (because the same data), with rules about the
different staff being able to see this or that function body. I can easily
imagine such a case, for instance, in a large organization with different
departments and different responsibilities. It seems a shame that the only
answer we have there is, "Give them different databases."

I actually think organizations that think keeping function bodies secret
like this to be a good idea are organizations that will eventually make
really stupid mistakes. But that doesn't mean they're not under the legal
requirement to do this. For instance, my current employer has
(externally-mandated) organizational conflict of interest rules that require
all disclosure to be done exclusively as "need to know". Under the right
(!) legal guidance, such a requirement could easily lead to rules about
function-body disclosure. From my point of view, such a use case is way
more compelling than function-body encryption (although I understand that
one too).

A


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq