GRANT ALL ON recursive for all tables in my scheme?

"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

On Tuesday 04 March 2008, "A Lau" <lau.studium@gmx.net> wrote:

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?

Normally you would have a group role or roles that have appropriate
permissions already, and then just grant role to new_user for group
membership.

--
Alan