From: Erik Wienhold <ewie@ewie.name>
To: Johnson, Bruce E - (bjohnson) <Johnson@pharmacy.arizona.edu>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Granting privileges to a schema to a role
Date: Tue, 12 Sep 2023 00:32:47 +0200 (CEST)
Message-ID: <356824368.210846.1694471567414@office.mailbox.org> (raw)
In-Reply-To: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu>
References: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu>
On 11/09/2023 20:07 CEST Johnson, Bruce E - (bjohnson) <johnson@pharmacy.arizona.edu> wrote:
> I’ve created the database ‘webdata', successfully used ora2pg to migrate one
> schema ‘trav’ 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 role
>
> GRANT ALL ON ALL TABLES IN SCHEMA trav TO trav.
>
> When I log into pgsql as trav and run \dp the privileges appear correct but
> trying a simple select fails with ‘permission denied’ error:
>
>
> psql webdata -U trav
>
> psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
>
> Type "help" for help.
>
>
> webdata=> \dp trav.sectors
>
> Access privileges
>
> Schema | Name | Type | Access privileges | Column privileges | Policies
>
> --------+---------+-------+-------------------------+-------------------+----------
>
> trav | sectors | table | webdata=arwdDxt/webdata+| |
>
> | | | trav=arwdDxt/webdata | |
>
> (1 row)
>
>
> webdata=> select sectorname, count(worldname) from trav.sectors group by 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
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-general@postgresql.org
Cc: ewie@ewie.name, Johnson@pharmacy.arizona.edu
Subject: Re: Granting privileges to a schema to a role
In-Reply-To: <356824368.210846.1694471567414@office.mailbox.org>
References: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu> <356824368.210846.1694471567414@office.mailbox.org>
* 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