Owner of schema cant access foreign tables in that schema

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Owner of schema cant access foreign tables in that schema
Date: 2018-09-30 07:48:45
Message-ID: CA+t6e1=vRDP=vQocVcvAX018tGgVMiOqDSLye8DURti4c+mfPg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi, I'm importing an entire schema(all the tables) from sqlite database
into my postgres database.
-I created the sqlite_fdw extension as postgres :
psql -d mariel -U postgres -v "ON_ERROR_STOP=1" -c "create extension
sqlite_fdw"

-I created the foreign data wrapper as postgres :
psql -d mariel -U postgres -v "ON_ERROR_STOP=1" -c "create server
sqlite_server foreign data wrapper sqlite_fdw options(database 'db.sqlite'"

created a specific schema :
psql -d mariel -U postgres -v "ON_ERROR_STOP=1" -c "create schema
sqlite_foreign_schema authorization mariel"

import entire schema :
psql -d mariel-U postgres -v "ON_ERROR_STOP=1" -c "import foreign schema
public from server sqlite_server into sqlite_foreign_schema"

When I created the sqlite_foreign_schema i mentioned the authorization
flag(authorization mariel). It suppose to mean that all objects that will
be created in the sqlite_foreign_Schema db will be owned by the user
mariel. However, when I try to query the foreign tables in the
sqlite_foreign_schema I'm getting the permissions denied error.

Only after running the next query I could query from the foreign tables:
grant all on all tables in schema sqlite_foreign_schema to mariel;

Isnt it is enough to mention that the user mariel has authorization on the
schema ? Moreover, why the foreign objects that were created are owned by
the postgres user ?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2018-09-30 08:11:01 Re: Owner of schema cant access foreign tables in that schema
Previous Message Prince Pathria 2018-09-28 10:13:36 Re: Postgres 10 automatic failover