Allow GRANT/REVOKE permissions to be applied to all schema objects with one command

Hi Tom + *,

as I learned from severall posts this TODO splits into two distinct
TODO's

TODO1: Allow GRANT/REVOKE permissions to be applied to all schema
objects with one command.
TODO2: Assign Permissions to schemas wich get automatically inherited
by objects created in the schema.

my questions are:

a) should we pursue both of them?
b) how can a syntax for TODO1 look like? Anchored at 'GRANT ... ON
SCHEMA' or 'GRANT ... ON <objecttype>' ?

greetings,

Matthias

----------------------------------------------------------------------
Matthias Schmidt
Viehtriftstr. 49

67346 Speyer
GERMANY

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

Hi everybody,

I thought a little bit on possible GRANT syntax for granting to groups
of objects.

In general, we have the following entities we can grant permissions to:

1. TABLE
2. DATABASE
3. FUNCTION
4. LANGUAGE
5. SCHEMA
6. TABLESPACE

since the requirement is to grant to all objects in a given schema
(hope this still holds true) we are interested in:

TABLE
FUNCTION
LANGUAGE

The others (DATABASE, SCHEMA, TABLESPACE) are basically ruled out. I
suspect that the majority of users like to grant to TABLE's and
FUNCTIONS most of the time rather than LANGUAGE (correct me if i'm
wrong).

This reduces the question to TABLE's and probably FUNCTION's. Now we
have two choices:

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?

cheers,

Matthias

> Hi Tom + *,
>
> as I learned from severall posts this TODO splits into two distinct
> TODO's
>
> TODO1: Allow GRANT/REVOKE permissions to be applied to all schema
> objects with one command.
> TODO2: Assign Permissions to schemas wich get automatically inherited
> by objects created in the schema.
>
> my questions are:
>
> a) should we pursue both of them?
> b) how can a syntax for TODO1 look like? Anchored at 'GRANT ... ON
> SCHEMA' or 'GRANT ... ON <objecttype>' ?
>
> greetings,
>
> Matthias
>
> ----------------------------------------------------------------------
> Matthias Schmidt
> Viehtriftstr. 49
>
> 67346 Speyer
> GERMANY
>
> Tel.: +49 6232 4867
> Fax.: +49 6232 640089
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
----------------------------------------------------------------------
Matthias Schmidt
Viehtriftstr. 49

67346 Speyer
GERMANY

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

On Fri, Jan 28, 2005 at 21:17:46 +0100,
Matthias Schmidt <schmidtm(at)mock-software(dot)de> wrote:
> Hi everybody,
>
> I thought a little bit on possible GRANT syntax for granting to groups
> of objects.
>
> In general, we have the following entities we can grant permissions to:
>
> 1. TABLE
> 2. DATABASE
> 3. FUNCTION
> 4. LANGUAGE
> 5. SCHEMA
> 6. TABLESPACE

You left out SEQUENCES.

On Fri, Jan 28, 2005 at 09:17:46PM +0100, Matthias Schmidt wrote:

> a) accept some sort of wildcard for the grant on table syntax:
> GRANT ... ON TABLE schema.*

What about a list,

GRANT ... ON TABLE table1, table2, ... TO user1, user2, ...;

It would be good if it was a list of wildcards. Not sure if that is
workable.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)

Alvaro Herrera wrote:
> On Fri, Jan 28, 2005 at 09:17:46PM +0100, Matthias Schmidt wrote:
>
> > a) accept some sort of wildcard for the grant on table syntax:
> > GRANT ... ON TABLE schema.*
>
> What about a list,
>
> GRANT ... ON TABLE table1, table2, ... TO user1, user2, ...;
>
> It would be good if it was a list of wildcards. Not sure if that is
> workable.

Actually, what I'd *love* to see is for statements such as GRANT to
allow select result sets to be used in place of arguments, e.g.:

GRANT ... ON TABLE (SELECT table_schema || '.' || table_name FROM
information_schema.tables WHERE table_schema IN ('public', 'postgres'))
TO (SELECT usename from PG_USER WHERE usecatupd = true);

Actually, it would be very nice if all DDL statements could work that
way.

--
Kevin Brown kevin(at)sysexperts(dot)com

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> What about a list,

> GRANT ... ON TABLE table1, table2, ... TO user1, user2, ...;

We already allow a list (and have since at least 7.0).

> It would be good if it was a list of wildcards.

I'm a bit itchy about allowing wildcards --- it doesn't seem to fit well
with SQL syntax. The idea of allowing a subselect that returns a set of
names seems cleaner, though I'm not totally sure what to do to make it
schema-proof. I don't much like the idea that it returns a set of
strings that we then parse as possibly-quoted identifiers --- that opens
all sorts of traps for the unwary who forget to use quote_ident etc.

It would be unambiguous to make the subselect return a set of OIDs, eg

GRANT SELECT ON TABLE (SELECT oid FROM pg_class
WHERE relname LIKE 'some-pattern') TO ...

but exposing OIDs like this seems mighty bletcherous too, not to mention
not very easy to use for someone not intimately familiar with the system
catalog layout.

Josh's last suggestion (ALL TABLES IN someschema) seems to me to be a
reasonable compromise between usefulness, syntactic weirdness, and
hiding implementation details.

regards, tom lane

On Sat, Jan 29, 2005 at 12:01:09AM -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> > What about a list,
>
> > GRANT ... ON TABLE table1, table2, ... TO user1, user2, ...;
>
> We already allow a list (and have since at least 7.0).
>
> > It would be good if it was a list of wildcards.
>
> I'm a bit itchy about allowing wildcards --- it doesn't seem to fit well
> with SQL syntax. The idea of allowing a subselect that returns a set of
> names seems cleaner, though I'm not totally sure what to do to make it
> schema-proof. I don't much like the idea that it returns a set of
> strings that we then parse as possibly-quoted identifiers --- that opens
> all sorts of traps for the unwary who forget to use quote_ident etc.
>
> It would be unambiguous to make the subselect return a set of OIDs, eg
>
> GRANT SELECT ON TABLE (SELECT oid FROM pg_class
> WHERE relname LIKE 'some-pattern') TO ...
>
> but exposing OIDs like this seems mighty bletcherous too, not to mention
> not very easy to use for someone not intimately familiar with the system
> catalog layout.

FWIW, I like the subselect idea. What if there was some kind of column
or function added that returned the data as the command needed it?
Something like ( quote_ident(schema_name) || '.' ||
quote_ident(table_name) ) AS object_id.

Is there a way to go from an OID to a named identifier? That might make
it easier, though I guess it's still kindof exposing OID.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"