Re: strange problem with not existing roles

On 09/17/2014 08:08 AM, ludwig@kni-online.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.klaver@aklaver.com