"A Lau" <lau.studium@gmx.net> writes:
> I recently searched for a way to grant permissions to a new created user for
> all tables in a scheme or database. I just found ways who uses psql and
> scripts. But I'm astonished that there is no way to do it with the "grant
> all on database [schema]..."-option. Actually i thought that a grant on a
> schema or database would recusivly set the accoding permissions to the
> corresponding objects (eg. tables, views...). Is there a way to do it easily
> in SQL-Syntax without psql and scripting? Why it can't be done with the
> grant-operator?
Because the SQL spec says what GRANT should do, and that's not in it.
If you plan in advance for this sort of thing then it can be quite
painless. The best way is to grant permissions on the individual
objects to roles, and then grant membership in those roles to particular
users. Users can come and go but the role permissions grants stay about
the same.
If you didn't plan in advance then you find yourself wishing for
recursive grants, wildcard grants, future grants, and all sorts of
action-at-a-distance ideas that have been seen before on these lists
:-(. Personally I think that scripts and plpgsql functions are
perfectly fine solutions for such needs, mainly because they're easily
customizable. Anything we were to hard-wire into GRANT would solve only
some cases.
regards, tom lane
