WIP: plpgsql source code obfuscation

On Jan 28, 2008 12:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger).

ToDo: Dump

Without making any comment of whether or not we should actually do
this, a flag in pg_proc to indicate that the function is obfuscated
would be handy for apps like pgAdmin, rather than assuming a - in
prosrc has that meaning (which may be valid for some interpreters).

/D

On 28/01/2008, Dave Page <dpage@postgresql.org> wrote:

On Jan 28, 2008 12:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger).

ToDo: Dump

Without making any comment of whether or not we should actually do
this, a flag in pg_proc to indicate that the function is obfuscated
would be handy for apps like pgAdmin, rather than assuming a - in
prosrc has that meaning (which may be valid for some interpreters).

sure, but do you know, Tom dislikes new columns in pg_proc :). This
patch is usable sample of one possible solution and doesn't need
initdb. And there is dependency on pgcrypto :(. But it is simply and
it does all what is expected. Some customers wonted it. But I am not
sure if similar patch can be accepted - this is prototype. And when
I'll have some signals so patch can be commited I'll send final
version with obfuscate col in pg_proc. Any patch of pg_proc needs two
hours of work, and any change needs actualization - so lot of maybe
useless work.

Pavel

On Jan 28, 2008 2:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

sure, but do you know, Tom dislikes new columns in pg_proc :).

Tom doesn't seem to like the idea of obfuscation of function code much
either :-)

This
patch is usable sample of one possible solution and doesn't need
initdb. And there is dependency on pgcrypto :(. But it is simply and
it does all what is expected. Some customers wonted it. But I am not
sure if similar patch can be accepted - this is prototype. And when
I'll have some signals so patch can be commited I'll send final
version with obfuscate col in pg_proc. Any patch of pg_proc needs two
hours of work, and any change needs actualization - so lot of maybe
useless work.

Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect
you to necessarily do it immediately - I just wanted to throw my
requirements from a tools perspective into the inevitable discussion.

Cheers, Dave.

On 28/01/2008, Dave Page <dpage@postgresql.org> wrote:

On Jan 28, 2008 2:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

sure, but do you know, Tom dislikes new columns in pg_proc :).

Tom doesn't seem to like the idea of obfuscation of function code much
either :-)

This
patch is usable sample of one possible solution and doesn't need
initdb. And there is dependency on pgcrypto :(. But it is simply and
it does all what is expected. Some customers wonted it. But I am not
sure if similar patch can be accepted - this is prototype. And when
I'll have some signals so patch can be commited I'll send final
version with obfuscate col in pg_proc. Any patch of pg_proc needs two
hours of work, and any change needs actualization - so lot of maybe
useless work.

Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect
you to necessarily do it immediately - I just wanted to throw my
requirements from a tools perspective into the inevitable discussion.

with "obfuscate" col in pg_proc source can be little bit more readable
and robust - current patch is +/- fast hack - so your requirement is
accurate.

Regards
Pavel
.

Pavel Stehule wrote:

Hello

this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger)

ToDo: Dump

Maybe a better TODO would be to do this task in the way that has
previously been suggested:
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php

I'm certainly not happy about any proposal to put a password/key in a
GUC var - that strikes me as a major footgun.

cheers

andrew

On 28/01/2008, Andrew Dunstan <andrew@dunslane.net> wrote:

Pavel Stehule wrote:

Hello

this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger)

ToDo: Dump

Maybe a better TODO would be to do this task in the way that has
previously been suggested:
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php

I'm certainly not happy about any proposal to put a password/key in a
GUC var - that strikes me as a major footgun.

why? we cannot ensure bigger real security. Anybody with superuser
rights can add modules that show source code of plpgsql procedure, or
can run debugger and attach postgres process.

p.s. this topic was discussed in http://markmail.org/message/r6jy7m6oryi5owyb

Pavel

Someone along the way suggested doing this as a kind of "wrapper" PL language.
So you would have a PL language like "obfuscate:plperl" which would obfuscate
the source code on the way in. Then when you execute a function it would
deobfuscate the source code and then just pass it to the normal plperl.

In such a scheme I think you would put the key in an attribute of the
language. Either in pg_lang or some configuration location which the
obfuscate:plperl interpreter knows where to find.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

On 28/01/2008, Gregory Stark <stark@enterprisedb.com> wrote:

Someone along the way suggested doing this as a kind of "wrapper" PL language.
So you would have a PL language like "obfuscate:plperl" which would obfuscate
the source code on the way in. Then when you execute a function it would
deobfuscate the source code and then just pass it to the normal plperl.

you can call Deobfuscate proc from any language handler - no problem

In such a scheme I think you would put the key in an attribute of the
language. Either in pg_lang or some configuration location which the
obfuscate:plperl interpreter knows where to find.

what is advantage?

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

In such a scheme I think you would put the key in an attribute of the
language. Either in pg_lang or some configuration location which the
obfuscate:plperl interpreter knows where to find.

what is advantage?

It wouldn't require any core changes. It would be just another PL language to
load which can be installed like other ones. This could be a big advantage
because it doesn't look like there is a lot of support for putting th
obfuscation directly into the core code.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

On 28/01/2008, Gregory Stark <stark@enterprisedb.com> wrote:

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

In such a scheme I think you would put the key in an attribute of the
language. Either in pg_lang or some configuration location which the
obfuscate:plperl interpreter knows where to find.

what is advantage?

It wouldn't require any core changes. It would be just another PL language to
load which can be installed like other ones. This could be a big advantage
because it doesn't look like there is a lot of support for putting th
obfuscation directly into the core code.

can be. but I am afraid so any changes are necessary in core too

Pavel

Andrew Dunstan <andrew@dunslane.net> writes:

Maybe a better TODO would be to do this task in the way that has
previously been suggested:
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php
I'm certainly not happy about any proposal to put a password/key in a
GUC var - that strikes me as a major footgun.

We didn't really have a better solution to the key management problem,
though, did we? At least I don't see anything about it in that thread.

However, I definitely agree that a separate loadable PL is the way to go
for functionality of this sort. There is no way that a dependency on
pgcrypto is going to be accepted into core, not even in the (ahem)
obfuscated way that it's presented here.

regards, tom lane

On 28/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Maybe a better TODO would be to do this task in the way that has
previously been suggested:
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php
I'm certainly not happy about any proposal to put a password/key in a
GUC var - that strikes me as a major footgun.

We didn't really have a better solution to the key management problem,
though, did we? At least I don't see anything about it in that thread.

However, I definitely agree that a separate loadable PL is the way to go
for functionality of this sort. There is no way that a dependency on
pgcrypto is going to be accepted into core, not even in the (ahem)
obfuscated way that it's presented here.

Do you thing some binary module that load some encrypted sources from
files? It can be possible too. But if source code will be stored in
pg_proc, then we need third method. Some like "obfuscate" (prev. are
validate and call"), because we can't to store plain text to prosrc
col.

My patch is only solution for some users, and I know about problem
with dependency.

Reagards

Pavel Stehule

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

Do you thing some binary module that load some encrypted sources from
files? It can be possible too. But if source code will be stored in
pg_proc, then we need third method. Some like "obfuscate" (prev. are
validate and call"), because we can't to store plain text to prosrc
col.

Is there a reason you couldn't, for instance, provide a function which takes
source code and encrypts it. Then you would write dump the data it spits into
your function declaration like:

CREATE FUNCTION foo() returns integer AS $$
... base64 encoded data
$$ language "obfuscated:plperl";

"obfuscated:plperl"'s handler function would just decrypt it and pass it off
to plperl.

There is a validator function which gets called when you create a function but
I don't think it has any opportunity to substitute its result for the original
in prosrc. That might be interesting for other applications like compiled
languages, though I think they would still want to save the source in prosrc
and the bytecode in probin.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

On 28/01/2008, Gregory Stark <stark@enterprisedb.com> wrote:

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

Do you thing some binary module that load some encrypted sources from
files? It can be possible too. But if source code will be stored in
pg_proc, then we need third method. Some like "obfuscate" (prev. are
validate and call"), because we can't to store plain text to prosrc
col.

Is there a reason you couldn't, for instance, provide a function which takes
source code and encrypts it. Then you would write dump the data it spits into
your function declaration like:

CREATE FUNCTION foo() returns integer AS $$
... base64 encoded data
$$ language "obfuscated:plperl";

it's solve problem with dump well, but it's similar to my solution.
"obfuscated:plperl" can be virtual language - we can have one common
handler, because there is same work. I am not sure. This doesn't care
any better security, only add some other necessary external toolkit.
With obfuscate column or obfuscate language (it carry same
information) I can use prosrc and I have not problem with dump too. It
is true, so obfuscate languages move dependency to out of core - but
it is more complex.

"obfuscated:plperl"'s handler function would just decrypt it and pass it off
to plperl.

you need same handler for plpgsql, python, sql, ... so why don't do it
generally?

Pavel

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Maybe a better TODO would be to do this task in the way that has
previously been suggested:
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php
I'm certainly not happy about any proposal to put a password/key in a
GUC var - that strikes me as a major footgun.

We didn't really have a better solution to the key management problem,
though, did we? At least I don't see anything about it in that thread.

Yeah. Maybe we could have the GUC var contain the name of a key file
rather than the key itself. If we require that the name be relative to
the datadir that might be tolerably secure.

However, I definitely agree that a separate loadable PL is the way to go
for functionality of this sort. There is no way that a dependency on
pgcrypto is going to be accepted into core, not even in the (ahem)
obfuscated way that it's presented here.

If we do anything in core it could be to make provision for an
obfuscation/encryption hook via a loadable module.

Various interesting encoding issues could arise with dumping and
restoring transformed program text - I haven't thought that through yet.

But I agree a simple PL wrapper makes sense to start with, at any rate.

cheers

andrew

Gregory Stark <stark@enterprisedb.com> writes:

There is a validator function which gets called when you create a
function but I don't think it has any opportunity to substitute its
result for the original in prosrc.

It would have to do a heap_update on the prosrc row, but that doesn't
seem like a showstopper problem.

regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

However, I definitely agree that a separate loadable PL is the way to go
for functionality of this sort. There is no way that a dependency on
pgcrypto is going to be accepted into core, not even in the (ahem)
obfuscated way that it's presented here.

If we do anything in core it could be to make provision for an
obfuscation/encryption hook via a loadable module.

My recollection is that certain cryptography laws make hooks for crypto
just as problematic as actual crypto code. We'd have to tread very
carefully --- "general purpose" hooks are OK but anything narrowly
tailored to encryption purposes would be a hazard. This is one reason
that I'd prefer to see it as an external PL rather than embedded in core.

Various interesting encoding issues could arise with dumping and
restoring transformed program text - I haven't thought that through yet.

I think we have already solved that with md5 passwords, and could easily
reuse the same kind of approach. You just base64 encode the crypted
text (or whatever you need to do to avoid funny characters in it), and
make sure that there's some way to distinguish already-crypted from
not-already-crypted function bodies.

regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

My recollection is that certain cryptography laws make hooks for crypto
just as problematic as actual crypto code. We'd have to tread very
carefully --- "general purpose" hooks are OK but anything narrowly
tailored to encryption purposes would be a hazard.

Afaik the US was the only country with such a scheme with the ITAR export
regulations and that's long since gone, at least as it applied to crypto. The
current US export regulations don't have any of the stuff about hooks in them
and exempt free software from any crypto export licenses.

Doesn't stop some other country from coming up with the same idea of course
but we don't generally worry about what laws some hypothetical country might
introduce at some point in the future. That way lies madness.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

However, I definitely agree that a separate loadable PL is the way to go
for functionality of this sort. There is no way that a dependency on
pgcrypto is going to be accepted into core, not even in the (ahem)
obfuscated way that it's presented here.

If we do anything in core it could be to make provision for an
obfuscation/encryption hook via a loadable module.

My recollection is that certain cryptography laws make hooks for crypto
just as problematic as actual crypto code. We'd have to tread very
carefully --- "general purpose" hooks are OK but anything narrowly
tailored to encryption purposes would be a hazard. This is one reason
that I'd prefer to see it as an external PL rather than embedded in core.

It could be something other than encryption; any sort of transformation
might fit. For example, one might do something like:

gzip | some-sort-of-shuffle | base64-encode

as a sort or poor man's obfuscation

Various interesting encoding issues could arise with dumping and
restoring transformed program text - I haven't thought that through yet.

I think we have already solved that with md5 passwords, and could easily
reuse the same kind of approach. You just base64 encode the crypted
text (or whatever you need to do to avoid funny characters in it), and
make sure that there's some way to distinguish already-crypted from
not-already-crypted function bodies.

I don't see how a binary MD5 checksum has any encoding component. But
using this example, it seems to me that if we dump the encrypted/encoded
source and restore into another database with a different encoding, the
decoded/decrypted source will still be in the old database encoding,
i.e. not valid in the new database encoding. We've just gone around
closing doors like this.

You might be able to fix it by storing the database encoding name along
with the encrypted/encoded source, so it could be transformed at the
other end.

cheers

andrew