Re: Issue dumping schema using readonly user

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(at)snowman(dot)net> wrote:

> Daniel,
>
> * Daniel LaMotte (lamotte85(at)gmail(dot)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.