Thread: Issue dumping schema using readonly user
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
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
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 allowsthe 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 objectto 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@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
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@mail.gmail.com 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@aklaver.com
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver Sent: Tuesday, February 17, 2015 4:12 PM To: Daniel LaMotte Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Issue dumping schema using readonly user 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@mail.gmail.com 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@aklaver.com So, basically OP wants his "read-only" user to be able to execute what's provided by Oracles DBMS_METADATA package. I'm not familiar with EnterprizeDB (Oracle-related) extensions of Postgresql, but they may have it implemented, check theirdocs. Regards, Igor Neyman
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@aklaver.com> 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.--
Myguess 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
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@aklaver.com> 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@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Daniel, * Daniel LaMotte (lamotte85@gmail.com) 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. > > 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). The issue is that pg_dump wants to lock the table against changes, which is really to prevent the table to change between "we got the definition of the table" and "pulling the records out of the table." It's not immediately obvious, to me at least, that there's really any need to lock the tables when doing a schema-only dump. Accesses to the catalogs should be consistent across the lifetime of the transaction which pg_dump is operating in and a schema-only dump isn't doing anything else. So, for my 2c, it seems like we should be able avoid issuing the LOCK TABLE statements when we're doing a schema-only dump and then this would work. Thanks! Stephen
Attachment
Simply put, giving access to a schema DOES NOT automatically give access to any table in the schema. So if you want a specific user ( or role) to be able to read (or pg_dump) all tables in the schema, then you must GRANT SELECT of all tables in that schema to the user (or role).
On Tue, Feb 17, 2015 at 5:41 PM, Stephen Frost <sfrost@snowman.net> wrote:
Daniel,
* Daniel LaMotte (lamotte85@gmail.com) 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.
>
> 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).
The issue is that pg_dump wants to lock the table against changes, which
is really to prevent the table to change between "we got the definition
of the table" and "pulling the records out of the table." It's not
immediately obvious, to me at least, that there's really any need to
lock the tables when doing a schema-only dump. Accesses to the catalogs
should be consistent across the lifetime of the transaction which
pg_dump is operating in and a schema-only dump isn't doing anything
else.
So, for my 2c, it seems like we should be able avoid issuing the LOCK
TABLE statements when we're doing a schema-only dump and then this would
work.
Thanks!
Stephen
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Melvin, * Melvin Davidson (melvin6925@gmail.com) wrote: > Simply put, giving access to a schema DOES NOT automatically give access to > any table in the schema. So if you want a specific user ( or role) to be > able to read (or pg_dump) all tables in the schema, then you must GRANT > SELECT of all tables in that schema to the user (or role). That's not the question at hand, however. He's not asking about dumping out the records of the table but rather about pulling out the schema, which any user can do, regardless of their permissions on the tables. There's no need to 'grant select' access on a table, or even to 'grant usage' on the schema, simply issue queries against the catalog. pg_dump is going beyond what's strictly necessary for a schema-only dump though, it's trying to lock all of the tables too, which really isn't necessary in this case. Thanks, Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes: > * Daniel LaMotte (lamotte85@gmail.com) 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]). > The issue is that pg_dump wants to lock the table against changes, which > is really to prevent the table to change between "we got the definition > of the table" and "pulling the records out of the table." It's not > immediately obvious, to me at least, that there's really any need to > lock the tables when doing a schema-only dump. Accesses to the catalogs > should be consistent across the lifetime of the transaction which > pg_dump is operating in and a schema-only dump isn't doing anything > else. This is the standard mistake about pg_dump, which is to imagine that it depends only on userspace operations while inspecting schema info. It doesn't; it makes use of things like ruleutils.c which operate on "latest available data" rules. Accordingly, no we're not going to skip taking the table locks. At least not without a ground-up rewrite of that whole mess, which as you know has been discussed multiple times without anything useful happening. regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost <sfrost@snowman.net> writes: > > The issue is that pg_dump wants to lock the table against changes, which > > is really to prevent the table to change between "we got the definition > > of the table" and "pulling the records out of the table." It's not > > immediately obvious, to me at least, that there's really any need to > > lock the tables when doing a schema-only dump. Accesses to the catalogs > > should be consistent across the lifetime of the transaction which > > pg_dump is operating in and a schema-only dump isn't doing anything > > else. > > This is the standard mistake about pg_dump, which is to imagine that it > depends only on userspace operations while inspecting schema info. It > doesn't; it makes use of things like ruleutils.c which operate on "latest > available data" rules. Accordingly, no we're not going to skip taking > the table locks. At least not without a ground-up rewrite of that whole > mess, which as you know has been discussed multiple times without anything > useful happening. There's two different points here- the first is the whole discussion around why pg_dump is depending on the backend for bits and pieces but not everything, but the second is- aren't the accesses from ruleutils.c now using an MVCC snapshot? Certainly there's a comment about that happening for pg_get_constraintdef_worker(), and other parts appear to go through SPI, but not everything does. Of particular relevance to this appears to be trigger and index handling, considering that the only thing pg_dump locks is relations anyway, much of the rest isn't relevant. Thanks, Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> This is the standard mistake about pg_dump, which is to imagine that it >> depends only on userspace operations while inspecting schema info. It >> doesn't; it makes use of things like ruleutils.c which operate on "latest >> available data" rules. > There's two different points here- the first is the whole discussion > around why pg_dump is depending on the backend for bits and pieces but > not everything, but the second is- aren't the accesses from ruleutils.c > now using an MVCC snapshot? Yeah, they're using *an* MVCC snapshot. But it's not the transaction snapshot, it's one that postdates all sinval traffic the backend has received. Robert's changes to get rid of SnapshotNow didn't really affect this issue at all. (To clarify: I'm worried about all the stuff that involves syscache consultations; those queries executed via SPI are not the issue.) It now strikes me that it might be possible to use Andreas' logical decoding infrastructure to allow pg_dump's backend to operate with a "historical catalog snapshot", which perhaps could resolve this problem. But there's no such logic there today, and I have no idea what the disadvantages might be. > Certainly there's a comment about that > happening for pg_get_constraintdef_worker(), and other parts appear to > go through SPI, but not everything does. Yeah, Jan originally had a plan of making ruleutils operate exclusively through SPI, but that lasted probably about a month and a half before people started using syscache-accessing shortcuts. I think we really would be best off to eliminate the SPI usage there altogether; it has little effect except to waste cycles and mislead the credulous into thinking ruleutils operates in userspace. regards, tom lane
Thank you all so much for the feedback.
At this point, I'm convinced that the issue is more complicated than I originally thought :)
FWIW, my use case is for a company internal database. I open the database up to all users by simply having a "readonly" user that anyone can use to connect to the database and run queries. Some tables have sensitive data in them that I would prefer not to allow users to simply have access to via this account. However, that said, there are internal people that we want to empower to develop our internal tool and so the idea is that they can take a database dump using the readonly user to replicate the schema in their own instance.
I realize this usage may be quite specific to our company, but it seemed like the issue (as an outsider) was very simple.
Just want to reiterate my thanks for taking the time to look into this issue and consider it. This was my first interaction with the Postgres community and it was an overwhelmingly good one!
- Dan
On Wed, Feb 18, 2015 at 6:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> This is the standard mistake about pg_dump, which is to imagine that it
>> depends only on userspace operations while inspecting schema info. It
>> doesn't; it makes use of things like ruleutils.c which operate on "latest
>> available data" rules.
> There's two different points here- the first is the whole discussion
> around why pg_dump is depending on the backend for bits and pieces but
> not everything, but the second is- aren't the accesses from ruleutils.c
> now using an MVCC snapshot?
Yeah, they're using *an* MVCC snapshot. But it's not the transaction
snapshot, it's one that postdates all sinval traffic the backend has
received. Robert's changes to get rid of SnapshotNow didn't really
affect this issue at all. (To clarify: I'm worried about all the stuff
that involves syscache consultations; those queries executed via SPI
are not the issue.)
It now strikes me that it might be possible to use Andreas' logical
decoding infrastructure to allow pg_dump's backend to operate with a
"historical catalog snapshot", which perhaps could resolve this problem.
But there's no such logic there today, and I have no idea what the
disadvantages might be.
> Certainly there's a comment about that
> happening for pg_get_constraintdef_worker(), and other parts appear to
> go through SPI, but not everything does.
Yeah, Jan originally had a plan of making ruleutils operate exclusively
through SPI, but that lasted probably about a month and a half before
people started using syscache-accessing shortcuts. I think we really
would be best off to eliminate the SPI usage there altogether; it has
little effect except to waste cycles and mislead the credulous into
thinking ruleutils operates in userspace.
regards, tom lane
On 02/19/2015 01:46 AM, Daniel LaMotte wrote: > Thank you all so much for the feedback. > > At this point, I'm convinced that the issue is more complicated than I > originally thought :) > > FWIW, my use case is for a company internal database. I open the > database up to all users by simply having a "readonly" user that anyone > can use to connect to the database and run queries. Some tables have > sensitive data in them that I would prefer not to allow users to simply > have access to via this account. However, that said, there are internal > people that we want to empower to develop our internal tool and so the > idea is that they can take a database dump using the readonly user to > replicate the schema in their own instance. > > I realize this usage may be quite specific to our company, but it seemed > like the issue (as an outsider) was very simple. FYI, using pgAdmin(http://www.pgadmin.org/) might be a solution. I just tried it. I logged in as readonly and looked at the mytable_is_not_readonly table. pgAdmin threw a permissions error, but still showed the CREATE TABLE script in the SQL pane. Could be a way to let your users get at the schema definitions. > > Just want to reiterate my thanks for taking the time to look into this > issue and consider it. This was my first interaction with the Postgres > community and it was an overwhelmingly good one! > > - Dan > > -- Adrian Klaver adrian.klaver@aklaver.com