(2011/04/15 3:43), Robert Haas wrote:
On Fri, Apr 1, 2011 at 1:29 AM, Shigeru HANADA
<han...@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
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 72ecc45..3ad9fab 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** GRANT { { SELECT | INSERT | UPDATE | REF
*** 32,37 ****
--- 32,46 ----
ON [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [,
...]
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
+ GRANT { SELECT | ALL [ PRIVILEGES ] }
+ ON { FOREIGN TABLE <replaceable
class="PARAMETER">foreign_table_name</replaceable> [, ...]
+ | ALL FOREIGN TABLES IN SCHEMA <replaceable
class="PARAMETER">schema_name</replaceable> [, ...] }
+ TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
+ GRANT { SELECT | ALL [ PRIVILEGES ] } ( <replaceable
class="PARAMETER">column</replaceable> [, ...] )
+ ON FOREIGN TABLE <replaceable
class="PARAMETER">foreign_table_name</replaceable> [, ...]
+ TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable>
[, ...]
*************** GRANT <replaceable class="PARAMETER">rol
*** 81,87 ****
<para>
The <command>GRANT</command> command has two basic variants: one
that grants privileges on a database object (table, column, view, sequence,
! database, foreign-data wrapper, foreign server, function,
procedural language, schema, or tablespace), and one that grants
membership in a role. These variants are similar in many ways, but
they are different enough to be described separately.
--- 90,96 ----
<para>
The <command>GRANT</command> command has two basic variants: one
that grants privileges on a database object (table, column, view, sequence,
! foreign table, database, foreign-data wrapper, foreign server, function,
procedural language, schema, or tablespace), and one that grants
membership in a role. These variants are similar in many ways, but
they are different enough to be described separately.
*************** GRANT <replaceable class="PARAMETER">rol
*** 100,107 ****
<para>
There is also an option to grant privileges on all objects of the same
type within one or more schemas. This functionality is currently supported
! only for tables, sequences, and functions (but note that <literal>ALL
! TABLES</> is considered to include views).
</para>
<para>
--- 109,116 ----
<para>
There is also an option to grant privileges on all objects of the same
type within one or more schemas. This functionality is currently supported
! only for tables, sequences, functions and foreign tables (but note that
! <literal>ALL TABLES</> is considered to include views).
</para>
<para>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 204c986..70702bd 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** REVOKE [ GRANT OPTION FOR ]
*** 37,42 ****
--- 37,56 ----
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
+ { SELECT | ALL [ PRIVILEGES ] }
+ ON { FOREIGN TABLE <replaceable
class="PARAMETER">foreign_table_name</replaceable> [, ...]
+ | ALL FOREIGN TABLES IN SCHEMA
<replaceable>schema_name</replaceable> [, ...] }
+ FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> |
PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
+
+ REVOKE [ GRANT OPTION FOR ]
+ { SELECT ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
+ | ALL [ PRIVILEGES ] ( <replaceable
class="PARAMETER">column</replaceable> [, ...] ) }
+ ON FOREIGN TABLE <replaceable
class="PARAMETER">foreign_table_name</replaceable> [, ...]
+ FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> |
PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
+
+ REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable>
[, ...]
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 693b634..5aa9977 100644
*** a/src/backend/catalog/aclchk.c
--- b/src/backend/catalog/aclchk.c
*************** ExecuteGrantStmt(GrantStmt *stmt)
*** 518,524 ****
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column
privileges are only valid for relations")));
--- 518,525 ----
*/
if (privnode->cols)
{
! if (stmt->objtype != ACL_OBJECT_RELATION &&
! stmt->objtype !=
ACL_OBJECT_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_GRANT_OPERATION),
errmsg("column
privileges are only valid for relations")));
*************** objectsInSchemaToOids(GrantObjectType ob
*** 729,746 ****
switch (objtype)
{
case ACL_OBJECT_RELATION:
! /* Process regular tables, views and foreign
tables */
objs = getRelationsInNamespace(namespaceId,
RELKIND_RELATION);
objects = list_concat(objects, objs);
objs = getRelationsInNamespace(namespaceId,
RELKIND_VIEW);
objects = list_concat(objects, objs);
- objs = getRelationsInNamespace(namespaceId,
RELKIND_FOREIGN_TABLE);
- objects = list_concat(objects, objs);
break;
case ACL_OBJECT_SEQUENCE:
objs = getRelationsInNamespace(namespaceId,
RELKIND_SEQUENCE);
objects = list_concat(objects, objs);
break;
case ACL_OBJECT_FUNCTION:
{
ScanKeyData key[1];
--- 730,750 ----
switch (objtype)
{
case ACL_OBJECT_RELATION:
! /* Process regular tables and views */
objs = getRelationsInNamespace(namespaceId,
RELKIND_RELATION);
objects = list_concat(objects, objs);
objs = getRelationsInNamespace(namespaceId,
RELKIND_VIEW);
objects = list_concat(objects, objs);
break;
case ACL_OBJECT_SEQUENCE:
objs = getRelationsInNamespace(namespaceId,
RELKIND_SEQUENCE);
objects = list_concat(objects, objs);
break;
+ case ACL_OBJECT_FOREIGN_TABLE:
+ objs = getRelationsInNamespace(namespaceId,
+
RELKIND_FOREIGN_TABLE);
+ objects = list_concat(objects, objs);
+ break;
case ACL_OBJECT_FUNCTION:
{
ScanKeyData key[1];
*************** ExecGrant_Relation(InternalGrant *istmt)
*** 1938,1944 ****
AccessPriv *col_privs = (AccessPriv *)
lfirst(cell_colprivs);
if (col_privs->priv_name == NULL)
! this_privileges = ACL_ALL_RIGHTS_COLUMN;
else
this_privileges =
string_to_privilege(col_privs->priv_name);
--- 1942,1957 ----
AccessPriv *col_privs = (AccessPriv *)
lfirst(cell_colprivs);
if (col_privs->priv_name == NULL)
! {
! /*
! * Columns of a foreign table accept same
privilege as foreign
! * table itself.
! */
! if (pg_class_tuple->relkind ==
RELKIND_FOREIGN_TABLE)
! this_privileges =
ACL_ALL_RIGHTS_FOREIGN_TABLE;
! else
! this_privileges = ACL_ALL_RIGHTS_COLUMN;
! }
else
this_privileges =
string_to_privilege(col_privs->priv_name);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a22ab66..5ad69e0 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** privilege_target:
*** 5463,5468 ****
--- 5463,5476 ----
n->objs = $5;
$$ = n;
}
+ | ALL FOREIGN TABLES IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = ACL_OBJECT_FOREIGN_TABLE;
+ n->objs = $6;
+ $$ = n;
+ }
| ALL FUNCTIONS IN_P SCHEMA name_list
{
PrivTarget *n = (PrivTarget *)
palloc(sizeof(PrivTarget));
diff --git a/src/test/regress/expected/foreign_data.out
b/src/test/regress/expected/foreign_data.out
index c05bcab..4e284a5 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** SELECT * FROM ft1;
*** 675,680 ****
--- 675,811 ----
ERROR: foreign-data wrapper "dummy" has no handler
EXPLAIN SELECT * FROM ft1; -- ERROR
ERROR: foreign-data wrapper "dummy" has no handler
+ -- GRANT FOREIGN TABLE
+ GRANT SELECT ON FOREIGN TABLE ft1 TO foreign_data_user;
+ GRANT SELECT (c1) ON FOREIGN TABLE ft1 TO foreign_data_user;
+ GRANT INSERT ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type INSERT for foreign table
+ GRANT INSERT (c1) ON FOREIGN TABLE ft1 TO foreign_data_user; -- WARNING
+ WARNING: foreign table "ft1" only supports SELECT column privileges
+ GRANT UPDATE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type UPDATE for foreign table
+ GRANT UPDATE (c1) ON FOREIGN TABLE ft1 TO foreign_data_user; -- WARNING
+ WARNING: foreign table "ft1" only supports SELECT column privileges
+ GRANT DELETE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type DELETE for foreign table
+ GRANT TRUNCATE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type TRUNCATE for foreign table
+ GRANT REFERENCES ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type REFERENCES for foreign table
+ GRANT REFERENCES (c1) ON FOREIGN TABLE ft1 TO foreign_data_user;-- WARNING
+ WARNING: foreign table "ft1" only supports SELECT column privileges
+ GRANT TRIGGER ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type TRIGGER for foreign table
+ GRANT USAGE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type USAGE for foreign table
+ GRANT EXECUTE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type EXECUTE for foreign table
+ -- omitting FOREIGN TABLE
+ GRANT SELECT ON ft1 TO foreign_data_user;
+ GRANT SELECT (c1) ON ft1 TO foreign_data_user;
+ GRANT INSERT ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT INSERT (c1) ON ft1 TO foreign_data_user; -- WARNING
+ WARNING: foreign table "ft1" only supports SELECT column privileges
+ GRANT UPDATE ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT UPDATE (c1) ON ft1 TO foreign_data_user; -- WARNING
+ WARNING: foreign table "ft1" only supports SELECT column privileges
+ GRANT DELETE ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT TRUNCATE ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT REFERENCES ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT REFERENCES (c1) ON ft1 TO foreign_data_user ;-- WARNING
+ WARNING: foreign table "ft1" only supports SELECT column privileges
+ GRANT TRIGGER ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT USAGE ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: foreign table "ft1" only supports SELECT privileges
+ GRANT EXECUTE ON ft1 TO foreign_data_user; -- ERROR
+ ERROR: invalid privilege type EXECUTE for relation
+ \dp ft1
+ Access privileges
+ Schema | Name | Type | Access privileges |
Column access privileges
+
--------+------+---------------+---------------------------------------+-----------------------------------------
+ public | ft1 | foreign table | foreign_data_user=r/foreign_data_user | c1:
+
+ | | | |
foreign_data_user=r/foreign_data_user
+ (1 row)
+
+ REVOKE SELECT ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ REVOKE SELECT (c1) ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ REVOKE INSERT ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type INSERT for foreign table
+ REVOKE UPDATE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type UPDATE for foreign table
+ REVOKE DELETE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type DELETE for foreign table
+ REVOKE TRUNCATE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type TRUNCATE for foreign table
+ REVOKE REFERENCES ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type REFERENCES for foreign table
+ REVOKE TRIGGER ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type TRIGGER for foreign table
+ REVOKE USAGE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type USAGE for foreign table
+ REVOKE EXECUTE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ ERROR: invalid privilege type EXECUTE for foreign table
+ \dp ft1
+ Access privileges
+ Schema | Name | Type | Access privileges | Column access privileges
+ --------+------+---------------+-------------------+--------------------------
+ public | ft1 | foreign table | |
+ (1 row)
+
+ -- GRANT/REVOKE ALL
+ GRANT ALL ON FOREIGN TABLE ft1 TO foreign_data_user;
+ GRANT ALL (c1) ON FOREIGN TABLE ft1 TO foreign_data_user;
+ \dp ft1
+ Access privileges
+ Schema | Name | Type | Access privileges |
Column access privileges
+
--------+------+---------------+---------------------------------------+-----------------------------------------
+ public | ft1 | foreign table | foreign_data_user=r/foreign_data_user | c1:
+
+ | | | |
foreign_data_user=r/foreign_data_user
+ (1 row)
+
+ REVOKE ALL ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ REVOKE ALL (c1) ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ \dp ft1
+ Access privileges
+ Schema | Name | Type | Access privileges | Column access privileges
+ --------+------+---------------+-------------------+--------------------------
+ public | ft1 | foreign table | |
+ (1 row)
+
+ -- GRANT ALL FOREIGN TABLES IN SCHEMA
+ ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
+ CREATE FOREIGN TABLE foreign_schema.ft2 (c1 int) SERVER sc;
+ GRANT SELECT ON ALL FOREIGN TABLES IN SCHEMA foreign_schema TO
foreign_data_user;
+ GRANT SELECT (c1) ON ALL FOREIGN TABLES IN SCHEMA foreign_schema TO
foreign_data_user;
+ GRANT UPDATE ON ALL TABLES IN SCHEMA foreign_schema TO foreign_data_user;
+ \dp foreign_schema.ft*
+ Access privileges
+ Schema | Name | Type | Access privileges
| Column access privileges
+
----------------+------+---------------+---------------------------------------+-----------------------------------------
+ foreign_schema | ft1 | foreign table |
foreign_data_user=r/foreign_data_user | c1: +
+ | | |
| foreign_data_user=r/foreign_data_user
+ foreign_schema | ft2 | foreign table |
foreign_data_user=r/foreign_data_user | c1: +
+ | | |
| foreign_data_user=r/foreign_data_user
+ (2 rows)
+
+ REVOKE SELECT ON ALL FOREIGN TABLES IN SCHEMA foreign_schema FROM
foreign_data_user;
+ REVOKE SELECT (c1) ON ALL FOREIGN TABLES IN SCHEMA foreign_schema FROM
foreign_data_user;
+ \dp foreign_schema.ft*
+ Access privileges
+ Schema | Name | Type | Access privileges | Column access
privileges
+
----------------+------+---------------+-------------------+--------------------------
+ foreign_schema | ft1 | foreign table | |
+ foreign_schema | ft2 | foreign table | |
+ (2 rows)
+
+ ALTER FOREIGN TABLE foreign_schema.ft1 SET SCHEMA public;
+ DROP FOREIGN TABLE foreign_schema.ft2;
-- ALTER FOREIGN TABLE
COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
COMMENT ON FOREIGN TABLE ft1 IS NULL;
diff --git a/src/test/regress/sql/foreign_data.sql
b/src/test/regress/sql/foreign_data.sql
index 0d12b98..ddd1cf0 100644
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
*************** CREATE INDEX id_ft1_c2 ON ft1 (c2);
*** 276,281 ****
--- 276,341 ----
SELECT * FROM ft1; -- ERROR
EXPLAIN SELECT * FROM ft1; -- ERROR
+ -- GRANT FOREIGN TABLE
+ GRANT SELECT ON FOREIGN TABLE ft1 TO foreign_data_user;
+ GRANT SELECT (c1) ON FOREIGN TABLE ft1 TO foreign_data_user;
+ GRANT INSERT ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT INSERT (c1) ON FOREIGN TABLE ft1 TO foreign_data_user; -- WARNING
+ GRANT UPDATE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT UPDATE (c1) ON FOREIGN TABLE ft1 TO foreign_data_user; -- WARNING
+ GRANT DELETE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT TRUNCATE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT REFERENCES ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT REFERENCES (c1) ON FOREIGN TABLE ft1 TO foreign_data_user;-- WARNING
+ GRANT TRIGGER ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT USAGE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ GRANT EXECUTE ON FOREIGN TABLE ft1 TO foreign_data_user; -- ERROR
+ -- omitting FOREIGN TABLE
+ GRANT SELECT ON ft1 TO foreign_data_user;
+ GRANT SELECT (c1) ON ft1 TO foreign_data_user;
+ GRANT INSERT ON ft1 TO foreign_data_user; -- ERROR
+ GRANT INSERT (c1) ON ft1 TO foreign_data_user; -- WARNING
+ GRANT UPDATE ON ft1 TO foreign_data_user; -- ERROR
+ GRANT UPDATE (c1) ON ft1 TO foreign_data_user; -- WARNING
+ GRANT DELETE ON ft1 TO foreign_data_user; -- ERROR
+ GRANT TRUNCATE ON ft1 TO foreign_data_user; -- ERROR
+ GRANT REFERENCES ON ft1 TO foreign_data_user; -- ERROR
+ GRANT REFERENCES (c1) ON ft1 TO foreign_data_user ;-- WARNING
+ GRANT TRIGGER ON ft1 TO foreign_data_user; -- ERROR
+ GRANT USAGE ON ft1 TO foreign_data_user; -- ERROR
+ GRANT EXECUTE ON ft1 TO foreign_data_user; -- ERROR
+ \dp ft1
+ REVOKE SELECT ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ REVOKE SELECT (c1) ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ REVOKE INSERT ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE UPDATE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE DELETE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE TRUNCATE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE REFERENCES ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE TRIGGER ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE USAGE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ REVOKE EXECUTE ON FOREIGN TABLE ft1 FROM foreign_data_user; -- ERROR
+ \dp ft1
+ -- GRANT/REVOKE ALL
+ GRANT ALL ON FOREIGN TABLE ft1 TO foreign_data_user;
+ GRANT ALL (c1) ON FOREIGN TABLE ft1 TO foreign_data_user;
+ \dp ft1
+ REVOKE ALL ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ REVOKE ALL (c1) ON FOREIGN TABLE ft1 FROM foreign_data_user;
+ \dp ft1
+ -- GRANT ALL FOREIGN TABLES IN SCHEMA
+ ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
+ CREATE FOREIGN TABLE foreign_schema.ft2 (c1 int) SERVER sc;
+ GRANT SELECT ON ALL FOREIGN TABLES IN SCHEMA foreign_schema TO
foreign_data_user;
+ GRANT SELECT (c1) ON ALL FOREIGN TABLES IN SCHEMA foreign_schema TO
foreign_data_user;
+ GRANT UPDATE ON ALL TABLES IN SCHEMA foreign_schema TO foreign_data_user;
+ \dp foreign_schema.ft*
+ REVOKE SELECT ON ALL FOREIGN TABLES IN SCHEMA foreign_schema FROM
foreign_data_user;
+ REVOKE SELECT (c1) ON ALL FOREIGN TABLES IN SCHEMA foreign_schema FROM
foreign_data_user;
+ \dp foreign_schema.ft*
+ ALTER FOREIGN TABLE foreign_schema.ft1 SET SCHEMA public;
+ DROP FOREIGN TABLE foreign_schema.ft2;
+
-- ALTER FOREIGN TABLE
COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
COMMENT ON FOREIGN TABLE ft1 IS NULL;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
