| 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 16:44:53 |
| Message-ID: | trinity-8c70f648-bee6-48fa-8b62-e1498e521bd8-1411058693031@3capp-1and1-bs03 |
| 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>this database runs as develop-version on my PC and was created by hand, no dumps or pg_upgrade.</div>
<div> </div>
<div>The same database runs as production-version on another server (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far without these problems.</div>
<div> </div>
<div>pgAdmin shows a mix of the normal roles and these "ghost-roles", I don't know the queries running in pgAdmins-background for that result.</div>
<div> </div>
<div>[SNIP]</div>
<div>select * from pg_roles where oid in(10, 482499, 17708, 17687);</div>
<div>=></div>
<div>[SNIP]</div>
<div>
<div>kniprath;t;t;t;t;t;t;f;-1;********;infinity;;17687<br/>
postgres;t;t;t;t;t;t;t;-1;********;infinity;;10</div>
<div>[/SNIP]</div>
<div> </div>
<div>[SNIP]</div>
<div>select * from pg_roles where rolname in('482499', '17708');</div>
<div>[/SNIP]</div>
<div>=> empty result</div>
<div> </div>
<div>One tested workaround was to dump the schema-contents (tables, sequences, functions etc.), drop and recreate the schema and restore the dumped contents.</div>
<div>But I'm curious about what has caused the problems and how to avoid them...</div>
<div> </div>
<div>Ludwig</div>
</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 style="margin:0 0 10px 0;"><b>Gesendet:</b> Donnerstag, 18. September 2014 um 15:53 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/18/2014 04:12 AM, ludwig(at)kni-online(dot)de wrote:<br/>
> Hi Adrian,<br/>
> data got into the database with normal update/insert-queries from<br/>
> logged-in database-users using "normal" PG-Users/roles,<br/>
> the "ghost-roles" (with these unusual numerical role-names) were never<br/>
> created by me, I don't know where they come from.<br/>
<br/>
I should have been more specific.<br/>
<br/>
Did the database get created by restoring a dump file from somewhere, or<br/>
via pg_upgrade or just by creating the schema and adding data over time?<br/>
<br/>
The numeric part, at least as shown below, is the oid of the role and<br/>
all roles have that. The question is whether pgAdmin is showing the oid<br/>
or the actual role name? See below for a queries to help determine that.<br/>
<br/>
> The query<br/>
> [SNIP]<br/>
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in<br/>
> ('243683','243666','243689','482499','482499','17708');<br/>
> [/SNIP]<br/>
> has the following result:<br/>
> [SNIP]<br/>
> roleid;member;grantor;admin_option;<br/>
> 17699;17708;10;f<br/>
> 17699;482499;17687;f<br/>
> 17701;243666;17687;f<br/>
> 17699;243683;17687;f<br/>
> 17710;243689;17687;f<br/>
> [/SNIP]<br/>
<br/>
So what is the result if you do?:<br/>
<br/>
select * from pg_roles where oid in(10, 482499, 17708, 17687);<br/>
<br/>
select * from pg_roles where rolname in('482499', '17708');<br/>
<br/>
10 should be the postgres role, it is the others that are of interest.<br/>
<br/>
> Yust a thought:<br/>
> In some schemas the public user has full default-privileges (it's for<br/>
> uploading GIS-data from Shapefiles, each uploaded file generates a new<br/>
> table).<br/>
<br/>
So who originaly created the schema?<br/>
<br/>
> [SNIP]<br/>
> ALTER DEFAULT PRIVILEGES IN SCHEMA user_data<br/>
> GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER<br/>
> ON TABLES<br/>
> TO public;<br/>
> [/SNIP]<br/>
> Another thought:<br/>
> Each "normal" DB-user has *one* granted role, but some of theses roles<br/>
> themselves can have mutliple granted subroles.<br/>
> Perhaps a reason for my problems?<br/>
<br/>
Not sure. At this point just trying to establish the current state.<br/>
<br/>
> Ludwig<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.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | John R Pierce | 2014-09-18 16:54:19 | Re: Why isn't Java support part of Postgresql core? |
| Previous Message | cowwoc | 2014-09-18 16:07:52 | Re: Why isn't Java support part of Postgresql core? |