Re: [GENERAL] permission denied for schema topology
On 09/12/2014 02:27 AM, Iain Mott wrote: Thanks Tom and everyone that replied. Since my last email my service provider managed to solve the problem on my main database. I looked at the schemas listed in phpPgAdmin on this database before it was fixed and there were two main schemas listed, "public" and "topology", both owned by postgres. Now when I look, topology is no longer there. The problem was fixed on that database however not on others and when I create a new datbase via cpanel (the only way the system allows) - the problem reoccurs and I can't do a dump of the new db. Two things: 1) Looks like the template database they are using with CREATE DATABASE has the topology schema already in it, which is why it is showing up again. 2) What user are you running the pg_dump as? Thanks again for your input - I think it will help me communicate with them to resolve the problem now fully. Cheers, Iain -- 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] permission denied for schema topology
Thanks Tom and everyone that replied. Since my last email my service provider managed to solve the problem on my main database. I looked at the schemas listed in phpPgAdmin on this database before it was fixed and there were two main schemas listed, "public" and "topology", both owned by postgres. Now when I look, topology is no longer there. The problem was fixed on that database however not on others and when I create a new datbase via cpanel (the only way the system allows) - the problem reoccurs and I can't do a dump of the new db. Thanks again for your input - I think it will help me communicate with them to resolve the problem now fully. Cheers, Iain Em Qui, 2014-09-11 às 10:05 -0400, Tom Lane escreveu: > Iain Mott writes: > > Here's what happens (the important error messages are in English): > > > [~]# pg_dump mydatabase > dump.sql > > pg_dump: comando SQL falhou > > pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema > > topology > > pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE > > > I am able to perform dumps of the databases via phpPdAdmin in the > > "cpanel" of the server, but this is going very inconvenient - hoping to > > use pg_dump > > If it works through phpPgAdmin, then phpPgAdmin must be using some other > (more privileged) user ID than what pg_dump is using by default. A simple > workaround therefore ought to be to use pg_dump's -U switch to use that > other user ID. > > From the rest of the thread I gather that you ought to complain to your > service provider that they installed postgis when you didn't ask for it. > But selecting the right user ID to dump as is important anyway; it > generally doesn't work to use an underprivileged ID for pg_dump. > > regards, tom lane > > -- 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] permission denied for schema topology
Iain Mott writes: > Here's what happens (the important error messages are in English): > [~]# pg_dump mydatabase > dump.sql > pg_dump: comando SQL falhou > pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema > topology > pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE > I am able to perform dumps of the databases via phpPdAdmin in the > "cpanel" of the server, but this is going very inconvenient - hoping to > use pg_dump If it works through phpPgAdmin, then phpPgAdmin must be using some other (more privileged) user ID than what pg_dump is using by default. A simple workaround therefore ought to be to use pg_dump's -U switch to use that other user ID. >From the rest of the thread I gather that you ought to complain to your service provider that they installed postgis when you didn't ask for it. But selecting the right user ID to dump as is important anyway; it generally doesn't work to use an underprivileged ID for pg_dump. regards, tom lane -- 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] permission denied for schema topology
On 09/11/2014 04:37 AM, Iain Mott wrote: Thanks a lot. Here is the response to => SELECT postgis_full_version(); postgi So what does pgAdmin show for ownership of the schemas? -- 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] permission denied for schema topology
Thanks a lot. Here is the response to => SELECT postgis_full_version(); postgi s_full_version - POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" G DAL="GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found" LIBXML="2.7.6" TOPOLOG Y RASTER (1 registro) Em Qui, 2014-09-11 às 13:21 +0200, Rémi Cura escreveu: > Yep, > > this means you are using postgis. > > I don't know if you use postgis topology. > > If you have a schema topology containing a topology table, then you > also have postgis_topology installed. > > > You can check this with this query : "SELECT postgis_full_version();" > > > > Maybe the extensions have been created with another user id than > your's, hence the trouble. > > Another trouble could come from the fact that postgis and > postgis_topology may have been installed without using postgres > CREAtE EXTENSION (old version of postgis). > > > > Cheers, > Rémi-C > > > 2014-09-11 13:12 GMT+02:00 Iain Mott : > Thanks Rémi-C for the quick reply. By coincidence my site does > involve > mapping, however I wasn't aware that the server might be using > postgis > and postgis_topology. In psql when I type "\d" i get the > following: > > public | comentarios | tabela | myusername > public | featuredata | tabela | myusername > public | geography_columns | visão | myusername > public | geometry_columns | visão | myusername > public | raster_columns| visão | myusername > public | raster_overviews | visão | myusername > public | rss | tabela | myusername > public | spatial_ref_sys | tabela | myusername > > geography_columns, geometry_columns, raster_columns, > raster_overviews > and spatial_ref_sys are all recent changes to my database (I > didn't > create them and I wasn't aware of their existence until the > switch to > the new server). Do these and the errors I'm receiving > indicate the use > of postgis and postgis_topology by the server? > > Just googled for doing a dump of a postgis database and > encountered this > command: > > pg_dump --no-acl --no-owner $DATABASE > dump.sql > > I tried it, but it results in the same error messages > > Cheers, > > > > > > > > > > Em Qui, 2014-09-11 às 12:53 +0200, Rémi Cura escreveu: > > Hey, > > > > if you are using postgis and postgis_topology, > > there are specific backup/restore process. > > Cheers, > > Rémi-C > > > > > > 2014-09-11 12:49 GMT+02:00 Iain Mott : > > Hello, > > > > The server for my websites was recently changed and > upgraded. > > I have ssh > > access to the server and since the upgrade I am no > longer able > > to use > > "pg_dump" to perform scripted backups. I've written > to the > > site's > > support services, but until now, they've not been > able to help > > (they've > > responded saying they will likely need to refer the > case on to > > a > > developer - I don't have any guarantees that it > will be > > resolved). > > > > I've googled for this - but didn't come up with any > relevant > > solutions. > > Can someone on this list please make suggestions > that I can > > pass on to > > the technical support? > > > > Here's what happens (the important error messages > are in > > English): > > > > > > [~]# pg_dump mydatabase > dump.sql > > Senha: > > pg_dump: comando SQL falhou > > pg_dump: Mensagem de erro do servidor: ERROR: > permission > > denied for schema topology > > pg_dump: O comando foi: LOCK TABLE topology.topology > IN ACCESS > > SHARE MODE > > > > > > --- > > > > I am able to perform dumps of the databases via > phpPdAdmin in > > the > > "cpanel" of the server, but this is going very >
Re: [GENERAL] permission denied for schema topology
Yep, this means you are using postgis. I don't know if you use postgis topology. If you have a schema topology containing a topology table, then you also have postgis_topology installed. You can check this with this query : "SELECT postgis_full_version();" Maybe the extensions have been created with another user id than your's, hence the trouble. Another trouble could come from the fact that postgis and postgis_topology may have been installed without using postgres CREAtE EXTENSION (old version of postgis). Cheers, Rémi-C 2014-09-11 13:12 GMT+02:00 Iain Mott : > Thanks Rémi-C for the quick reply. By coincidence my site does involve > mapping, however I wasn't aware that the server might be using postgis > and postgis_topology. In psql when I type "\d" i get the following: > > public | comentarios | tabela | myusername > public | featuredata | tabela | myusername > public | geography_columns | visão | myusername > public | geometry_columns | visão | myusername > public | raster_columns| visão | myusername > public | raster_overviews | visão | myusername > public | rss | tabela | myusername > public | spatial_ref_sys | tabela | myusername > > geography_columns, geometry_columns, raster_columns, raster_overviews > and spatial_ref_sys are all recent changes to my database (I didn't > create them and I wasn't aware of their existence until the switch to > the new server). Do these and the errors I'm receiving indicate the use > of postgis and postgis_topology by the server? > > Just googled for doing a dump of a postgis database and encountered this > command: > > pg_dump --no-acl --no-owner $DATABASE > dump.sql > > I tried it, but it results in the same error messages > > Cheers, > > > > > > > > > > Em Qui, 2014-09-11 às 12:53 +0200, Rémi Cura escreveu: > > Hey, > > > > if you are using postgis and postgis_topology, > > there are specific backup/restore process. > > Cheers, > > Rémi-C > > > > > > 2014-09-11 12:49 GMT+02:00 Iain Mott : > > Hello, > > > > The server for my websites was recently changed and upgraded. > > I have ssh > > access to the server and since the upgrade I am no longer able > > to use > > "pg_dump" to perform scripted backups. I've written to the > > site's > > support services, but until now, they've not been able to help > > (they've > > responded saying they will likely need to refer the case on to > > a > > developer - I don't have any guarantees that it will be > > resolved). > > > > I've googled for this - but didn't come up with any relevant > > solutions. > > Can someone on this list please make suggestions that I can > > pass on to > > the technical support? > > > > Here's what happens (the important error messages are in > > English): > > > > > > [~]# pg_dump mydatabase > dump.sql > > Senha: > > pg_dump: comando SQL falhou > > pg_dump: Mensagem de erro do servidor: ERROR: permission > > denied for schema topology > > pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS > > SHARE MODE > > > > > > --- > > > > I am able to perform dumps of the databases via phpPdAdmin in > > the > > "cpanel" of the server, but this is going very inconvenient - > > hoping to > > use pg_dump > > > > Thanks, > > > > > > > > -- > > 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] permission denied for schema topology
Thanks Rémi-C for the quick reply. By coincidence my site does involve mapping, however I wasn't aware that the server might be using postgis and postgis_topology. In psql when I type "\d" i get the following: public | comentarios | tabela | myusername public | featuredata | tabela | myusername public | geography_columns | visão | myusername public | geometry_columns | visão | myusername public | raster_columns| visão | myusername public | raster_overviews | visão | myusername public | rss | tabela | myusername public | spatial_ref_sys | tabela | myusername geography_columns, geometry_columns, raster_columns, raster_overviews and spatial_ref_sys are all recent changes to my database (I didn't create them and I wasn't aware of their existence until the switch to the new server). Do these and the errors I'm receiving indicate the use of postgis and postgis_topology by the server? Just googled for doing a dump of a postgis database and encountered this command: pg_dump --no-acl --no-owner $DATABASE > dump.sql I tried it, but it results in the same error messages Cheers, Em Qui, 2014-09-11 às 12:53 +0200, Rémi Cura escreveu: > Hey, > > if you are using postgis and postgis_topology, > there are specific backup/restore process. > Cheers, > Rémi-C > > > 2014-09-11 12:49 GMT+02:00 Iain Mott : > Hello, > > The server for my websites was recently changed and upgraded. > I have ssh > access to the server and since the upgrade I am no longer able > to use > "pg_dump" to perform scripted backups. I've written to the > site's > support services, but until now, they've not been able to help > (they've > responded saying they will likely need to refer the case on to > a > developer - I don't have any guarantees that it will be > resolved). > > I've googled for this - but didn't come up with any relevant > solutions. > Can someone on this list please make suggestions that I can > pass on to > the technical support? > > Here's what happens (the important error messages are in > English): > > > [~]# pg_dump mydatabase > dump.sql > Senha: > pg_dump: comando SQL falhou > pg_dump: Mensagem de erro do servidor: ERROR: permission > denied for schema topology > pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS > SHARE MODE > > > --- > > I am able to perform dumps of the databases via phpPdAdmin in > the > "cpanel" of the server, but this is going very inconvenient - > hoping to > use pg_dump > > Thanks, > > > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- 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] permission denied for schema topology
Hey, if you are using postgis and postgis_topology, there are specific backup/restore process. Cheers, Rémi-C 2014-09-11 12:49 GMT+02:00 Iain Mott : > Hello, > > The server for my websites was recently changed and upgraded. I have ssh > access to the server and since the upgrade I am no longer able to use > "pg_dump" to perform scripted backups. I've written to the site's > support services, but until now, they've not been able to help (they've > responded saying they will likely need to refer the case on to a > developer - I don't have any guarantees that it will be resolved). > > I've googled for this - but didn't come up with any relevant solutions. > Can someone on this list please make suggestions that I can pass on to > the technical support? > > Here's what happens (the important error messages are in English): > > > [~]# pg_dump mydatabase > dump.sql > Senha: > pg_dump: comando SQL falhou > pg_dump: Mensagem de erro do servidor: ERROR: permission denied for > schema topology > pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE > > > --- > > I am able to perform dumps of the databases via phpPdAdmin in the > "cpanel" of the server, but this is going very inconvenient - hoping to > use pg_dump > > Thanks, > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] permission denied for schema topology
Hello, The server for my websites was recently changed and upgraded. I have ssh access to the server and since the upgrade I am no longer able to use "pg_dump" to perform scripted backups. I've written to the site's support services, but until now, they've not been able to help (they've responded saying they will likely need to refer the case on to a developer - I don't have any guarantees that it will be resolved). I've googled for this - but didn't come up with any relevant solutions. Can someone on this list please make suggestions that I can pass on to the technical support? Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase > dump.sql Senha: pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE --- I am able to perform dumps of the databases via phpPdAdmin in the "cpanel" of the server, but this is going very inconvenient - hoping to use pg_dump Thanks, -- 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] permission denied for schema even as superuser.
Chris Young writes: > Greetings, > I'm trying to perform the following query, but receive a perplexing error, > even as superuser (postgres): > umdb_db=# insert into mainview_teststatusevent > (timestamp,host_id,test_id,old_status_id,new_status_id) values(now(), > 726,4,6,1); > ERROR: permission denied for schema nms > LINE 1: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" OPER... >^ > QUERY: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" > OPERATOR(pg_catalog.=) $1 FOR SHARE OF x > Could you please let me know how I might troubleshoot and/or fix this > 'permission denied' error? I'm running 9.0.4. That's a foreign key checking query. FK checks are done as the owner of the target table, not as the user who did the original query. So your problem is that the owner of mainview_teststatusevent lacks permissions to access the other table (or more specifically, the schema it's in). regards, tom lane -- 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] permission denied for schema even as superuser.
Could it be triggering a function that is defined with "SECURITY DEFINER" and the definer of the function does not have the right permissions? On Thu, May 5, 2011 at 4:03 AM, Chris Young wrote: > Greetings, > I'm trying to perform the following query, but receive a perplexing error, > even as superuser (postgres): > umdb_db=# insert into mainview_teststatusevent > (timestamp,host_id,test_id,old_status_id,new_status_id) values(now(), > 726,4,6,1); > ERROR: permission denied for schema nms > LINE 1: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" OPER... > ^ > QUERY: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" > OPERATOR(pg_catalog.=) $1 FOR SHARE OF x > Could you please let me know how I might troubleshoot and/or fix this > 'permission denied' error? I'm running 9.0.4. > In case it's relevant, the events leading up to this error are that I tried > migrating all my tables and sequences from the public schama to my newly > created nms schema. I simply did this: > create schema nms; > alter table foo set schema nms; > alter sequence foo_id_seq set schema nms; > For all my tables and sequences. > The query worked when the tables were in the public schema, but not after I > tried to change them to the new schema. > I already searched the mailing list for this issue, and I could only find > old posts (circa 2007) that suggested it might be a bug, and I expect it's > more likely I've just done something stupid or missed something out. > Cheers, > Chris Young -- --- John L Cheng -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] permission denied for schema even as superuser.
Greetings, I'm trying to perform the following query, but receive a perplexing error, even as superuser (postgres): umdb_db=# insert into mainview_teststatusevent (timestamp,host_id,test_id,old_status_id,new_status_id) values(now(), 726,4,6,1); ERROR: permission denied for schema nms LINE 1: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" OPER... ^ QUERY: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x Could you please let me know how I might troubleshoot and/or fix this 'permission denied' error? I'm running 9.0.4. In case it's relevant, the events leading up to this error are that I tried migrating all my tables and sequences from the public schama to my newly created nms schema. I simply did this: create schema nms; alter table foo set schema nms; alter sequence foo_id_seq set schema nms; For all my tables and sequences. The query worked when the tables were in the public schema, but not after I tried to change them to the new schema. I already searched the mailing list for this issue, and I could only find old posts (circa 2007) that suggested it might be a bug, and I expect it's more likely I've just done something stupid or missed something out. Cheers, Chris Young
Re: [GENERAL] permission denied for schema even as superuser.
Apologies, I have already solved this. I forgot to grant usage on schema nms to foo; where foo is the owner of mainview_teststatusevent. Cheers, Chris On Thu, May 5, 2011 at 7:03 PM, Chris Young wrote: > Greetings, > > I'm trying to perform the following query, but receive a perplexing error, > even as superuser (postgres): > > umdb_db=# insert into mainview_teststatusevent > (timestamp,host_id,test_id,old_status_id,new_status_id) values(now(), > 726,4,6,1); > ERROR: permission denied for schema nms > LINE 1: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" OPER... >^ > QUERY: SELECT 1 FROM ONLY "nms"."mainview_status" x WHERE "id" > OPERATOR(pg_catalog.=) $1 FOR SHARE OF x > > Could you please let me know how I might troubleshoot and/or fix this > 'permission denied' error? I'm running 9.0.4. > > In case it's relevant, the events leading up to this error are that I tried > migrating all my tables and sequences from the public schama to my newly > created nms schema. I simply did this: > > create schema nms; > alter table foo set schema nms; > alter sequence foo_id_seq set schema nms; > For all my tables and sequences. > > The query worked when the tables were in the public schema, but not after I > tried to change them to the new schema. > > I already searched the mailing list for this issue, and I could only find > old posts (circa 2007) that suggested it might be a bug, and I expect it's > more likely I've just done something stupid or missed something out. > > Cheers, > Chris Young >
Re: [GENERAL] permission denied for schema
Tom Lane <[EMAIL PROTECTED]> writes:
> jef peeraer <[EMAIL PROTECTED]> 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.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] permission denied for schema
Tom Lane schreef:
jef peeraer <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] permission denied for schema
jef peeraer <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] permission denied for schema
Tom Lane schreef:
jef peeraer <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] permission denied for schema
jef peeraer <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] 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}
te
