On Fri, 1 Apr 2011 00:54:18 +0100 Thom Brown <t...@linux.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
20110401_column_privs.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
