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.
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?
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
COPY TO xyz ...; -- table data
...
% pg_dump --schema-only --table=mytable
...
% 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.
- Dan
On Tue, Feb 17, 2015 at 8:55 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 02/17/2015 03:11 AM, Daniel LaMotte wrote:
> 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?
Whenever I deal with permissions I am reminded of the old Abbott and Costello skit; Who's on first?
To put it another way, it can be confusing. So let me walk through it below:
aklaver@panda:~> /usr/local/pgsql93/bin/psql -d test -h localhost -U aklaver -p 5452
# Changed to int, because I do not have uuid on this instance.
test=# create table mytable_is_readonly (id int primary key, text text not null);
CREATE TABLE
test=# create table mytable_is_not_readonly (id int primary key, text text not null);
CREATE TABLE
# Need to change because aklaver does have role creation privileges.
test=# \c - postgres
You are now connected to database "test" as user "postgres".
test=# create user readonly with password 'readonly';
CREATE ROLE
# Change back and create data
test=# \c - aklaver
You are now connected to database "test" as user "aklaver".
test=# insert into mytable_is_readonly values (1, 'test1'), (2, 'test2'), (3, 'test3');
INSERT 0 3
test=# insert into mytable_is_not_readonly values (1, 'test1'), (2, 'test2'), (3, 'test3');
INSERT 0 3
# Show who owns the tables
test=> \dt+ mytable_is_readonly
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------------+-------+---------+-------+-------------
public | mytable_is_readonly | table | aklaver | 16 kB |
(1 row)
test=> \dt+ mytable_is_not_readonly
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------------+-------+---------+-------+-------------
public | mytable_is_not_readonly | table | aklaver | 16 kB |
(1 row)
# As aklaver GRANT SELECT to readonly.
test=> grant select on mytable_is_readonly to readonly;
GRANT
# Show permissions for tables.
test=> \dp mytable_is_readonly
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------------------+-------+-------------------------+--------------------------
public | mytable_is_readonly | table | aklaver=arwdDxt/aklaver+|
| | | readonly=r/aklaver |
(1 row)
test=> \dp mytable_is_not_readonly
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------------------------+-------+-------------------+--------------------------
public | mytable_is_not_readonly | table | |
(1 row)
# As aklaver select data.
test=> select * from mytable_is_readonly ;
id | text
----+-------
1 | test1
2 | test2
3 | test3
(3 rows)
test=> select * from mytable_is_not_readonly ;
id | text
----+-------
1 | test1
2 | test2
3 | test3
(3 rows)
# As readonly role select data.
test=> \c - readonly
Password for user readonly:
You are now connected to database "test" as user "readonly".
test=> select * from mytable_is_readonly ;
id | text
----+-------
1 | test1
2 | test2
3 | test3
(3 rows)
test=> select * from mytable_is_not_readonly ;
ERROR: permission denied for relation mytable_is_not_readonly
# Dump with readonly role
aklaver@panda:~> /usr/local/pgsql93/bin/pg_dump -U readonly test --schema-only --table=mytable_is_readonly -p 5452 > readonly.sql
aklaver@panda:~> /usr/local/pgsql93/bin/pg_dump -U readonly test --schema-only --table=mytable_is_not_readonly -p 5452 > not_readonly.sql
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
# Dump with table owner
aklaver@panda:~> /usr/local/pgsql93/bin/pg_dump -U aklaver test --schema-only --table=mytable_is_readonly -p 5452 > readonly.sql
aklaver@panda:~> /usr/local/pgsql93/bin/pg_dump -U aklaver test --schema-only --table=mytable_is_not_readonly -p 5452 > not_readonly.sql
So the error you are seeing is correct. You gave the readonly role a specific permission,
select, for mytable_is_readonly. You did not do the same for mytable_is_not_readonly so only the
table owner has access to it, in this case aklaver. Per the docs:
http://www.postgresql.org/docs/9.3/interactive/sql-grant.html
SELECT
Allows SELECT from any column, or the specific columns listed, of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE. For sequences, this privilege also allows the use of the currval function. For large objects, this privilege allows the object to be read.
So you could also dump the data from mytable_is_readonly as the role readonly:
/usr/local/pgsql93/bin/pg_dump -U readonly test --table=mytable_is_readonly -p 5452 > readonly_data.sql
>
> - Dan
>
> On Fri, Feb 13, 2015 at 4:48 AM, Adrian Klaver
> <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
>
> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
