SEO

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFB+5pMrzgMPqB3kigRAubnAKCBJL6OQxa7xI72wcxprS 4b07XLogCeOBJS
tsbPV2fXWovjbT1d2KFCtJc=
=vMgI
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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)

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 public TO 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, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org