Thread: 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

> 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.
>
> 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>' ?

I vote no on a.  Reason: it's relatively easy to do the same thing
already.  However if you do end up doing that, I'd suggest using
'CASCADE'.  This is reasonably consistent with other dependency honoring
commands in pg.

What I would really like to see is TODO2: because this allows greater
flexibility for controlling security.  This is impossible in pg
currently, and may be a slightly more sophisticated job.

Good luck!
Merlin

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'sneed 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@mock-software.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.

> > 1. TABLE
> > 2. DATABASE
> > 3. FUNCTION
> > 4. LANGUAGE
> > 5. SCHEMA
> > 6. TABLESPACE
>
> You left out SEQUENCES.

And views, but he was just listing the acceptable targets to the 'grant'
command.  Basically, views and sequences are treated as tables in this
respect.

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
>> You left out SEQUENCES.

> And views, but he was just listing the acceptable targets to the 'grant'
> command.  Basically, views and sequences are treated as tables in this
> respect.

Right.  Also, LANGUAGEs do not live within schemas, so they drop out of
the consideration as well.

Since FUNCTIONs grant EXECUTE to PUBLIC by default, I don't think we
need to worry too much about them either.  In practice it would be
enough to solve this problem for tables.
        regards, tom lane

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[@]dcc.uchile.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@sysexperts.com

Alvaro Herrera <alvherre@dcc.uchile.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@dcc.uchile.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@decibel.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?"

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

Maybe it is not necessary to extend the syntax to distinguish between
the two cases.  Maybe it's worth considering to have newly created
tables/functions automatically 'GRANTED' with permissions set at the
schema level.  This could perhaps by guarded with GUC variable to
preserve compatibility with previous versions.  That way people like me
who prefer this behavior can just set security at the schema level which
is what we want.

In the event that the schema security changes, I don't mind having to
issue one of Matthias's beefed up GRANTS to get everything right.

This removes confusion and allows more freedom to tinker with the GRANT
sytax.  Plus, it makes having to mess with the system tables/views less
likely, IMO.

Merlin

Hi Merlin,

sorry - I replied to Tom & PG hackers before I saw you last post.

I think it is best to code the basic functionallity within the two new 
commands, and see
how this works out. We can add your idea and others on top of it later 
on.

what about that?

cheers,

Matthias

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

67346 Speyer
GERMANY

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

Matthias wrote:
> I think it is best to code the basic functionallity within the two new
> commands, and see
> how this works out. We can add your idea and others on top of it later
> on.

I think you should do whatever you think is most
appropriate...discussion can of course continue after you have a
workable patch...I'm just a pundit anyways...

Just for your consideration though:

Is this:
GRANT SELECT ON ALL TABLES IN public TO phpuser;
GRANT SELECT ON NEW TABLES IN public TO phpuser;

Really better than this?
GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER
| EXECUTE | CREATE | ALL [ PRIVILEGES ] }    ON SCHEMA schemaname [,
...]   TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT
OPTION ]

A table or function privilege, if it exists, will override anything for
the table.  This will be faster (FWIW) than a multiple table grant
because it's just setting one permission at the schema level.  Someone
else will have to comment on how effectively this will work with
existing implementation, however.

For example, granting 'select' to a schema (which currently is
impossible) solves both the 'all'/'new' problem...it implicitly adds
select privileges to all current tables and new ones...is there really
any reason to distinguish between the two cases?  This is simple and
effective, IMO.

Good luck,
Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Is this:
> GRANT SELECT ON ALL TABLES IN public TO phpuser;
> GRANT SELECT ON NEW TABLES IN public TO phpuser;

> Really better than this?
> GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER
> | EXECUTE | CREATE | ALL [ PRIVILEGES ] }    ON SCHEMA schemaname [,
> ...]

The latter confuses privileges-for-a-schema with privileges-for-a-table.
The proposal would fail completely if we had any similarly spelled
privileges for both schemas and tables.  Which we don't at the moment,
but it would be foolish to assume that we never will --- especially when
you consider extending this idea to non-table objects.

If you want it to work that way (essentially, losing the distinction
between ALL and NEW cases) then you could spell it like

GRANT privileges ON TABLES IN schemas TO users;

which is implementation-wise the same but avoids the assumption about
non overlap of privilege types.

This does seem conceptually cleaner than GRANT ON NEW TABLES, which to
me has a flavor of action-at-a-distance about it.  Does anyone see any
cases where it's really important to have the distinction between acting
on existing tables and acting on future tables?

> This will be faster (FWIW) than a multiple table grant
> because it's just setting one permission at the schema level.

I think this argument is bogus, because the savings in time spent to do
the GRANT will be eaten many times over by extra time spent to look in
two places every time the privileges are checked.  But it might be worth
doing it this way anyway, because of the cleaner conceptual model.
        regards, tom lane

> > GRANT SELECT ON ALL TABLES IN public TO phpuser;
> > GRANT SELECT ON NEW TABLES IN public TO phpuser;
>
> > Really better than this?
> > GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES |
TRIGGER
> > | EXECUTE | CREATE | ALL [ PRIVILEGES ] }    ON SCHEMA schemaname [,
> > ...]
>
> The latter confuses privileges-for-a-schema with
privileges-for-a-table.

Right.
> > This will be faster (FWIW) than a multiple table grant
> > because it's just setting one permission at the schema level.
> I think this argument is bogus, because the savings in time spent to
do

Of course.  GRANT is not really performance sensitive, anyways. Is the
price of looking up a schema a deal breaker here, or is it possible to
avoid it?

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Is the price of looking up a schema a deal breaker here, or is it
> possible to avoid it?

My guess is "no" as to both questions.  I've never seen any profiles
suggesting that permissions-checking is a significant part of query
startup.  In any case, if you assume that the same set of permissions
are going to get checked either way (they're just distributed
differently) then the only direct cost involved would be one additional
syscache fetch, which surely ought not be significant.
        regards, tom lane