Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qfpSn-00DIGv-Ch for pgsql-general@arkaria.postgresql.org; Mon, 11 Sep 2023 22:33:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qfpSm-003Rv8-5t for pgsql-general@arkaria.postgresql.org; Mon, 11 Sep 2023 22:32:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qfpSl-003Ruy-Me for pgsql-general@lists.postgresql.org; Mon, 11 Sep 2023 22:32:59 +0000 Received: from mout-u-204.mailbox.org ([80.241.59.204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qfpSd-004X4Q-P1 for pgsql-general@lists.postgresql.org; Mon, 11 Sep 2023 22:32:58 +0000 Received: from smtp102.mailbox.org (smtp102.mailbox.org [IPv6:2001:67c:2050:b231:465::102]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4Rl1gh0gFnz9sQS; Tue, 12 Sep 2023 00:32:48 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1694471568; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=LIEbi4Fn5uDJa2oRzkJDRbJrGdA7Se4D2T5ICyHHcJ0=; b=q9k0hGbUNHE9ERn6bqTYDm6hvKyO3ciP8qIZ5V3XLaG1jAUrk7Q1O35ZDEvxGw2+W1Zsrx Ia/baBlUPdLcDvY6TS6rJkQFvFuttsOQNVflWApPLwfsSP0X1SujsZ2Yfv9vozMO35oj3l tKodDU5VZKFW90B6eVK/e4C4SyA8XX1AtLM/T4lKP62fb/l3tWmFkKZfgBqQHr4E6bX1tz kMTofO9uc9W/3Nv24qHcKfwRovcOUqAl/PEZ5yYmBWgssafoYpFSWSYmIE1MBGoxAWUf2c vTUW2dYSu0eOjMMAiWhnsKU+dtY4W5VtAwEEQB2zh6Xy+iczGpCpK/Y/ZW8gLw== Date: Tue, 12 Sep 2023 00:32:47 +0200 (CEST) From: Erik Wienhold To: "Johnson, Bruce E - (bjohnson)" , "pgsql-general@lists.postgresql.org" Message-ID: <356824368.210846.1694471567414@office.mailbox.org> In-Reply-To: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu> References: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu> Subject: Re: Granting privileges to a schema to a role MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Rl1gh0gFnz9sQS List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/09/2023 20:07 CEST Johnson, Bruce E - (bjohnson) wrote: > I=E2=80=99ve created the database =E2=80=98webdata', successfully used or= a2pg to migrate one > schema =E2=80=98trav=E2=80=99 to Postgres. > > The schema on the oracle side is called trav the owner is webdata, and I > created the role trav and granted all table rights in the schema to the r= ole > > GRANT ALL ON ALL TABLES IN SCHEMA trav TO trav. > > When I log into pgsql as trav and run \dp the privileges appear correct b= ut > trying a simple select fails with =E2=80=98permission denied=E2=80=99 err= or: > > > psql webdata -U trav > > psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1)) > > Type "help" for help. > > > webdata=3D> \dp trav.sectors > > Access privileges > > Schema | Name | Type | Access privileges | Column privileges |= Policies > > --------+---------+-------+-------------------------+-------------------+= ---------- > > trav | sectors | table | webdata=3DarwdDxt/webdata+| = | > > | | | trav=3DarwdDxt/webdata | = | > > (1 row) > > > webdata=3D> select sectorname, count(worldname) from trav.sectors group b= y sectorname order by sectorname; > > ERROR: permission denied for schema trav > > LINE 1: select sectorname, count(worldname) from trav.sectors group ... > > What am I missing? You must also grant USAGE on schema trav to role trav to access objects in = that schema. Use \dn+ trav to check the schema privileges. -- Erik