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.klaver@aklaver.com