From: soumitra bhandary <soumitra.bhandary@hotmail.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Owner of schema cant access foreign tables in that schema
Date: Sun, 30 Sep 2018 11:04:28 +0000
Message-ID: <MA1PR0101MB16870CF7719F1544C96DEAE7E0EE0@MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CA+t6e1=vRDP=vQocVcvAX018tGgVMiOqDSLye8DURti4c+mfPg@mail.gmail.com>
References: <CA+t6e1=vRDP=vQocVcvAX018tGgVMiOqDSLye8DURti4c+mfPg@mail.gmail.com>
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.cherkassky@gmail.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 ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: soumitra.bhandary@hotmail.com, mariel.cherkassky@gmail.com
Subject: Re: Owner of schema cant access foreign tables in that schema
In-Reply-To: <MA1PR0101MB16870CF7719F1544C96DEAE7E0EE0@MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM>
References: <CA+t6e1=vRDP=vQocVcvAX018tGgVMiOqDSLye8DURti4c+mfPg@mail.gmail.com> <MA1PR0101MB16870CF7719F1544C96DEAE7E0EE0@MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox