Granting privileges to a schema to a role - Mailing list pgsql-general

From Johnson, Bruce E - (bjohnson)
Subject Granting privileges to a schema to a role
Date
Msg-id 93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu
Whole thread Raw
List pgsql-general
(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


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Upgrade problem
Next
From: Erik Wienhold
Date:
Subject: Re: Granting privileges to a schema to a role