Re: Foreign table permissions and cloning

On 1 April 2011 00:54, Thom Brown <thom(at)linux(dot)com> wrote:
> 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.

Also, there probably needs to be some elaboration of how a NOT NULL
declaration operates on a foreign table column on the CREATE FOREIGN
TABLE reference page. From what I can see, if the foreign table
cannot be modified such as those defined using the file_fdw handler,
it bears no relevance, and if the foreign table can be written to, it
won't prevent NULL values being returned if they're already in there,
just prevent them being entered (presumably). It also won't validate
data in the writable foreign table upon its creation.

And another problem I've found is if you try to create a table named
the same as a foreign table, and you use the IF NOT EXISTS clause:

postgres=# CREATE TABLE IF NOT EXISTS animals (id serial, stuff text);
NOTICE: CREATE TABLE will create implicit sequence "animals_id_seq1"
for serial column "animals.id"
NOTICE: relation "animals" already exists, skipping
CREATE TABLE
postgres=# CREATE TABLE IF NOT EXISTS stuff (id serial, stuff text);
NOTICE: CREATE TABLE will create implicit sequence "stuff_id_seq" for
serial column "stuff.id"
NOTICE: relation "stuff" already exists, skipping
ERROR: referenced relation "stuff" is not a table

The reverse doesn't error though, where you attempt to create a
foreign table named the same as a regular table using IF NOT EXISTS.

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

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