Re: GRANT ON ALL IN schema - Mailing list pgsql-hackers

On Wed, Jun 17, 2009 at 12:25 PM, Guillaume
Smet<guillaume.smet@gmail.com> wrote:
> 2009/6/17 Petr Jelinek <pjmodos@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