Granting privileges to a schema to a role

(Background I’m working on migrating an existing set of Oracle schemas with a bunch of inter-schema grants to a Postgres 15 system)

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?

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs