Re: pg_dump broken for non-super user

On Wed, May 4, 2016 at 9:35 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> On Wed, May 4, 2016 at 5:29 AM, Stephen Frost <sfrost(at)snowman(dot)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.

OK.

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

Hmm, I guess that's reasonable. It might cause problems for some
users, but then again it might not. I guess that's what a beta is
for.

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

Maybe that recommendation should be added to the documentation and/or
release notes someplace.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company