Re: Owner of schema cant access foreign tables in that schema

Hi Mariel,

This is a known limitation for Postgres roles/ users .

No future object level access is provided to the users created apart from super users.

You have to explicitly provide grant schema usage followed by object level usage permission i.e. read only , write only etc.
Same you can try with simply creating one user and create one schema and some objects in it .

Thanks ,
Soumitra

Sent from my iPhone

> On 30-Sep-2018, at 1:19 PM, Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> wrote:
>
> 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 ?