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