From: | "ludwig(at)kni-online(dot)de" <ludwig(at)kni-online(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | strange problem with not existing roles |
Date: | 2014-09-17 15:08:58 |
Message-ID: | trinity-fbe3bb20-4aa0-491f-8bec-fb0caa5eb0fb-1410966538450@3capp-1and1-bs04 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi list,<br/>
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.</div>
<div><br/>
Example:<br/>
In pgAdmin for schema user_data the follwing wrong grants are reported:<br/>
...<br/>
GRANT ALL ON SCHEMA user_data TO "482499";<br/>
GRANT ALL ON SCHEMA user_data TO "17708";<br/>
...</div>
<div><br/>
Problem is:<br/>
- these roles don't exist,<br/>
- they can't be dropped (DROP ROLE "482499"; => FEHLER: Rolle „482499“ existiert nicht)<br/>
- grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM "482499"; => FEHLER: Rolle „482499“ existiert nicht)<br/>
- ROLES can be recreated and dropped afterwards, but the grants persists:<br/>
CREATE ROLE "482499";<br/>
DROP OWNED BY "482499";<br/>
REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";<br/>
REVOKE ALL ON SCHEMA "user_data" FROM "482499";<br/>
DROP ROLE "482499";<br/>
- new tables can't be created in schemas with these grants<br/>
CREATE TABLE user_data.test<br/>
(<br/>
id serial,<br/>
PRIMARY KEY (id)<br/>
);<br/>
=> FEHLER: Rolle 17708 wurde gleichzeitig gelöscht<br/>
<br/>
<br/>
The roles are not listed in any catalog<br/>
SELECT * FROM information_schema.xxxxxxx WHERE grantee in ('243683','243666','243689','482499','482499','17708');</div>
<div><br/>
Only in pg_auth_members there is a set for each of these roles:<br/>
SELECT * FROM pg_catalog.pg_auth_members WHERE member in ('243683','243666','243689','482499','482499','17708');</div>
<div> </div>
<div>What can I do to get rid of these roles and grants?</div>
<div> </div>
<div>Ludwig</div></div></body></html>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 2.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-09-17 15:22:57 | Re: Regarding timezone |
Previous Message | Dev Kumkar | 2014-09-17 14:54:42 | Re: [SQL] pg_multixact issues |