Another nasty pg_dump problem

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

On my 7.3 server:
REVOKE ALL ON TABLE exercise_activities FROM PUBLIC;
GRANT ALL ON TABLE exercise_activities TO chriskl;
GRANT SELECT ON TABLE exercise_activities TO "au-diary";
GRANT SELECT ON TABLE exercise_activities TO "au-php";

Now if you load that into 7.4CVS, you get:

REVOKE ALL ON TABLE exercise_activities FROM PUBLIC;
GRANT ALL ON TABLE exercise_activities TO chriskl;
GRANT SELECT ON TABLE exercise_activities TO "\""au-diary\""";
GRANT SELECT ON TABLE exercise_activities TO "\""au-php\""";

I've repaired this in CVS tip. While testing it, though, I notice that
CVS-tip pg_dump puts out useless commands

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;

which are not generated when dumping from 7.3. The reason evidently is
that this check in pg_dump.c no longer works:

/*
* If it's the PUBLIC namespace, don't emit a CREATE SCHEMA record
* for it, since we expect PUBLIC to exist already in the
* destination database. And emit ACL info only if the ACL isn't
* the standard value for PUBLIC.
*/
if (strcmp(nspinfo->nspname, "public") == 0)
{
if (!aclsSkip && strcmp(nspinfo->nspacl, "{=UC}") != 0)
dumpACL(fout, "SCHEMA", qnspname, nspinfo->nspname, NULL,
nspinfo->usename, nspinfo->nspacl,
nspinfo->oid);
}

since the default ACL for public no longer looks like that. Can we fix
this?

regards, tom lane

Tom Lane writes:

I've repaired this in CVS tip. While testing it, though, I notice that
CVS-tip pg_dump puts out useless commands

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;

which are not generated when dumping from 7.3. The reason evidently is
that this check in pg_dump.c no longer works:

This could be fixed, but note that elsewhere we use

/*
* Always start with REVOKE ALL FROM PUBLIC, so that we don't have to
* wire-in knowledge about the default public privileges for different
* kinds of objects.
*/
appendPQExpBuffer(firstsql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",
type, name);

So maybe this isn't such a bad state after all.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

This could be fixed, but note that elsewhere we use

/*
* Always start with REVOKE ALL FROM PUBLIC, so that we don't have to
* wire-in knowledge about the default public privileges for different
* kinds of objects.
*/
appendPQExpBuffer(firstsql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",
type, name);

So maybe this isn't such a bad state after all.

Well, if you want to take that position then the test for "{=UC}" ought
to be ripped out, so that we are consistent about it across backend
versions.

regards, tom lane