Re: strange problem with not existing roles

On 09/18/2014 04:12 AM, ludwig(at)kni-online(dot)de 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(dot)klaver(at)aklaver(dot)com