Re: empty role names in pg_dumpall output

On Wed, Apr 25, 2012 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?Filip_Rembia=C5=82kowski?= <filip.rembialkowski@gmail.com> writes:
>> PostgreSQL 9.0.4
>
>> I have this in pg_dumpall -g output (non-empty role names changed):
>
>> GRANT "" TO a  GRANTED BY postgres;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO "" GRANTED BY c;
>> GRANT "" TO "" GRANTED BY postgres;
>> GRANT "" TO "" GRANTED BY postgres;
>
> Hmm.  A look at the code in pg_dumpall suggests that the problem is
> unmatched entries in pg_auth_members, ie this query:
>
> SELECT ur.rolname AS roleid
> FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid
>
> is returning some null results.

Yes that is the case:

SELECT ur.rolname AS roleid, member, grantor
FROM pg_auth_members a
LEFT JOIN pg_authid ur on ur.oid = a.roleid
WHERE ur.oid IS NULL;

 roleid | member | grantor
--------+--------+---------
 <NULL> |  21468 |   19553
 <NULL> |  21468 |   19553
 <NULL> |  18332 |   19553
 <NULL> |  21468 |   19553
 <NULL> |  18332 |      10
 <NULL> |  20615 |      10
 <NULL> |  18332 |      10
 <NULL> |  21468 |   19553
(8 rows)


> You might look into that catalog
> and see if you can figure out what happened.
>

Could it be (theoretically) caused by human-made insertions into
pg_auth_members?

Maybe you remember some bug which could have caused this in the past?



Thanks alot for help,
Filip