Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g6ZWl-0007uo-Vb for pgsql-admin@arkaria.postgresql.org; Sun, 30 Sep 2018 11:04:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g6ZWi-0000Oe-2e for pgsql-admin@arkaria.postgresql.org; Sun, 30 Sep 2018 11:04:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g6ZWh-0000NP-4v for pgsql-admin@lists.postgresql.org; Sun, 30 Sep 2018 11:04:39 +0000 Received: from mail-oln040092255054.outbound.protection.outlook.com ([40.92.255.54] helo=APC01-HK2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g6ZWd-0002rz-D7 for pgsql-admin@lists.postgresql.org; Sun, 30 Sep 2018 11:04:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=pISLwQIzTmTk9VmJ8Ls1RY/pwzMwPwfBo+yadJ7eCBg=; b=urPWnYHxfTMjnmLmkeamR5I4Rj2xFBwuKEu5FgBlX4Urq6PP75jc574N0lTEgHV4Hk4nNQoygd0QQUJU26vslyZBurUXAgN+1IkED7Jy8Ru4XicN64/xw2t0XLxb8IjlsiNa/T0/WZreDK/jObz9/cJCpE/Qgbmmmmip+KrenU/apQ1fxY4m1q8NewUcW/BE2zOhdLhOvE61pJ2SJ4K6+EJ0vEn80LQ/1TBB+clyd9r/jNpVJJiJpQAOCHI2jIcGLnXHT42sxwAUcRorrrkEVvAIIoFBcJNZh8tQC//EmJnTvhsvkr4dEBNdINbAOwMNkRfW6BYs1TkkY51NIPcm5Q== Received: from PU1APC01FT008.eop-APC01.prod.protection.outlook.com (10.152.252.51) by PU1APC01HT074.eop-APC01.prod.protection.outlook.com (10.152.253.113) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.20.1185.13; Sun, 30 Sep 2018 11:04:29 +0000 Received: from MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM (10.152.252.59) by PU1APC01FT008.mail.protection.outlook.com (10.152.252.66) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.20.1185.13 via Frontend Transport; Sun, 30 Sep 2018 11:04:29 +0000 Received: from MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM ([fe80::906a:8c91:fc33:a68b]) by MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM ([fe80::906a:8c91:fc33:a68b%4]) with mapi id 15.20.1164.029; Sun, 30 Sep 2018 11:04:28 +0000 From: soumitra bhandary To: Mariel Cherkassky CC: "pgsql-admin@lists.postgresql.org" Subject: Re: Owner of schema cant access foreign tables in that schema Thread-Topic: Owner of schema cant access foreign tables in that schema Thread-Index: AQHUWJIcCiWHFnsusUybKR9C4UTRbaUIqZKs Date: Sun, 30 Sep 2018 11:04:28 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:170BAF9A0FEE2900745D664A1E438DF086D3D6D93BA8012DB2CDF25CA2498104;UpperCasedChecksum:CC6DCE373E8AC0D3F6E45A059681CB2D2C021C87C691A337EF205B3FC9A0D565;SizeAsReceived:7213;Count:47 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [xySZPRtuLfP3HjWSiC+0Eco17A4iTXk1ALAkR2r5Ae4=] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;PU1APC01HT074;6:R/4KDv27h5sVHox3xmOPUKWW12m/D+mB52LLhxEz6l9ICl0EhW0lU/VVmb4CrIDuL2Dl+CvZXFTyl+XtY3QoyEFgTNvtAMlp0Cd8+4uT/8yF6wuZgiXmPjYadDrC7WGbHCqxnmSyOXo51JimfJaxkNv7yphIPJAM8ISXTI65kqTe0T5uAzeyJbbXGH5MFs5ChnqqxJ6LuaIfesFQHD9OSsuVQZI0bH00R/8sx7qGaJ//wPZpWsn2fhjiAnKwZRdh0PFvPUV8+YSPHJ1ku8zfilbvP4XTTkMomR7T0FeeZW91aDQkETpY2gVfJZnQVA4QazrPv4l/NBAfPK7xKZEEccqdk+qUOSaRVzPV+8CsqqlXfV7h2sR9XAuVwxUJcCDpi37L9YFMbqZrGYv3a7YcQLQSuLyUzNBqmLsN7pPr5oKCOKT+P5mEUFaC4kpsPFFzuzBlLMu/tOjdBPLF+ZSIvQ==;5:iM+sHZpHryz4EqAsXKFnDTKQzxoR9MVbA7MduC7ablnI/axtgJRWwhfsC88BY2vP0znJgoaTrP2eWC4fc/HRE4M4dX7wGm33IJ2WWOpularf72NY/X0KTtAZvodGo4ZFwXe0UztO0m34cvLz3qZTbst5Ocls+1yqEqLoy4a+RFo=;7:xLzvKfgzqtVxDlYFzrHlbZNmRIJxVij82JjlgndCw8B725q3xg7sTbZuAxdR4I6cU6koemAQ6MlT2Cg7h1eCnFCKv5KVxfjlkbP7QfYCbNkskQdcH18U6Lk61YJzKbkDsMBT+5fZzouHBGa0pEYRDiNanYw3PSu9M+GbUGHAUr/DROlrQ2nd2DBEhwSaMkU/tYwG4d32ZXwWrvxjicnBTKjxz7Rc389iIcB930c71iEfEfQdarAv1amAlY1P767Y x-incomingheadercount: 47 x-eopattributedmessage: 0 x-microsoft-antispam: BCL:0;PCL:0;RULEID:(7020095)(201702061078)(5061506573)(5061507331)(1603103135)(2017031320274)(2017031324274)(2017031323274)(2017031322404)(1601125500)(1603101475)(1701031045);SRVR:PU1APC01HT074; x-ms-traffictypediagnostic: PU1APC01HT074: x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(4566010)(82015058);SRVR:PU1APC01HT074;BCL:0;PCL:0;RULEID:;SRVR:PU1APC01HT074; x-forefront-prvs: 08118EFC2B x-forefront-antispam-report: SFV:NSPM;SFS:(7070007)(189003)(199004)(86362001)(104016004)(486006)(476003)(39060400002)(68736007)(4326008)(9686003)(86152003)(83332001)(76176011)(7696005)(2900100001)(55016002)(73972006)(87572001)(5660300001)(256004)(71190400001)(56003)(5250100002)(446003)(71200400001)(34290500001)(14444005)(53546011)(6346003)(97736004)(102836004)(8676002)(6916009)(82202002)(11346002)(81156014)(6506007)(26005)(33656002)(99286004)(74316002)(25786009)(5003630100001)(6436002)(14454004)(305945005)(20460500001)(105586002)(6246003)(229853002)(8936002)(106356001)(15852004);DIR:OUT;SFP:1901;SCL:1;SRVR:PU1APC01HT074;H:MA1PR0101MB1687.INDPRD01.PROD.OUTLOOK.COM;FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1; received-spf: None (protection.outlook.com: hotmail.com does not designate permitted sender hosts) authentication-results: spf=none (sender IP is ) smtp.mailfrom=soumitra.bhandary@hotmail.com; x-microsoft-antispam-message-info: x+MEtjUCZoJGPN12lkbbB1jN18FAS6m2YIcL56YuCSvFgpfoe08SmfL+qduzxEKjxQn5Z+KNvk1lBK05Adyf5b1nrhyJm+4Of2nPUYJlMhGexi5voLfv19+yNavQiC/pPSP2UuDVZX1G4c+IyI44eQVMzt1yJDfKcMMnU5zRnYKAPDHcmqjJUvwwQLLfRAUBjGDm9/RzPUkb22js3KDJgaJeYTA2ogKw+1RU/EynL6s= Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginatorOrg: hotmail.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 7181d4b0-87d6-4f4e-ba33-0d3746212cec X-MS-Exchange-CrossTenant-Network-Message-Id: ed0649b0-9c19-4f34-4217-08d626c47dfa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 7181d4b0-87d6-4f4e-ba33-0d3746212cec X-MS-Exchange-CrossTenant-originalarrivaltime: 30 Sep 2018 11:04:28.7564 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: PU1APC01HT074 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi Mariel, This is a known limitation for Postgres roles/ users . No future object level access is provided to the users created apart from s= uper 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 so= me objects in it .=20 Thanks , Soumitra=20 Sent from my iPhone > On 30-Sep-2018, at 1:19 PM, Mariel Cherkassky wrote: >=20 > Hi, I'm importing an entire schema(all the tables) from sqlite database i= nto my postgres database. > -I created the sqlite_fdw extension as postgres :=20 > psql -d mariel -U postgres -v "ON_ERROR_STOP=3D1" -c "create extension sq= lite_fdw"=20 >=20 > -I created the foreign data wrapper as postgres :=20 > psql -d mariel -U postgres -v "ON_ERROR_STOP=3D1" -c "create server sqlit= e_server foreign data wrapper sqlite_fdw options(database 'db.sqlite'"=20 >=20 > created a specific schema :=20 > psql -d mariel -U postgres -v "ON_ERROR_STOP=3D1" -c "create schema sqlit= e_foreign_schema authorization mariel"=20 >=20 > import entire schema :=20 > psql -d mariel-U postgres -v "ON_ERROR_STOP=3D1" -c "import foreign schem= a public from server sqlite_server into sqlite_foreign_schema"=20 >=20 >=20 > When I created the sqlite_foreign_schema i mentioned the authorization fl= ag(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. H= owever, when I try to query the foreign tables in the sqlite_foreign_schema= I'm getting the permissions denied error. >=20 > Only after running the next query I could query from the foreign tables:= =20 > grant all on all tables in schema sqlite_foreign_schema to mariel;=20 >=20 > Isnt it is enough to mention that the user mariel has authorization on th= e schema ? Moreover, why the foreign objects that were created are owned by= the postgres user ?