Thread: 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.cheersandrew---------------------------(end of broadcast)---------------------------TIP 7: You can help support the PostgreSQL project by donating at
--
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 andask for it again and again.If you want to design a security feature, you need to offer a threat and riskanalysis, not just the whining of customers.--Peter Eisentraut
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@gregsmith.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@decibel.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 ;)
--
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
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
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@decibel.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@crankycanuck.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@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland
-----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-----
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
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@dunslane.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@gmail.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 justa thin wrapper around the plpgsql call handler, and all it does isdecrypt 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@decibel.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.Kotala@sun.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@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
"Jonah H. Harris" <jonah.harris@gmail.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.harris@gmail.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@enterprisedb.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@enterprisedb.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@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On 8/10/07, Decibel! <decibel@decibel.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@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On Fri, 10 Aug 2007, Gregory Stark wrote:
> "Jonah H. Harris" <jonah.harris@gmail.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.