Обсуждение: Re: [pgsql-hackers] Allow GRANT/REVOKE permissions to be applied to all schema

Matt,

> a) accept some sort of wildcard for the grant on table syntax:
>     GRANT ... ON TABLE schema.*
>
> b) use something like CASCADE for the grant on schema syntax:
>     GRANT ... ON SCHEMA CASCADE
>     In this case the grant on schema's need to swallow the permissions
>     (SELECT, INSERT, UPDATE ...) which are intended for TABLES. This
> seems to me
>     kind of strange.
>
> therefore I vote for Syntax a)
>
> What do you think?

Can't say I like either.     I'd prefer:

GRANT [PERM] ON ALL TABLES IN SCHEMA [schemaname] TO [user];

In fact, it would be good if you could multiplex this so that applicable
grants could be performed on all objects, for example:

GRANT SELECT ON ALL TABLES, VIEWS IN SCHEMA public TO php-user;

Of course, if you enhanced this further, we'd be storing a "default
permission" to each *new* table/view/function/etc. in the schema definition,
which would be the ideal.  That way, this command:

GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user;

.. would set the defaults for any NEW tables created in public, and this
command:

GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user CASCADE;

... would grant for existing tables as well.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:
> Can't say I like either.     I'd prefer:

> GRANT [PERM] ON ALL TABLES IN SCHEMA [schemaname] TO [user];

I agree that this syntax seems more SQL-ish than relying on a wildcard.

> GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user;

> .. would set the defaults for any NEW tables created in public, and this 
> command:

> GRANT SELECT, UPDATE, INSERT ON TABLES IN SCHEMA public TO php-user CASCADE;

> ... would grant for existing tables as well.

This however seems a rather whimsical reinvention of the meaning of
CASCADE.  I'm not sure if we really need to support both immediate and
delayed inheritance of privileges from a schema, but if we do, let's
please use some other keyword than CASCADE to distinguish the cases.
Also it'd probably be better if they were independent commands, rather
than one subsuming the other as you suggest.
        regards, tom lane

Tom,

> This however seems a rather whimsical reinvention of the meaning of
> CASCADE.  I'm not sure if we really need to support both immediate and
> delayed inheritance of privileges from a schema, but if we do, let's
> please use some other keyword than CASCADE to distinguish the cases.
> Also it'd probably be better if they were independent commands, rather
> than one subsuming the other as you suggest.

Hmm, what about using, ALL and NEW?   i.e.

GRANT SELECT ON NEW TABLES IN public TO phpuser;
GRANT SELECT ON ALL TABLES IN public TO phpuser;

The first sets defaults for new objects, the second sets permissions on
existing tables, and this:

GRANT SELECT ON ALL, NEW TABLES IN public TO phpuser;

... does both.

Of course, this assumes that tables named "new" or "all" are not possible ...
are those reserved?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:
> Hmm, what about using, ALL and NEW?   i.e.

> GRANT SELECT ON NEW TABLES IN public TO phpuser;
> GRANT SELECT ON ALL TABLES IN public TO phpuser;

That seems good to me.  More generally it would be
GRANT perm [,...] ON NEW/ALL TABLES IN schema [,...] TO user [,...]

which leaves us the freedom to later add
GRANT perm [,...] ON NEW/ALL FUNCTIONS IN schema [,...] TO user [,...]

and so on.

> Of course, this assumes that tables named "new" or "all" are not possible ...
> are those reserved?

As long as you aren't allowed to omit the word "TABLES", I think we can
do this without making ALL or NEW any more reserved than they are
already.  I haven't actually tried it in bison though.

TABLES (and later FUNCTIONS, etc) isn't a keyword at all right now IIRC,
but as long as we can do this without making it reserved, I think that's
not a big objection.
        regards, tom lane

Josh Berkus <josh@agliodbs.com> writes:
> GRANT SELECT ON ALL, NEW TABLES IN public TO phpuser;
> ... does both.

Ah, I overlooked that part of your message.  I think the above probably
doesn't work in bison, but if not we could spell it like
 GRANT SELECT ON ALL AND NEW TABLES IN public TO phpuser;

Or just make the user enter two commands for this case.  Aside from
syntactic simplicity, that might be a good idea anyway.  The NEW TABLES
case is *fundamentally* different from every other form of GRANT, in
that it causes future actions.  So it might be a wise idea from the
standpoint of understandability to keep it as a separate command from
the immediate-acting ALL TABLES.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Or just make the user enter two commands for this case.  Aside from
> syntactic simplicity, that might be a good idea anyway.  The NEW TABLES
> case is *fundamentally* different from every other form of GRANT, in
> that it causes future actions.  So it might be a wise idea from the
> standpoint of understandability to keep it as a separate command from
> the immediate-acting ALL TABLES.

I agree with this- issueing two seperate commands in this instance seems
like it'd be fine and not terribly onerous.
Stephen

On Saturday 29 January 2005 09:14, Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Or just make the user enter two commands for this case.  Aside from
> > syntactic simplicity, that might be a good idea anyway.  The NEW TABLES
> > case is *fundamentally* different from every other form of GRANT, in
> > that it causes future actions.  So it might be a wise idea from the
> > standpoint of understandability to keep it as a separate command from
> > the immediate-acting ALL TABLES.
>
> I agree with this- issueing two seperate commands in this instance seems
> like it'd be fine and not terribly onerous.
>

In general I'd agree, although as I start to think of the different 
combinations of tables/views/functions/sequences/types/operators/etc.. my 
head does start to spin.  

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Hi *,

I will start implementing this stuff based on this syntax:

GRANT SELECT ON ALL TABLES IN public TO phpuser;
GRANT SELECT ON NEW TABLES IN public TO phpuser;

so there are two seperate commands to use.

is everybody fine with this aproach?

cheers,

Matthias

PS.: Tom, shouldn't we mention the fact, that one can grant on views 
and sequences as well in the documentation?



Am 29.01.2005 um 01:43 schrieb Tom Lane:

> Josh Berkus <josh@agliodbs.com> writes:
>> GRANT SELECT ON ALL, NEW TABLES IN public TO phpuser;
>> ... does both.
>
> Ah, I overlooked that part of your message.  I think the above probably
> doesn't work in bison, but if not we could spell it like
>
>   GRANT SELECT ON ALL AND NEW TABLES IN public TO phpuser;
>
> Or just make the user enter two commands for this case.  Aside from
> syntactic simplicity, that might be a good idea anyway.  The NEW TABLES
> case is *fundamentally* different from every other form of GRANT, in
> that it causes future actions.  So it might be a wise idea from the
> standpoint of understandability to keep it as a separate command from
> the immediate-acting ALL TABLES.
>
>             regards, tom lane
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
----------------------------------------------------------------------
Matthias Schmidt
Viehtriftstr. 49

67346 Speyer
GERMANY

Tel.: +49 6232 4867
Fax.: +49 6232 640089

I have added this syntax to the TODO list:

* Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
 The proposed syntax is:       GRANT SELECT ON ALL TABLES IN public TO phpuser;       GRANT SELECT ON NEW TABLES IN
publicTO phpuser;
 


---------------------------------------------------------------------------

Matthias Schmidt wrote:
> Hi *,
> 
> I will start implementing this stuff based on this syntax:
> 
> GRANT SELECT ON ALL TABLES IN public TO phpuser;
> GRANT SELECT ON NEW TABLES IN public TO phpuser;
> 
> so there are two seperate commands to use.
> 
> is everybody fine with this aproach?
> 
> cheers,
> 
> Matthias
> 
> PS.: Tom, shouldn't we mention the fact, that one can grant on views 
> and sequences as well in the documentation?
> 
> 
> 
> Am 29.01.2005 um 01:43 schrieb Tom Lane:
> 
> > Josh Berkus <josh@agliodbs.com> writes:
> >> GRANT SELECT ON ALL, NEW TABLES IN public TO phpuser;
> >> ... does both.
> >
> > Ah, I overlooked that part of your message.  I think the above probably
> > doesn't work in bison, but if not we could spell it like
> >
> >   GRANT SELECT ON ALL AND NEW TABLES IN public TO phpuser;
> >
> > Or just make the user enter two commands for this case.  Aside from
> > syntactic simplicity, that might be a good idea anyway.  The NEW TABLES
> > case is *fundamentally* different from every other form of GRANT, in
> > that it causes future actions.  So it might be a wise idea from the
> > standpoint of understandability to keep it as a separate command from
> > the immediate-acting ALL TABLES.
> >
> >             regards, tom lane
> >
> > ---------------------------(end of 
> > broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
> >
> ----------------------------------------------------------------------
> Matthias Schmidt
> Viehtriftstr. 49
> 
> 67346 Speyer
> GERMANY
> 
> Tel.: +49 6232 4867
> Fax.: +49 6232 640089
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073