Re: [PATCHES] WIP: plpgsql source code obfuscation
Hi, On Sun, 2008-04-06 at 21:59 -0700, Joshua D. Drake wrote: For the record. I think this todo is bogus. We are an Open Source database, let others worry about obfuscation. It isn't like it can't be done within the facilities that already exist. +1. IMHO, this patch should live as a pgfoundry module, not as a core feature. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [PATCHES] WIP: plpgsql source code obfuscation
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: o Add ability to obfuscate function bodies For the record. I think this todo is bogus. For the record, I think so too ;-). The agreed-on TODO wording makes no mention of what an acceptable implementation would look like, and that's because there very possibly *is* no generally-acceptable implementation. (Though if someone has a bright new idea, I'm sure we'd all listen.) The point of the TODO entry is just to acknowledge that this is an issue for some folk, and that a real solution would be welcomed. We could list it as a feature we don't want, but I don't think that is accurate. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] WIP: plpgsql source code obfuscation
Added to TODO: o Add ability to obfuscate function bodies http://archives.postgresql.org/pgsql-patches/2008-01/msg00125.php --- 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 Sample: postgres=# show obfuscator_password; obfuscator_password --- moje supertajne heslo (1 row) postgres=# \x Expanded display is on. postgres=# create or replace function fx() returns int as $$begin return -1; end; $$ language plpgsql; CREATE FUNCTION postgres=# \df+ fx List of functions -[ RECORD 1 ]---+--- Schema | public Name| fx Result data type| integer Argument data types | Volatility | volatile Owner | bob Language| plpgsql Source code | begin return -1; end; Description | postgres=# ALTER FUNCTION fx() obfuscate; NOTICE: begin return -1; end; ALTER FUNCTION postgres=# \df+ fx List of functions -[ RECORD 1 ]---+- Schema | public Name| fx Result data type| integer Argument data types | Volatility | volatile Owner | bob Language| plpgsql Source code | - Description | postgres=# select fx(); -[ RECORD 1 ] fx | -1 postgres=# create or replace function fx() returns int as $$begin return -1; end; $$ language plpgsql obfuscate; CREATE FUNCTION postgres=# select fx(); -[ RECORD 1 ] fx | -1 postgres=# \df+ fx List of functions -[ RECORD 1 ]---+- Schema | public Name| fx Result data type| integer Argument data types | Volatility | volatile Owner | bob Language| plpgsql Source code | - Description | postgres=# select * from pg_proc where proname = 'fx'; -[ RECORD 1 ]--+ proname| fx pronamespace | 2200 proowner | 16385 prolang| 16421 procost| 100 prorows| 0 proisagg | f prosecdef | f proisstrict| f proretset | f provolatile| v pronargs | 0 prorettype | 23 proargtypes| proallargtypes | proargmodes| proargnames| prosrc | - probin | \231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222 proconfig | proacl | [EMAIL PROTECTED] ~]$ psql -U bob postgres Welcome to psql 8.3RC2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres= \x Expanded display is on. postgres= show obfuscator_password; ERROR: must be superuser to examine obfuscator_password postgres= select fx(); -[ RECORD 1 ] fx | -1 postgres= \df+ fx List of functions -[ RECORD 1 ]---+- Schema | public Name| fx Result data type| integer Argument data types | Volatility | volatile Owner | bob Language| plpgsql Source code | - Description | postgres= select * from pg_proc where proname = 'fx'; -[ RECORD 1 ]--+ proname| fx pronamespace | 2200 proowner | 16385 prolang| 16421 procost| 100 prorows| 0 proisagg | f prosecdef | f proisstrict| f proretset | f provolatile| v pronargs | 0 prorettype | 23 proargtypes| proallargtypes | proargmodes| proargnames| prosrc | - probin | \231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222 proconfig | proacl | [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] WIP: plpgsql source code obfuscation
On Sun, 6 Apr 2008 22:14:01 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: o Add ability to obfuscate function bodies http://archives.postgresql.org/pgsql-patches/2008-01/msg00125.php For the record. I think this todo is bogus. We are an Open Source database, let others worry about obfuscation. It isn't like it can't be done within the facilities that already exist. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit signature.asc Description: PGP signature
Re: [PATCHES] WIP: plpgsql source code obfuscation
Joshua D. Drake [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] wrote: Added to TODO: o Add ability to obfuscate function bodies For the record. I think this todo is bogus. For the record, I think so too ;-). The agreed-on TODO wording makes no mention of what an acceptable implementation would look like, and that's because there very possibly *is* no generally-acceptable implementation. (Though if someone has a bright new idea, I'm sure we'd all listen.) The point of the TODO entry is just to acknowledge that this is an issue for some folk, and that a real solution would be welcomed. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] WIP: plpgsql source code obfuscation
Hi, Pavel Stehule írta: On 29/01/2008, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Montag, 28. Januar 2008 schrieb Pavel Stehule: 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). Have you thought about a solution that applies the regular access privileges to pg_proc in order to hide some content from less privileged users? it's second way, and maybe better. It can close way to table definitions too (and this request is adequate too). But you cannot to hide complete column, visibility depend on content and it can be slow, complex :(. Encrypt, decrypt aren't fast too. Pavel We made a similar encrypted plpgsql for a customer. It was a fork of plpgsql from 8.2.x and uses pgcrypto internally. Functions are cached the same way by the backend as regular plpgsql functions, hence fast. The hashkey of the cached function is the hash of the already encrypted function so it doesn't need to be decrypted every time it's looked up. Only the first run of a function is slower where it is needed to be decrypted for compilation. The pgcrypto dependency can be lifted and similar Obfuscate() and Deobfuscate() functions can be used as in the WIP patch posted here. The encrypted body is stored inside prosrc in our solution and dumpable/restorable just fine. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 29/01/2008, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Montag, 28. Januar 2008 schrieb Pavel Stehule: 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). Have you thought about a solution that applies the regular access privileges to pg_proc in order to hide some content from less privileged users? it's second way, and maybe better. It can close way to table definitions too (and this request is adequate too). But you cannot to hide complete column, visibility depend on content and it can be slow, complex :(. Encrypt, decrypt aren't fast too. Pavel -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
Am Montag, 28. Januar 2008 schrieb Pavel Stehule: 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). Have you thought about a solution that applies the regular access privileges to pg_proc in order to hide some content from less privileged users? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Am Montag, 28. Januar 2008 schrieb Tom Lane: 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. PostgreSQL already has built-in support for SSL and Kerberos, so it is already infested with enough weaponized code to fail most applicable regulations. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] WIP: plpgsql source code obfuscation
Peter Eisentraut wrote: Am Montag, 28. Januar 2008 schrieb Pavel Stehule: 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). Have you thought about a solution that applies the regular access privileges to pg_proc in order to hide some content from less privileged users? This question is a good one, especially since we have waiting in the wings the work my SOC student did a few months ago on column level privileges. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On Jan 28, 2008 12:51 PM, Pavel Stehule [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Dave Page [EMAIL PROTECTED] wrote: On Jan 28, 2008 2:26 PM, Pavel Stehule [EMAIL PROTECTED] 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 . Cheers, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark [EMAIL PROTECTED] 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? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On Jan 28, 2008 2:26 PM, Pavel Stehule [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Dave Page [EMAIL PROTECTED] wrote: On Jan 28, 2008 12:51 PM, Pavel Stehule [EMAIL PROTECTED] 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 /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: plpgsql source code obfuscation
Pavel Stehule [EMAIL PROTECTED] 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! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
Pavel Stehule [EMAIL PROTECTED] 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! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP: plpgsql source code obfuscation
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Andrew Dunstan [EMAIL PROTECTED] 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 cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
Gregory Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: plpgsql source code obfuscation
Tom Lane [EMAIL PROTECTED] 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! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] 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 regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] 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 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] WIP: plpgsql source code obfuscation
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
Sample:
postgres=# show obfuscator_password;
obfuscator_password
---
moje supertajne heslo
(1 row)
postgres=# \x
Expanded display is on.
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+---
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | begin return -1; end;
Description |
postgres=# ALTER FUNCTION fx() obfuscate;
NOTICE: begin return -1; end;
ALTER FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql obfuscate;
CREATE FUNCTION
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=# select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+
proname| fx
pronamespace | 2200
proowner | 16385
prolang| 16421
procost| 100
prorows| 0
proisagg | f
prosecdef | f
proisstrict| f
proretset | f
provolatile| v
pronargs | 0
prorettype | 23
proargtypes|
proallargtypes |
proargmodes|
proargnames|
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
[EMAIL PROTECTED] ~]$ psql -U bob postgres
Welcome to psql 8.3RC2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres= \x
Expanded display is on.
postgres= show obfuscator_password;
ERROR: must be superuser to examine obfuscator_password
postgres= select fx();
-[ RECORD 1 ]
fx | -1
postgres= \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres= select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+
proname| fx
pronamespace | 2200
proowner | 16385
prolang| 16421
procost| 100
prorows| 0
proisagg | f
prosecdef | f
proisstrict| f
proretset | f
provolatile| v
pronargs | 0
prorettype | 23
proargtypes|
proallargtypes |
proargmodes|
proargnames|
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
*** ./src/backend/catalog/pg_proc.c.orig 2008-01-27 21:29:42.0 +0100
--- ./src/backend/catalog/pg_proc.c 2008-01-28 11:13:31.0 +0100
***
*** 27,32
--- 27,33
#include mb/pg_wchar.h
#include miscadmin.h
#include parser/parse_type.h
+ #include parser/parse_func.h
#include tcop/pquery.h
#include tcop/tcopprot.h
#include utils/acl.h
***
*** 34,39
--- 35,41
#include utils/lsyscache.h
#include utils/syscache.h
+ extern char *obfuscator_password;
Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
Datum fmgr_c_validator(PG_FUNCTION_ARGS);
***
*** 45,51
static bool match_prosrc_to_literal(const char *prosrc, const char *literal,
int cursorpos, int *newcursorpos);
-
/*
* ProcedureCreate
*
--- 47,52
***
*** 74,80
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows)
{
Oid retval;
int parameterCount;
--- 75,82
Datum
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] 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 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! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: plpgsql source code obfuscation
Andrew Dunstan [EMAIL PROTECTED] writes: 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. Ah, right, I hadn't thought about that, but it would be a hazard. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
