<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Hi Adrian,</div>
<div>data got into the database with normal update/insert-queries from logged-in database-users using "normal" PG-Users/roles,</div>
<div>the "ghost-roles" (with these unusual numerical role-names) were never created by me, I don't know where they come from.</div>
<div> </div>
<div>The query</div>
<div>[SNIP]</div>
<div>SELECT * FROM pg_catalog.pg_auth_members WHERE member in ('243683','243666','243689','482499','482499','17708');</div>
<div>[/SNIP]</div>
<div> </div>
<div>has the following result:</div>
<div>[SNIP]</div>
<div>roleid;member;grantor;admin_option;</div>
<div>
<div>17699;17708;10;f<br/>
17699;482499;17687;f<br/>
17701;243666;17687;f<br/>
17699;243683;17687;f<br/>
17710;243689;17687;f</div>
</div>
<div>[/SNIP]</div>
<div> </div>
<div>Yust a thought:</div>
<div>In some schemas the public user has full default-privileges (it's for uploading GIS-data from Shapefiles, each uploaded file generates a new table).</div>
<div>[SNIP]</div>
<div>ALTER DEFAULT PRIVILEGES IN SCHEMA user_data<br/>
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES<br/>
TO public;</div>
<div>
<div>[/SNIP]</div>
<div> </div>
<div>Another thought:</div>
<div>Each "normal" DB-user has *one* granted role, but some of theses roles themselves can have mutliple granted subroles.</div>
<div> </div>
<div>Perhaps a reason for my problems?</div>
<div> </div>
<div>Ludwig</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 style="margin:0 0 10px 0;"><b>Gesendet:</b> Mittwoch, 17. September 2014 um 17:33 Uhr<br/>
<b>Von:</b> "Adrian Klaver" <***@aklaver.com><br/>
<b>An:</b> "***@kni-online.de" <***@kni-online.de>, pgsql-***@postgresql.org<br/>
<b>Betreff:</b> Re: [GENERAL] strange problem with not existing roles</div>
<div name="quoted-content">On 09/17/2014 08:08 AM, ***@kni-online.de wrote:<br/>
> Hi list,<br/>
> I have a strange problem in postgres (PostgreSQL 9.3.1, compiled by<br/>
> Visual C++ build 1600, 64-bit), there are granted privileges on schemas,<br/>
> tables, columns for roles that don't exist.<br/>
<br/>
So how did the data get into the database?<br/>
<br/>
><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/>
<br/>
Where these actual roles at some point in time?<br/>
<br/>
> ...<br/>
><br/>
> Problem is:<br/>
> - these roles don't exist,<br/>
> - they can't be dropped (DROP ROLE "482499"; => FEHLER: Rolle „482499“<br/>
> existiert nicht)<br/>
> - grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM<br/>
> "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<br/>
> ('243683','243666','243689','482499','482499','17708');<br/>
><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<br/>
> ('243683','243666','243689','482499','482499','17708');<br/>
<br/>
What does pg_auth_members show for the problem roles?<br/>
<br/>
> What can I do to get rid of these roles and grants?<br/>
> Ludwig<br/>
<br/>
<br/>
--<br/>
Adrian Klaver<br/>
***@aklaver.com<br/>
<br/>
<br/>
--<br/>
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)<br/>
To make changes to your subscription:<br/>
<a href="http://www.postgresql.org/mailpref/pgsql-general" target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a></div>
</div>
</div>
</div></div></body></html>