Thread: 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 |
[pavel@okbob-bb ~]$ 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
On Jan 28, 2008 12:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > this patch define new function flag - OBFUSCATE. With this flag > encrypted source code is stored to probin column. Password is stored > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > does (where privileged users can access system tables with source code > or can use debugger). > > ToDo: Dump Without making any comment of whether or not we should actually do this, a flag in pg_proc to indicate that the function is obfuscated would be handy for apps like pgAdmin, rather than assuming a - in prosrc has that meaning (which may be valid for some interpreters). /D
On 28/01/2008, Dave Page <dpage@postgresql.org> wrote: > On Jan 28, 2008 12:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > Hello > > > > this patch define new function flag - OBFUSCATE. With this flag > > encrypted source code is stored to probin column. Password is stored > > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > > does (where privileged users can access system tables with source code > > or can use debugger). > > > > ToDo: Dump > > Without making any comment of whether or not we should actually do > this, a flag in pg_proc to indicate that the function is obfuscated > would be handy for apps like pgAdmin, rather than assuming a - in > prosrc has that meaning (which may be valid for some interpreters). sure, but do you know, Tom dislikes new columns in pg_proc :). This patch is usable sample of one possible solution and doesn't need initdb. And there is dependency on pgcrypto :(. But it is simply and it does all what is expected. Some customers wonted it. But I am not sure if similar patch can be accepted - this is prototype. And when I'll have some signals so patch can be commited I'll send final version with obfuscate col in pg_proc. Any patch of pg_proc needs two hours of work, and any change needs actualization - so lot of maybe useless work. Pavel > > /D >
On Jan 28, 2008 2:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > sure, but do you know, Tom dislikes new columns in pg_proc :). Tom doesn't seem to like the idea of obfuscation of function code much either :-) > This > patch is usable sample of one possible solution and doesn't need > initdb. And there is dependency on pgcrypto :(. But it is simply and > it does all what is expected. Some customers wonted it. But I am not > sure if similar patch can be accepted - this is prototype. And when > I'll have some signals so patch can be commited I'll send final > version with obfuscate col in pg_proc. Any patch of pg_proc needs two > hours of work, and any change needs actualization - so lot of maybe > useless work. Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect you to necessarily do it immediately - I just wanted to throw my requirements from a tools perspective into the inevitable discussion. Cheers, Dave.
On 28/01/2008, Dave Page <dpage@postgresql.org> wrote: > On Jan 28, 2008 2:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > sure, but do you know, Tom dislikes new columns in pg_proc :). > > Tom doesn't seem to like the idea of obfuscation of function code much > either :-) > > > This > > patch is usable sample of one possible solution and doesn't need > > initdb. And there is dependency on pgcrypto :(. But it is simply and > > it does all what is expected. Some customers wonted it. But I am not > > sure if similar patch can be accepted - this is prototype. And when > > I'll have some signals so patch can be commited I'll send final > > version with obfuscate col in pg_proc. Any patch of pg_proc needs two > > hours of work, and any change needs actualization - so lot of maybe > > useless work. > > Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect > you to necessarily do it immediately - I just wanted to throw my > requirements from a tools perspective into the inevitable discussion. with "obfuscate" col in pg_proc source can be little bit more readable and robust - current patch is +/- fast hack - so your requirement is accurate. Regards Pavel . > > Cheers, Dave. >
Pavel Stehule wrote: > Hello > > this patch define new function flag - OBFUSCATE. With this flag > encrypted source code is stored to probin column. Password is stored > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > does (where privileged users can access system tables with source code > or can use debugger) > > ToDo: Dump > Maybe a better TODO would be to do this task in the way that has previously been suggested: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php I'm certainly not happy about any proposal to put a password/key in a GUC var - that strikes me as a major footgun. cheers andrew
On 28/01/2008, Andrew Dunstan <andrew@dunslane.net> wrote: > > > Pavel Stehule wrote: > > Hello > > > > this patch define new function flag - OBFUSCATE. With this flag > > encrypted source code is stored to probin column. Password is stored > > in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server > > does (where privileged users can access system tables with source code > > or can use debugger) > > > > ToDo: Dump > > > > Maybe a better TODO would be to do this task in the way that has > previously been suggested: > http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php > > I'm certainly not happy about any proposal to put a password/key in a > GUC var - that strikes me as a major footgun. > why? we cannot ensure bigger real security. Anybody with superuser rights can add modules that show source code of plpgsql procedure, or can run debugger and attach postgres process. p.s. this topic was discussed in http://markmail.org/message/r6jy7m6oryi5owyb Pavel > cheers > > andrew > > >
Someone along the way suggested doing this as a kind of "wrapper" PL language. So you would have a PL language like "obfuscate:plperl" which would obfuscate the source code on the way in. Then when you execute a function it would deobfuscate the source code and then just pass it to the normal plperl. In such a scheme I think you would put the key in an attribute of the language. Either in pg_lang or some configuration location which the obfuscate:plperl interpreter knows where to find. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
On 28/01/2008, Gregory Stark <stark@enterprisedb.com> wrote: > > Someone along the way suggested doing this as a kind of "wrapper" PL language. > So you would have a PL language like "obfuscate:plperl" which would obfuscate > the source code on the way in. Then when you execute a function it would > deobfuscate the source code and then just pass it to the normal plperl. > you can call Deobfuscate proc from any language handler - no problem > In such a scheme I think you would put the key in an attribute of the > language. Either in pg_lang or some configuration location which the > obfuscate:plperl interpreter knows where to find. > what is advantage? > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's On-Demand Production Tuning >
"Pavel Stehule" <pavel.stehule@gmail.com> writes: >> In such a scheme I think you would put the key in an attribute of the >> language. Either in pg_lang or some configuration location which the >> obfuscate:plperl interpreter knows where to find. >> > > what is advantage? It wouldn't require any core changes. It would be just another PL language to load which can be installed like other ones. This could be a big advantage because it doesn't look like there is a lot of support for putting th obfuscation directly into the core code. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
On 28/01/2008, Gregory Stark <stark@enterprisedb.com> wrote: > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > > >> In such a scheme I think you would put the key in an attribute of the > >> language. Either in pg_lang or some configuration location which the > >> obfuscate:plperl interpreter knows where to find. > >> > > > > what is advantage? > > It wouldn't require any core changes. It would be just another PL language to > load which can be installed like other ones. This could be a big advantage > because it doesn't look like there is a lot of support for putting th > obfuscation directly into the core code. can be. but I am afraid so any changes are necessary in core too Pavel > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's 24x7 Postgres support! >
Andrew Dunstan <andrew@dunslane.net> writes: > Maybe a better TODO would be to do this task in the way that has > previously been suggested: > http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php > I'm certainly not happy about any proposal to put a password/key in a > GUC var - that strikes me as a major footgun. We didn't really have a better solution to the key management problem, though, did we? At least I don't see anything about it in that thread. However, I definitely agree that a separate loadable PL is the way to go for functionality of this sort. There is no way that a dependency on pgcrypto is going to be accepted into core, not even in the (ahem) obfuscated way that it's presented here. regards, tom lane
On 28/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > > Maybe a better TODO would be to do this task in the way that has > > previously been suggested: > > http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php > > I'm certainly not happy about any proposal to put a password/key in a > > GUC var - that strikes me as a major footgun. > > We didn't really have a better solution to the key management problem, > though, did we? At least I don't see anything about it in that thread. > > However, I definitely agree that a separate loadable PL is the way to go > for functionality of this sort. There is no way that a dependency on > pgcrypto is going to be accepted into core, not even in the (ahem) > obfuscated way that it's presented here. > Do you thing some binary module that load some encrypted sources from files? It can be possible too. But if source code will be stored in pg_proc, then we need third method. Some like "obfuscate" (prev. are validate and call"), because we can't to store plain text to prosrc col. My patch is only solution for some users, and I know about problem with dependency. Reagards Pavel Stehule > regards, tom lane >
"Pavel Stehule" <pavel.stehule@gmail.com> writes: > Do you thing some binary module that load some encrypted sources from > files? It can be possible too. But if source code will be stored in > pg_proc, then we need third method. Some like "obfuscate" (prev. are > validate and call"), because we can't to store plain text to prosrc > col. Is there a reason you couldn't, for instance, provide a function which takes source code and encrypts it. Then you would write dump the data it spits into your function declaration like: CREATE FUNCTION foo() returns integer AS $$ ... base64 encoded data $$ language "obfuscated:plperl"; "obfuscated:plperl"'s handler function would just decrypt it and pass it off to plperl. There is a validator function which gets called when you create a function but I don't think it has any opportunity to substitute its result for the original in prosrc. That might be interesting for other applications like compiled languages, though I think they would still want to save the source in prosrc and the bytecode in probin. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
On 28/01/2008, Gregory Stark <stark@enterprisedb.com> wrote: > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > > > Do you thing some binary module that load some encrypted sources from > > files? It can be possible too. But if source code will be stored in > > pg_proc, then we need third method. Some like "obfuscate" (prev. are > > validate and call"), because we can't to store plain text to prosrc > > col. > > Is there a reason you couldn't, for instance, provide a function which takes > source code and encrypts it. Then you would write dump the data it spits into > your function declaration like: > > CREATE FUNCTION foo() returns integer AS $$ > ... base64 encoded data > $$ language "obfuscated:plperl"; > it's solve problem with dump well, but it's similar to my solution. "obfuscated:plperl" can be virtual language - we can have one common handler, because there is same work. I am not sure. This doesn't care any better security, only add some other necessary external toolkit. With obfuscate column or obfuscate language (it carry same information) I can use prosrc and I have not problem with dump too. It is true, so obfuscate languages move dependency to out of core - but it is more complex. > "obfuscated:plperl"'s handler function would just decrypt it and pass it off > to plperl. you need same handler for plpgsql, python, sql, ... so why don't do it generally? Pavel > > 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! >
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > >> Maybe a better TODO would be to do this task in the way that has >> previously been suggested: >> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php >> I'm certainly not happy about any proposal to put a password/key in a >> GUC var - that strikes me as a major footgun. >> > > We didn't really have a better solution to the key management problem, > though, did we? At least I don't see anything about it in that thread. > Yeah. Maybe we could have the GUC var contain the name of a key file rather than the key itself. If we require that the name be relative to the datadir that might be tolerably secure. > However, I definitely agree that a separate loadable PL is the way to go > for functionality of this sort. There is no way that a dependency on > pgcrypto is going to be accepted into core, not even in the (ahem) > obfuscated way that it's presented here. > > > If we do anything in core it could be to make provision for an obfuscation/encryption hook via a loadable module. Various interesting encoding issues could arise with dumping and restoring transformed program text - I haven't thought that through yet. But I agree a simple PL wrapper makes sense to start with, at any rate. cheers andrew
Gregory Stark <stark@enterprisedb.com> writes:
> There is a validator function which gets called when you create a
> function but I don't think it has any opportunity to substitute its
> result for the original in prosrc.
It would have to do a heap_update on the prosrc row, but that doesn't
seem like a showstopper problem.
regards, tom lane
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> However, I definitely agree that a separate loadable PL is the way to go
>> for functionality of this sort. There is no way that a dependency on
>> pgcrypto is going to be accepted into core, not even in the (ahem)
>> obfuscated way that it's presented here.
> If we do anything in core it could be to make provision for an
> obfuscation/encryption hook via a loadable module.
My recollection is that certain cryptography laws make hooks for crypto
just as problematic as actual crypto code. We'd have to tread very
carefully --- "general purpose" hooks are OK but anything narrowly
tailored to encryption purposes would be a hazard. This is one reason
that I'd prefer to see it as an external PL rather than embedded in core.
> Various interesting encoding issues could arise with dumping and
> restoring transformed program text - I haven't thought that through yet.
I think we have already solved that with md5 passwords, and could easily
reuse the same kind of approach. You just base64 encode the crypted
text (or whatever you need to do to avoid funny characters in it), and
make sure that there's some way to distinguish already-crypted from
not-already-crypted function bodies.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > My recollection is that certain cryptography laws make hooks for crypto > just as problematic as actual crypto code. We'd have to tread very > carefully --- "general purpose" hooks are OK but anything narrowly > tailored to encryption purposes would be a hazard. Afaik the US was the only country with such a scheme with the ITAR export regulations and that's long since gone, at least as it applied to crypto. The current US export regulations don't have any of the stuff about hooks in them and exempt free software from any crypto export licenses. Doesn't stop some other country from coming up with the same idea of course but we don't generally worry about what laws some hypothetical country might introduce at some point in the future. That way lies madness. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > >> Tom Lane wrote: >> >>> However, I definitely agree that a separate loadable PL is the way to go >>> for functionality of this sort. There is no way that a dependency on >>> pgcrypto is going to be accepted into core, not even in the (ahem) >>> obfuscated way that it's presented here. >>> > > >> If we do anything in core it could be to make provision for an >> obfuscation/encryption hook via a loadable module. >> > > My recollection is that certain cryptography laws make hooks for crypto > just as problematic as actual crypto code. We'd have to tread very > carefully --- "general purpose" hooks are OK but anything narrowly > tailored to encryption purposes would be a hazard. This is one reason > that I'd prefer to see it as an external PL rather than embedded in core. > It could be something other than encryption; any sort of transformation might fit. For example, one might do something like: gzip | some-sort-of-shuffle | base64-encode as a sort or poor man's obfuscation > >> Various interesting encoding issues could arise with dumping and >> restoring transformed program text - I haven't thought that through yet. >> > > I think we have already solved that with md5 passwords, and could easily > reuse the same kind of approach. You just base64 encode the crypted > text (or whatever you need to do to avoid funny characters in it), and > make sure that there's some way to distinguish already-crypted from > not-already-crypted function bodies. > > > I don't see how a binary MD5 checksum has any encoding component. But using this example, it seems to me that if we dump the encrypted/encoded source and restore into another database with a different encoding, the decoded/decrypted source will still be in the old database encoding, i.e. not valid in the new database encoding. We've just gone around closing doors like this. You might be able to fix it by storing the database encoding name along with the encrypted/encoded source, so it could be transformed at the other end. cheers andrew
Andrew Dunstan <andrew@dunslane.net> 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
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/
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/
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
On 29/01/2008, Peter Eisentraut <peter_e@gmx.net> 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/ >
Hi, Pavel Stehule írta: > On 29/01/2008, Peter Eisentraut <peter_e@gmx.net> 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/
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 |
>
>
> [pavel@okbob-bb ~]$ 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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Sun, 6 Apr 2008 22:14:01 -0400 (EDT) Bruce Momjian <bruce@momjian.us> 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
Attachment
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Bruce Momjian <bruce@momjian.us> 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
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/
Attachment
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > Bruce Momjian <bruce@momjian.us> 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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +