[GENERAL] Fw: strange problem with not existing roles
Hi List,
a workaround for my own problems as described below:
In the following system-table-colums (type aclitem[]) I replaced all entries with these non-existing Group-Roles with something like {postgres=arwdDxt/postgres,kniprath=arwdDxt/kniprath}, this resets the privileges to just these two (Admin-) users.
pg_namespace.nspacl
pg_class.relacl
pg_default_acl.defaclacl
I dont't think it's good practice to update systemtables manually, but apparently I previously did something in my database, that messed the contents.
Ludwig
Gesendet: Mittwoch, 17. September 2014 um 17:08 Uhr
Von: "lud...@kni-online.de"
An: pgsql-general@postgresql.org
Betreff: strange problem with not existing roles
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.
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";
...
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.xxx 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 can I do to get rid of these roles and grants?
Ludwig
Re: [GENERAL] strange problem with not existing roles
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
[/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"
An: "lud...@kni-online.de" , pgsql-general@postgresql.org
Betreff: Re: [GENERAL] strange problem with not existing roles
On 09/18/2014 04:12 AM, lud...@kni-online.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.kla...@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
Re: [GENERAL] strange problem with not existing roles
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"
An: "lud...@kni-online.de" , pgsql-general@postgresql.org
Betreff: Re: [GENERAL] strange problem with not existing roles
On 09/17/2014 08:08 AM, lud...@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.xxx 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.kla...@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
[GENERAL] strange problem with not existing roles
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.
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";
...
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.xxx 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 can I do to get rid of these roles and grants?
Ludwig
