Fw: strange problem with not existing roles

["ludwig@xxxxxxxxxxxxx" <ludwig@xxxxxxxxxxxxx>]

Hi List,

a workaround for my own problems as described below:

In the following system-table-colums (type aclitem[]) I replaced all entries with these non-existing Group-Roles with something like {postgres=arwdDxt/postgres,kniprath=arwdDxt/kniprath}, this resets the privileges to just these two (Admin-) users.

   pg_namespace.nspacl

   pg_class.relacl

   pg_default_acl.defaclacl

I dont't think it's good practice to update systemtables manually, but apparently I previously did something in my database, that messed the contents.

Ludwig

Gesendet: Mittwoch, 17. September 2014 um 17:08 Uhr
Von: "ludwig@xxxxxxxxxxxxx" <ludwig@xxxxxxxxxxxxx>
An: pgsql-general@xxxxxxxxxxxxxx
Betreff: strange problem with not existing roles

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.

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";
...

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 can I do to get rid of these roles and grants?

 

Ludwig