On 09/17/2014 08:08 AM, ludwig@xxxxxxxxxxxxx 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
