Re: Issue dumping schema using readonly user

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver
Sent: Tuesday, February 17, 2015 4:12 PM
To: Daniel LaMotte
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue dumping schema using readonly user

On 02/17/2015 08:43 AM, Daniel LaMotte wrote:
> I understand this.  This is the behavior I want.  What I don't 
> understand is why the readonly user can inspect the schema of the 
> table interactively when pg_dump refuses to do the same via the 
> command line (assumably it asks for too much permission when simply 
> trying to dump the schema [NOT the table data]).  I do not care about 
> the data.  I only care that the pg_dump would emit "CREATE TABLE ..." 
> statements for the table.

Just to be clear, you want the readonly user to be able to dump the schema definition for mytable_is_not_readonly?


>
> The --schema-only option makes me think that it would emit only these
> CREATE TABLE ... statements and not the COPY statements (which consist
> of table data).
>
> I want the pg_dump to dump the "schema" of the table but without the data.
>
> My example is that the readonly user is able to inspect the schema of
> the table (it can see [even though I have not given permission to the
> table] that the table has int and text columns).  I would expect that
> since I can inspect the schema of the table, that pg_dump using the
> --schema-only option would emit a CREATE TABLE ... for the table _even
> though_ it cannot dump the data of the table.
>
> Have I made myself clear?

Yes, I understand now.

>
> I have no interest in this user being allowed to COPY or SELECT the
> tables data.  My only interest is in the user's ability to see what
> columns and column types exist for the table so it can emit CREATE TABLE
> ... commands in the pg_dump output.
>
> In my mind, the following is true:
>
> % pg_dump --table=mytable
> ...
> CREATE TABLE xyz (...);
> COPY TO xyz ...; -- table data
> ...
>
> % pg_dump --schema-only --table=mytable
> ...
> CREATE TABLE xyz (...);
> ...
>
> The second example uses --schema-only and does not dump table data.
> Therefore, if the user can inspect the schema, that is all it needs
> permissions to do in order to write a CREATE TABLE statement for the
> table.  But it seems that pg_dump still asks for permission to read the
> table data in order to simply dump the schema (which is what I'm trying
> to confirm if its intentional or a bug) which results in permission
> denied error.

The issue such as it is, arises because the information you are getting 
is coming from two different paths.

1) psql
Using the -E switch to psql shows what happens in the background

aklaver@killi:~> /usr/local/pgsql93/bin/psql -d test -U aklaver -p 5452 -E

aklaver@test=> \dt mytable_is_not_readonly
********* QUERY **********
SELECT n.nspname as "Schema",
   c.relname as "Name",
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' 
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' 
WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
       AND n.nspname !~ '^pg_toast'
   AND c.relname ~ '^(mytable_is_not_readonly)$'
   AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

                  List of relations
  Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+---------
  public | mytable_is_not_readonly | table | aklaver
(1 row)

As you can see in psql Postgres uses the system catalogs to fetch the 
schema definition. By default the information in there is available to 
all users. There is another thread going on that addresses this in 
relation to function definitions. If you want some gory details:

http://www.postgresql.org/message-id/CAOkVcQ66muZW7QyeYrO0n8V4r4FjzCaBYSk9u3H+fmRFZw1-dA@mail.gmail.com


2) pg_dump

aklaver@killi:~> /usr/local/pgsql93/bin/pg_dump -U readonly  test 
--schema-only --table=mytable_is_not_readonly -p 5452
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for 
relation mytable_is_not_readonly
pg_dump: [archiver (db)] query was: LOCK TABLE 
public.mytable_is_not_readonly IN ACCESS SHARE MODE


pg_dump uses the -U to determine permissions and from that what can or 
cannot be dumped based on what permissions are actually set on the 
objects. As is mentioned below, a user has to have at a minimum SELECT 
permissions on an object.

http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html
"
Diagnostics

pg_dump internally executes SELECT statements. If you have problems 
running pg_dump, make sure you are able to select information from the 
database using, for example, psql."

To answer your question, this is intentional. At the moment a work 
around does not come to mind, but the gears are grinding:)

>
> - Dan
>

-- 
Adrian Klaver
adrian.klaver@aklaver.com


So, basically OP wants his "read-only" user to be able to execute what's provided by Oracles DBMS_METADATA package.
I'm not familiar with EnterprizeDB (Oracle-related) extensions of Postgresql, but they may have it implemented, check
theirdocs.
 

Regards,
Igor Neyman