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
thatall objects that will be created in the sqlite_foreign_Schema db will be owned by the user mariel. However, when I
tryto 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
thatwere created are owned by the postgres user ?