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 ?