Re: strange problem with not existing roles

On 09/18/2014 09:44 AM, ludwig@kni-online.de wrote:
> Hi Adrian,
> this database runs as develop-version on my PC and was created by hand,
> no dumps or pg_upgrade.
> The same database runs as production-version on another server
> (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far
> without these problems.
> pgAdmin shows a mix of the normal roles and these "ghost-roles", I don't
> know the queries running in pgAdmins-background for that result.

I would say pgAdmin could not find a rolname in pg_roles so it just used
the role oid as the role 'name'.

> [SNIP]
> select * from pg_roles where oid in(10, 482499, 17708, 17687);
> =>
> [SNIP]
> kniprath;t;t;t;t;t;t;f;-1;********;infinity;;17687
> postgres;t;t;t;t;t;t;t;-1;********;infinity;;10
> [/SNIP]
> [SNIP]
> select * from pg_roles where rolname in('482499', '17708');
> [/SNIP]
> => empty result
> One tested workaround was to dump the schema-contents (tables,
> sequences, functions etc.), drop and recreate the schema and restore the
> dumped contents.
> But I'm curious about what has caused the problems and how to avoid them...

Hard to say at this point. The only thing I can point out is the
postgres role granted membership to the 17708 'role' to whatever role
has the oid of 17699 and the kniprath role did the same for the 482499
'role'. Maybe looking up what is the role with an oid of 17699 might jog
the memory, so:

select * from pg_roles where oid = 17699;

The only other thing I can think to do is troll the Postgres logs over
the time period in question for the oids, GRANT, REVOKE, the schema
user_data and see if anything stands out.

> Ludwig



--
Adrian Klaver
adrian.klaver@aklaver.com