Re: Issue dumping schema using readonly user

The point is that the user seems to have permissions to view the schema but
not the table data. If I can interactively inspect the table schema but
pg_dump is unable to dump the table schema, that seems like a bug.

The account explicitly is not allowed access to the table's data but seems
to be able to access the schema (at least interactively).

Does that make more sense?

- Dan

On Fri, Feb 13, 2015 at 4:48 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 02/11/2015 01:47 PM, Daniel LaMotte wrote:
>
>> Here’s the situation:
>>
>> | % psql --version
>> psql (PostgreSQL) 9.3.5
>> % postgres --version
>> postgres (PostgreSQL) 9.3.5
>> % psql mydatabase
>> create table mytable_is_readonly (id uuid primary key, text text not
>> null);
>> create table mytable_is_not_readonly (id uuid primary key, text text
>> not null);
>> create user readonly with password 'readonly';
>> grant select on mytable_is_readonly to readonly;
>>
>> % psql mydatabase readonly
>> \d mytable_is_readonly
>> Table "public.mytable_is_readonly"
>> Column │ Type │ Modifiers
>> ────────┼─────────┼─────────────────────────────────────────
>> ─────────────────────────
>> id │ integer │ not null default nextval('mytable_is_readonly_
>> id_seq'::regclass)
>> text │ text │ not null
>> Indexes:
>> "mytable_is_readonly_pkey" PRIMARY KEY, btree (id)
>>
>> \d mytable_is_not_readonly
>> Table "public.mytable_is_not_readonly"
>> Column │ Type │ Modifiers
>> ────────┼─────────┼─────────────────────────────────────────
>> ─────────────────────────────
>> id │ integer │ not null default nextval('mytable_is_not_
>> readonly_id_seq'::regclass)
>> text │ text │ not null
>> Indexes:
>> "mytable_is_not_readonly_pkey" PRIMARY KEY, btree (id)
>>
>> % pg_dump -U readonly mydatabase --schema-only
>> --table=mytable_is_readonly
>> ... this outputs and works
>>
>> % pg_dump -U readonly mydatabase --schema-only
>> --table=mytable_is_not_readonly
>> 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
>> |
>>
>> Is this a bug? Or defined behavior that is expected? My use case is that
>> I have some tables that I don’t want to allow the readonly account to
>> access data in but want to allow it to see the schema of that table.
>>
>
> To me at least SELECT is accessing the data, so I am not sure that the
> above meets your criteria in any case. I would do \dt+
> mytable_is_not_readonly to see who has permissions on the table.
>
>
>
> My
>
>> guess was that since it could see the schema interactively in psql, that
>> it should be allowed to pg_dump the table with schema only no problem.
>>
>> Thanks for the help!
>>
>> - Dan
>>
>> ​
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>