| From: | "ludwig(at)kni-online(dot)de" <ludwig(at)kni-online(dot)de> |
|---|---|
| To: | "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: strange problem with not existing roles |
| Date: | 2014-09-18 11:12:48 |
| Message-ID: | trinity-52f8ef48-0d1f-497e-8639-b05379ddb958-1411038768282@3capp-1and1-bs01 |
| 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 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" <adrian(dot)klaver(at)aklaver(dot)com><br/>
<b>An:</b> "ludwig(at)kni-online(dot)de" <ludwig(at)kni-online(dot)de>, pgsql-general(at)postgresql(dot)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, ludwig(at)kni-online(dot)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/>
adrian(dot)klaver(at)aklaver(dot)com<br/>
<br/>
<br/>
--<br/>
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 4.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dev Kumkar | 2014-09-18 12:50:59 | Re: [GENERAL] pg_multixact issues |
| Previous Message | Andres Freund | 2014-09-18 10:33:38 | Re: [SQL] pg_multixact issues |