Thread: REVOKE CREATE does not work on default tablespace

I try to revoke create privileges (PG8.2.4) on the default tablespace and I
found following strange behavior:


----------------
As superuser:

revoke create on TABLESPACE pg_default from u_test1;

As u_test1:

ns_test=> create table test_06 (id int) tablespace pg_default;
ERROR:  permission denied for tablespace pg_default
ns_test=> create table test_06 (id int);
CREATE TABLE

ns_test=> select relname, reltablespace from pg_class where relname like
'%test_06%';

    relname    | reltablespace
--------------+---------------
  test_06      |             0

ns_test=# select oid,spcname from pg_tablespace;
   oid  |  spcname
-------+------------
   1664 | pg_global
  24585 | ts_test
   1663 | pg_default

------------------

It seems that we not able to revoke create privilege on default tablespace.

I think the main problem is that pg_default has OID=1663, but all objects in
this tablespace have reltablespace=0. Is there reason why 0 is used instead of
correct oid?


        Zdenek

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> It seems that we not able to revoke create privilege on default tablespace.

This is intentional.

            regards, tom lane

Tom Lane wrote:
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> It seems that we not able to revoke create privilege on default tablespace.
>
> This is intentional.

I don't understand why. I did not find any mention about this in the
documentation. Please, could you explain it?

        Thanks Zdenek

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> Tom Lane wrote:
>> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>>> It seems that we not able to revoke create privilege on default tablespace.
>>
>> This is intentional.

> I don't understand why.

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.

By not making that check, we make it feasible to only grant rights on
tablespaces to DB owners/creators, plus any individual users who should
have the right to determine where their tables go.

There is a permission check when a tablespace is first named as the
default space for a new database, but not afterwards.

            regards, tom lane

Tom Lane wrote:
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> Tom Lane wrote:
>>> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>>>> It seems that we not able to revoke create privilege on default tablespace.
>>> This is intentional.
>
>> I don't understand why.
>
> 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 (if
temp_tablespaces is not set) and this table space will have create rights for
everyone. It should be stored in separate directory (e.g. data/pg_temp).

Maybe add temp flag to tablespace should make sense - It will mean that only
temporary object can be created in this tablespace.


        Zdenek

Zdenek Kotala <Zdenek.Kotala@Sun.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.)

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.

            regards, tom lane

Tom Lane wrote:
> Zdenek Kotala <Zdenek.Kotala@Sun.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

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> 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.

"Security risk"?  Now you're just making things up.

            regards, tom lane