Re: Issue dumping schema using readonly user

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