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.klaver@xxxxxxxxxxx> wrote:
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.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.
--
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@xxxxxxxxxxx
