Robert,
* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, May 4, 2016 at 5:29 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > There is no such limitation on using pg_dump as a non-superuser. It's
> > always been the case that you need to be able to LOCK the table that
> > you're dumping. If you don't have rights to LOCK a certain table then
> > pg_dump is going to throw an error just like the one you saw.
>
> But in Rushabh's example, he's not doing that. He's trying to do a
> full-database dump of a database that contains one object which the
> dump user has rights to access. Previously, that worked. Now, it
> fails with an error about a system catalog. How is that not broken?
As I mentioned up-thread, the optimization to skip tables which are not
"interesting" has been improved in the patch-set posted this morning to
skip over tables whose ACLs haven't changed from the defaults. With
that patch, we will skip over catalog tables whose ACLs haven't been
changed and Rushabh's command will work as a non-superuser, assuming
none of the ACLs on tables in pg_catalog have been changed.
However, if any of the ACLs have been changed on tables in pg_catalog,
we'll attempt to lock those tables and include those ACLs. That will
still work in many cases as you only need SELECT access to be able to
lock a table in access share mode, but if the permissions on pg_authid
are changed, the same failure will occur.
I wouldn't recommend depending on those tables being excluded in backup
scripts. If you wish to dump everything except pg_catalog, the -N
switch can be used to exclude that schema.
Thanks!
Stephen