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
