On 11/09/2023 20:07 CEST Johnson, Bruce E - (bjohnson) <johnson@xxxxxxxxxxxxxxxxxxxx> 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
