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.

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]

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).

[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?

Ludwig

Gesendet: Mittwoch, 17. September 2014 um 17:33 Uhr
Von: "Adrian Klaver" <adrian.klaver@aklaver.com>
An: "ludwig@kni-online.de" <ludwig@kni-online.de>, pgsql-general@postgresql.org
Betreff: Re: [GENERAL] strange problem with not existing roles

On 09/17/2014 08:08 AM, ludwig@kni-online.de wrote:
> Hi list,
> 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't exist.

So how did the data get into the database?

>
> Example:
> In pgAdmin for schema user_data the follwing wrong grants are reported:
> ...
> GRANT ALL ON SCHEMA user_data TO "482499";
> GRANT ALL ON SCHEMA user_data TO "17708";

Where these actual roles at some point in time?

> ...
>
> Problem is:
> - these roles don't exist,
> - they can't be dropped (DROP ROLE "482499"; => FEHLER: Rolle „482499“
> existiert nicht)
> - grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM
> "482499"; => FEHLER: Rolle „482499“ existiert nicht)
> - ROLES can be recreated and dropped afterwards, but the grants persists:
> CREATE ROLE "482499";
> DROP OWNED BY "482499";
> REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";
> REVOKE ALL ON SCHEMA "user_data" FROM "482499";
> DROP ROLE "482499";
> - new tables can't be created in schemas with these grants
> CREATE TABLE user_data.test
> (
> id serial,
> PRIMARY KEY (id)
> );
> => FEHLER: Rolle 17708 wurde gleichzeitig gelöscht
>
>
> The roles are not listed in any catalog
> SELECT * FROM information_schema.xxxxxxx WHERE grantee in
> ('243683','243666','243689','482499','482499','17708');
>
> Only in pg_auth_members there is a set for each of these roles:
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');

What does pg_auth_members show for the problem roles?

> What can I do to get rid of these roles and grants?
> Ludwig

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general