Thread: 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/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 =====================
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 >
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?
>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 =====================
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
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
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
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 #
> 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
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