Fw: strange problem with not existing roles
От | ludwig@kni-online.de |
---|---|
Тема | Fw: strange problem with not existing roles |
Дата | |
Msg-id | trinity-04f50951-c5a9-4535-aa86-a45e117d77be-1411469675572@3capp-1and1-bs05 обсуждение исходный текст |
Ответ на | Re: Regarding timezone (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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 somethinglike {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 mydatabase, that messed the contents. Ludwig Gesendet: Mittwoch, 17. September 2014 um 17:08 Uhr Von: "ludwig@kni-online.de" <ludwig@kni-online.de> An: pgsql-general@postgresql.org 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 privilegeson 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
В списке pgsql-general по дате отправления: