Re: empty role names in pg_dumpall output

On Fri, Apr 27, 2012 at 10:23 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ah-ha.  How about the member and grantor OIDs in those rows --- do
> they correspond to still-existing roles?  (I believe "10" would be
> the bootstrap superuser, so that should certainly still exist, but
> those other numbers are for user-made roles.)

The grantors still exists, the members - some yes, some not.

>
>> Could it be (theoretically) caused by human-made insertions into
>> pg_auth_members?
>
> Well, perhaps ... are you in the habit of hacking that catalog directly?

No, I dont have this bad habit, but the database is several years old,
and I can't rule it out.

I wonder if this case can be taken as catalog corruption, and maybe
postgres should guard users against it.

When you delete a role, its memberships are deleted.

Maybe there should be a NOT NULL && FK constraint on member and grantor?

I'm not that much into pg_catalog, maybe there is a good reason for
NOT having it.

Anyway, on my side the problem is closed, we just delete them and the
pg_dumpall output is fixed.

Thank you