Re: strange problem with not existing roles - Mailing list pgsql-general

<div style="font-family: Verdana;font-size: 12.0px;"><div><div>Hi Adrian,</div><div>this database runs as
develop-versionon my PC and was created by hand, no dumps or pg_upgrade.</div><div> </div><div>The same database runs
asproduction-version on another server (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far without
theseproblems.</div><div> </div><div>pgAdmin shows a mix of the normal roles and these "ghost-roles", I don't know the
queriesrunning in pgAdmins-background for that result.</div><div> </div><div>[SNIP]</div><div>select * from pg_roles
whereoid 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_roleswhere rolname in('482499', '17708');</div><div>[/SNIP]</div><div>=> empty result</div><div> </div><div>One
testedworkaround was to dump the schema-contents (tables, sequences, functions etc.), drop and recreate the schema and
restorethe 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
010px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break:
after-white-space;"><divstyle="margin:0 0 10px 0;"><b>Gesendet:</b> Donnerstag, 18. September 2014 um 15:53 Uhr<br
/><b>Von:</b> "AdrianKlaver" <adrian.klaver@aklaver.com><br /><b>An:</b> "ludwig@kni-online.de"
<ludwig@kni-online.de>,pgsql-general@postgresql.org<br /><b>Betreff:</b> Re: [GENERAL] strange problem with not
existingroles</div><div name="quoted-content">On 09/18/2014 04:12 AM, ludwig@kni-online.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
databaseget created by restoring a dump file from somewhere, or<br /> via pg_upgrade or just by creating the schema and
addingdata over time?<br /><br /> The numeric part, at least as shown below, is the oid of the role and<br /> all roles
havethat. The question is whether pgAdmin is showing the oid<br /> or the actual role name? See below for a queries to
helpdetermine that.<br /><br /> > The query<br /> > [SNIP]<br /> > SELECT * FROM pg_catalog.pg_auth_members
WHEREmember in<br /> > ('243683','243666','243689','482499','482499','17708');<br /> > [/SNIP]<br /> > has the
followingresult:<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
whereoid 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
someschemas the public user has full default-privileges (it's for<br /> > uploading GIS-data from Shapefiles, each
uploadedfile 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
havemutliple granted subroles.<br /> > Perhaps a reason for my problems?<br /><br /> Not sure. At this point just
tryingto establish the current state.<br /><br /> > Ludwig<br /><br /> --<br /> Adrian Klaver<br />
adrian.klaver@aklaver.com<br/><br /><br /> --<br /> Sent via pgsql-general mailing list
(pgsql-general@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>