create table permissions bug for 7.3.2

Evgeny Duzhakow <diabolo@philol.msu.ru> writes:

I have found a bug in version 7.3.2.
At creation of tables the user not having grants on it can create the table
in database. (For temporary tables all works normally).

Oh?

regression=# create user z;
CREATE USER
regression=# create schema s;
CREATE SCHEMA
regression=# \c - z
You are now connected as new user z.
regression=> create table s.t (f1 int);
ERROR: s: permission denied
regression=>

I'm not seeing the problem. Please provide a test case showing that
there's a bug here.

Your patch is wrong in any case, I think, since CREATE rights on a
database control the right to create schemas, not individual tables
within schemas ...

regards, tom lane

On Thu, 13 Mar 2003, Tom Lane wrote:

TL>regression=# create user z;
TL>CREATE USER
TL>regression=# create schema s;
TL>CREATE SCHEMA
TL>regression=# \c - z
TL>You are now connected as new user z.
TL>regression=> create table s.t (f1 int);
TL>ERROR: s: permission denied
TL>regression=>
TL>I'm not seeing the problem. Please provide a test case showing that
TL>there's a bug here.
TL>
TL>Your patch is wrong in any case, I think, since CREATE rights on a
TL>database control the right to create schemas, not individual tables
TL>within schemas ...
Ok, i have a database 'testtables' and some user like 'fake' enabled to
connect via pg_hba.conf

--- cut ---
local   testtables  fake                                         trust
--- cut ---

All my tables in db are created in the schema public (i don't have
opportunity to correct 500 scripts on an insert there concept of
schemas).

testtables=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+---------
public | lalala | table | diabolo

It is necessary for me, that the user fake only could read tables of a
database and create temp tables for his work, but could not create other
objects.

I have grant permissions on testtables

testtables=# select datname,datacl from pg_database where datname='testtables';
datname | datacl
------------+----------------
testtables | {=,diabolo=CT}

Now i grant 'fake' for creating only temp tables on my db;

testtables=# GRANT TEMP ON DATABASE testtables TO fake;
GRANT
testtables=# select datname,datacl from pg_database where datname='testtables';
datname | datacl
------------+-----------------------
testtables | {=,diabolo=CT,fake=T}

Now connect and try to create the table:

testtables=# \c - fake
You are now connected as new user fake.
testtables=> create table qq(i int);
CREATE TABLE
testtables=> \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+---------
public | lalala | table | diabolo
public | qq | table | fake

But the basic problem that I have public bases in which it is necessary for
me too most. And it bug for me as without an opportunity of work with grants
at a level of base I receive dust in the bases from users.

Diabolo.

P.S. Sorry for my bad english :(

---                                                                       ---
   System Administrator of                          Phone: +7-095-939-1478
      the Philological Faculty of         E-Mail: diabolo@philol.msu.ru
         Moscow State University.      Web: http://www.philol.msu.ru

Evgeny Duzhakow <diabolo@philol.msu.ru> writes:

Now connect and try to create the table:

testtables=# \c - fake
You are now connected as new user fake.
testtables=> create table qq(i int);
CREATE TABLE

Sure. You need to revoke CREATE access on the public schema (not the
same thing as the database) if you don't want user fake to create tables
in it. Or possibly you could remove the public schema altogether.
See the discussion of common usage patterns in the User's Guide chapter
about schemas.
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/ddl-schemas.html

regards, tom lane