Re: crypting prosrc in pg_proc

hello everybody,

one of our customers wants to store the code of interpreted
procedures (PL/pgSQL, PL/Perl) and so in an encrypted way.
so the idea we had to add one more column to pg_proc telling us
whether prosrc is encrypted or not. people could chose then whether
to crypt codes there or not (speed of decryption can be an issue).

should not be hard to implement ...

what do people think about this feature?

many thanks,

hans

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

On Thu, Aug 09, 2007 at 03:39:06PM +0200, Hans-Juergen Schoenig wrote:
> so the idea we had to add one more column to pg_proc telling us whether
> prosrc is encrypted or not. people could chose then whether to crypt codes
> there or not (speed of decryption can be an issue).
> should not be hard to implement ...
> what do people think about this feature?

how would you decrypt the source before using the function?

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Hans-Juergen Schoenig wrote:
> hello everybody,
>
> one of our customers wants to store the code of interpreted procedures
> (PL/pgSQL, PL/Perl) and so in an encrypted way.
> so the idea we had to add one more column to pg_proc telling us
> whether prosrc is encrypted or not. people could chose then whether to
> crypt codes there or not (speed of decryption can be an issue).
>
> should not be hard to implement ...
>
> what do people think about this feature?
>
>

Perhaps you could give us a justification for it. Are you intending to
have stored procs contain security sensitive information? Or is this an
attempt to hide closed source code from prying eyes? Where would the
encryption keys be stored? And how would it work with pg_dump?

This doesn't sound very well thought out, frankly.

cheers

andrew

the idea is basically to hide codes - many companies want that and
ask for it again and again.

i would suggest keys to reside in $PGDATA. we do this for SSL and so
already.

initdb could create such keys so that they are unique to every
database instance.
decrypting could be avoided as much as possible basically we should
just decrypt on first all and when it changes.

for pg_dump i would suggest two options:
a.) pass the keys to dump in a decrypted way
b.) dump in encrypted way.

i would think that this is a quite valuable features. would be nice
to have it.
maybe we can agree on a nice mechanism here which will be implemented
then.

hans

On Aug 9, 2007, at 3:57 PM, Andrew Dunstan wrote:

>
>
> Hans-Juergen Schoenig wrote:
>> hello everybody,
>>
>> one of our customers wants to store the code of interpreted
>> procedures (PL/pgSQL, PL/Perl) and so in an encrypted way.
>> so the idea we had to add one more column to pg_proc telling us
>> whether prosrc is encrypted or not. people could chose then
>> whether to crypt codes there or not (speed of decryption can be an
>> issue).
>>
>> should not be hard to implement ...
>>
>> what do people think about this feature?
>>
>>
>
> Perhaps you could give us a justification for it. Are you intending
> to have stored procs contain security sensitive information? Or is
> this an attempt to hide closed source code from prying eyes? Where
> would the encryption keys be stored? And how would it work with
> pg_dump?
>
> This doesn't sound very well thought out, frankly.
>
> cheers
>
> andrew
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

Am Donnerstag, 9. August 2007 16:09 schrieb Hans-Juergen Schoenig:
> the idea is basically to hide codes - many companies want that and  
> ask for it again and again.

If you want to design a security feature, you need to offer a threat and risk
analysis, not just the whining of customers.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Aug 9, 2007, at 4:34 PM, Peter Eisentraut wrote:

> Am Donnerstag, 9. August 2007 16:09 schrieb Hans-Juergen Schoenig:
>> the idea is basically to hide codes - many companies want that and
>> ask for it again and again.
>
> If you want to design a security feature, you need to offer a
> threat and risk
> analysis, not just the whining of customers.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/

well, the complete analysis is easy - the solution is not.
currently we have basically no option to reduce access to the system
tables. this would be hard anyway as we need those tables for
basically all kinds of operations.
the problem here is that vendors of appliances don't want people to
spider their codes. this is a fact - it is not the idea of open
source to do so but bloody reality. in addition to that people are
not willing to code everything in C just to hide.
so, there has to be a concept to achieve this for stored procedures
somehow.
i am afraid the source level encryption is the easiest thing and most
understandable thing to do.

so, better ideas are welcome.

hans

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

Wouldn't it be better just to hide prosrc from the users you want to
hide it from? (Note: my SOC student is working on column level privs,
although unfortunately his work doesn't currently work with SELECT
privs). In fact, in many cases, depending on the app, you can simply
remove SELECT priv from public on pg_proc.

There are also some fairly impressive code obfuscators about, that your
clients might find useful. Of course, debugging the obfuscated code is a
nightmare, but that's their affair if they want to use it ;-)

cheers

andrew

Hans-Juergen Schoenig wrote:
> the idea is basically to hide codes - many companies want that and ask
> for it again and again.
>
> i would suggest keys to reside in $PGDATA. we do this for SSL and so
> already.
>
> initdb could create such keys so that they are unique to every
> database instance.
> decrypting could be avoided as much as possible basically we should
> just decrypt on first all and when it changes.
> for pg_dump i would suggest two options:
> a.) pass the keys to dump in a decrypted way
> b.) dump in encrypted way.
>
> i would think that this is a quite valuable features. would be nice to
> have it.
> maybe we can agree on a nice mechanism here which will be implemented
> then.
>
> hans
>
>
>
> On Aug 9, 2007, at 3:57 PM, Andrew Dunstan wrote:
>
>>
>>
>> Hans-Juergen Schoenig wrote:
>>> hello everybody,
>>>
>>> one of our customers wants to store the code of interpreted
>>> procedures (PL/pgSQL, PL/Perl) and so in an encrypted way.
>>> so the idea we had to add one more column to pg_proc telling us
>>> whether prosrc is encrypted or not. people could chose then whether
>>> to crypt codes there or not (speed of decryption can be an issue).
>>>
>>> should not be hard to implement ...
>>>
>>> what do people think about this feature?
>>>
>>>
>>
>> Perhaps you could give us a justification for it. Are you intending
>> to have stored procs contain security sensitive information? Or is
>> this an attempt to hide closed source code from prying eyes? Where
>> would the encryption keys be stored? And how would it work with pg_dump?
>>
>> This doesn't sound very well thought out, frankly.
>>
>> cheers
>>
>> andrew
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>> http://www.postgresql.org/about/donate
>
>
>
> --
> Cybertec Geschwinde & Schönig GmbH
> Gröhrmühlgasse 26, 2700 Wiener Neustadt
> Tel: +43/1/205 10 35 / 340
> www.postgresql.at, www.cybertec.at <http://www.cybertec.at>
>
>

> the idea is basically to hide codes - many companies want that and ask
> for it again and again.
Hide code from who (or is that whom?)? The PL compiler(s) will need to
decrypt the code. If a compiler can decrypt it, then anyone can decrypt
it (because the compilers are open-source).

And the problem is that any user that can run a function must be able to
compile that function, and therefore, any user that can run a function
must have the decryption key for that function. So, I'm not sure you've
secured the source code from any user that can run the function.

Of course, if your goal is to hide the code from someone snooping
through the pg_proc relation (on disk), then encryption will certainly
help (provided the key is properly protected).

Note: I think source-code encryption is a useful idea, it just seems
tricky to implement in an open environment.

-- Korry

Hans-Juergen Schoenig napsal(a):
> the idea is basically to hide codes - many companies want that and ask
> for it again and again.
>
> i would suggest keys to reside in $PGDATA. we do this for SSL and so
> already.
>
> initdb could create such keys so that they are unique to every database
> instance.
> decrypting could be avoided as much as possible basically we should just
> decrypt on first all and when it changes.

But, Companies want to hide code also because they distribute their software. If
you store key somewhere on server, user will be able to decrypt the original
code. If I remember correctly Oracle wrap generates something like bytecode and
each Oracle installation is able to understand them. But It is not possible
decode it back to original form.

My suggestion is to extend PL API and each PL language should offer wrap or
encrypt function which generate encrypted code and this code will be store in
the pg_proc. PL language will be responsible to detect if it raw or crypted
code. PG_Dump will dump crypted procedure and author is responsible keep his
uncrypted version in source repository.

Zdenek

On Aug 9, 2007, at 4:47 PM, korry.douglas wrote:

>
>> the idea is basically to hide codes - many companies want that and
>> ask for it again and again.
> Hide code from who (or is that whom?)?

the code should be hidden from the guy who is actually executing the
function.
so:
some user is doing: select func();

the backend loads the keys from PGDATA, decrypts the codes executes
them. as a normal user cannot look "into" the backend the code is safe.
the keys are only visible to the sysadmis but not at SQL level.

> The PL compiler(s) will need to decrypt the code.

no, the backend will pass the decrypted codes to the call handler.
there is no need for Perl, Python or so to be aware of this issue.

> If a compiler can decrypt it, then anyone can decrypt it (because
> the compilers are open-source).
> And the problem is that any user that can run a function must be
> able to compile that function, and therefore, any user that can run
> a function must have the decryption key for that function. So, I'm
> not sure you've secured the source code from any user that can run
> the function.

why that? the backend is doing the job. the user does not pass the
keys. it is a database internal thing. the only idea is to make sure
that pg_proc does not contain user readable code.

>
> Of course, if your goal is to hide the code from someone snooping
> through the pg_proc relation (on disk), then encryption will
> certainly help (provided the key is properly protected).
>

no, somebody who has access to the filesystem is not relevant.
just think of shipping some database inside some router or inside
some other stuff. the vendor just wants to make sure that other
people don't fully understand the "magic" going on.

hans

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

On Thu, 9 Aug 2007, Andrew Dunstan wrote:

> There are also some fairly impressive code obfuscators about, that your
> clients might find useful.

All they really need is to find a sufficiently clever PL/Perl programmer.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Thu, Aug 09, 2007 at 04:34:48PM +0200, Peter Eisentraut wrote:
> Am Donnerstag, 9. August 2007 16:09 schrieb Hans-Juergen Schoenig:
> > the idea is basically to hide codes - many companies want that and ?
> > ask for it again and again.
>
> If you want to design a security feature, you need to offer a threat and risk
> analysis, not just the whining of customers.

This isn't as much a security feature as a means for people to keep
their code private. To some degree you could just get away with storing
nothing but a parsed representation of the code, though of course
someone could always decompile that.

There's a non-trivial amount of work involved in handling key
management, etc, so I think we don't want to try and build that in. What
would be interesting is providing hooks for en/decrypting function code
and having the backend call those hooks as appropriate. That should
allow someone to develop the ability to encrypt the code in the database
outside of the backend.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Aug 9, 2007, at 5:03 PM, Greg Smith wrote:

> On Thu, 9 Aug 2007, Andrew Dunstan wrote:
>
>> There are also some fairly impressive code obfuscators about, that
>> your clients might find useful.
>
> All they really need is to find a sufficiently clever PL/Perl
> programmer.

we should make this a PL/Brainfuck implementation then ;)

hans

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

>
> The basic problem is this: if you have to decrypt the code in order
> to give it to a compiler (PL/pgSQL, Java, Perl, ...) then there is
> a point in time where the source code is in plaintext form - it
> would be trivial to add an fprintf( stderr, "%s", plainTextForm )
> to the PL handler to steal the code.
>
> -- Korry

if somebody is allowed to recompile on the box GRANT and REVOKE are
all useful.
you did not get the point, i think.
we are shipping appliances - users can only use SQL; no compilers and
no SSH logins allowed ...

hans

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

> My suggestion is to extend PL API and each PL language should offer
> wrap or encrypt function which generate encrypted code and this code
> will be store in the pg_proc. PL language will be responsible to
> detect if it raw or crypted code. PG_Dump will dump crypted procedure
> and author is responsible keep his uncrypted version in source
> repository.
That strategy assumes that there is no way to reproduce the source form
from the encrypted form. That means that the PL compiler must be able
to recognize two different languages (the unencrypted form and the
encrypted form). That may work for PL/pgSQL but it won't work for any
language where a third-party compiler is involved (unless the decryption
function produces an obfuscated source form that represents legitimate
code for the compiler in question).

The basic problem is this: if you have to decrypt the code in order to
give it to a compiler (PL/pgSQL, Java, Perl, ...) then there is a point
in time where the source code is in plaintext form - it would be trivial
to add an fprintf( stderr, "%s", plainTextForm ) to the PL handler to
steal the code.

-- Korry

On Thu, Aug 09, 2007 at 04:50:53PM +0200, Zdenek Kotala wrote:
> Hans-Juergen Schoenig napsal(a):
> >the idea is basically to hide codes - many companies want that and ask
> >for it again and again.
> >
> >i would suggest keys to reside in $PGDATA. we do this for SSL and so
> >already.
> >
> >initdb could create such keys so that they are unique to every database
> >instance.
> >decrypting could be avoided as much as possible basically we should just
> >decrypt on first all and when it changes.
>
> But, Companies want to hide code also because they distribute their
> software. If you store key somewhere on server, user will be able to
> decrypt the original code.

If folks keep that argument up, we'll never, ever get this useful
feature.

I don't care *what* security you implement (maybe short of quantum
physics), given enough time and motivation, someone can break it. That's
not the point here... the point is just to make it more difficult for
someone to obtain the source code for procedures. Right now, anyone who
can connect to the database can see *everything*.

This is also related to the desire to be able to restrict access to the
catalog tables. Doing so could potentially solve this problem; it also
solves other issues (such as being able to see all the databases that
exist on a server, something that hosting environments care about).
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Thu, Aug 09, 2007 at 04:42:19PM +0200, Hans-Juergen Schoenig wrote:

> the problem here is that vendors of appliances don't want people to
> spider their codes. this is a fact - it is not the idea of open
> source to do so but bloody reality. in addition to that people are
> not willing to code everything in C just to hide.

Well, then, they're out of luck.

> so, there has to be a concept to achieve this for stored procedures
> somehow.

Just because you want something does not mean that there must be a
way to achieve it.

> i am afraid the source level encryption is the easiest thing and most
> understandable thing to do.

It's easy and understandable, but it won't do anything for you,
because the code has to be decrypted automatically in order to run.
If it can be decrypted by the system, then it can obviously be
decrypted by someone who has superuser access to the system too. I
have seen systems that attempt this sort of "protection" anyway.
(Indeed, one might argue that all of the hopeless and failed copy
protection systems CDs and DVDs have come with are exactly this sort
of trick.) The fundamental fact is that, if you want users to be able
to use code that is interpreted, then you're never going to be able
to guarantee that the users can't also see that code.

Other equally useless, in my experience, techniques include code
obfuscation. You could probably bind things up in enough levels of
nested calls to various functions that mapping it out would be tricky
and tedious. Of course, this also means that maintaining the code is
a fairly significant pain.

I'll also note that I am not actually convinced these contortions do
anything to protect the system from people who want to figure out how
it works.

None of that, of course, does anything to relieve the pressure on you
to deliver the requested feature. But perhaps you can reason with
them. And they can always write it in C and deliver only compiled
object code; although whether anyone would be foolish enough to run
such object code without inspecting its safety is an excercise in
speculation I don't care to pursue.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The plural of anecdote is not data.
--Roger Brinner

On Thu, Aug 09, 2007 at 05:16:43PM +0200, Hans-Juergen Schoenig wrote:
> we are shipping appliances - users can only use SQL; no compilers and
> no SSH logins allowed ...

If you have shipped a physical thing to someone else, then they can
do what they want with it irrespective of what "rules" you think you
have imposed. If you believe otherwise, then you are deluding
yourself about the security of your approach. It was this sort of
magical thinking that caused SIM cards, WEP keys, DVD copy
protection, and other such "guaranteed safe" technologies to be
revealed as encumbered with needless weight that may foil casual
users, but that are trivially broken by anyone actually interested in
doing the breaking.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland

Andrew Sullivan wrote:
> And they can always write it in C and deliver only compiled
> object code; although whether anyone would be foolish enough to run
> such object code without inspecting its safety is an excercise in
> speculation I don't care to pursue.
>

The intended use for this is an appliance, not a general purpose server.
They wouldn't have any option :-)

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Andrew Sullivan wrote:
> On Thu, Aug 09, 2007 at 04:42:19PM +0200, Hans-Juergen Schoenig wrote:
>
>> the problem here is that vendors of appliances don't want people to
>> spider their codes. this is a fact - it is not the idea of open
>> source to do so but bloody reality. in addition to that people are
>> not willing to code everything in C just to hide.
>
> Well, then, they're out of luck.

This whole thread can be summed up as: plpgsql is an interpretive
language, not a compiled on.

You can and do see the source, just like perl, ruby, python, bash etc...

If you don't like it, don't use it and use C instead.

:)

Keep in mind that the obfuscation techniques that can be used by python
and perl are all reversible.

Sincerely,

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGuzMUATb/zqfZUUQRAn0uAJ9evWtJkzS5hMiJDNSi+1jxMobdoACghm3D
r7Tmmz801bKntXAipJBrvy4=
=yRz/
-----END PGP SIGNATURE-----

Decibel! wrote:
> This is also related to the desire to be able to restrict access to the
> catalog tables. Doing so could potentially solve this problem; it
> solves other issues (such as being able to see all the databases that
> exist on a server, something that hosting environments care about).
>

You can hide the catalogs, albeit at the cost of some functionality. I
did some experimentation a couple of years back with removing public
access from the catalogs, removing information_schema and the public
schema, etc, and it worked quite well. I set up a user who had access to
a single schema, which only contained functions, and the user wasn't
able (so far as I could determine) to see anything other than those
functions - no tables, no catalogs, no databases, no users. The user was
still able to function exactly as intended. The intended scenario was
for a web app user, where the web server was subverted, the aim being to
restrict the amount of information the intruder could steal.

That doesn't help with information leaking in shared hosting setups, I
agree.

cheers

andrew

On 8/9/07, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Decibel! wrote:
> > This is also related to the desire to be able to restrict access to the
> > catalog tables. Doing so could potentially solve this problem; it
> > solves other issues (such as being able to see all the databases that
> > exist on a server, something that hosting environments care about).
>
>
> You can hide the catalogs, albeit at the cost of some functionality. I
> did some experimentation a couple of years back with removing public
> access from the catalogs, removing information_schema and the public
> schema, etc, and it worked quite well. I set up a user who had access to
> a single schema, which only contained functions, and the user wasn't
> able (so far as I could determine) to see anything other than those
> functions - no tables, no catalogs, no databases, no users. The user was
> still able to function exactly as intended. The intended scenario was
> for a web app user, where the web server was subverted, the aim being to
> restrict the amount of information the intruder could steal.

This works very well to stop casual browsing of functions from psql, etc.

That said, I am in the camp that securing system catalogs (including
pg_proc) is a good and necessary feature. This debate came up a while
back with all the usual arguments pro- and con-. IIRC the general
conclusion was that if you want to truly encrypt the sources for your
functions, the basic idea is to create a new stored procedure language
that wraps pl/pgsql and handles encryption there.

This would be relatively easy to support as an external module, I think.

merlin

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> ... IIRC the general
> conclusion was that if you want to truly encrypt the sources for your
> functions, the basic idea is to create a new stored procedure language
> that wraps pl/pgsql and handles encryption there.

> This would be relatively easy to support as an external module, I think.

Yeah, I was about to make that same suggestion: it's not clear to me
that you need any support whatsoever from the core project for this.
Make a loadable PL "plpgsqlsec" or something like that that's just
a thin wrapper around the plpgsql call handler, and all it does is
decrypt the source text.

It's not clear exactly where the *en*cryption should happen, but
one possibility is to have the new PL have a validator function that
does an immediate UPDATE on the pg_proc row if the text isn't already
encrypted.

The encrypted representation would need to work like MD5 passwords:
it's ASCII so that dumps will work, and it's possible to tell whether a
given string is encrypted or not.

Given the very limited use-case for what's being suggested here,
I don't feel a need to put it in core.

regards, tom lane

> Make a loadable PL "plpgsqlsec" or something like that that's just
> a thin wrapper around the plpgsql call handler, and all it does is
> decrypt the source text.

perfect idea, simple perfect.
i did not consider that yet.
i was hoping for some enlightenment like that.

would be a nice module for contrib or pgfoundry ...

many thanks,

hans

--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

On Thursday 09 August 2007 11:30, Joshua D. Drake wrote:
> Andrew Sullivan wrote:
> > On Thu, Aug 09, 2007 at 04:42:19PM +0200, Hans-Juergen Schoenig wrote:
> >> the problem here is that vendors of appliances don't want people to
> >> spider their codes. this is a fact - it is not the idea of open
> >> source to do so but bloody reality. in addition to that people are
> >> not willing to code everything in C just to hide.
> >
> > Well, then, they're out of luck.
>
> This whole thread can be summed up as: plpgsql is an interpretive
> language, not a compiled on.
>
> You can and do see the source, just like perl, ruby, python, bash etc...
>
> If you don't like it, don't use it and use C instead.
>
> :)
>
> Keep in mind that the obfuscation techniques that can be used by python
> and perl are all reversible.
>

Hmm.... I wonder if you could wire plphp through something like Zend Gaurd?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Thu, Aug 09, 2007 at 11:41:02AM -0400, Andrew Dunstan wrote:
>
>
> Decibel! wrote:
> >This is also related to the desire to be able to restrict access to the
> >catalog tables. Doing so could potentially solve this problem; it
> >solves other issues (such as being able to see all the databases that
> >exist on a server, something that hosting environments care about).
> >
>
> You can hide the catalogs, albeit at the cost of some functionality. I
> did some experimentation a couple of years back with removing public
> access from the catalogs, removing information_schema and the public
> schema, etc, and it worked quite well. I set up a user who had access to
> a single schema, which only contained functions, and the user wasn't
> able (so far as I could determine) to see anything other than those
> functions - no tables, no catalogs, no databases, no users. The user was
> still able to function exactly as intended. The intended scenario was
> for a web app user, where the web server was subverted, the aim being to
> restrict the amount of information the intruder could steal.
>
> That doesn't help with information leaking in shared hosting setups, I
> agree.

No, but that combined with row-level security might. Actually, if we had
a standard set of views that all the tools were expected to use instead
of the raw catalogs, it wouldn't be hard at all to secure things in a
hosted environment.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hans-Juergen Schoenig wrote:
>
> On Aug 9, 2007, at 5:03 PM, Greg Smith wrote:
>
>> On Thu, 9 Aug 2007, Andrew Dunstan wrote:
>>
>>> There are also some fairly impressive code obfuscators about, that
>>> your clients might find useful.
>>
>> All they really need is to find a sufficiently clever PL/Perl programmer.
>
>
> we should make this a PL/Brainfuck implementation then ;)

PL/Whitespace

http://compsoc.dur.ac.uk/whitespace/

>
> hans
>
>
> --
> Cybertec Geschwinde & Schönig GmbH
> Gröhrmühlgasse 26, 2700 Wiener Neustadt
> Tel: +43/1/205 10 35 / 340
> www.postgresql.at, www.cybertec.at
>
>
>

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGu6dbATb/zqfZUUQRAm5lAKCvNjklINez5AfioKTJ2FT4BeZ1yQCfbvXz
//mGwmoSYWsiAbdAtOR9/vU=
=QaI0
-----END PGP SIGNATURE-----

Ühel kenal päeval, N, 2007-08-09 kell 16:42, kirjutas Hans-Juergen
Schoenig:

>
>
> well, the complete analysis is easy - the solution is not.
> currently we have basically no option to reduce access to the system
> tables. this would be hard anyway as we need those tables for
> basically all kinds of operations.
> the problem here is that vendors of appliances don't want people to
> spider their codes. this is a fact - it is not the idea of open source
> to do so but bloody reality. in addition to that people are not
> willing to code everything in C just to hide.

Maybe you can write PL/PgSQL compiler, just compiling to C would be
enough.

And it would be generally useful.

> so, there has to be a concept to achieve this for stored procedures
> somehow.
> i am afraid the source level encryption is the easiest thing and most
> understandable thing to do.

make it just rot13 then. will be asy to break anyhow ;)

--------------
Hannu

Ühel kenal päeval, N, 2007-08-09 kell 16:54, kirjutas Hans-Juergen
Schoenig:

> >
> > Of course, if your goal is to hide the code from someone snooping
> > through the pg_proc relation (on disk), then encryption will
> > certainly help (provided the key is properly protected).
> >
> >
>
>
>
>
> no, somebody who has access to the filesystem is not relevant.
> just think of shipping some database inside some router or inside some
> other stuff. the vendor just wants to make sure that other people
> don't fully understand the "magic" going on.

Just access everything through functions and REVOKE all right except
EXECUTE from user.

------------
Hannu

>

On 8/9/07, Zdenek Kotala <Zdenek(dot)Kotala(at)sun(dot)com> wrote:
> If I remember correctly Oracle wrap generates something like bytecode and
> each Oracle installation is able to understand them. But It is not possible
> decode it back to original form.

Regardless of what Oracle tells you, it has always been reversible.
The thing is, there's only a few people who know how to do it. In my
Oracle circles, at last count there were 7 people in the world who
knew how to unwrap 9i and earlier. I know of at least 2 more who know
10g.

Oracle is closed-source, and people can still figure it out. It is
*impossible* to implement a completely secure way to do something
similar with an open-source database.

As Korry said, at some point it's going to be in clear-text... anyone
can break into the hardware, startup a debugger (and/or insert a
hacked version of the PL), and print it out.

The only other way is to actually store the interpreted form of
PL/pgSQL, which would be a large job, and still wouldn't really
protect you. All someone would have to do is build a simple code
generator and run the interpreted form through it.

Obfuscation doesn't really work, it just makes big wigs in companies
*think* it's not easily reversible.

There is no real security. With enough time and experience, anything
can be broken.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:

> Obfuscation doesn't really work, it just makes big wigs in companies
> *think* it's not easily reversible.
>
> There is no real security. With enough time and experience, anything
> can be broken.

But that said, I wonder if having something may be useful legally for some
users.

If someone just went and did "select * from pg_proc" they could claim they
weren't violating their EULA or any protection you had put in place. If they
went through the trouble having to de-obfuscate it then you would have a
strong DMCA claim in the US.

But Jonah's entirely right that there's no way to make it technically
impossible to de-obfuscate. All you can do is make any casual observer pause
and decide to break your license agreement.

If you don't believe him, just as the DVDCCA...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Aug 10, 2007, at 12:00 PM, Gregory Stark wrote:

> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
>
>> Obfuscation doesn't really work, it just makes big wigs in companies
>> *think* it's not easily reversible.
>>
>> There is no real security. With enough time and experience, anything
>> can be broken.
>
> But that said, I wonder if having something may be useful legally
> for some
> users.
>
> If someone just went and did "select * from pg_proc" they could
> claim they
> weren't violating their EULA or any protection you had put in
> place. If they
> went through the trouble having to de-obfuscate it then you would
> have a
> strong DMCA claim in the US.

If doing so would violate their contract with you then it'll violate
their contract (and make them liable for large amounts of liquidated
damages) whether they violate the DMCA or not.

If the code in question isn't important enough to your business that
you have a signed contract with those using it, then it's not really
that important.

>
> But Jonah's entirely right that there's no way to make it technically
> impossible to de-obfuscate. All you can do is make any casual
> observer pause
> and decide to break your license agreement.

Code obfuscation is a bad non-solution to a problem that's far
better solved contractually (assuming it's a real problem, which
it often isn't).

Cheers,
Steve

On 8/10/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> If someone just went and did "select * from pg_proc" they could claim they
> weren't violating their EULA or any protection you had put in place. If they
> went through the trouble having to de-obfuscate it then you would have a
> strong DMCA claim in the US.

I agree. De-obfuscating the code could likely show willful violation
of the software owner's license agreement.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

On Fri, Aug 10, 2007 at 12:11:01PM -0700, Steve Atkins wrote:
> >If someone just went and did "select * from pg_proc" they could
> >claim they
> >weren't violating their EULA or any protection you had put in
> >place. If they
> >went through the trouble having to de-obfuscate it then you would
> >have a
> >strong DMCA claim in the US.
>
> If doing so would violate their contract with you then it'll violate
> their contract (and make them liable for large amounts of liquidated
> damages) whether they violate the DMCA or not.

And what if someone just happens to run \df+ because he doesn't know any
better?

> If the code in question isn't important enough to your business that
> you have a signed contract with those using it, then it's not really
> that important.

Yes, but I'm betting (IANAL) that if you make your trade secrets "easily
accessible" that suddenly your standing in a court of law just got a lot
weaker.

This is a debate I've dealt with in the past, working for a company that
does wide-area distributed computing. People don't want their data
spread all over creation in clear-text, even though it's entirely
possible for someone to connect to a running client with a debugger and
get a memory dump.

As I said before, I don't care what security you come up with, *it can
be broken*. The point of security measures isn't to make it impossible
to break the security, it's to make it more difficult than it's worth.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On 8/10/07, Decibel! <decibel(at)decibel(dot)org> wrote:
> As I said before, I don't care what security you come up with, *it can
> be broken*. The point of security measures isn't to make it impossible
> to break the security, it's to make it more difficult than it's worth.

I agree... but this type of security is really only
security-through-obscurity. If the source code is out there (because
it's open source), it's not difficult at all. The code to
encrypt/obfuscate it and decrypt/execute it will all be out there.
Anyone with a couple years of programming experience could strip it
out and create a utility for it in a couple hours. My bet is that it
wouldn't even be a monetary-driven exercise... just some geek doing it
to see if he/she could.

As there's basically no security provided, I just don't see any reason
to spend the effort adding something like this to PostgreSQL.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

On Fri, 10 Aug 2007, Gregory Stark wrote:

> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
>
>> Obfuscation doesn't really work, it just makes big wigs in companies
>> *think* it's not easily reversible.
>>
>> There is no real security. With enough time and experience, anything
>> can be broken.
>
> But that said, I wonder if having something may be useful legally for some
> users.
>
> If someone just went and did "select * from pg_proc" they could claim they
> weren't violating their EULA or any protection you had put in place. If they
> went through the trouble having to de-obfuscate it then you would have a
> strong DMCA claim in the US.

We can split pg_proc into pg_procinfo and pg_procsrc.

pg_procinfo contains all information about function except source code and
is readable to all.

pg_procsrc has the source and readable only to system and superusers.

pg_proc is a view that joins the two. If database developer wants to limit
user access, they add conditions to the view as necessary, e.g.

proowner in ('postgres',current_user)

or whatever they want.