Thread: pg_dumpall -g problem

I have a Postgresql 7.2.2 installation, and I am having a problem with
the results of doing a pg_dumpall -g.

It dumps out three users, ignoring the fourth.  If I connect to template1
with psql and do select * from pg_users I get four, and actually this fourth
is the one who owns all the user DBs, so I know it exists.  It does dump
the postgres ID.

I also have a 7.3.2 system and that reports all except the postgres ID but
including the one that the 7.2.2 system ignores.  Ignoring the postgres ID
makes some sense in that when you install Postgresql it gets set up by
default so it will be there anyway.

The missing one has a user ID of 101 if that is of any significance.

Is this a known problem?  Is there a workaround?

Thanks in advance

David

David Goodenough <david.goodenough@btconnect.com> writes:
> I have a Postgresql 7.2.2 installation, and I am having a problem with
> the results of doing a pg_dumpall -g.

> It dumps out three users, ignoring the fourth.

Looking at the source code for 7.2's pg_dumpall, it's trying to dump
everything except the postgres user --- on the assumption that you'll
recreate the postgres user at initdb time.  The code is

$PSQL -d template1 -At -c "\
SELECT
  'CREATE USER \"' || usename || '\" WITH SYSID ' || usesysid
  || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' else '' end
  || CASE WHEN usecreatedb THEN ' CREATEDB'::text ELSE ' NOCREATEDB' END
  || CASE WHEN usesuper THEN ' CREATEUSER'::text ELSE ' NOCREATEUSER' END
  || CASE WHEN valuntil IS NOT NULL THEN ' VALID UNTIL '''::text
    || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';'
FROM pg_shadow
WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');"

I'd guess that your missing user is the one who owns template0.  Now
that should be the postgres user, unless you've been hacking on
pg_database ...

            regards, tom lane