On 02/17/2015 08:43 AM, Daniel LaMotte wrote:
I understand this. This is the behavior I want. What I don't
understand is why the readonly user can inspect the schema of the table
interactively when pg_dump refuses to do the same via the command line
(assumably it asks for too much permission when simply trying to dump
the schema [NOT the table data]). I do not care about the data. I only
care that the pg_dump would emit "CREATE TABLE ..." statements for the
table.
Just to be clear, you want the readonly user to be able to dump the
schema definition for mytable_is_not_readonly?
The --schema-only option makes me think that it would emit only these
CREATE TABLE ... statements and not the COPY statements (which consist
of table data).
I want the pg_dump to dump the "schema" of the table but without the data.
My example is that the readonly user is able to inspect the schema of
the table (it can see [even though I have not given permission to the
table] that the table has int and text columns). I would expect that
since I can inspect the schema of the table, that pg_dump using the
--schema-only option would emit a CREATE TABLE ... for the table _even
though_ it cannot dump the data of the table.
Have I made myself clear?
Yes, I understand now.
I have no interest in this user being allowed to COPY or SELECT the
tables data. My only interest is in the user's ability to see what
columns and column types exist for the table so it can emit CREATE TABLE
... commands in the pg_dump output.
In my mind, the following is true:
% pg_dump --table=mytable
...
CREATE TABLE xyz (...);
COPY TO xyz ...; -- table data
...
% pg_dump --schema-only --table=mytable
...
CREATE TABLE xyz (...);
...
The second example uses --schema-only and does not dump table data.
Therefore, if the user can inspect the schema, that is all it needs
permissions to do in order to write a CREATE TABLE statement for the
table. But it seems that pg_dump still asks for permission to read the
table data in order to simply dump the schema (which is what I'm trying
to confirm if its intentional or a bug) which results in permission
denied error.
The issue such as it is, arises because the information you are getting
is coming from two different paths.
1) psql
Using the -E switch to psql shows what happens in the background
aklaver@killi:~> /usr/local/pgsql93/bin/psql -d test -U aklaver -p 5452 -E
aklaver@test=> \dt mytable_is_not_readonly
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname ~ '^(mytable_is_not_readonly)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+---------
public | mytable_is_not_readonly | table | aklaver
(1 row)
As you can see in psql Postgres uses the system catalogs to fetch the
schema definition. By default the information in there is available to
all users. There is another thread going on that addresses this in
relation to function definitions. If you want some gory details:
http://www.postgresql.org/message-id/CAOkVcQ66muZW7QyeYrO0n8V4r4FjzCaBYSk9u3H+fmRFZw1-dA@xxxxxxxxxxxxxx
2) pg_dump
aklaver@killi:~> /usr/local/pgsql93/bin/pg_dump -U readonly test
--schema-only --table=mytable_is_not_readonly -p 5452
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
pg_dump uses the -U to determine permissions and from that what can or
cannot be dumped based on what permissions are actually set on the
objects. As is mentioned below, a user has to have at a minimum SELECT
permissions on an object.
http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html
"
Diagnostics
pg_dump internally executes SELECT statements. If you have problems
running pg_dump, make sure you are able to select information from the
database using, for example, psql."
To answer your question, this is intentional. At the moment a work
around does not come to mind, but the gears are grinding:)
- Dan
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
