pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Wienhold <ewie@ewie.name>
To: Johnson, Bruce E - (bjohnson) <Johnson@pharmacy.arizona.edu>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Granting privileges to a schema to a role
Date: Tue, 12 Sep 2023 00:32:47 +0200 (CEST)
Message-ID: <356824368.210846.1694471567414@office.mailbox.org> (raw)
In-Reply-To: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu>
References: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu>

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





view thread (2+ messages)

Message-ID: <356824368.210846.1694471567414@office.mailbox.org>
Permalink:  ../356824368.210846.1694471567414@office.mailbox.org/
Also on:    postgresql.org/message-id/356824368.210846.1694471567414@office.mailbox.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ewie@ewie.name, Johnson@pharmacy.arizona.edu
  Subject: Re: Granting privileges to a schema to a role
  In-Reply-To: <356824368.210846.1694471567414@office.mailbox.org>
  References: <93A9C6E2-01A7-47C4-8A71-26EC122D25F6@arizona.edu> <356824368.210846.1694471567414@office.mailbox.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox