Issue dumping schema using readonly user

From: Daniel LaMotte <lamotte85(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Issue dumping schema using readonly user
Date: 2015-02-11 21:47:58
Message-ID: CAAP0YCp1YvEZjzW+ue36LDDOYHwYB3E5iYOrY=tX+tkSq6Z9qQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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. 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2015-02-11 23:34:01 Re: Prepared statements with bind parameters for DDL
Previous Message Tom Lane 2015-02-11 19:22:10 Re: Prepared statements with bind parameters for DDL