Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Merlin Moncure [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Merlin Moncure [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Merlin Moncure [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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 ([EMAIL PROTECTED]) La fuerza no está en los medios físicos sino que reside en una voluntad indomable (Gandhi) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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 [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
On Sat, Jan 29, 2005 at 12:01:09AM -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
