Thread: Using default tablespace for database?

I've searched the archives on this subject - appreciate some
clarification on tablespaces defined during create database.  Using
8.0beta5 on Linux, I've revoked permission to use default tablespaces
and created a new default tablespace for a new database with the
following:

revoke create on schema public from public
revoke create on tablespace pg_default from public
revoke create on tablespace pg_global from public
create tablespace XXXXts location '/a/b/c/XXXXts'
create database XXXXdb tablespace XXXXts

-- connect to database XXXXdb as postgres
create schema authorization YYYY

-- Now, I connect to database XXXXdb as YYYY.
create table public.t1 (f1 smallint) - fails as it should
create table t1 (f1 smallint) tablespace pg_default - fails as it should
(1) create table t1 (f1 smallint) tablespace XXXXts- fails - should it?
(2) create table t1 (f1 smallint) - succeeds

I'm unclear about the last two.  Doesn't the fact that (2) succeeds
imply that (1) should also succeed?  The documentation says that when
created without an explicit tablespace, the table is being created in
the default tablespace for the database.  I can't verify that because
when I look in pg_class, the tablespace column is null.  In psql, \d+
YYYY.t1 does not identify the tablespace.  If it is not going into the
XXXXts tablespace, then where is it going, since I've prohibited it from
using the default tablespaces?

If I explicitly "grant create on tablespace XXXXts to YYYY", then (1)
works.  Even then, \d+ and pg_class still show no tablespace for this
table.  (1) and (2) look the same in the system catalog tables (and in
the views in PgAdmin III.)

--
Guy Rouillier

"Guy Rouillier" <guyr@masergy.com> writes:
> (1) create table t1 (f1 smallint) tablespace XXXXts- fails - should it?

Sure.  You didn't grant any permissions on tablespace XXXXts.

> (2) create table t1 (f1 smallint) - succeeds

The presumption is that there should be no direct permission checks on
the default tablespace for a database --- if a user has the ability to
create tables in a database at all, then he's got the right to create
'em in the database's default tablespace.  To do otherwise would break
too many applications for too little gain.  However, if you explicitly
mention "tablespace foo", then you'd better have permissions on foo.

            regards, tom lane

Tom Lane wrote:
>
> The presumption is that there should be no direct permission checks
> on the default tablespace for a database --- if a user has the
> ability to create tables in a database at all, then he's got the
> right to create 'em in the database's default tablespace.  To do
> otherwise would break too many applications for too little gain.
> However, if you explicitly mention "tablespace foo", then you'd
> better have permissions on foo.

Tom, thank you for the reply.  I understand what you are saying, and now
that I understand the rules I can work within them.  This does seem
logically inconsistent, though.  That I can create a table in the
database's default tablespace if I don't specify it demonstrates that I
have permission to do; this permission has been implicitly granted to
all users of the database.  That implicit permission doesn't disappear
because I mention the same tablespace explicitly.  I suppose the safest
thing to do is to grant create on the tablespace to all users of the DB,
so that their creates will always work if they mention the tablespace or
not.

Any idea why the tablespace name does not appear to be associated with
the table in the system catalog?  Perhaps the tablespace name is not
recorded if it is the default TS?

--
Guy Rouillier

"Guy Rouillier" <guyr@masergy.com> writes:
> ... This does seem
> logically inconsistent, though.  That I can create a table in the
> database's default tablespace if I don't specify it demonstrates that I
> have permission to do; this permission has been implicitly granted to
> all users of the database.  That implicit permission doesn't disappear
> because I mention the same tablespace explicitly.

I'm not convinced.  I think that
    CREATE TABLE foo (...);
means "create my table in whatever tablespace my database likes", while
    CREATE TABLE foo (...) TABLESPACE bar;
means "create my table in tablespace bar".  It might happen that bar is
the same tablespace as foo's default, but that doesn't make the cases
equivalent; in the latter case I'm asserting that I have the right to
control the tablespace selection, whereas in the former I'm not.  So in
the latter case I should need the permissions to make that assertion,
in the former case not.

There isn't a whole lot of practical difference right at the moment, but
let's suppose that in a release or two someone writes an ALTER DATABASE
SET TABLESPACE command.  My expectation would be that a table declared
the first way would move to the new database-default tablespace, but a
table declared the second way would stay right in tablespace bar.
That's why you need some explicit permissions to say the latter.

> Any idea why the tablespace name does not appear to be associated with
> the table in the system catalog?

Right at the moment we don't have a way to distinguish "this table is in
the database's default tablespace" from "this table is in tablespace foo
that just happens to be the same as the database's default tablespace".
But IMHO there is a semantic difference there; it's only an implementation
glitch that we can't enforce the difference yet.

            regards, tom lane