Thread: viewing source code
Is it possible yet in PostgreSQL to hide the source code of functions from users based on role membership? I would like to avoid converting the code to C to secure the source code and I don't want it obfuscated either. In an ideal world, if a user can't modify a function, he/she shouldn't be able to see the source code. If the user can execute the function, then the user should be able to see the signature of the function but not the body. Thanks! Jon
In response to "Roberts, Jon" <Jon.Roberts@asurion.com>: > Is it possible yet in PostgreSQL to hide the source code of functions from > users based on role membership? I would like to avoid converting the code > to C to secure the source code and I don't want it obfuscated either. > > In an ideal world, if a user can't modify a function, he/she shouldn't be > able to see the source code. If the user can execute the function, then the > user should be able to see the signature of the function but not the body. I doubt that's going to happen. Mainly because I disagree completely with your ideal world description (any user who can execute a function should have the right to examine it to see what it actually does). I suspect that others would agree with me, the result being that there's no universally-agreed-on approach. As a result, what _really_ needs to be done is an extra permission bit added to functions so administrators can control who can view the function body. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
> > In an ideal world, if a user can't modify a function, he/she shouldn't > be > > able to see the source code. If the user can execute the function, then > the > > user should be able to see the signature of the function but not the > body. > > I doubt that's going to happen. Mainly because I disagree completely > with your ideal world description (any user who can execute a function > should have the right to examine it to see what it actually does). > That is like saying anyone that has rights to call a web service should be able to see the source code for it. There should be the ability to create some level of abstraction when appropriate. However, in the current configuration, all users with permission to log in can see all source code. They don't have rights to execute the functions but they can see the source code for them. Shouldn't I be able to revoke both the ability to execute and the ability to see functions? Jon
In response to "Roberts, Jon" <Jon.Roberts@asurion.com>: > > > In an ideal world, if a user can't modify a function, he/she shouldn't > > be > > > able to see the source code. If the user can execute the function, then > > the > > > user should be able to see the signature of the function but not the > > body. > > > > I doubt that's going to happen. Mainly because I disagree completely > > with your ideal world description (any user who can execute a function > > should have the right to examine it to see what it actually does). > > That is like saying anyone that has rights to call a web service should be > able to see the source code for it. I think that's a good idea. If vendors were forced publish their code, we'd have less boneheaded security breaches. > There should be the ability to create > some level of abstraction when appropriate. I agree. If vendors want to have boneheaded security breaches, they should be allowed. > However, in the current configuration, all users with permission to log in > can see all source code. They don't have rights to execute the functions > but they can see the source code for them. Shouldn't I be able to revoke > both the ability to execute and the ability to see functions? Um ... why did you snip my second paragraph where I said exactly this? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 14 Dec 2007 11:18:49 -0500 Bill Moran <wmoran@collaborativefusion.com> wrote: > > That is like saying anyone that has rights to call a web service > > should be able to see the source code for it. > > I think that's a good idea. If vendors were forced publish their > code, we'd have less boneheaded security breaches. Not all closed source code is subject to boneheaded security breaches. I believe that this individuals request is a valid one from a business requirements perspective. > > > There should be the ability to create > > some level of abstraction when appropriate. > > I agree. If vendors want to have boneheaded security breaches, they > should be allowed. It is not up to your or me to make the determination of what people are able to do with their code. > > > However, in the current configuration, all users with permission to > > log in can see all source code. They don't have rights to execute > > the functions but they can see the source code for them. Shouldn't > > I be able to revoke both the ability to execute and the ability to > > see functions? Yes and know. If your functions are interpreted then no, I don't see any reason for this feature, e.g; python,perl,plpgsql,sql,ruby. I can read them on disk anyway. If you want to obfuscate your code I suggest you use a compilable form or a code obfuscation module for your functions (which can be had for at least python, I am sure others as well). Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHYrejATb/zqfZUUQRAjd7AJ9iCqsvsB/7FfvUeLkpCUZ4/14/+wCcCD+w Z4kjQ44yOgfR4ph0SKkUuUI= =v3Fz -----END PGP SIGNATURE-----
Joshua D. Drake wrote: > > > However, in the current configuration, all users with permission to > > > log in can see all source code. They don't have rights to execute > > > the functions but they can see the source code for them. Shouldn't > > > I be able to revoke both the ability to execute and the ability to > > > see functions? > > Yes and know. If your functions are interpreted then no, I don't see > any reason for this feature, e.g; python,perl,plpgsql,sql,ruby. I can > read them on disk anyway. If you have access to the files, which is not necessarily the case. Random users, in particular, won't. Maybe this can be done by revoking privileges to pg_proc. I am sure it can be made to work. It does work for pg_auth_id, and nobody says that "they can read the passwords from disk anyway." -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "We're here to devour each other alive" (Hobbes)
In response to "Joshua D. Drake" <jd@commandprompt.com>: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Fri, 14 Dec 2007 11:18:49 -0500 > Bill Moran <wmoran@collaborativefusion.com> wrote: > > > > That is like saying anyone that has rights to call a web service > > > should be able to see the source code for it. > > > > I think that's a good idea. If vendors were forced publish their > > code, we'd have less boneheaded security breaches. > > Not all closed source code is subject to boneheaded security breaches. > I believe that this individuals request is a valid one from a business > requirements perspective. I could go into all sorts of philosophical debates on this ... for example, "not all drivers are stupid enough to ram their cars into other things, yet we still have seatbelt laws in the US." > > > There should be the ability to create > > > some level of abstraction when appropriate. > > > > I agree. If vendors want to have boneheaded security breaches, they > > should be allowed. > > It is not up to your or me to make the determination of what people are > able to do with their code. That's what I said. Despite my cynical nature, I _do_ believe in allowing people to shoot their own foot. Sometimes it's funny to watch. Any, yes, there are some folks who have very good QA and documentation teams and can avoid pitfalls of security breaches and poorly documented functions with unexpected side-effects. Even if they're not that brilliant, they deserve the right to make their own choices. > > > However, in the current configuration, all users with permission to > > > log in can see all source code. They don't have rights to execute > > > the functions but they can see the source code for them. Shouldn't > > > I be able to revoke both the ability to execute and the ability to > > > see functions? > > Yes and know. If your functions are interpreted then no, I don't see > any reason for this feature, e.g; python,perl,plpgsql,sql,ruby. I can > read them on disk anyway. I disagree here. If they're connecting remotely to PG, they have no direct access to the disk. > If you want to obfuscate your code I suggest you use a compilable form > or a code obfuscation module for your functions (which can be had for > at least python, I am sure others as well). Although this is an excellent suggestion as well. But I still think the feature is potentially useful. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
On Dec 14, 2007 2:03 PM, Bill Moran <wmoran@collaborativefusion.com> wrote: > I disagree here. If they're connecting remotely to PG, they have no > direct access to the disk. pg_read_file? -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
Roberts, Jon <Jon.Roberts@asurion.com> schrieb: > Is it possible yet in PostgreSQL to hide the source code of functions from > users based on role membership? I would like to avoid converting the code > to C to secure the source code and I don't want it obfuscated either. Some days ago i have seen a pl/pgsql- code - obfuscator, iirc somewhere under http://www.pgsql.cz/index.php/PostgreSQL, but i don't know how it works, and i can't find the correkt link now, i'm sorry... (maybe next week in the browser-history, my pc@work) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
I'm not familiar at all with pg_read_file. Is it wide open so a user can read any file they want? Can you not lock it down like utl_file and directories in Oracle? Jon > -----Original Message----- > From: Jonah H. Harris [mailto:jonah.harris@gmail.com] > Sent: Friday, December 14, 2007 3:04 PM > To: Bill Moran > Cc: Joshua D. Drake; Roberts, Jon; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > On Dec 14, 2007 2:03 PM, Bill Moran <wmoran@collaborativefusion.com> > wrote: > > I disagree here. If they're connecting remotely to PG, they have no > > direct access to the disk. > > pg_read_file? > > -- > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > EnterpriseDB Corporation | fax: 732.331.1301 > 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com > Edison, NJ 08837 | http://www.enterprisedb.com/
On Dec 14, 2007 4:24 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Some days ago i have seen a pl/pgsql- code - obfuscator, iirc somewhere > under http://www.pgsql.cz/index.php/PostgreSQL, but i don't know how it > works, and i can't find the correkt link now, i'm sorry... I started one awhile ago... but it may have been part of my mass purge for disk space. I searched that site and can't find one... but it would be a nice-to-have for a lot of users. Of course, I know it's easy to get around obfuscation, but it makes people *think* it's secure, and as JD always says, it just makes it difficult for the average user to understand what it's doing. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
Roberts, Jon escribió: > I'm not familiar at all with pg_read_file. Is it wide open so a user can > read any file they want? Can you not lock it down like utl_file and > directories in Oracle? That function is restricted to superusers. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "The Postgresql hackers have what I call a "NASA space shot" mentality. Quite refreshing in a world of "weekend drag racer" developers." (Scott Marlowe)
Alvaro Herrera pointed out that pg_read_file requires superuser access which these users won't have so revoking access to the function code should be possible. Joshua D. Drake suggested revoking pg_proc but that isn't the source code, it just has the definition of the functions. If it isn't a feature today, what table has the source code in it? Maybe I can revoke that. Jon > -----Original Message----- > From: Jonah H. Harris [mailto:jonah.harris@gmail.com] > Sent: Friday, December 14, 2007 3:04 PM > To: Bill Moran > Cc: Joshua D. Drake; Roberts, Jon; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > On Dec 14, 2007 2:03 PM, Bill Moran <wmoran@collaborativefusion.com> > wrote: > > I disagree here. If they're connecting remotely to PG, they have no > > direct access to the disk. > > pg_read_file? > > -- > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > EnterpriseDB Corporation | fax: 732.331.1301 > 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com > Edison, NJ 08837 | http://www.enterprisedb.com/
On Dec 17, 2007 8:11 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > Alvaro Herrera pointed out that pg_read_file requires superuser access which > these users won't have so revoking access to the function code should be > possible. > > Joshua D. Drake suggested revoking pg_proc but that isn't the source code, > it just has the definition of the functions. > > If it isn't a feature today, what table has the source code in it? Maybe I > can revoke that. the table is pg_proc. you have to revoke select rights from public and the user of interest. be aware this will make it very difficult for that user to do certain things in psql and (especially) pgadmin. it works. a better solution to this problem is to make a language wrapper for pl/pgsql that encrypts the source on disk. afaik, no one is working on th is. it would secure the code from remote users but not necessarily from people logged in to the server. the pg_proc hack works ok though. merlin
Roberts, Jon wrote: > Alvaro Herrera pointed out that pg_read_file requires superuser access which > these users won't have so revoking access to the function code should be > possible. > > Joshua D. Drake suggested revoking pg_proc but that isn't the source code, > it just has the definition of the functions. Actually I suggested using a obfuscation module. > > If it isn't a feature today, what table has the source code in it? Maybe I > can revoke that. If your pl is perl or plpgsql it will be in the prosrc (pro_src?) column in pg_proc. Joshua D. Drake > > > Jon >> -----Original Message----- >> From: Jonah H. Harris [mailto:jonah.harris@gmail.com] >> Sent: Friday, December 14, 2007 3:04 PM >> To: Bill Moran >> Cc: Joshua D. Drake; Roberts, Jon; pgsql-performance@postgresql.org >> Subject: Re: [PERFORM] viewing source code >> >> On Dec 14, 2007 2:03 PM, Bill Moran <wmoran@collaborativefusion.com> >> wrote: >>> I disagree here. If they're connecting remotely to PG, they have no >>> direct access to the disk. >> pg_read_file? >> >> -- >> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 >> EnterpriseDB Corporation | fax: 732.331.1301 >> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com >> Edison, NJ 08837 | http://www.enterprisedb.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On Mon, 17 Dec 2007, Merlin Moncure wrote: > the table is pg_proc. you have to revoke select rights from public > and the user of interest. be aware this will make it very difficult > for that user to do certain things in psql and (especially) pgadmin. > it works. > > a better solution to this problem is to make a language wrapper for > pl/pgsql that encrypts the source on disk. afaik, no one is working on > th is. it would secure the code from remote users but not necessarily > from people logged in to the server. the pg_proc hack works ok > though. > Another enhancement that would improve this situation would be to implement per column permissions as the sql spec has, so that you could revoke select on just the prosrc column and allow clients to retrieve the metadata they need. Kris Jurka
If we are talking about enhancement requests, I would propose we create a role that can be granted/revoked that enables a user to see dictionary objects like source code. Secondly, users should be able to see their own code they write but not others unless they have been granted this dictionary role. Revoking pg_proc isn't good for users that shouldn't see other's code but still need to be able to see their own code. Jon > -----Original Message----- > From: Kris Jurka [mailto:books@ejurka.com] > Sent: Monday, December 17, 2007 10:51 PM > To: Merlin Moncure > Cc: Roberts, Jon; Jonah H. Harris; Bill Moran; Joshua D. Drake; pgsql- > performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > > > On Mon, 17 Dec 2007, Merlin Moncure wrote: > > > the table is pg_proc. you have to revoke select rights from public > > and the user of interest. be aware this will make it very difficult > > for that user to do certain things in psql and (especially) pgadmin. > > it works. > > > > a better solution to this problem is to make a language wrapper for > > pl/pgsql that encrypts the source on disk. afaik, no one is working on > > th is. it would secure the code from remote users but not necessarily > > from people logged in to the server. the pg_proc hack works ok > > though. > > > > Another enhancement that would improve this situation would be to > implement per column permissions as the sql spec has, so that you could > revoke select on just the prosrc column and allow clients to retrieve the > metadata they need. > > Kris Jurka
Roberts, Jon escribió: > Revoking pg_proc isn't good for users that shouldn't see other's code but > still need to be able to see their own code. So create a view on top of pg_proc restricted by current role, and grant select on that to users. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
So you are saying I need to create a view per user to achieve this? That isn't practical for an enterprise level database. I'm basically suggesting row level security that would be implemented for a system table and then RLS could be used for user defined tables too. Jon > -----Original Message----- > From: Alvaro Herrera [mailto:alvherre@commandprompt.com] > Sent: Tuesday, December 18, 2007 12:27 PM > To: Roberts, Jon > Cc: 'Kris Jurka'; Merlin Moncure; Jonah H. Harris; Bill Moran; Joshua D. > Drake; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > Roberts, Jon escribió: > > > Revoking pg_proc isn't good for users that shouldn't see other's code > but > > still need to be able to see their own code. > > So create a view on top of pg_proc restricted by current role, and grant > select on that to users. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc.
Roberts, Jon wrote: > So you are saying I need to create a view per user to achieve this? That > isn't practical for an enterprise level database. Surely you'd just have: CREATE VIEW ... AS SELECT * FROM pg_proc WHERE author=CURRENT_USER -- Richard Huxton Archonet Ltd
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 18 Dec 2007 10:05:46 -0600 "Roberts, Jon" <Jon.Roberts@asurion.com> wrote: > If we are talking about enhancement requests, I would propose we > create a role that can be granted/revoked that enables a user to see > dictionary objects like source code. Secondly, users should be able > to see their own code they write but not others unless they have been > granted this dictionary role. You are likely not going to get any support on an obfuscation front. This is an Open Source project :P Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHaBcBATb/zqfZUUQRAiHPAJ9qXeWnMEKRItO6HKZpqi/c4r5XdQCeMC4M Ivdv24nAt63YkJz/5mr95aQ= =+3Wm -----END PGP SIGNATURE-----
Roberts, Jon escribió: > So you are saying I need to create a view per user to achieve this? That > isn't practical for an enterprise level database. No -- that would be quite impractical indeed. I'm talking about something like revoke all privileges on pg_proc from public; create view limited_pg_proc as select * from pg_proc where proowner = (select oid from pg_authid where rolname = current_user); grant select on limited_pg_proc to public; Of course, it is only a rough sketch. It needs to be improved in a number of ways. But it shows that even with pure SQL the solution is not far; with backend changes it is certainly doable (for example invent a separate "view source" privilege for functions). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 12/18/07, Joshua D. Drake <jd@commandprompt.com> wrote: > On Tue, 18 Dec 2007 10:05:46 -0600 > "Roberts, Jon" <Jon.Roberts@asurion.com> wrote: > > If we are talking about enhancement requests, I would propose we > > create a role that can be granted/revoked that enables a user to see > > dictionary objects like source code. Secondly, users should be able > > to see their own code they write but not others unless they have been > > granted this dictionary role. > You are likely not going to get any support on an obfuscation front. > This is an Open Source project :P Wait, what? This is a DBMS, with some existing security controls regarding the data users are able to access, and the proposal is about increasing the granularity of that control. Arbitrary function bodies are just as much data as anything else in the system. Obfuscation would be something like encrypting the function bodies so that even the owner or administrator cannot view or modify the code without significant reverse engineering. I mean, some people do want that sort of thing, but this proposal isn't even close. Where on earth did "obfuscation" come from?
> -----Original Message----- > From: Trevor Talbot [mailto:quension@gmail.com] > Sent: Wednesday, December 19, 2007 9:45 AM > To: Joshua D. Drake > Cc: Roberts, Jon; Kris Jurka; Merlin Moncure; Jonah H. Harris; Bill Moran; > pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > On 12/18/07, Joshua D. Drake <jd@commandprompt.com> wrote: > > > On Tue, 18 Dec 2007 10:05:46 -0600 > > "Roberts, Jon" <Jon.Roberts@asurion.com> wrote: > > > > If we are talking about enhancement requests, I would propose we > > > create a role that can be granted/revoked that enables a user to see > > > dictionary objects like source code. Secondly, users should be able > > > to see their own code they write but not others unless they have been > > > granted this dictionary role. > > > You are likely not going to get any support on an obfuscation front. > > This is an Open Source project :P > > Wait, what? This is a DBMS, with some existing security controls > regarding the data users are able to access, and the proposal is about > increasing the granularity of that control. Arbitrary function bodies > are just as much data as anything else in the system. > > Obfuscation would be something like encrypting the function bodies so > that even the owner or administrator cannot view or modify the code > without significant reverse engineering. I mean, some people do want > that sort of thing, but this proposal isn't even close. Trevor, thank you for making the proposal clearer. The more I thought about a counter proposal to put views on pg_proc, I realized that isn't feasible either. It would break functionality of pgAdmin because users couldn't view their source code with the tool. > > Where on earth did "obfuscation" come from? Don't know. :) This really is a needed feature to make PostgreSQL more attractive to businesses. A more robust security model that better follows commercial products is needed for adoption. Jon
Roberts, Jon escribió: > The more I thought about a counter proposal to put views on pg_proc, I > realized that isn't feasible either. It would break functionality of > pgAdmin because users couldn't view their source code with the tool. What's wrong with patching pgAdmin? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
So your suggestion is first to come up with a query that dynamically checks permissions and create a view for it. Secondly, change pgAdmin to reference this view in place of pg_proc. Actually, it should be extended to all objects in the database, not just pg_proc. If you don't have this dictionary role, you shouldn't be able to look at any of the objects in the database unless you own the object or have been granted rights to the object. I don't know the information_schema that well so I don't know if this is something that should sit on top of PostgreSQL with views and then make subsequent changes to pgAdmin or if the database itself needs to change to handle this. Jon > -----Original Message----- > From: Alvaro Herrera [mailto:alvherre@commandprompt.com] > Sent: Wednesday, December 19, 2007 9:56 AM > To: Roberts, Jon > Cc: 'Trevor Talbot'; Joshua D. Drake; Kris Jurka; Merlin Moncure; Jonah H. > Harris; Bill Moran; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > Roberts, Jon escribió: > > > The more I thought about a counter proposal to put views on pg_proc, I > > realized that isn't feasible either. It would break functionality of > > pgAdmin because users couldn't view their source code with the tool. > > What's wrong with patching pgAdmin? > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > So your suggestion is first to come up with a query that dynamically checks > permissions and create a view for it. Secondly, change pgAdmin to reference > this view in place of pg_proc. Actually, it should be extended to all This solution will not work. It requires cooperation from pgAdmin which is not going to happen and does nothing about psql or direct queries from within pgadmin. Considered from a security/obfuscation perspective, its completely ineffective. As I've said many times, there are only two solutions to this problem: 1. disable permissions to pg_proc and deal with the side effects (mainly, pgadmin being broken). 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that the procedure code is encrypted in pg_proc. this is an ideal solution, but the most work. merlin
> -----Original Message----- > From: Merlin Moncure [mailto:mmoncure@gmail.com] > Sent: Thursday, December 20, 2007 8:30 AM > To: Roberts, Jon > Cc: Alvaro Herrera; Trevor Talbot; Joshua D. Drake; Kris Jurka; Jonah H. > Harris; Bill Moran; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > > So your suggestion is first to come up with a query that dynamically > checks > > permissions and create a view for it. Secondly, change pgAdmin to > reference > > this view in place of pg_proc. Actually, it should be extended to all > > This solution will not work. It requires cooperation from pgAdmin > which is not going to happen and does nothing about psql or direct > queries from within pgadmin. Considered from a security/obfuscation > perspective, its completely ineffective. As I've said many times, > there are only two solutions to this problem: > > 1. disable permissions to pg_proc and deal with the side effects > (mainly, pgadmin being broken). > > 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that > the procedure code is encrypted in pg_proc. this is an ideal > solution, but the most work. > I think there is an option 3. Enhance the db to have this feature built in which is more inline with commercial databases. This feature would drive adoption of PostgreSQL. It isn't feasible in most companies to allow everyone with access to the database to view all code written by anyone and everyone. For instance, you could have a Finance group writing functions to calculate your financial earnings. These calculations could be changing frequently and should only be visible to a small group of people. If the calculations were visible by anyone with database access, they could figure out earnings prior to the release and thus have inside information on the stock. Jon
Roberts, Jon wrote: > > > This really is a needed feature to make PostgreSQL more attractive to > businesses. A more robust security model that better follows commercial > products is needed for adoption. > I would argue that commercial products need to get a clue and stop playing bondage with their users to help stop their imminent and frankly obvious downfall from the Open Source competition. This "feature" as it is called can be developed externally and has zero reason to exist within PostgreSQL. If the feature has the level of demand that people think that it does, then the external project will be very successful and that's cool. Sincerely, Joshua D. Drake
On Dec 20, 2007, at 11:30 AM, Roberts, Jon wrote: > > >> -----Original Message----- >> From: Merlin Moncure [mailto:mmoncure@gmail.com] >> Sent: Thursday, December 20, 2007 8:30 AM >> To: Roberts, Jon >> Cc: Alvaro Herrera; Trevor Talbot; Joshua D. Drake; Kris Jurka; >> Jonah H. >> Harris; Bill Moran; pgsql-performance@postgresql.org >> Subject: Re: [PERFORM] viewing source code >> >> On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> >> wrote: >>> So your suggestion is first to come up with a query that dynamically >> checks >>> permissions and create a view for it. Secondly, change pgAdmin to >> reference >>> this view in place of pg_proc. Actually, it should be extended >>> to all >> >> This solution will not work. It requires cooperation from pgAdmin >> which is not going to happen and does nothing about psql or direct >> queries from within pgadmin. Considered from a security/obfuscation >> perspective, its completely ineffective. As I've said many times, >> there are only two solutions to this problem: >> >> 1. disable permissions to pg_proc and deal with the side effects >> (mainly, pgadmin being broken). >> >> 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that >> the procedure code is encrypted in pg_proc. this is an ideal >> solution, but the most work. >> > > I think there is an option 3. Enhance the db to have this feature > built in > which is more inline with commercial databases. This feature would > drive > adoption of PostgreSQL. It isn't feasible in most companies to allow > everyone with access to the database to view all code written by > anyone and > everyone. > > For instance, you could have a Finance group writing functions to > calculate > your financial earnings. These calculations could be changing > frequently > and should only be visible to a small group of people. If the > calculations > were visible by anyone with database access, they could figure out > earnings > prior to the release and thus have inside information on the stock. Does everyone in your organization have login access to your database? That seems like the main issue. Perhaps you should stick an application server in between. The application server could also upload functions from the "Finance group" and ensure that no one can see stored procedures. Cheers, M
On Dec 20, 2007 11:30 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > > -----Original Message----- > > From: Merlin Moncure [mailto:mmoncure@gmail.com] > > Sent: Thursday, December 20, 2007 8:30 AM > > To: Roberts, Jon > > Cc: Alvaro Herrera; Trevor Talbot; Joshua D. Drake; Kris Jurka; Jonah H. > > Harris; Bill Moran; pgsql-performance@postgresql.org > > Subject: Re: [PERFORM] viewing source code > > > > > On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > > > So your suggestion is first to come up with a query that dynamically > > checks > > > permissions and create a view for it. Secondly, change pgAdmin to > > reference > > > this view in place of pg_proc. Actually, it should be extended to all > > > > This solution will not work. It requires cooperation from pgAdmin > > which is not going to happen and does nothing about psql or direct > > queries from within pgadmin. Considered from a security/obfuscation > > perspective, its completely ineffective. As I've said many times, > > there are only two solutions to this problem: > > > > 1. disable permissions to pg_proc and deal with the side effects > > (mainly, pgadmin being broken). > > > > 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that > > the procedure code is encrypted in pg_proc. this is an ideal > > solution, but the most work. > > > > I think there is an option 3. Enhance the db to have this feature built in > which is more inline with commercial databases. This feature would drive > adoption of PostgreSQL. It isn't feasible in most companies to allow > everyone with access to the database to view all code written by anyone and > everyone. option 3 is really option 2. having this option is all the flexibility you need. i understand in certain cases you want to prevent code from being available to see from certain users, but i don't buy the adoption argument...most people dont actually become aware of implications of pg_proc until after development has started. simply having a choice, either directly community supported or maintained outside in pgfoundry should be enough. in the majority of cases, who can see the code doesn't matter. i do however strongly disagree that hiding the code is bad in principle... i was in the past in this exact situation for business reasons out of my control (this is why I know the pgadmin route wont work, i've chased down that angle already), so i'm highly sympathetic to people who need to do this. i opted for revoke from pg_proc route, which, while crude was highly effective. merlin
On Dec 20, 2007 12:39 PM, A.M. <agentm@themactionfaction.com> wrote: > On Dec 20, 2007, at 11:30 AM, Roberts, Jon wrote: > >> On Dec 20, 2007 9:07 AM, Roberts, Jon <Jon.Roberts@asurion.com> > >> wrote: > >>> So your suggestion is first to come up with a query that dynamically > >> checks > >>> permissions and create a view for it. Secondly, change pgAdmin to > >> reference > >>> this view in place of pg_proc. Actually, it should be extended > >>> to all > >> > >> This solution will not work. It requires cooperation from pgAdmin > >> which is not going to happen and does nothing about psql or direct > >> queries from within pgadmin. Considered from a security/obfuscation > >> perspective, its completely ineffective. As I've said many times, > >> there are only two solutions to this problem: > >> > >> 1. disable permissions to pg_proc and deal with the side effects > >> (mainly, pgadmin being broken). > >> > >> 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that > >> the procedure code is encrypted in pg_proc. this is an ideal > >> solution, but the most work. > >> > > > > I think there is an option 3. Enhance the db to have this feature > > built in > > which is more inline with commercial databases. This feature would > > drive > > adoption of PostgreSQL. It isn't feasible in most companies to allow > > everyone with access to the database to view all code written by > > anyone and > > everyone. > > > > For instance, you could have a Finance group writing functions to > > calculate > > your financial earnings. These calculations could be changing > > frequently > > and should only be visible to a small group of people. If the > > calculations > > were visible by anyone with database access, they could figure out > > earnings > > prior to the release and thus have inside information on the stock. > > Does everyone in your organization have login access to your > database? That seems like the main issue. Perhaps you should stick an > application server in between. The application server could also > upload functions from the "Finance group" and ensure that no one can > see stored procedures. forcing all database access through an app server is a (too) high price to pay in many scenarios. while it works great for some things (web apps), in many companies the db is the 'brain' of the company that must serve all kinds of different purposes across many interfaces. for example, ups provides software that communicates with databases over odbc for purposes to apply tracking #s to parts. think about all the report engines, etc etc that run over those type of interfaces. merlin
Roberts, Jon escribió: > So your suggestion is first to come up with a query that dynamically checks > permissions and create a view for it. Secondly, change pgAdmin to reference > this view in place of pg_proc. Actually, it should be extended to all > objects in the database, not just pg_proc. If you don't have this > dictionary role, you shouldn't be able to look at any of the objects in the > database unless you own the object or have been granted rights to the > object. Right. Another thing that just occured to me was to rename pg_proc to something else, and create the restricted view using the pg_proc name. This sounds dangerous in terms of internals, but actually the system catalogs are invoked by OID not name, so maybe it will still work. You do need to make sure that superusers continue to see all functions though ... (the view test should really be "does the current user have access to this function".) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 12/20/07, Joshua D. Drake <jd@commandprompt.com> wrote: > Roberts, Jon wrote: > > This really is a needed feature to make PostgreSQL more attractive to > > businesses. A more robust security model that better follows commercial > > products is needed for adoption. > I would argue that commercial products need to get a clue and stop > playing bondage with their users to help stop their imminent and frankly > obvious downfall from the Open Source competition. I'm still not seeing where your comments are actually coming from, and I can't decipher your argument as a result. Exactly what is it about fine-grained security controls that is "playing bondage with their users"? > This "feature" as it is called can be developed externally and has zero > reason to exist within PostgreSQL. If the feature has the level of > demand that people think that it does, then the external project will be > very successful and that's cool. I'm unsure of what you consider "external" here. Is SE-PostgreSQL the type of thing you mean?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 20 Dec 2007 10:47:53 -0800 "Trevor Talbot" <quension@gmail.com> wrote: > > > This "feature" as it is called can be developed externally and has > > zero reason to exist within PostgreSQL. If the feature has the > > level of demand that people think that it does, then the external > > project will be very successful and that's cool. > > I'm unsure of what you consider "external" here. Is SE-PostgreSQL the > type of thing you mean? I don't know that it needs to be that extensive. I noted elsewhere in the thread the idea of a plpgsql_s. I think that is an interesting idea. I just don't think it needs to be incorporated into postgresql-core. If we were to remove viewing source from postgresql-core an interesting possibility would be to remove prosrc from pg_proc altogether. Instead prosrc becomes a lookup field to the prosrc table. The prosrc table would only be accessible from a called function (thus you can't grab source via select). Of course this wouldn't apply to superusers but any normal user would not be able to so much as look sideways at the prosrc table. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHasUbATb/zqfZUUQRAqINAJsFpvPkUJ6oL/gH7dX4YLsbldIC4gCfdujh /S2b/ZmQU+R54MlO5ATelns= =+2Ut -----END PGP SIGNATURE-----
> -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Thursday, December 20, 2007 10:40 AM > To: Roberts, Jon > Cc: 'Trevor Talbot'; Kris Jurka; Merlin Moncure; Jonah H. Harris; Bill > Moran; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > Roberts, Jon wrote: > > > > > > This really is a needed feature to make PostgreSQL more attractive to > > businesses. A more robust security model that better follows commercial > > products is needed for adoption. > > > > I would argue that commercial products need to get a clue and stop > playing bondage with their users to help stop their imminent and frankly > obvious downfall from the Open Source competition. > > This "feature" as it is called can be developed externally and has zero > reason to exist within PostgreSQL. If the feature has the level of > demand that people think that it does, then the external project will be > very successful and that's cool. > I am obviously hitting on the nerve of the open source community because it contradicts the notion that all source code should be open. However, data needs to be protected. I don't want to share with the world my social security number. I also don't want to share with the world my code I use to manipulate data. My code is an extension of the data and is useless without data. Businesses use databases like crazy. Non-technical people write their own code to analyze data. The stuff they write many times is as valuable as the data itself and should be protected like the data. They don't need or want many times to go through a middle tier to analyze data or through the hassle to obfuscate the code. I think it is foolish to not make PostgreSQL as feature rich when it comes to security as the competition because you are idealistic when it comes to the concept of source code. PostgreSQL is better in many ways to MS SQL Server and equal to many features of Oracle but when it comes to security, it is closer to MS Access. Jon
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 20 Dec 2007 13:45:08 -0600 "Roberts, Jon" <Jon.Roberts@asurion.com> wrote: > I think it is foolish to not make PostgreSQL as feature rich when it > comes to security as the competition because you are idealistic when > it comes to the concept of source code. PostgreSQL is better in many > ways to MS SQL Server and equal to many features of Oracle but when > it comes to security, it is closer to MS Access. If this were true, we would be in a lot more trouble than what you are presenting here. Let's think about what PostgreSQL supports.... GSSAPI Kerberos SSL PAM Role based security Security definer functions Data based views (ability to assign restrictions to particular roles via views) External security providers ... Sounds like you have some reading to do before you make broad assumptions about PostgreSQL security. Everything you want to do is possible with Postgresql today. You may have write an executor function to hide your code but you can do it. You may not be able to do it with plpgsql but you certainly could with any of the other procedural languages. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHashRATb/zqfZUUQRAmlRAJoDWr44yld8Ow2qdcvoUdtMiOs5AgCfQ/e7 4OGIPE6ZAHPQPCQ/Mc/dusk= =73a1 -----END PGP SIGNATURE-----
> -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Thursday, December 20, 2007 1:54 PM > To: Roberts, Jon > Cc: 'Trevor Talbot'; Kris Jurka; Merlin Moncure; Jonah H. Harris; Bill > Moran; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Thu, 20 Dec 2007 13:45:08 -0600 > "Roberts, Jon" <Jon.Roberts@asurion.com> wrote: > > > I think it is foolish to not make PostgreSQL as feature rich when it > > comes to security as the competition because you are idealistic when > > it comes to the concept of source code. PostgreSQL is better in many > > ways to MS SQL Server and equal to many features of Oracle but when > > it comes to security, it is closer to MS Access. > > If this were true, we would be in a lot more trouble than what you are > presenting here. Let's think about what PostgreSQL supports.... > > GSSAPI > Kerberos > SSL > PAM > Role based security > Security definer functions > Data based views (ability to assign restrictions to particular > roles via views) > External security providers > > ... > > Sounds like you have some reading to do before you make broad > assumptions about PostgreSQL security. Everything you want to do is > possible with Postgresql today. You may have write an executor function > to hide your code but you can do it. You may not be able to do it with > plpgsql but you certainly could with any of the other procedural > languages. > > I'm tired of arguing. You win. I still say this I a needed feature if you want adoption for enterprise level databases in larger companies. The security out of the box is not enough and it is too much to ask everyone implementing PostgreSQL to do it themselves. It will remain a small niche database for small groups of people that have access to everything if they can connect to the database at all. Jon
Joshua D. Drake escribió: > I don't know that it needs to be that extensive. I noted elsewhere in > the thread the idea of a plpgsql_s. I think that is an interesting > idea. I just don't think it needs to be incorporated into > postgresql-core. I don't think that makes any kind of sense. Hiding prosrc should happen on a entirely different level from the language on which the function is written. It's a completely orthogonal decision. Besides, you probably don't want prosrc to be encrypted -- just not accesible to everyone, and it doesn't make sense to have a different _language_ to do that. Also, having an encrypted source code means there must be a decryption key somewhere, which is a pain on itself. And if you expose the crypted prosrc, you are exposing to brute force attacks (to which you are not if prosrc is hidden). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 20 Dec 2007 14:02:57 -0600 "Roberts, Jon" <Jon.Roberts@asurion.com> wrote: > I'm tired of arguing. You win. I still say this I a needed feature > if you want adoption for enterprise level databases in larger > companies. The security out of the box is not enough and it is too > much to ask everyone implementing PostgreSQL to do it themselves. It > will remain a small niche database for small groups of people that > have access to everything if they can connect to the database at > all. Jon, Welcome to Open Source. We argue, we disagree, we try to prove one way or another that on or the other is right. That's life. I do not concur with your assessment in the least, especially the amount of enterprise deployments that actually exist but you are welcome to your opinion and you certainly don't have to accept mine. Have a great Christmas! Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHas3eATb/zqfZUUQRAjmUAKCn1djme0RcGjOgqidUPTCgqSatSgCgnJdV Kpvo0TaYKTE6AQElq3eEKxM= =aPCx -----END PGP SIGNATURE-----
On Dec 20, 2007 3:07 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > I don't think that makes any kind of sense. Hiding prosrc should happen > on a entirely different level from the language on which the function is > written. It's a completely orthogonal decision. Besides, you probably > don't want prosrc to be encrypted -- just not accesible to everyone, and > it doesn't make sense to have a different _language_ to do that. I kinda agree, kinda disagree on this point. You may recall the huge debate a while back where AndrewSN and others were promoting a revised set of views to expose the system catalogs. I thought this was a good idea because the internal catalogs could be hidden from all but the su and the views could be much easier to manipulate in that fashion. The proposal was however shot down for other reasons. I don't really agree that wrapping pl/pgsql with encryptor/decryptor is a bad idea. It's fairly easy to do and very flexible (you don't have to stop at encryption...for example you could run the code through a pre-processor for token substitution). We are not adding a language in the semantic sense, wrapping an existing one. Could probably be extended to multiple languages if desired without too much effort...I think it's only worthwhile bringing in core if you want to hide the internals inside the syntax (CREATE ENCRYPTED FUNCTION foo...) Key management is an issue but easily solved. Uber simple solution is to create a designated table holding the key(s) and use classic permissions to guard it. So I don't agree with your negative comments in this direction but I'm not saying this is the only way to solve this. It is, however the only realistic way to do it without changes to the project or breaking pgadmin. > Also, having an encrypted source code means there must be a decryption > key somewhere, which is a pain on itself. And if you expose the crypted > prosrc, you are exposing to brute force attacks (to which you are not if > prosrc is hidden). i don't buy the brute force argument at all...aes256 or blowfish are perfectly safe. The purpose of encryption is to move sensitive data through public channels...otherwise, why encrypt? merlin
On Thu, Dec 20, 2007 at 03:35:42PM -0500, Merlin Moncure wrote: > > Key management is an issue but easily solved. Uber simple solution is > to create a designated table holding the key(s) and use classic > permissions to guard it. Any security expert worth the title would point and laugh at that suggestion. If the idea is that the contents have to be encrypted to protect them, then it is just not acceptable to have the encryption keys online. That's the sort of "security" that inevitably causes programs to get a reputation for ill-thought-out protections. A
On Thu, Dec 20, 2007 at 01:45:08PM -0600, Roberts, Jon wrote: > Businesses use databases like crazy. Non-technical people write their own > code to analyze data. The stuff they write many times is as valuable as the > data itself and should be protected like the data. They don't need or want > many times to go through a middle tier to analyze data or through the hassle > to obfuscate the code. I'm not opposed to this goal, I should note. I just think that any proposal that is going to go anywhere may need to be better than the one you seem to have made. I think column-level permissions is probably something that is needed. a
Jon.Roberts@asurion.com ("Roberts, Jon") writes:
> I think it is foolish to not make PostgreSQL as feature rich when it
> comes to security as the competition because you are idealistic when
> it comes to the concept of source code. PostgreSQL is better in
> many ways to MS SQL Server and equal to many features of Oracle but
> when it comes to security, it is closer to MS Access.
I don't think that's quite fair.
There most certainly *is* a rich set of security features in
PostgreSQL, with some not-unreasonable defaults, to the point that it
has been pointed at as being 'more secure out of the box' than pretty
well any DBMS.
When people try to put security measures into the database that are
intended to secure it from, yea, verily, even the DBAs, it often
appears that once the feature list gets long enough, the critical
faculties of peoples' brains seem to shut off. They seem to imagine
that since there's a named set of features, that:
a) They are actually usable, and
b) They actually accomplish what they claim to be intended for.
Frequently, neither condition is true.
We've run into cases where attempts to manage fairly complex sets of
role-based security pretty much falls apart (e.g. - "they are not
usable") because for it to work, it's necessary that too many people
understand and follow the security design.
When *reality* is that the developers build things in an ad-hoc
fashion without regard to security, then you've got a ball of mud,
from a security standpoint, that no amount of pounding will force into
the rigidly-defined "security hole."
Note that ad-hoc reporting and analysis will always tend to fall into
this "ball of mud" category. They don't know what data they need
until they start exploring the problem they're given, and that tends
to fit Really Badly with any attempt to strictly define security
access.
Usability (item a) is troublesome :-(.
When you write about trying to hide source code and the likes, we
start thinking of item b), the matter of whether it actually
accomplishes what is claimed.
------------------------------
[Vizzini has just cut the rope The Dread Pirate Roberts is climbing up]
Vizzini: HE DIDN'T FALL? INCONCEIVABLE.
Inigo Montoya: You keep using that word. I do not think it means what
you think it means.
------------------------------
People seem to think that adding passwords, encrypting things, whether
via private or public key encryption, or other obfuscation "provides
security."
Rephrasing Inigo Montoy, I am not so sure that "provides security"
means what you think it means.
I worked one place where I heard a tale of "Payroll of Years Past."
They used to manage executive payroll (for a Fortune 500 company,
hence with some multi-million dollar paycheques!) via temporarily
adding the data into the "peons' system."
They had this clever idea:
- We want to keep the execs' numbers secret from the peons who run the
system.
- Ergo, we'll load the data in, temporarily, run the cheques, whilst
having someone watch that the peons aren't reading anything they
shouldn't.
- Then we'll reverse that data out, and the peons won't know what
they shouldn't know.
Unfortunately, the joker that thought this up didn't realize that the
transactional system would record those sets of changes multiple
times. So anyone looking over the audit logs would see the Secret
Values listed, not once, but twice. And they couldn't purge those
audit logs without bringing down the wrath of the auditors; to do so
would be to invalidate internal controls that they spent more money
than those executive salaries on. Duh.
They quickly shifted Executive Payroll to be managed, by hand, by
certain members of the executives' administrative staff.
That's much the same kind of problem that pops up here. You may
*imagine* that you're hiding the stored procedures, but if they're
sufficiently there that they can be run, they obviously aren't hidden
as far as the DBMS is concerned, and there can't be *too* much of a
veil between DBA and DBMS, otherwise you have to accept that the
system is not intended to be manageable.
We've done some thinking about how to try to hide this information;
unfortunately, a whole lot of the mechanisms people think of simply
don't work. Vendors may *claim* that their products are "secure," but
that may be because they know their customers neither know nor truly
care what the word means; they merely feel reassured because it's
"inconceivable" (in roughly the _Princess Bride_ sense!) to break the
security of the product.
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/spreadsheets.html
Rules of the Evil Overlord #109. "I will see to it that plucky young
lads/lasses in strange clothes and with the accent of an outlander
shall REGULARLY climb some monument in the main square of my capital
and denounce me, claim to know the secret of my power, rally the
masses to rebellion, etc. That way, the citizens will be jaded in case
the real thing ever comes along." <http://www.eviloverlord.com/>
> -----Original Message----- > From: Andrew Sullivan [mailto:ajs@crankycanuck.ca] > Sent: Thursday, December 20, 2007 3:04 PM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] viewing source code > > On Thu, Dec 20, 2007 at 01:45:08PM -0600, Roberts, Jon wrote: > > Businesses use databases like crazy. Non-technical people write their > own > > code to analyze data. The stuff they write many times is as valuable as > the > > data itself and should be protected like the data. They don't need or > want > > many times to go through a middle tier to analyze data or through the > hassle > > to obfuscate the code. > > I'm not opposed to this goal, I should note. I just think that any > proposal > that is going to go anywhere may need to be better than the one you seem > to > have made. > > I think column-level permissions is probably something that is needed. > > a Actually, PostgreSQL already has column level security for pg_stat_activity. select * from pg_stat_activity The current_query column shows "<insufficient privilege>" for all rows except for rows related to my account. It seems that we would want to do exact same thing for pg_proc. Jon
On Thu, Dec 20, 2007 at 03:24:34PM -0600, Roberts, Jon wrote: > > Actually, PostgreSQL already has column level security for pg_stat_activity. Not exactly. pg_stat_activity is a view. But I think someone suggested upthread experimenting with making pg_proc into a view, and making the real table pg_proc_real or something. This might work. A
On Dec 20, 2007 3:52 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote: > On Thu, Dec 20, 2007 at 03:35:42PM -0500, Merlin Moncure wrote: > > > > Key management is an issue but easily solved. Uber simple solution is > > to create a designated table holding the key(s) and use classic > > permissions to guard it. > > Any security expert worth the title would point and laugh at that > suggestion. If the idea is that the contents have to be encrypted to > protect them, then it is just not acceptable to have the encryption keys > online. That's the sort of "security" that inevitably causes programs to > get a reputation for ill-thought-out protections. right, right, thanks for the lecture. I am aware of various issues with key management. I said 'simple' not 'good'. there are many stronger things, like forcing the key to be passed in for each invocation, hmac, etc. etc. I am not making a proposal here and you don't have to denigrate my broad suggestion on a technical detail which is quite distracting from the real issue at hand, btw. I was just suggesting something easy to stop casual browsing. If you want to talk specifics, we can talk specifics... merlin
Merlin Moncure escribió: > I don't really agree that wrapping pl/pgsql with encryptor/decryptor > is a bad idea. Right. But do you agree that it is separate from having hidden prosrc? If we can complete a design then let's shot that way, and aim at encryption sometime in the future :-) I have to note that I would probably not be the one to actually produce a patch in this direction, or even to work on a working, detailed design :-) You just read Joshua's opinion on this issue and I don't think I need to say more :-) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, Dec 20, 2007 at 05:04:33PM -0500, Merlin Moncure wrote: > right, right, thanks for the lecture. I am aware of various issues > with key management. Sorry to come off that way. It wasn't my intention to lecture, but rather to try to stop dead a cure that, in my opinion, is rather worse than the disease. > I said 'simple' not 'good'. I think this is where we disagree. It's simple only because it's no security at all. It's not that it's "not good for some purposes". I'm arguing that it's the sort of approach that shouldn't be used ever, period. We have learned, over and over again, that simple answers that might have been good enough for a very narrow purpose inevitably get used for a slightly wider case than that for which they're appropriate. Anything that involves storing the keys in the same repository as the encrypted data is just begging to be misused that way. > I am not making a proposal here and you don't have to denigrate my > broad suggestion on a technical detail which is quite distracting from > the real issue at hand, btw. This isn't a technical detail that I'm talking about: it's a very serious mistake in the entire approach to which you alluded, and goes to the heart of why I think any talk of somehow encrypting or otherwise obfuscating the contents of pg_proc are a bad idea. Column controls based on user roles are another matter, because they'd be part of the access control system in the DBMS. Best, A
On Thu, 20 Dec 2007, Roberts, Jon wrote: > I still say this I a needed feature if you want adoption for enterprise > level databases in larger companies. It is to some people, and Joshua's opinion is, like everybody else's, just one person's view on what's important. > The security out of the box is not enough and it is too much to ask > everyone implementing PostgreSQL to do it themselves. This is a fair statement coming from the perspective of someone who expects source code protection. What's not a fair statement is to compare the security to Access just because you don't don't understand all the options or think they're too complicated. An inflammatory comment like that is just going to make the very developers who could be helping you here mad. The larger distinction that you might not be aware of here is that PostgreSQL tries to keep things that can be implemented separately out of the database engine itself. As far as the core database group is concerned, if there is a good interface available to provide these features, it would be better to have an external project worry about things like how to make that interface more palatable to people. Look at pgadmin--that's the reason it's a separate project. The right question to ask here may not be "why isn't PostgreSQL adding these features?", but instead "is there a project that makes this low-level capability that already exists easier to use?". Unfortunately for you, making that distinction right now means you're stuck with a little bit of study to see whether any of the existing mechanisms might meet the need you've already got, which is why people have been suggesting things you might look into. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Dec 20, 2007 5:28 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > > I don't really agree that wrapping pl/pgsql with encryptor/decryptor > > is a bad idea. > > Right. But do you agree that it is separate from having hidden prosrc? > If we can complete a design then let's shot that way, and aim at > encryption sometime in the future :-) > > I have to note that I would probably not be the one to actually produce > a patch in this direction, or even to work on a working, detailed design > :-) You just read Joshua's opinion on this issue and I don't think I > need to say more :-) it is separate. doing it hiding prosrc way requires, as i see it a) row/col security, or b) view switcheroo row/col security is great but views (IMO) are a better approach to this generally. archives is of course replete with numerous generally fruitless treatments of both topics. view switcheroo is more of a 'do the ends justify the means' debate. this could turn into a big discussion about what else could be done with the system catalogs. since its not really all that difficult to disable access to pg_proc, and there are relatively few side effects outside of hosing pgadmin, i don't think the ends do justify the means at least in terms of internal server changes. If the necessary features get added in for other reasons, then perhaps... wrapping language handlers is interesting from other angles too. many times I've wanted to do preprocessing on functions without sacrificing ability of pasting from psql. merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
> I don't really agree that wrapping pl/pgsql with encryptor/decryptor
> is a bad idea.
It's quite a good idea, because it has more than zero chance of
succeeding politically in the community.
The fundamental reason why preventing access to pg_proc.prosrc won't
happen is this: all the pain (and there will be plenty) will be
inflicted on people who get none of the benefit (because they don't give
a damn about hiding their own functions' code). The folks who want
function hiding can shout all they want, but as long as there is a very
sizable fraction of the community who flat out *don't* want it, it's
not going to get applied.
Encrypted function bodies avoid this problem because they inflict no
performance penalty, operational complexity, or client-code breakage
on people who don't use the feature. They are arguably also a better
solution because they can guard against more sorts of threats than
a column-hiding solution can.
I don't deny that the key-management problem is interesting, but it
seems soluble; moreover, the difficulties that people have pointed to
are nothing but an attempt to move the goalposts, because they
correspond to requirements that a column-hiding solution would never
meet at all.
So if you want something other than endless arguments to happen,
come up with a nice key-management design for encrypted function
bodies.
regards, tom lane
wrapping pl/pgsql with encryptor/decryptorIt's quite a good idea, because it has more than zero chance of
succeeding politically in the community.
It's additionally a good idea because the other big database is using the same approach. Easier sell to phb.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Is this a TODO? --------------------------------------------------------------------------- Tom Lane wrote: > "Merlin Moncure" <mmoncure@gmail.com> writes: > > I don't really agree that wrapping pl/pgsql with encryptor/decryptor > > is a bad idea. > > It's quite a good idea, because it has more than zero chance of > succeeding politically in the community. > > The fundamental reason why preventing access to pg_proc.prosrc won't > happen is this: all the pain (and there will be plenty) will be > inflicted on people who get none of the benefit (because they don't give > a damn about hiding their own functions' code). The folks who want > function hiding can shout all they want, but as long as there is a very > sizable fraction of the community who flat out *don't* want it, it's > not going to get applied. > > Encrypted function bodies avoid this problem because they inflict no > performance penalty, operational complexity, or client-code breakage > on people who don't use the feature. They are arguably also a better > solution because they can guard against more sorts of threats than > a column-hiding solution can. > > I don't deny that the key-management problem is interesting, but it > seems soluble; moreover, the difficulties that people have pointed to > are nothing but an attempt to move the goalposts, because they > correspond to requirements that a column-hiding solution would never > meet at all. > > So if you want something other than endless arguments to happen, > come up with a nice key-management design for encrypted function > bodies. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Dec 21, 2007 9:34 AM, Bruce Momjian <bruce@momjian.us> wrote: > > Is this a TODO? > I don't think so, at least not yet (it's not clear what if anything there is to do). see: http://archives.postgresql.org/pgsql-hackers/2007-12/msg00788.php merlin
Bruce Momjian wrote: > Is this a TODO? > > --------------------------------------------------------------------------- > > Tom Lane wrote: >> "Merlin Moncure" <mmoncure@gmail.com> writes: >>> I don't really agree that wrapping pl/pgsql with encryptor/decryptor >>> is a bad idea. >> It's quite a good idea, because it has more than zero chance of >> succeeding politically in the community. >> >> The fundamental reason why preventing access to pg_proc.prosrc won't >> happen is this: all the pain (and there will be plenty) will be >> inflicted on people who get none of the benefit (because they don't give >> a damn about hiding their own functions' code). The folks who want >> function hiding can shout all they want, but as long as there is a very >> sizable fraction of the community who flat out *don't* want it, it's >> not going to get applied. >> >> Encrypted function bodies avoid this problem because they inflict no >> performance penalty, operational complexity, or client-code breakage >> on people who don't use the feature. They are arguably also a better >> solution because they can guard against more sorts of threats than >> a column-hiding solution can. >> >> I don't deny that the key-management problem is interesting, but it >> seems soluble; moreover, the difficulties that people have pointed to >> are nothing but an attempt to move the goalposts, because they >> correspond to requirements that a column-hiding solution would never >> meet at all. >> >> So if you want something other than endless arguments to happen, >> come up with a nice key-management design for encrypted function >> bodies. I keep thinking the problem of keys is similar that of Apache servers which use certificates that require passphrases. When the server is started, the passphrase is entered on the command line. -- Dan Langille - http://www.langille.org/
It seems like a lot of people only saw "hide source code" in the original message, and then went off on tangents that don't have anything to do with the request. Again: On 12/14/07, Roberts, Jon <Jon.Roberts@asurion.com> wrote: > Is it possible yet in PostgreSQL to hide the source code of functions from > users based on role membership? I would like to avoid converting the code > to C to secure the source code and I don't want it obfuscated either. > > In an ideal world, if a user can't modify a function, he/she shouldn't be > able to see the source code. If the user can execute the function, then the > user should be able to see the signature of the function but not the body. As a Role under PostgreSQL, I can create tables, views, functions, etc. As the owner of those objects, I can control what other roles can view data through them, and what roles can modify them. However, unlike tables, I cannot control what roles can view the data contained within my PL functions (body). That's it. A very simple problem. One that has absolutely nothing whatsoever to do with encrypted storage on disk or hiding things from DBAs or superusers. I'm surprised this group ended up so far off point. It's not as if objecting to this requires a bunch of abstract hyperbole, just a simple "it's not worth the effort and it's considered a bad idea to put security-senstive data inside PL function bodies". On 12/20/07, Joshua D. Drake <jd@commandprompt.com> wrote: > On Thu, 20 Dec 2007 10:47:53 -0800 > "Trevor Talbot" <quension@gmail.com> wrote: > > > This "feature" as it is called can be developed externally and has > > > zero reason to exist within PostgreSQL. If the feature has the > > > level of demand that people think that it does, then the external > > > project will be very successful and that's cool. > > I'm unsure of what you consider "external" here. Is SE-PostgreSQL the > > type of thing you mean? > I don't know that it needs to be that extensive. I noted elsewhere in > the thread the idea of a plpgsql_s. I think that is an interesting > idea. I just don't think it needs to be incorporated into > postgresql-core. I was trying to get a handle on whether you meant external as in middleware, or external as in third-party patches to PostgreSQL. The OP's request doesn't necessarily need something as extensive as SE-PostgreSQL, but it needs to be on the same level: something that affects the database surface clients see, not apps behind middleware. On 12/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Merlin Moncure" <mmoncure@gmail.com> writes: > > I don't really agree that wrapping pl/pgsql with encryptor/decryptor > > is a bad idea. > It's quite a good idea, because it has more than zero chance of > succeeding politically in the community. Something that looks a lot like encryption of the entire database is more likely to succeed politically than a simple addition to PostgreSQL's existing role-based security model? Really? It's not like I can claim otherwise, I'm just wondering if I woke up in an alternate universe this morning... > The fundamental reason why preventing access to pg_proc.prosrc won't > happen is this: all the pain (and there will be plenty) will be > inflicted on people who get none of the benefit (because they don't give > a damn about hiding their own functions' code). The folks who want > function hiding can shout all they want, but as long as there is a very > sizable fraction of the community who flat out *don't* want it, it's > not going to get applied. I don't understand. Can you give an example of pain you see coming?
I wrote: > That's it. A very simple problem. It was hinted to me off-list that my mail was fanning the flames, so to clarify: when I say things like the above, I mean conceptually. I think there might be a shared pool of knowledge that says it's anything but simple in practical terms, but that hasn't been communicated clearly in this thread. That's what I was getting at.
"Trevor Talbot" <quension@gmail.com> writes:
> Something that looks a lot like encryption of the entire database is
> more likely to succeed politically than a simple addition to
> PostgreSQL's existing role-based security model? Really?
I guess that you have failed to understand any of the discussion.
Adding a GRANT SEESOURCECODE ON FUNCTION type of privilege would
perhaps be a "simple addition to PostgreSQL's existing security model",
but it would accomplish precisely zero, because anyone smart enough
to be using Postgres in the first place would simply look directly into
pg_proc to see the function body. In order to make it into a meaningful
restriction, we would have to restrict direct SQL access to the system
catalogs --- at least that one --- which would break vast quantities of
stuff. The fact that psql, pg_dump, and pgAdmin would all break is
daunting in itself, but those are likely just the tip of the iceberg.
Looking at the system catalogs has always been part of the culture
around here, and it's impossible to guess how many one-off client
programs do it. I'd bet on "a lot", though.
Another problem is that you're facing a cultural bias. You quote
> On 12/14/07, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
>> In an ideal world, if a user can't modify a function, he/she shouldn't be
>> able to see the source code.
but what neither of you apparently grasp is that to most open source
programmers, that's not an "ideal world", that's a pretty good
description of hell on earth. There is no way that you will persuade
this project that hiding source code should be the default behavior,
or even especially easy.
We're willing to think about ways to hide source code where there is a
really serious commercial imperative to do it --- but in cases like
that, schemes that are as easily broken into as a SQL-level GRANT are
probably not good enough anyhow. And thus we arrive at encrypted source
text and discussions of where to keep the key.
Once again: this discussion is 100% off-topic for pgsql-performance.
If you want to keep talking about it, please join the child thread on
pgsql-hackers.
regards, tom lane
On Thu, Dec 20, 2007 at 02:02:57PM -0600, Roberts, Jon wrote: >I'm tired of arguing. You win. I still say this I a needed feature if you >want adoption for enterprise level databases in larger companies. The >security out of the box is not enough What a classic "I want this, and if it isn't implemented postgres sucks" argument. Mike Stone