[PERFORM] Reinitialising stats once only without restarting
Hi, Sorry but I couldn't find the answer to this... I would like to empty all stats (pg_stat_all_tables probably mostly) so I can get an idea of what's going on now. Is this possible? I didn't want to just go deleting without knowing what it would do... Thanks Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Minimizing dead tuples caused by update triggers
Le jeudi 20 décembre 2007, Decibel! a écrit : A work-around others have used is to have the trigger just insert into a 'staging' table and then periodically take the records from that table and summarize them somewhere else. And you can even use the PgQ skytools implementation to easily have this kind of 'staging'-table with a producer and one or many subscribers. See those references if you're interrested: http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/ http://skytools.projects.postgresql.org/doc/pgq-sql.html http://skytools.projects.postgresql.org/doc/pgq-admin.html http://skytools.projects.postgresql.org/doc/pgq-nodupes.html Hope this helps, regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [PERFORM] Reinitialising stats once only without restarting
Anton Melser escribió: Hi, Sorry but I couldn't find the answer to this... I would like to empty all stats (pg_stat_all_tables probably mostly) so I can get an idea of what's going on now. Is this possible? I didn't want to just go deleting without knowing what it would do... Sure, use pg_stat_reset(); -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Dual core Opterons beating quad core Xeons?
On 12/20/07, Tom Lane [EMAIL PROTECTED] wrote: Scott Marlowe [EMAIL PROTECTED] writes: Well, I'm not even sure if those got applied or were just Tom hacking in the basement or, heck, my fevered imagination. :) For the record, I hack in the attic ... or what I tell the IRS is my third-floor office ... Awesome band name - 'Hacking in the Attic' :) jan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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] Reinitialising stats once only without restarting
On 20/12/2007, Alvaro Herrera [EMAIL PROTECTED] wrote: Anton Melser escribió: Hi, Sorry but I couldn't find the answer to this... I would like to empty all stats (pg_stat_all_tables probably mostly) so I can get an idea of what's going on now. Is this possible? I didn't want to just go deleting without knowing what it would do... Sure, use pg_stat_reset(); Pura vida, gracias. A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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
[PERFORM] performance index scan vs bitmap-seq scan.
The server is running 8.2.5 FreeBSD 6.1 with 3 GB of RAM. I have a table with over 100M rows. I have a unique index (primary key) on column name called aid. The select count(aid) .. does a Bitmap heap scan when the right side condition is above 100,000,000 (if i take one zero off it does a pure index scan). My question : why is the optimizer choosing an Bitmap Heap Scan when count can be done with index. When i set the bitmap scan to off, it selects an seq scan, after which i turn the seq scan off , then it does index scan only but it takes 8 minutes longer than doing a index scan+bitmap scan. Any insight is appreciated. thank you ! g explain select count(aid) from topcat.aid where aid = 1000 and aid = 1; QUERY PLAN Aggregate (cost=2786143.42..2786143.43 rows=1 width=4) - Bitmap Heap Scan on aid (cost=142949.13..2742507.95 rows=17454188 width=4) Recheck Cond: ((aid = 1000) AND (aid = 1)) - Bitmap Index Scan on idx_aid_aid (cost=0.00..142949.13rows=17454188 width=0) Index Cond: ((aid = 1000) AND (aid = 1)) (5 rows) explain select count(aid) from topcat.aid where aid = 10 and aid = 1; QUERY PLAN Aggregate (cost=2786143.42..2786143.43 rows=1 width=4) - Bitmap Heap Scan on aid (cost=142949.13..2742507.95 rows=17454188 width=4) Recheck Cond: ((aid = 10) AND (aid = 1)) - Bitmap Index Scan on idx_aid_aid (cost=0.00..142949.13rows=17454188 width=0) Index Cond: ((aid = 10) AND (aid = 1)) (5 rows) explain select count(aid) from topcat.aid where aid = 100 and aid = 1; QUERY PLAN Aggregate (cost=2786143.42..2786143.43 rows=1 width=4) - Bitmap Heap Scan on aid (cost=142949.13..2742507.95 rows=17454188 width=4) Recheck Cond: ((aid = 100) AND (aid = 1)) - Bitmap Index Scan on idx_aid_aid (cost=0.00..142949.13rows=17454188 width=0) Index Cond: ((aid = 100) AND (aid = 1)) (5 rows) explain select count(aid) from topcat.aid where aid = 100 and aid = 1000; QUERY PLAN - Aggregate (cost=5.58..5.59 rows=1 width=4) - Index Scan using idx_aid_aid on aid (cost=0.00..5.58 rows=1 width=4) Index Cond: ((aid = 100) AND (aid = 1000))
Re: [PERFORM] performance index scan vs bitmap-seq scan.
On Dec 20, 2007 4:06 PM, S Golly [EMAIL PROTECTED] wrote: The server is running 8.2.5 FreeBSD 6.1 with 3 GB of RAM. I have a table with over 100M rows. I have a unique index (primary key) on column name called aid. The select count(aid) .. does a Bitmap heap scan when the right side condition is above 100,000,000 (if i take one zero off it does a pure index scan). My question : why is the optimizer choosing an Bitmap Heap Scan when count can be done with index. Because count can't be done with the index alone. In pgsql the visibility info is in the table itself, so even if it can hit the index, it has to go back and hit the table to be sure if the tuple is visible. ---(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
