pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: A Lau <lau.studium@gmx.net>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: GRANT ALL ON recursive for all tables in my scheme?
Date: Tue, 04 Mar 2008 18:14:50 -0500
Message-ID: <27678.1204672490@sss.pgh.pa.us> (raw)
In-Reply-To: <000f01c87e34$6b981fb0$0264a8c0@foxy>
References: <000f01c87e34$6b981fb0$0264a8c0@foxy>

"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



view thread (3+ messages)  latest in thread

Message-ID: <27678.1204672490@sss.pgh.pa.us>
Permalink:  ../27678.1204672490@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/27678.1204672490@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, lau.studium@gmx.net, pgsql-general@postgresql.org
  Subject: Re: GRANT ALL ON recursive for all tables in my scheme?
  In-Reply-To: <27678.1204672490@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox