Re: strange problem with not existing roles

On 09/17/2014 08:08 AM, ludwig(at)kni-online(dot)de wrote:
> Hi list,
> I have a strange problem in postgres (PostgreSQL 9.3.1, compiled by
> Visual C++ build 1600, 64-bit), there are granted privileges on schemas,
> tables, columns for roles that don't exist.

So how did the data get into the database?

>
> Example:
> In pgAdmin for schema user_data the follwing wrong grants are reported:
> ...
> GRANT ALL ON SCHEMA user_data TO "482499";
> GRANT ALL ON SCHEMA user_data TO "17708";

Where these actual roles at some point in time?

> ...
>
> Problem is:
> - these roles don't exist,
> - they can't be dropped (DROP ROLE "482499"; => FEHLER: Rolle „482499“
> existiert nicht)
> - grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM
> "482499"; => FEHLER: Rolle „482499“ existiert nicht)
> - ROLES can be recreated and dropped afterwards, but the grants persists:
> CREATE ROLE "482499";
> DROP OWNED BY "482499";
> REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";
> REVOKE ALL ON SCHEMA "user_data" FROM "482499";
> DROP ROLE "482499";
> - new tables can't be created in schemas with these grants
> CREATE TABLE user_data.test
> (
> id serial,
> PRIMARY KEY (id)
> );
> => FEHLER: Rolle 17708 wurde gleichzeitig gelöscht
>
>
> The roles are not listed in any catalog
> SELECT * FROM information_schema.xxxxxxx WHERE grantee in
> ('243683','243666','243689','482499','482499','17708');
>
> Only in pg_auth_members there is a set for each of these roles:
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');

What does pg_auth_members show for the problem roles?

> What can I do to get rid of these roles and grants?
> Ludwig

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com