| From: | "ludwig(at)kni-online(dot)de" <ludwig(at)kni-online(dot)de> |
|---|---|
| To: | pgsql-general <pgsql-general(at)postgresql(dot)org>, "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com> |
| Subject: | Fw: strange problem with not existing roles |
| Date: | 2014-09-23 10:54:35 |
| Message-ID: | trinity-04f50951-c5a9-4535-aa86-a45e117d77be-1411469675572@3capp-1and1-bs05 |
| 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>
<div>Hi List,</div>
<div>a workaround for my own problems as described below:</div>
<div style="margin:0 0 10px 0;">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.</div>
<div style="margin:0 0 10px 0;"> pg_namespace.nspacl</div>
<div style="margin:0 0 10px 0;"> pg_class.relacl</div>
<div style="margin:0 0 10px 0;"> pg_default_acl.defaclacl</div>
<div style="margin:0 0 10px 0;">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.</div>
<div style="margin:0 0 10px 0;">Ludwig</div>
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Mittwoch, 17. September 2014 um 17:08 Uhr<br/>
<b>Von:</b> "ludwig(at)kni-online(dot)de" <ludwig(at)kni-online(dot)de><br/>
<b>An:</b> pgsql-general(at)postgresql(dot)org<br/>
<b>Betreff:</b> strange problem with not existing roles</div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div name="quoted-content">
<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>
</div>
</div>
</div>
</div></div></body></html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | FarjadFarid(ChkNet) | 2014-09-23 11:03:15 | Re: csv import error |
| Previous Message | Rémi Cura | 2014-09-23 10:54:16 | Re: csv import error |