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@xxxxxxxxxxx> wrote:
Daniel,
* Daniel LaMotte (lamotte85@xxxxxxxxx) 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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
