Postgres Pro
Downloads
Home   >   mailing lists

Thread: Another nasty pg_dump problem

Another nasty pg_dump problem

From
"Christopher Kings-Lynne"
Date:
On my 7.3 server:

australia=# \dp exercise_activities                      Access privileges for database "australia"Schema |
Table       |                    Access privileges
 
--------+---------------------+---------------------------------------------
------------public | exercise_activities |
{=,chriskl=arwdRxt,auadmin=arwdRxt,au-diary=r,au-php=r}
(1 row)

is dumped as:

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:

australia=# \dp exercise_activities                                              Access privileges for
database "australia"Schema |        Table        |
Access privileges
--------+---------------------+---------------------------------------------
-------------------------------------------------------------public | exercise_activities |
{auadmin=a*r*w*d*R*x*t*/auadmin,chriskl=arwdRxt/auadmin,"\"au-diary\"=r/auad
min","\"au-php\"=r/auadmin"}
(1 row)

Which is dumped as:

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\""";

ie. 7.4 considers the double quotes around a username to be part of the
username...

Chris

Re: Another nasty pg_dump problem

From
Tom Lane
Date:
"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
PUBLICto exist already in the        * destination database.  And emit ACL info only if the ACL isn't        * the
standardvalue 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

Re: Another nasty pg_dump problem

From
Peter Eisentraut
Date:
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
publicprivileges 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

Re: Another nasty pg_dump problem

From
Tom Lane
Date:
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