Thread: Tablespace permissions issue
Chris KL just raised an issue on IRC:
test=> create table test (a int4) tablespace pg_default;
ERROR: permission denied for tablespace pg_default
This wasn't encountered in my original patch because
pg_tablespace_aclmask() had this test reasonably early on:
+ if(tbloid == DEFAULTTBLSPC)
+ return (mask);
I guess that might have been a bit presumptuous but a similar thing
happens if you don't specify a tablespace:
tablespaceId = get_namespace_tablespace(namespaceId); /* note no permission check on tablespace in this
case*/
What should the solution be?
Gavin
> This wasn't encountered in my original patch because > pg_tablespace_aclmask() had this test reasonably early on: > > + if(tbloid == DEFAULTTBLSPC) > + return (mask); > > I guess that might have been a bit presumptuous but a similar thing > happens if you don't specify a tablespace: > > tablespaceId = get_namespace_tablespace(namespaceId); > /* note no permission check on tablespace in this case */ > > What should the solution be? Seems to me that the solution is that the inital pg_default tablespace should be create with USAGE rights to PUBLIC at initdb time. Then a superuser can always revoke the CREATE right if they like. All the CREATE and ALTER commands that specify tablespaces should actually check that they are allowed to create in pg_default rather than just assume. That way, an admin can force users to NOT use the default tablespace if they wish, even though the users have CREATE table rights on the schema. Chris
Gavin Sherry <swm@linuxworld.com.au> writes:
> Chris KL just raised an issue on IRC:
> test=> create table test (a int4) tablespace pg_default;
> ERROR: permission denied for tablespace pg_default
This is not a bug. If you don't have tablespace permissions, you don't
get to specify where your table goes --- it gets put into the tablespace
that is default for your schema or database. Which is not necessarily
pg_default.
You could possibly argue that pg_default ought to have world permissions
in the default setup, but I don't really see why. Joe User probably
shouldn't be fooling with tablespaces at all.
> I guess that might have been a bit presumptuous but a similar thing
> happens if you don't specify a tablespace:
> tablespaceId = get_namespace_tablespace(namespaceId);
> /* note no permission check on tablespace in this case */
That's not a bug either. The presumption is that whoever assigned a
default tablespace to your schema or database had the right to do so.
Users who just make use of that default don't need any extra
permissions.
regards, tom lane
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> That way, an admin can force users to NOT use the default tablespace if
> they wish, even though the users have CREATE table rights on the schema.
I think the above statement is nonsensical. Perhaps you are confusing
the notions of "default tablespace for a schema" and "pg_default"?
If the DBA has set a particular default tablespace for a schema, then
that is what he wants users who create in that schema to use --- there
really isn't any other sensible interpretation IMHO. I do not see why
overriding such a choice and forcing the table back into pg_default
should be an operation that is (by default) allowed to unprivileged
users.
Really the only thing special about pg_default is that it is the default
tablespace for template1. If you create a new database and give it a
different default tablespace, then pg_default has got absolutely no
relevance to that database; so I can't see any reason that ordinary
users of that database should be allowed to force their tables back into
pg_default.
regards, tom lane
BTW, it occurs to me that there's a bug in the current implementation of
CREATE DATABASE when you change the database's default tablespace.
The CREATE DATABASE code assumes that it can physically copy all content
of the old-database's-default-tablespace into the
new-database's-default-tablespace, rather than keeping it in the same
tablespace as it does for all non-default tablespaces.
This fails in just one case: where a table inside the old database has
explicitly specified use of a tablespace that happens to be the same as
the old database's default tablespace. Now, when looking at the new
database's reltablespace column, it will appear that that table is in
the old database's default tablespace ... but that ain't where CREATE
DATABASE put it. Ooops.
I don't see any reasonable way for CREATE DATABASE to avoid this
problem, since it can't necessarily look inside the source database.
My thought is that the cleanest fix is to never allow reltablespace
(or nsptablespace) to explicitly specify the database's default
tablespace; that is, if you writeCREATE TABLE ... TABLESPACE x
and "x" is the database-level default, we should silently store zero
instead of x's OID into reltablespace. The table would get created
in the same place either way, but the implications for future cloning
or dump/reload of the database would be different. The table would go
to the new database default tablespace, whatever that is, instead of
staying in "x".
Thoughts?
regards, tom lane
Dear Tom, > BTW, it occurs to me that there's a bug in the current implementation of > CREATE DATABASE when you change the database's default tablespace. > [...] > Now, when looking at the new database's reltablespace column, it will > appear that that table is in the old database's default tablespace ... > but that ain't where CREATE DATABASE put it. Ooops. > > Thoughts? I submitted a patch 3 weeks ago so as to update the namespace ownership wrt the database owner on the first connexion to a database. It seems to me that you need just the very same hook for housekeeping stuff after "create database": that would not avoid the problem, but at least fix the information before it is used. I'm not sure it is that easy to check for the table space of a table... But as it is just a newly created database, maybe we can simply assume that all tables are in the new database "default" tablespace, so the update is really simple? It's more a "fix-me later" approach, but it does not look that bad, IMHO. -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> It's more a "fix-me later" approach, but it does not look that bad, IMHO.
It seems a bit risky to me. The worst possible consequence of the
ownership stuff not happening is that objects have wrong ownership
(and even there it's not so much "wrong" as "we decided we'd like
this other behavior better"). But the consequence of not fixing
reltablespace is that the database is broken... so I'd prefer not to
need to.
regards, tom lane
On Mon, 28 Jun 2004, Tom Lane wrote: > I don't see any reasonable way for CREATE DATABASE to avoid this > problem, since it can't necessarily look inside the source database. > My thought is that the cleanest fix is to never allow reltablespace > (or nsptablespace) to explicitly specify the database's default > tablespace; that is, if you write > CREATE TABLE ... TABLESPACE x > and "x" is the database-level default, we should silently store zero > instead of x's OID into reltablespace. The table would get created > in the same place either way, but the implications for future cloning > or dump/reload of the database would be different. The table would go > to the new database default tablespace, whatever that is, instead of > staying in "x". > > Thoughts? I don't see any other way around it than this (other than looking at the template database's data, but that wont happen for 7.5). Thanks, Gavin
Dear Tom, > Fabien COELHO <coelho@cri.ensmp.fr> writes: > > It's more a "fix-me later" approach, but it does not look that bad, IMHO. > > It seems a bit risky to me. The worst possible consequence of the > ownership stuff not happening is that objects have wrong ownership (and > even there it's not so much "wrong" as "we decided we'd like this other > behavior better"). Well, if the ownership-switch transaction would fail, then I think the login would also fail and the connection would be broken... No data is lost because none where put in the database, as it has just been created. But the system would be blocked anyway. > But the consequence of not fixing reltablespace is that the database is > broken... If it is simply broken, that is it does not work at all, as it is a newly created database hency mostly empty database, it is not that bad as no data is lost. If it is broken but the fact appears much later on, that's another issue. My intuition is that a failure of such transactions would just show that there is a big underlying problem, thus having a early-on failure would be a rather good thing as it would prevent the user to go on with an instable installation. > so I'd prefer not to need to. I cannot see how it could fail under normal condition (i.e. apart disk full or hardware/os failure), but you're sure a better juge of that than me!! -- Fabien Coelho - coelho@cri.ensmp.fr