Re: 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(dot)peeraer(at)telenet(dot)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(dot)peeraer(at)telenet(dot)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(dot)peeraer(at)telenet(dot)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(dot)peeraer(at)telenet(dot)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(at)sss(dot)pgh(dot)pa(dot)us> writes:

> jef peeraer <jef(dot)peeraer(at)telenet(dot)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.