Re: Foreign table permissions and cloning
От | Shigeru Hanada |
---|---|
Тема | Re: Foreign table permissions and cloning |
Дата | |
Msg-id | 4DAEB112.4050603@metrosystems.co.jp обсуждение исходный текст |
Ответ на | Re: Foreign table permissions and cloning (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: Foreign table permissions and cloning
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
(2011/04/15 3:43), Robert Haas wrote: > On Fri, Apr 1, 2011 at 1:29 AM, Shigeru HANADA > <hanada@metrosystems.co.jp> wrote: >> In addition to the 2nd GRANT above, "GRANT SELECT (colour) ON stuff TO >> user_a" (omitting TABLE) will succeed too because parser assumes that >> the target object is a regular table if object type was TABLE or >> omitted. This inconsistent behavior would be an oversight and need to >> be fixed. > > +1. > >> How about to drop "GRANT xxx ON FOREIGN TABLE foo" syntax support and >> use "GRANT xxx ON [TABLE] foo" for foreign tables? ISTM that "ON >> FOREIGN TABLE" specification is useless because possible privilege >> type would be same as TABLE. > > -1. We should be consistent about treating foreign tables as their > own object type - and the possible privilege types are NOT the same - > only SELECT is supported. > >> Probabry we should mention in GRANT documents that ALL TABLES >> IN SCHEMA is considered to include foreign tables. > > Or else change the behavior so that it doesn't, which would probably be my vote. Thanks for the comments. I agree that foreign table is a different object type from regular table or view in the context of ACL. Attached patch implements along specifications below. It also includes documents and regression tests. Some of regression tests might be redundant and removable. 1) "GRANT privilege [(column_list)] ON [TABLE] TO role" also work for foreign tables as well as regular tables, if specified privilege was SELECT. This might seem little inconsistent but I feel natural to use this syntax for SELECT-able objects. Anyway, such usage can be disabled with trivial fix. 2) "GRANT privilege [(column_list)] ON FOREIGN TABLE table TO role" works only for foreign tables, and accepts only SELECT as privilege. 3) "GRANT privilege ON ALL TABLES IN SCHEMA schema TO role" doesn't affect any foreign table in the schema. 4) "GRANT privilege [(column_list)] ON ALL FOREIGN TABLES IN SCHEMA schema TO role" works for all foreign tables in the schema, but not affect any regular table, view or sequence in the schema. BTW, I noticed some issues about ACL below. Some of them might have to be fixed in future. a) "GRANT privilege(column_list) ON ALL TABLES IN SCHEMA schema" works fine if all of the tables in the schema have all of listed columns. It is not documented, and might be unintentional. b) ALTER DEFAULT PRIVILEGE doesn't support foreign tables. c) Currently SELECT privilege allow user to retrieve contents of the foreign table, but this is different from SQL/MED Standard. Should this difference be mentioned in GRANT document? > [4.14.1 Privileges] > NOTE 9 — Privileges granted on foreign tables are not privileges to > use the data constituting foreign tables, but privileges to use the > definitions of the foreign tables. The privileges to access the data > constituting the foreign tables are enforced by the foreign server, > based on the user mapping. Consequently, a request by an SQL-client > to access external data may raise exceptions. Regards, -- Shigeru Hanada
Вложения
В списке pgsql-hackers по дате отправления: