Thread: Compiling pl/pgsql functions

Compiling pl/pgsql functions

From
"Rodrigo Sakai"
Date:
Hi, I'm responsable for the database here in the company, and I like to know if is there a way to compile my
pl/pgsqlfunctions, its not a performance problem, it is more a security problem, i don like to have somebody looking
intomy codes and see the company rules. Is there a way to do that, or the only way is writting my functions in C??????
Thanksfor any help and regards to all!!!
 



=====================
Rodrigo Sakai
Database Programmer
rodrigo@2bfree.com.br
http://www.2bfree.com.br
Tel:  (55) (11) 5083-5577
Fax: (55) (11) 5549-3598
=====================



Re: Compiling pl/pgsql functions

From
"Viorel Dragomir"
Date:
So use Grant more wisely.

----- Original Message ----- 
From: "Rodrigo Sakai" <rodrigo@2bfree.com.br>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, February 19, 2004 2:44 PM
Subject: [SQL] Compiling pl/pgsql functions


>    Hi, I'm responsable for the database here in the company, and I like to
know if is there a way to compile my pl/pgsql functions, its not a
performance problem, it is more a security problem, i don like to have
somebody looking into my codes and see the company rules.
>   Is there a way to do that, or the only way is writting my functions in
C??????
>
>   Thanks for any help and regards to all!!!
>
>
>
> =====================
> Rodrigo Sakai
> Database Programmer
> rodrigo@2bfree.com.br
> http://www.2bfree.com.br
> Tel:  (55) (11) 5083-5577
> Fax: (55) (11) 5549-3598
> =====================
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>




Re: Compiling pl/pgsql functions

From
Stephan Szabo
Date:
On Thu, 19 Feb 2004, Rodrigo Sakai wrote:

>    Hi, I'm responsable for the database here in the company, and I like
>    to know if is there a way to compile my pl/pgsql functions, its not a
>    performance problem, it is more a security problem, i don like to
>    have somebody looking into my codes and see the company rules.

AFAIK there's not much you can do for obfuscation of pl functions right
now since someone will be able to see the src text in pg_proc. However,
are you allowing people that you don't want to see the code access to
write arbitrary sql to the database?


Re: Compiling pl/pgsql functions

From
"Rodrigo Sakai"
Date:
>AFAIK there's not much you can do for obfuscation of pl functions right
>now since someone will be able to see the src text in pg_proc. However,
>are you allowing people that you don't want to see the code access to
>write arbitrary sql to the database?
 Let me explain myself a little better. Actualy we sell software,  and some codes of the systems we develope here are
insidethe database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes that
arein the postgresql. Some of our clientes, need that a employee of them get total access to the database instaled
locally, becoming the database administrator. Thats ok, but to protect our postgresql codes (functions) i like to
compilemy plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not
beable to get our codes.  I insist in my question, is there a way to compile the plpgsql codes or something like that,
orits better to think about writting this postgres functions in C??????
 
 Thanks for all!!!

=====================
Rodrigo Sakai
Database Programmer
rodrigo@2bfree.com.br
http://www.2bfree.com.br
Tel:  (55) (11) 5083-5577
Fax: (55) (11) 5549-3598
=====================



Re: Compiling pl/pgsql functions

From
Joe Conway
Date:
Rodrigo Sakai wrote:
> I insist in my question, is there a way to compile the
> plpgsql codes or something like that

no

> think about writting this postgres functions in C??????

yes

Joe


Re: Compiling pl/pgsql functions

From
Josh Berkus
Date:
Rodrigo,

>   I insist in my question, is there a way to compile the plpgsql codes or 
something like that, or its better to think about writting this postgres 
functions in C??????

No, there is not.   Nor is there likely to be for any PL, as it would add 
significant overhead for no real gain.

You have, as I see it, 3 choices:

1) You can give up on code obfuscation and simply provide the functions 
normally, and rely on your contracts and copyright law to protect your code.  
This is what I do, and I feel pretty strongly that code obfuscation is a dumb 
and ineffective way to protect copyright.   Personally, I find it hard to 
believe that any of my PL/SQL functions (or yours) are so brilliant that they 
need trade secret protection.

2) You can write your functions in C and compile them.

3) You can carefully engineer your database permissions so that the user can 
have almost full DBA powers without being the superuser, and deny them direct 
access to the pg_proc table.   This would be a real PITA, though.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco



Re: Compiling pl/pgsql functions

From
Richard Huxton
Date:
On Thursday 19 February 2004 19:26, Josh Berkus wrote:
> Rodrigo,
>
> >   I insist in my question, is there a way to compile the plpgsql codes or
>
> something like that, or its better to think about writting this postgres
> functions in C??????
>
> No, there is not.   Nor is there likely to be for any PL, as it would add
> significant overhead for no real gain.

It's worse than that - if you really denied access to them, you wouldn't be 
able to dump/restore the database - absolute nightmare.

> Personally, I find it
> hard to believe that any of my PL/SQL functions (or yours) are so brilliant
> that they need trade secret protection.

Some of mine are so ugly, I wish they were hidden away mind you ;-)

--  Richard Huxton Archonet Ltd


Re: Compiling pl/pgsql functions

From
Jan Wieck
Date:
Rodrigo Sakai wrote:

>>AFAIK there's not much you can do for obfuscation of pl functions right
>>now since someone will be able to see the src text in pg_proc. However,
>>are you allowing people that you don't want to see the code access to
>>write arbitrary sql to the database?
> 
>   Let me explain myself a little better. Actualy we sell software,  and some codes of the systems we develope here
areinside the database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes
thatare in the postgresql. Some of our clientes, need that a employee of them get total access to the database instaled
locally, becoming the database administrator. Thats ok, but to protect our postgresql codes (functions) i like to
compilemy plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not
beable to get our codes.  I insist in my question, is there a way to compile the plpgsql codes or something like that,
orits better to think about writting this postgres functions in C??????
 
> 

Security through obscurity? Why do those people you want to hide your 
code from direct SQL access to the database in the first place?


Jan

>   Thanks for all!!!
> 
> =====================
> Rodrigo Sakai
> Database Programmer
> rodrigo@2bfree.com.br
> http://www.2bfree.com.br
> Tel:  (55) (11) 5083-5577
> Fax: (55) (11) 5549-3598
> =====================
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Re: Compiling pl/pgsql functions

From
Rod Taylor
Date:
> AFAIK there's not much you can do for obfuscation of pl functions right
> now since someone will be able to see the src text in pg_proc. However,
> are you allowing people that you don't want to see the code access to
> write arbitrary sql to the database?

This is another one of those items where it would be nice if users
didn't need access to read the system tables, but instead could rely on
the information schema (with extensions) to see what they own or have
access to use -- but nothing else.

Sometimes HR gets paranoid about billing seeing their business logic, or
lack thereof, but accounting needs to use both sets of information to do
their work.

Otherwise, having each group relegated to their own schema with
semi-public views is a nice way to pass information from department to
department for small companies. Sure beats the spreadsheets on the
central filer approach.

--
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

Re: Compiling pl/pgsql functions

From
Josh Berkus
Date:
Rod,

> This is another one of those items where it would be nice if users
> didn't need access to read the system tables, but instead could rely on
> the information schema (with extensions) to see what they own or have
> access to use -- but nothing else.

Hmmm, that is a good question: can I, as a database user, query the source 
code for functions I don't have permissions on?    This seems like an easy 
adjustment to the system tables, if so.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco