Re: Foreign table permissions and cloning

(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