Обсуждение: empty role names in pg_dumpall output

Hi,

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;

There is no CREATE ROLE "" ... in the output.

select * from pg_authid where length(rolname)<1 -- returns no rows

I would like to get rid of these roles, but how?

I appreciate any hints / ideas.

=?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.  You might look into that catalog
and see if you can figure out what happened.

As far as making the problem go away is concerned, deleting the bogus
pg_auth_members rows should do it; but it would be a good idea to try to
figure out what happened first.

            regards, tom lane

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

=?UTF-8?Q?Filip_Rembia=C5=82kowski?= <filip.rembialkowski@gmail.com> writes:
> On Wed, Apr 25, 2012 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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:

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.)

> 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?
Aside from the possibility of inserting a just-plain-wrong OID, there's
the possibility of inserting a valid row but forgetting to add a
pg_shdepend entry that would protect the row against the underlying
role being dropped.

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

Doesn't ring a bell offhand.

            regards, tom lane

On Fri, Apr 27, 2012 at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.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