Re: function body actors (was: [PERFORM] viewing source code)
I have similar patch and it works. There is two isues: * we missing column in pg_proc about state (not all procedures are obfuscated), I solved it for plpgsl with using probin. * decrypt is expensive on language handler level. Every session have to do it again and again, better decrypt in system cache or somewhere there. Regards Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: function body actors (was: [PERFORM] viewing source code)
On Dec 21, 2007 3:18 AM, Pavel Stehule [EMAIL PROTECTED] wrote: I have similar patch and it works. There is two isues: * we missing column in pg_proc about state (not all procedures are obfuscated), I solved it for plpgsl with using probin. I was hoping to avoid making any catalog or other changes to support encryption specifically. Maybe your patch stands on its own merits...I missed the original discussion. Do you think the code you wrote can be adapted to do other things besides encryption? * decrypt is expensive on language handler level. Every session have to do it again and again, better decrypt in system cache or somewhere there. Doesn't bother me in the least...and caching unencrypted data is scary. Also, aes256 is pretty fast for what it gives you and function bodies are normally short. The real issue as I see it is where to keep the key. How did you handle that? merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
Bruce Momjian wrote: Is this a TODO? --- Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
On Dec 21, 2007 9:34 AM, Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: function body actors (was: [PERFORM] viewing source code)
On 21/12/2007, Merlin Moncure [EMAIL PROTECTED] wrote: On Dec 21, 2007 3:18 AM, Pavel Stehule [EMAIL PROTECTED] wrote: I have similar patch and it works. There is two isues: * we missing column in pg_proc about state (not all procedures are obfuscated), I solved it for plpgsl with using probin. I was hoping to avoid making any catalog or other changes to support encryption specifically. Maybe your patch stands on its own merits...I missed the original discussion. Do you think the code you wrote can be adapted to do other things besides encryption? I don't know. It was fast hack that just works. It hat to do obfuscation, and it do it well. * decrypt is expensive on language handler level. Every session have to do it again and again, better decrypt in system cache or somewhere there. Doesn't bother me in the least...and caching unencrypted data is scary. Also, aes256 is pretty fast for what it gives you and function bodies are normally short. The real issue as I see it is where to keep the key. How did you handle that? merlin Simply. I use for password some random plpgsql message text and compile it. I though about GUC, and about storing password in postgresql.conf. It's equal to protection level. We cannot protect code on 100%. If you have admin or superuser account and if you know some internal, you can simply get code. http://blog.pgsql.cz/index.php?/archives/10-Obfuscator-PLpgSQL-procedur.html#extended sorry for czech desc Pavel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
Is this a TODO? --- Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] function body actors (was: [PERFORM] viewing source code)
On Fri, Dec 21, 2007 at 12:09:28AM -0500, Merlin Moncure wrote: Maybe a key management solution isn't required. If, instead of strictly wrapping a language with an encryption layer, we provide hooks (actors) that have the ability to operate on the function body when it arrives and leaves pg_proc, we may sidestep the key problem (leaving it to the user) and open up the doors to new functionality at the same time. I like this idea much better, because the same basic mechanism can be used for more than one thing, and it doesn't build in a system that is fundamentally weak. Of course, you _can_ build a weak system this way, but there's an important difference between building a fundamentally weak system and making weak systems possible. A ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: function body actors (was: [PERFORM] viewing source code)
On Fri, Dec 21, 2007 at 12:40:05AM -0500, Tom Lane wrote: whether there is a useful policy for it to implement. Andrew Sullivan argued upthread that we cannot get anywhere with both keys and encrypted function bodies stored in the same database (I hope that's an adequate summary of his point). It is. I'm not a security expert, but I've been spending some time listening to some of them lately. The fundamental problem with a system that stores the keys online in the same repository is not just its potential for compromise, but its brittle failure mode: once the key is recovered, you're hosed. And there's no outside check of key validity, which means attackers have a nicely-contained target to hit. I'm not convinced that he's right, but that has to be the first issue we think about. The whole thing is a dead end if there's no way to do meaningful encryption --- punting an insoluble problem to the user doesn't make it better. Well, one thing you could do with the proposal is build a PKCS#11 actor, that could talk to an HSM. Not everyone needs HSMs, of course, but they do make online key storage much less risky (because correctly designed ones make key recovery practically impossible). So the mechanism can be made effectively secure even for very strong cryptographic uses. Weaker cases might use a two-level key approach, with a data-signing key online all the time to do the basic encryption and validation, but a key-signing key that is always offline or otherwise unavailable from within the system. The key signing key only authenticates (and doesn't encrypt) the data signing key. You could use a different actor for this, to provide an interface to one-way functions or something. This gives you a way to revoke a data-signing key. You couldn't protect already compromised data this way, but at least you could prevent new disclosures. Yes, I'm being hand-wavy now, but I can at least see how these different approaches are possible under the suggestion, so it seems like a possibly fruitful avenue to explore. The more I think about it, actually, the more I like it. A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: On Thu, 20 Dec 2007 10:47:53 -0800 Trevor Talbot [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
Trevor Talbot [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
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:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
On Dec 20, 2007 9:07 AM, Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] viewing source code
-Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] viewing source code
On Dec 20, 2007, at 11:30 AM, Roberts, Jon wrote: -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
On Dec 20, 2007 11:30 AM, Roberts, Jon [EMAIL PROTECTED] wrote: -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
On Dec 20, 2007 12:39 PM, A.M. [EMAIL PROTECTED] wrote: On Dec 20, 2007, at 11:30 AM, Roberts, Jon wrote: On Dec 20, 2007 9:07 AM, Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] viewing source code
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
On 12/20/07, Joshua D. Drake [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] viewing source code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 20 Dec 2007 10:47:53 -0800 Trevor Talbot [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
-Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 20 Dec 2007 13:45:08 -0600 Roberts, Jon [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
-Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] viewing source code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 20 Dec 2007 14:02:57 -0600 Roberts, Jon [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] viewing source code
On Dec 20, 2007 3:07 PM, Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
[EMAIL PROTECTED] (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
Re: [PERFORM] viewing source code
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
On Dec 20, 2007 3:52 PM, Andrew Sullivan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] viewing source code
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] viewing source code
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
On Dec 20, 2007 5:28 PM, Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
Merlin Moncure [EMAIL PROTECTED] 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
Re: [PERFORM] viewing source code
wrapping pl/pgsql with encryptor/decryptor It'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!
function body actors (was: [PERFORM] viewing source code)
On Dec 20, 2007 6:01 PM, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: I don't really agree that wrapping pl/pgsql with encryptor/decryptor is a bad idea. So if you want something other than endless arguments to happen, come up with a nice key-management design for encrypted function bodies. Maybe a key management solution isn't required. If, instead of strictly wrapping a language with an encryption layer, we provide hooks (actors) that have the ability to operate on the function body when it arrives and leaves pg_proc, we may sidestep the key problem (leaving it to the user) and open up the doors to new functionality at the same time. The actor is basically a callback taking the function source code (as text) and returning text for storage in pg_proc. Perhaps some other house keeping variables such as function name, etc. are passed to the actor as parameters as well. The actor operates on the function body going into pg_proc (input actors) and going out (output actors). In either case, the function 'body' is modified if necessary, and may raise an error. The validator can be considered an actor that doesn't modify the body. Ideally, the actors can be written in any pl language. Naturally, dealing with actors is for the superuser. So, I'm suggesting to extend the validator concept, opening it up to the user, giving it more power, and the ability to operate in both directions. The actor will feel a lot like a trigger function. Now, everything is left to the user...by adding an 'encryption' actor to the language (trivial with pg_crypto), the user can broadly encrypt in a manner of their choosing. A clever user might write an actor to encrypt a subset of functions in a language, or register the same language twice with different actors. Since the actor can call out to other functions, we don't limit to a particular key management strategy. Another nice thing is we may solve a problem that's been bothering me for years, namely that 'CREATE FUNCTION' takes a string literal and not a string returning expression. This is pretty limiting...there are a broad range of reasons why I might want to modify the code before it hits pg_proc. For example, with an actor I can now feed the data into the C preprocessor without giving up the ability of pasting the function body directly into psql. This isn't a fully developed idea, and I'm glossing over several areas (for example, syntax to modify actors), and I'm not sure if it's a good idea in principle...I might be missing an obvious reason why this won't work. OTOH, it seems like a really neat way to introduce encryption. comments? is it worth going down this road? merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: function body actors (was: [PERFORM] viewing source code)
Merlin Moncure [EMAIL PROTECTED] writes: On Dec 20, 2007 6:01 PM, Tom Lane [EMAIL PROTECTED] wrote: So if you want something other than endless arguments to happen, come up with a nice key-management design for encrypted function bodies. Maybe a key management solution isn't required. If, instead of strictly wrapping a language with an encryption layer, we provide hooks (actors) that have the ability to operate on the function body when it arrives and leaves pg_proc, we may sidestep the key problem (leaving it to the user) and open up the doors to new functionality at the same time. I think you're focusing on mechanism and ignoring the question of whether there is a useful policy for it to implement. Andrew Sullivan argued upthread that we cannot get anywhere with both keys and encrypted function bodies stored in the same database (I hope that's an adequate summary of his point). I'm not convinced that he's right, but that has to be the first issue we think about. The whole thing is a dead end if there's no way to do meaningful encryption --- punting an insoluble problem to the user doesn't make it better. (This is not to say that you don't have a cute idea there, only that it's not a license to take our eyes off the ball.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: function body actors (was: [PERFORM] viewing source code)
On Dec 21, 2007 12:40 AM, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: On Dec 20, 2007 6:01 PM, Tom Lane [EMAIL PROTECTED] wrote: So if you want something other than endless arguments to happen, come up with a nice key-management design for encrypted function bodies. Maybe a key management solution isn't required. If, instead of strictly wrapping a language with an encryption layer, we provide hooks (actors) that have the ability to operate on the function body when it arrives and leaves pg_proc, we may sidestep the key problem (leaving it to the user) and open up the doors to new functionality at the same time. I think you're focusing on mechanism and ignoring the question of whether there is a useful policy for it to implement. Andrew Sullivan argued upthread that we cannot get anywhere with both keys and encrypted function bodies stored in the same database (I hope that's an adequate summary of his point). I'm not convinced that he's right, but that has to be the first issue we think about. The whole thing is a dead end if there's no way to do meaningful encryption --- punting an insoluble problem to the user doesn't make it better. Well, there is no 'one size fits all' policy. I'm still holding out that we don't need any specific designs for this...simply offering the example in the docs might get people started (just thinking out loud here): create function encrypt_proc(proname text, prosrc_in text, prosrc_out out text) returns text as $$ declare key bytea; begin -- could be a literal variable, field from a private table, temp table, or 3rd party -- literal is dangerous, since its visible until 'create or replaced' but thats maybe ok, depending key := get_key(); select magic_string || encode(encrypt(prosrc_in, key, 'bf'), 'hex'); -- magic string prevents attempting to unencrypt non-encrypted functions. end; $$ language plpgsql; -- ordering of actors is significant...need to think about that alter language plpgsql add actor 'encrypt_proc' on input; alter language plpgsql add actor 'decrypt_proc' on output; If that's not enough, then you have build something more structured, thinking about who provides the key and how the database asks for it. The user would have to seed the session somehow (maybe, stored in a temp table?) with a secret value which would be translated into the key directly on the database or by a 3rd party over a secure channel. The structured approach doesn't appeal to me much though... The temp table idea might not be so hot, since it's trivial for the database admin to see data from other user's temp tables, and maybe we don't want that in some cases. need to think about this some more... merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] viewing source code
-Original Message- From: Trevor Talbot [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: On Tue, 18 Dec 2007 10:05:46 -0600 Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] viewing source code
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:[EMAIL PROTECTED] Sent: Monday, December 17, 2007 10:51 PM To: Merlin Moncure Cc: Roberts, Jon; Jonah H. Harris; Bill Moran; Joshua D. Drake; pgsql- [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
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:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Dec 2007 10:05:46 -0600 Roberts, Jon [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] viewing source code
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
On Dec 17, 2007 8:11 AM, Roberts, Jon [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] viewing source code
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
In response to Roberts, Jon [EMAIL PROTECTED]: 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/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
In response to Roberts, Jon [EMAIL PROTECTED]: 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/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 14 Dec 2007 11:18:49 -0500 Bill Moran [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] viewing source code
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) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
In response to Joshua D. Drake [EMAIL PROTECTED]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 14 Dec 2007 11:18:49 -0500 Bill Moran [EMAIL PROTECTED] 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/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] viewing source code
Roberts, Jon [EMAIL PROTECTED] 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 [EMAIL PROTECTED]) 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° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] viewing source code
On Dec 14, 2007 2:03 PM, Bill Moran [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] viewing source code
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
On Dec 14, 2007 4:24 PM, Andreas Kretschmer [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] viewing source code
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) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
