Re: Granting privileges to a schema to a role

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