Re: Foreign table permissions and cloning

On Fri, 1 Apr 2011 00:54:18 +0100
Thom Brown <thom(at)linux(dot)com> wrote:
> I've noticed some weirdness when trying to grant various types of
> permissions on a foreign table and thought I'd report it here:
>
> postgres=# \d stuff
> Foreign table "public.stuff"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer |
> colour | text |
> animal | text |
> Server: file
>
> postgres=# GRANT SELECT (colour) ON FOREIGN TABLE stuff TO user_a;
> ERROR: column privileges are only valid for relations
> postgres=# GRANT SELECT (colour) ON TABLE stuff TO user_a;
> GRANT
> postgres=# GRANT SELECT ON ALL FOREIGN TABLES IN SCHEMA public TO user_a;
> ERROR: syntax error at or near "FOREIGN"
> LINE 1: GRANT SELECT ON ALL FOREIGN TABLES IN SCHEMA public TO user_...
> ^
> Granting select for all tables in a schema to a user will affect
> foreign tables however. And column-level permissions work with
> foreign tables if you refer to them as regular tables in the
> GRANT/REVOKE statement.
>
> Using the term FOREIGN TABLE in a GRANT statement isn't documented.
> I suspect this will need its own entry in the syntax definition
> section of the GRANT and REVOKE reference pages.

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.

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.

In this approach, "FOREIGN TABLE" (object type) is removed from
privilege_target of gram.y. With this change, parser can't determine
actual object type (ACL_OBJECT_RELATION or ACL_OBJECT_FOREIGN_TABLE),
but it wouldn't be problem because object type will be retrieved in
ExecGrant_Relation() for validate privilege type.

Probabry we should mention in GRANT documents that ALL TABLES
IN SCHEMA is considered to include foreign tables.

Attached patch includes removing GRANT ON FOREIGN TABLE syntax fix,
tab-completion fix, GRANT documents fix and additional regression
tests.

Regards,
--
Shigeru Hanada