Hi Adrian,
this database runs as develop-version on my PC and was created by hand, no dumps or pg_upgrade.
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.
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.
[SNIP]
select * from pg_roles where oid in(10, 482499, 17708, 17687);
=>
[SNIP]
kniprath;t;t;t;t;t;t;f;-1;********;infinity;;17687
postgres;t;t;t;t;t;t;t;-1;********;infinity;;10
postgres;t;t;t;t;t;t;t;-1;********;infinity;;10
[/SNIP]
[SNIP]
select * from pg_roles where rolname in('482499', '17708');
[/SNIP]
=> empty result
One tested workaround was to dump the schema-contents (tables, sequences, functions etc.), drop and recreate the schema and restore the dumped contents.
But I'm curious about what has caused the problems and how to avoid them...
Ludwig
Gesendet: Donnerstag, 18. September 2014 um 15:53 Uhr
Von: "Adrian Klaver" <adrian.klaver@xxxxxxxxxxx>
An: "ludwig@xxxxxxxxxxxxx" <ludwig@xxxxxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx
Betreff: Re: strange problem with not existing roles
Von: "Adrian Klaver" <adrian.klaver@xxxxxxxxxxx>
An: "ludwig@xxxxxxxxxxxxx" <ludwig@xxxxxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx
Betreff: Re: strange problem with not existing roles
On 09/18/2014 04:12 AM, ludwig@xxxxxxxxxxxxx wrote:
> Hi Adrian,
> data got into the database with normal update/insert-queries from
> logged-in database-users using "normal" PG-Users/roles,
> the "ghost-roles" (with these unusual numerical role-names) were never
> created by me, I don't know where they come from.
I should have been more specific.
Did the database get created by restoring a dump file from somewhere, or
via pg_upgrade or just by creating the schema and adding data over time?
The numeric part, at least as shown below, is the oid of the role and
all roles have that. The question is whether pgAdmin is showing the oid
or the actual role name? See below for a queries to help determine that.
> The query
> [SNIP]
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');
> [/SNIP]
> has the following result:
> [SNIP]
> roleid;member;grantor;admin_option;
> 17699;17708;10;f
> 17699;482499;17687;f
> 17701;243666;17687;f
> 17699;243683;17687;f
> 17710;243689;17687;f
> [/SNIP]
So what is the result if you do?:
select * from pg_roles where oid in(10, 482499, 17708, 17687);
select * from pg_roles where rolname in('482499', '17708');
10 should be the postgres role, it is the others that are of interest.
> Yust a thought:
> 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).
So who originaly created the schema?
> [SNIP]
> ALTER DEFAULT PRIVILEGES IN SCHEMA user_data
> GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
> ON TABLES
> TO public;
> [/SNIP]
> Another thought:
> Each "normal" DB-user has *one* granted role, but some of theses roles
> themselves can have mutliple granted subroles.
> Perhaps a reason for my problems?
Not sure. At this point just trying to establish the current state.
> Ludwig
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> Hi Adrian,
> data got into the database with normal update/insert-queries from
> logged-in database-users using "normal" PG-Users/roles,
> the "ghost-roles" (with these unusual numerical role-names) were never
> created by me, I don't know where they come from.
I should have been more specific.
Did the database get created by restoring a dump file from somewhere, or
via pg_upgrade or just by creating the schema and adding data over time?
The numeric part, at least as shown below, is the oid of the role and
all roles have that. The question is whether pgAdmin is showing the oid
or the actual role name? See below for a queries to help determine that.
> The query
> [SNIP]
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');
> [/SNIP]
> has the following result:
> [SNIP]
> roleid;member;grantor;admin_option;
> 17699;17708;10;f
> 17699;482499;17687;f
> 17701;243666;17687;f
> 17699;243683;17687;f
> 17710;243689;17687;f
> [/SNIP]
So what is the result if you do?:
select * from pg_roles where oid in(10, 482499, 17708, 17687);
select * from pg_roles where rolname in('482499', '17708');
10 should be the postgres role, it is the others that are of interest.
> Yust a thought:
> 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).
So who originaly created the schema?
> [SNIP]
> ALTER DEFAULT PRIVILEGES IN SCHEMA user_data
> GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
> ON TABLES
> TO public;
> [/SNIP]
> Another thought:
> Each "normal" DB-user has *one* granted role, but some of theses roles
> themselves can have mutliple granted subroles.
> Perhaps a reason for my problems?
Not sure. At this point just trying to establish the current state.
> Ludwig
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
