On Wed, Jun 17, 2009 at 12:25 PM, Guillaume Smet<guillaume.s...@gmail.com> wrote: > 2009/6/17 Petr Jelinek <pjmo...@pjmodos.net>: >> I agree that Default ACLs are more important and I already offered Stephen >> help on that. But I've seen countless requests for granting on all tables to >> a user and I already got some positive feedback outside of the list, so I >> believe there is demand for this. Also to paraphrase you Tom, by that logic >> you can tell people to write half of administration functionality as plpgsql >> functions. > > Indeed. > > How to do default ACLs and wildcards for GRANT is by far the most > common question asked by our customers. And they don't understand why > it's not by default in PostgreSQL. > > Installing a script/function for that on every database is just painful.
It's not just GRANT, either. I have a script that synchronizes data from <other database product> into PostgreSQL. It runs out of cron. I actually had to set it up so that it counts the total number of rows that it has inserted and fires of an ANALYZE when it hits a certain threshold (that might not be necessary with autovacuum, but this is 8.1); otherwise, the statistics can get so far from reality that the sync script never finishes, because the later stages of the sync query local data modified by earlier stages of the sync. This is not a joke; when there are heavy data modifications, the script MUST fire an ANALYZE midway through to complete in a reasonable amount of time. Now it just so happens that this application runs inside its own schema, and that it doesn't have permission to vacuum any of the other schemas, including the catalog tables. So what do you think happens when it kicks off an ANALYZE? A huge pile of warning messages. Now, since I've been reading pgsql-hackers religiously for a year now, I know that it's very easy to solve this problem by writing a table to issue a query against pg_class and then use quote_ident() to build up a query that we can EXECUTE from within a pl/pgsql loop. However, I certainly didn't know how to do that when I wrote the script two and a half years ago, at which time I had only about six years of experience with the product. Before I started reading -hackers, I relied on reading the fine manual: http://www.postgresql.org/docs/8.3/static/sql-analyze.html ...which doesn't describe how to do this. So I didn't know. But if the file manual had included the syntax "ANALYZE SCHEMA blat", I certainly would have used it, and thus avoided getting 10 emails a week from my cron job for the past two-and-half years. What to do about wildcards is a stickier wicket, and maybe we need to decide that first, but I really don't think we should be discouraging anyone from investigating this stuff and trying to come up with good solutions. There will always be some people for whom a custom PL/pgsql function that directly accesses the catalog tables is the only workable answer, but we can make PostgreSQL a whole lot easier to use by reducing the need to do that for simple cases. ...Robert -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers