Обсуждение: permission denied for schema
i get this when i try to insert a record into a table. all permissions / privileges seems ok, but probably i missed something. i am running a 8.1.3 on linux. registratie=> INSERT INTO clienten (anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES ('true','45','58','864','30','221'); ERROR: permission denied for schema deterp CONTEXT: SQL statement "SELECT 1 FROM ONLY "deterp"."teams" x WHERE "id" = $1 FOR SHARE OF x" registratie=> \dn List of schemas Name | Owner --------------------+--------------- dekempen | jpe deterp | jpe information_schema | postgres mozaiek | mozaiek_admin pg_catalog | postgres pg_toast | postgres public | postgres testsaw | testsaw_admin (8 rows) registratie=> \dn+ List of schemas Name | Owner | Access privileges | Description --------------------+---------------+--------------------------------------------------------------------------------------------------------------+---------------------------------- dekempen | jpe | {jpe=UC/jpe,cawdekempen=UC/jpe} | deterp | jpe | {jpe=UC/jpe,deterp_group=UC/jpe,terp_user=UC/jpe} | information_schema | postgres | {postgres=UC/postgres,=U/postgres} | mozaiek | mozaiek_admin | {mozaiek_admin=UC/mozaiek_admin,jpe=UC/mozaiek_admin,mozaiek_user=UC/mozaiek_admin,mozaiek=UC/mozaiek_admin} | pg_catalog | postgres | {postgres=UC/postgres,=U/postgres} | System catalog schema pg_toast | postgres | | Reserved schema for TOAST tables public | postgres | {postgres=UC/postgres,=UC/postgres,deterp_group=U/postgres,cawdekempen=U/postgres} | Standard public schema testsaw | testsaw_admin | {testsaw_admin=UC/testsaw_admin,jpe=UC/testsaw_admin,testsaw_user=UC/testsaw_admin,testsaw=UC/testsaw_admin} | (8 rows) registratie=> \d clienten Table "deterp.clienten" Column | Type | Modifiers -----------------+----------+------------------------------------------------------- id | integer | not null default nextval('clienten_id_seq'::regclass) anoniem | boolean | not null default false naam | text | voornamen | text | geslacht | smallint | leeftijd | smallint | geboortedatum | date | origine | smallint | team_id | integer | hulpverlener_id | integer | Indexes: "pk_deterp_clienten" PRIMARY KEY, btree (id) Foreign-key constraints: "fk_clienten_geslacht" FOREIGN KEY (geslacht) REFERENCES lijst_items(score) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_clienten_hv_id" FOREIGN KEY (hulpverlener_id) REFERENCES gebruikers(id) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_clienten_leeftijd" FOREIGN KEY (leeftijd) REFERENCES lijst_items(score) ON UPDATE RESTRICT ON DELETE RESTRICT "fk_deterp_clienten_team_id" FOREIGN KEY (team_id) REFERENCES teams(id) ON UPDATE RESTRICT ON DELETE RESTRICT registratie=> \z clienten_id_seq Access privileges for database "registratie" Schema | Name | Type | Access privileges --------+-----------------+----------+-------------------------------------------- deterp | clienten_id_seq | sequence | {jpe=arwdRxt/jpe,deterp_group=arwdRxt/jpe} (1 row) registratie=> \dg List of roles Role name | Superuser | Create role | Create DB | Connections | Member of ----------------+-----------+-------------+-----------+-------------+---------------- cawdekempen | no | no | no | no limit | db_admin | yes | yes | yes | no limit | dekempen_admin | no | no | no | no limit | {cawdekempen} dekempen_user | no | no | no | no limit | {cawdekempen} deterp | no | no | no | no limit | deterp_group | no | no | no | no limit | deterp_resp | no | no | no | no limit | jpe | yes | yes | yes | no limit | {mozaiek} liesbet | no | no | no | no limit | {cawdekempen} mozaiek | no | no | no | no limit | mozaiek_admin | no | yes | yes | no limit | {mozaiek} mozaiek_user | no | no | no | no limit | {mozaiek} postgres | yes | yes | yes | no limit | terp_admin | no | no | no | no limit | {deterp_group} terp_user | no | no | no | no limit | {deterp_group} testsaw | no | no | no | no limit | testsaw_admin | no | yes | yes | no limit | {testsaw} testsaw_user | no | no | no | no limit | {testsaw} tom | no | no | yes | no limit | tomdec | no | yes | yes | no limit | (20 rows) registratie=> \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of ----------------+-----------+-------------+-----------+-------------+---------------- cawdekempen | no | no | no | no limit | db_admin | yes | yes | yes | no limit | dekempen_admin | no | no | no | no limit | {cawdekempen} dekempen_user | no | no | no | no limit | {cawdekempen} deterp | no | no | no | no limit | deterp_group | no | no | no | no limit | deterp_resp | no | no | no | no limit | jpe | yes | yes | yes | no limit | {mozaiek} liesbet | no | no | no | no limit | {cawdekempen} mozaiek | no | no | no | no limit | mozaiek_admin | no | yes | yes | no limit | {mozaiek} mozaiek_user | no | no | no | no limit | {mozaiek} postgres | yes | yes | yes | no limit | terp_admin | no | no | no | no limit | {deterp_group} terp_user | no | no | no | no limit | {deterp_group} testsaw | no | no | no | no limit | testsaw_admin | no | yes | yes | no limit | {testsaw} testsaw_user | no | no | no | no limit | {testsaw} tom | no | no | yes | no limit | tomdec | no | yes | yes | no limit | (20 rows) r
jef peeraer <jef.peeraer@telenet.be> writes: > i get this when i try to insert a record into a table. all permissions / > privileges seems ok, but probably i missed something. i am running a > 8.1.3 on linux. > registratie=> INSERT INTO clienten > (anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES > ('true','45','58','864','30','221'); > ERROR: permission denied for schema deterp > CONTEXT: SQL statement "SELECT 1 FROM ONLY "deterp"."teams" x WHERE > "id" = $1 FOR SHARE OF x" That's a foreign-key check, which is supposed to be done as the owner of the table. You did not show us who owns table clienten, but I think that role must be missing the intended(?) membership in deterp_group. regards, tom lane
Tom Lane schreef: > jef peeraer <jef.peeraer@telenet.be> writes: >> i get this when i try to insert a record into a table. all permissions / >> privileges seems ok, but probably i missed something. i am running a >> 8.1.3 on linux. > >> registratie=> INSERT INTO clienten >> (anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES >> ('true','45','58','864','30','221'); >> ERROR: permission denied for schema deterp >> CONTEXT: SQL statement "SELECT 1 FROM ONLY "deterp"."teams" x WHERE >> "id" = $1 FOR SHARE OF x" > > That's a foreign-key check, which is supposed to be done as the owner of > the table. You did not show us who owns table clienten, but I think > that role must be missing the intended(?) membership in deterp_group. registratie=# \z deterp.clienten; Access privileges for database "registratie" Schema | Name | Type | Access privileges --------+----------+-------+------------------------------------------ deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe} (1 row) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
jef peeraer <jef.peeraer@telenet.be> writes: > Tom Lane schreef: >> That's a foreign-key check, which is supposed to be done as the owner of >> the table. You did not show us who owns table clienten, but I think >> that role must be missing the intended(?) membership in deterp_group. > registratie=# \z deterp.clienten; > Access privileges for database "registratie" > Schema | Name | Type | Access privileges > --------+----------+-------+------------------------------------------ > deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe} Hmm. This doesn't actually say so, but I think that jpe must be the owner of clienten, and he's also the owner of the schema. Which makes the failure really darn odd. I tried to reproduce the situation on 8.1.9 and couldn't see any problem. I do not see anything in the 8.1 CVS history that looks related, but it's slightly possible this is a bug that's fixed between 8.1.3 and 8.1.9 (and even if that is not the case, you really really ought to update anyway). Would you see if the problem recurs if you do a pg_dump -s and load the schema into a fresh database? If it doesn't, perhaps there is some weird corruption in the ACL entries. If it is reproducible, please post the complete schema (or enough of it to reproduce the problem) to pgsql-bugs. regards, tom lane
Tom Lane schreef: > jef peeraer <jef.peeraer@telenet.be> writes: >> Tom Lane schreef: >>> That's a foreign-key check, which is supposed to be done as the owner of >>> the table. You did not show us who owns table clienten, but I think >>> that role must be missing the intended(?) membership in deterp_group. > >> registratie=# \z deterp.clienten; >> Access privileges for database "registratie" >> Schema | Name | Type | Access privileges >> --------+----------+-------+------------------------------------------ >> deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe} > > Hmm. This doesn't actually say so, but I think that jpe must be the > owner of clienten, and he's also the owner of the schema. Which makes > the failure really darn odd. > > I tried to reproduce the situation on 8.1.9 and couldn't see any > problem. I do not see anything in the 8.1 CVS history that looks > related, but it's slightly possible this is a bug that's fixed between > 8.1.3 and 8.1.9 (and even if that is not the case, you really really > ought to update anyway). ok, i upgraded anywway to the latest stable one, also because i needed the 'drop table if exists' feature. it was indeed a foreign referenced table that was not owned by jpe. i must have overlooked it. thanks for you're time anyway. jef peeraer > > Would you see if the problem recurs if you do a pg_dump -s and load the > schema into a fresh database? If it doesn't, perhaps there is some > weird corruption in the ACL entries. If it is reproducible, please post > the complete schema (or enough of it to reproduce the problem) to > pgsql-bugs. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Tom Lane <tgl@sss.pgh.pa.us> writes: > jef peeraer <jef.peeraer@telenet.be> writes: >> Tom Lane schreef: >>> That's a foreign-key check, which is supposed to be done as the owner of >>> the table. You did not show us who owns table clienten, but I think >>> that role must be missing the intended(?) membership in deterp_group. > >> registratie=# \z deterp.clienten; >> Access privileges for database "registratie" >> Schema | Name | Type | Access privileges >> --------+----------+-------+------------------------------------------ >> deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe} > > Hmm. This doesn't actually say so, but I think that jpe must be the > owner of clienten, and he's also the owner of the schema. Which makes > the failure really darn odd. > > I tried to reproduce the situation on 8.1.9 and couldn't see any > problem. I do not see anything in the 8.1 CVS history that looks > related, but it's slightly possible this is a bug that's fixed between > 8.1.3 and 8.1.9 (and even if that is not the case, you really really > ought to update anyway). Just for the record, I've seen the same problem in my database (a superuser trying to INSERT leads to the same security exception), not sure which release but the version was 8.1. Haven't seen the problem for some time now, currently running 8.1.8. Regards, Manuel.