(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