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(at)metrosystems(dot)co(dot)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