pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCompiling pl/pgsql functions?
11+ messages / 9 participants
[nested] [flat]
* Compiling pl/pgsql functions?
@ 2003-01-03 17:27 Daniel Bruce Lynes <dlynes@shaw.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Daniel Bruce Lynes @ 2003-01-03 17:27 UTC (permalink / raw)
To: pgsql-sql
Through preliminary examination, it appears stored procedures using pl/pgsql
in PostgreSQL 7.3 are interpreted, rather than compiled?
When we insert a stored function into PostgreSQL, the syntax check is not done
until execution, and only on the branch/path of execution, not on the entire
function.
Is there any way to precompile the pl/pgsql functions so that we know if
there's a missing object dependency upon function creation, rather than at
runtime?
Thanks in advance.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Compiling pl/pgsql functions
@ 2004-02-19 12:44 Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 14:01 ` Re: Compiling pl/pgsql functions Viorel Dragomir <bc@vio.ro>
2004-02-19 16:20 ` Re: Compiling pl/pgsql functions Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 11+ messages in thread
From: Rodrigo Sakai @ 2004-02-19 12:44 UTC (permalink / raw)
To: pgsql-sql
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
=====================
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 12:44 Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
@ 2004-02-19 14:01 ` Viorel Dragomir <bc@vio.ro>
1 sibling, 0 replies; 11+ messages in thread
From: Viorel Dragomir @ 2004-02-19 14:01 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo@2bfree.com.br>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 12:44 Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
@ 2004-02-19 16:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-19 16:48 ` Re: Compiling pl/pgsql functions Rod Taylor <rbt@rbt.ca>
1 sibling, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2004-02-19 16:20 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo@2bfree.com.br>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 12:44 Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 16:20 ` Re: Compiling pl/pgsql functions Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-02-19 16:48 ` Rod Taylor <rbt@rbt.ca>
2004-02-22 19:34 ` Re: Compiling pl/pgsql functions Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Rod Taylor @ 2004-02-19 16:48 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Rodrigo Sakai <rodrigo@2bfree.com.br>; pgsql-sql
> 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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 12:44 Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 16:20 ` Re: Compiling pl/pgsql functions Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-19 16:48 ` Re: Compiling pl/pgsql functions Rod Taylor <rbt@rbt.ca>
@ 2004-02-22 19:34 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2004-02-22 19:34 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Rodrigo Sakai <rodrigo@2bfree.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
@ 2004-02-19 17:05 Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 19:00 ` Re: Compiling pl/pgsql functions Joe Conway <mail@joeconway.com>
2004-02-19 19:26 ` Re: Compiling pl/pgsql functions Josh Berkus <josh@agliodbs.com>
2004-02-19 20:09 ` Re: Compiling pl/pgsql functions Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 3 replies; 11+ messages in thread
From: Rodrigo Sakai @ 2004-02-19 17:05 UTC (permalink / raw)
To: pgsql-sql
>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 inside the database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes that are 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 compile my plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not be able to get our codes. 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??????
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
=====================
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 17:05 Re: Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
@ 2004-02-19 19:00 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 11+ messages in thread
From: Joe Conway @ 2004-02-19 19:00 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo@2bfree.com.br>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 17:05 Re: Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
@ 2004-02-19 19:26 ` Josh Berkus <josh@agliodbs.com>
2004-02-19 20:49 ` Re: Compiling pl/pgsql functions Richard Huxton <dev@archonet.com>
2 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2004-02-19 19:26 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo@2bfree.com.br>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 17:05 Re: Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 19:26 ` Re: Compiling pl/pgsql functions Josh Berkus <josh@agliodbs.com>
@ 2004-02-19 20:49 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2004-02-19 20:49 UTC (permalink / raw)
To: josh@agliodbs.com, "Rodrigo Sakai" <rodrigo@2bfree.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Compiling pl/pgsql functions
2004-02-19 17:05 Re: Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
@ 2004-02-19 20:09 ` Jan Wieck <JanWieck@Yahoo.com>
2 siblings, 0 replies; 11+ messages in thread
From: Jan Wieck @ 2004-02-19 20:09 UTC (permalink / raw)
To: Rodrigo Sakai <rodrigo@2bfree.com.br>; +Cc: pgsql-sql
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 are inside the database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes that are 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 compile my plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not be able to get our codes. 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??????
>
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 #
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-02-22 19:34 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-03 17:27 Compiling pl/pgsql functions? Daniel Bruce Lynes <dlynes@shaw.ca>
2004-02-19 12:44 Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 14:01 ` Re: Compiling pl/pgsql functions Viorel Dragomir <bc@vio.ro>
2004-02-19 16:20 ` Re: Compiling pl/pgsql functions Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-19 16:48 ` Re: Compiling pl/pgsql functions Rod Taylor <rbt@rbt.ca>
2004-02-22 19:34 ` Re: Compiling pl/pgsql functions Josh Berkus <josh@agliodbs.com>
2004-02-19 17:05 Re: Compiling pl/pgsql functions Rodrigo Sakai <rodrigo@2bfree.com.br>
2004-02-19 19:00 ` Re: Compiling pl/pgsql functions Joe Conway <mail@joeconway.com>
2004-02-19 19:26 ` Re: Compiling pl/pgsql functions Josh Berkus <josh@agliodbs.com>
2004-02-19 20:49 ` Re: Compiling pl/pgsql functions Richard Huxton <dev@archonet.com>
2004-02-19 20:09 ` Re: Compiling pl/pgsql functions Jan Wieck <JanWieck@Yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox