Обсуждение: pgadmin 8.3 beta 5: ERROR: permission denied for tablespace pg_default

Hello,<br /><br />it may or may not be considered as a pgadmin error:<br /><br />I try to create an index on a column
ofa table. Using the pgadmin-wizard, that results in:<br /><br />CREATE INDEX otformularkopf_test<br />   ON
otformularkopf(id_pkfk) <br />       TABLESPACE pg_default;<br /><br />with NO chance to get rid of the "      
TABLESPACEpg_default" clause; so I am stuck with "ERROR: permission denied for tablespace pg_default"<br /><br
/>Accordingto the words of Master Tom in <a
href="http://svr5.postgresql.org/pgsql-hackers/2004-06/msg00947.php">http://svr5.postgresql.org/pgsql-hackers/2004-06/msg00947.php</a>,<br
/>"Joe User probably <font><font face="Arial, Helvetica, sans-serif">shouldn't be fooling with tablespaces at all." <br
/><br/>the "Joe User way" to create an index would be:<br /></font></font><br />CREATE INDEX otformularkopf_test<br />
  ON otformularkopf (id_pkfk);<br /><br />and allow Postgres to decide in which tablespace to put that index. But the
"tablespace"dropdown is not blankable.<br /><br />So my suggestion: make tablespace default blank in the wizards. <br
/><br/>Harald<br clear="all" /><br />-- <br />GHUM Harald Massa<br />persuadere et programmare<br />Harald Armin
Massa<br/>Spielberger Straße 49<br />70435 Stuttgart<br />0173/9409607<br />fx 01212-5-13695179 <br />-<br />EuroPython
2008will take place in Vilnius, Lithuania - Stay tuned!  

Harald Armin Massa wrote:
> Hello,
> 
> it may or may not be considered as a pgadmin error:
> 
> I try to create an index on a column of a table. Using the
> pgadmin-wizard, that results in:
> 
> CREATE INDEX otformularkopf_test
>    ON otformularkopf (id_pkfk)
>        TABLESPACE pg_default;
> 
> with NO chance to get rid of the "       TABLESPACE pg_default" clause;
> so I am stuck with "ERROR: permission denied for tablespace pg_default"
>
> According to the words of Master Tom in
> http://svr5.postgresql.org/pgsql-hackers/2004-06/msg00947.php,
> " Joe User probably shouldn't be fooling with tablespaces at all."

Gah - so you can use it despite having no permissions on it if you don't
specify it? On what planet does that seem sane?

> and allow Postgres to decide in which tablespace to put that index. But
> the "tablespace" dropdown is not blankable.

No, and that was intentional to remove some ambiguity and subsequent
nasty surprises that we found could crop up as a result.

> So my suggestion: make tablespace default blank in the wizards.

I guess I'll have to on the CREATE version of the dialogs. Thankfully,
it shouldn't matter for the ALTER versions as they only script the
tabespace if you try to change it.

/D

Hi Harald!

On Sep 16, 8:56 pm, haraldarminma...@gmail.com ("Harald Armin Massa")
wrote:
> As much as I understood, pg_default is not necessarly the default
> tablespace??? But I never fiddled with tablespaces up to now :)

I have summed up which tablespace pg uses here:  http://archives.postgresql.org/pgadmin-hackers/2007-09/msg00051.php

Regards
Erwin

On Sep 15, 10:43 am, dp...@postgresql.org (Dave Page) wrote:
> Harald Armin Massa wrote:
> > Hello,
>
> > it may or may not be considered as a pgadmin error:
>
> > I try to create an index on a column of a table. Using the
> > pgadmin-wizard, that results in:
>
> > CREATE INDEX otformularkopf_test
> >    ON otformularkopf (id_pkfk)
> >        TABLESPACE pg_default;
>
> > with NO chance to get rid of the "       TABLESPACE pg_default" clause;
> > so I am stuck with "ERROR: permission denied for tablespace pg_default"
>
> > According to the words of Master Tom in
> >http://svr5.postgresql.org/pgsql-hackers/2004-06/msg00947.php,
> > " Joe User probably shouldn't be fooling with tablespaces at all."
>
> Gah - so you can use it despite having no permissions on it if you don't
> specify it? On what planet does that seem sane?


Unfortunately it affects ALTER cases as well. And there is nothing
pgAdmin even _could_ do about that.
Say, a user has CREATE rights on tablespace foo. He moves a table
there (or creates it there).  ALTER TABLE test SET TABLESPACE foo;

But later he is unhappy with the results. So he tries to move it back
where it came from: to pg_default, but he cannot!  ALTER TABLE test SET TABLESPACE pg_default;

In my book that's listed under "postgres bug", not under "sane". In
practice an admin has to explicitly grant CREATE rights on pg_default
along with any CREATE rights on any tablespace, or it will be a one
way trip. How many admins would think of that or even know it?



Regards
Erwin

Harald Armin Massa wrote:
> So my suggestion: make tablespace default blank in the wizards.

I've made it <default tablespace> so it's obvious what it is, and added
it to all the create dialogs.

Regards, Dave