Search Postgresql Archives

Re: Issue dumping schema using readonly user

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux