From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | "Johnson, Bruce E - (bjohnson)" <Johnson(at)pharmacy(dot)arizona(dot)edu>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Granting privileges to a schema to a role |
Date: | 2023-09-11 22:32:47 |
Message-ID: | 356824368.210846.1694471567414@office.mailbox.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/09/2023 20:07 CEST Johnson, Bruce E - (bjohnson) <johnson(at)pharmacy(dot)arizona(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-09-12 09:39:41 | Re: Upgrade problem |
Previous Message | Johnson, Bruce E - (bjohnson) | 2023-09-11 18:07:14 | Granting privileges to a schema to a role |