strange problem with not existing roles

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&#39;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 &quot;482499&quot;;<br/>
GRANT ALL ON SCHEMA user_data TO &quot;17708&quot;;<br/>
...</div>

<div><br/>
Problem is:<br/>
- these roles don&#39;t exist,<br/>
- they can&#39;t be dropped (DROP ROLE &quot;482499&quot;; =&gt; FEHLER:&nbsp; Rolle &bdquo;482499&ldquo; existiert nicht)<br/>
- grants can&#39;t be revoked (REVOKE ALL ON SCHEMA &quot;user_data&quot; FROM &quot;482499&quot;; =&gt; FEHLER:&nbsp; Rolle &bdquo;482499&ldquo; existiert nicht)<br/>
- ROLES can be recreated and dropped afterwards, but the grants persists:<br/>
&nbsp;&nbsp;&nbsp; CREATE ROLE &quot;482499&quot;;<br/>
&nbsp;&nbsp;&nbsp; DROP OWNED BY &quot;482499&quot;;<br/>
&nbsp;&nbsp;&nbsp; REVOKE CONNECT ON DATABASE &quot;wver_ims&quot; FROM &quot;482499&quot;;<br/>
&nbsp;&nbsp;&nbsp; REVOKE ALL ON SCHEMA &quot;user_data&quot; FROM &quot;482499&quot;;<br/>
&nbsp;&nbsp;&nbsp; DROP ROLE &quot;482499&quot;;<br/>
- new tables can&#39;t be created in schemas with these grants<br/>
&nbsp;&nbsp;&nbsp; CREATE TABLE user_data.test<br/>
&nbsp;&nbsp;&nbsp; (<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; id serial,<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PRIMARY KEY (id)<br/>
&nbsp;&nbsp;&nbsp; );<br/>
&nbsp;&nbsp;&nbsp; =&gt; FEHLER:&nbsp; Rolle 17708 wurde gleichzeitig gel&ouml;scht<br/>
&nbsp;&nbsp; &nbsp;<br/>
&nbsp;&nbsp; &nbsp;<br/>
The roles are not listed in any catalog<br/>
&nbsp;&nbsp; &nbsp;SELECT * FROM information_schema.xxxxxxx WHERE grantee in (&#39;243683&#39;,&#39;243666&#39;,&#39;243689&#39;,&#39;482499&#39;,&#39;482499&#39;,&#39;17708&#39;);</div>

<div><br/>
Only in pg_auth_members there is a set for each of these roles:<br/>
&nbsp;&nbsp; &nbsp;SELECT * FROM pg_catalog.pg_auth_members WHERE member in (&#39;243683&#39;,&#39;243666&#39;,&#39;243689&#39;,&#39;482499&#39;,&#39;482499&#39;,&#39;17708&#39;);</div>

<div>&nbsp;</div>

<div>What can I do to get rid of these roles and grants?</div>

<div>&nbsp;</div>

<div>Ludwig</div></div></body></html>

Attachment Content-Type Size
unknown_filename text/html 2.3 KB

In response to

Responses

Browse pgsql-general by date

  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