Re: REVOKE CREATE does not work on default tablespace

Tom Lane wrote:
> Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM> writes:
>> Tom Lane wrote:
>>> It's presumed that the right to create tables within a database entails
>>> the right to create them someplace; hence no permissions check is made
>>> on the database's default tablespace. Without that, not only does plain
>>> CREATE TABLE fail (including CREATE TEMP TABLE), but any query complex
>>> enough to require a temporary file would fail as well. So you'd pretty
>>> much have to grant rights on the tablespace to every user of the database
>>> anyway.
>
>> If only temporary objects are problem I think better solution is to create
>> pg_temp tablespace which will be used as default for temporary data
>
> Why are you so eager to make CREATE TABLE fail? (If you really want to
> do that there are other ways, for instance revoking create privilege
> within the DB.)

I expect it if I revoke rights to do it. This behavior is non documented (I did
not find it in documentation) and it is also exception of ACL behavior. If you
forgot to revoke create rights on public schema normal user is able to inject
own table and override another in different schema during search_path evaluation
(see for example security definer issue). I also expect when I use tablespace
name in command which is same as default tablespace I get same result.

I still does not see any benefit from user side why postgres has this exception.
It is confusing and it should generate potential security risk.

By the way, there is also strange behavior when for example you want to create
table with primary key. Index is stored in default tablespace instead of same as
table has.

> Once you've created a database with a given tablespace as default, the
> only way to make it stop using the tablespace is to drop the whole DB;
> there are no half measures because you can't move the system catalogs
> (particularly not pg_class). So I'm not seeing the point of enforcing
> tablespace usage against users of the database rather than at the time
> of DB creation.

I don't want to stop usage the default tablespace, I'm only want to stop user
create there new tables.

Zdenek