Skip site navigation (1) Skip section navigation (2)

Foreign table permissions and cloning

From: Thom Brown <thom(at)linux(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Foreign table permissions and cloning
Date: 2011-03-31 23:54:18
Message-ID: AANLkTi=rdu0Nrxa+wtqVLzWR=tvh3rzQoNS43i5DDkLN@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

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.

I also noticed this doesn't work:

postgres=# CREATE TABLE animals (LIKE stuff);
ERROR:  inherited relation "stuff" is not a table

Since LIKE doesn't maintain any sort of link with the table like
INHERITS does, it would be nice if this could work in future.

Thanks

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Responses

pgsql-hackers by date

Next:From: Joshua BerkusDate: 2011-04-01 00:10:36
Subject: Re: Should psql support URI syntax?
Previous:From: Greg SmithDate: 2011-03-31 23:41:25
Subject: Re: 2nd Level Buffer Cache

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group