Thread: BUG #1497: Default permissions allow any user to create objects in any database

The following bug has been logged online:

Bug reference:      1497
Logged by:          Barry Brown
Email address:      barry@cs.sierracollege.edu
PostgreSQL version: 8.0.1
Operating system:   RHEL 3.0
Description:        Default permissions allow any user to create objects in
any database
Details:

The docs say that initially only the owner of a database may use the objects
created in it. But I have found that ANY user can work with any object by
default, even in the template1 database.

To reproduce the bug, I will create a user called 'sqltest' with password
'foobar', connect to template1 as that user, and create a table.

First, create the user:
[root@cs data]# su - postgres
-bash-2.05b$ psql template1
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# create user sqltest password 'foobar';
CREATE USER

Next, connect as that user to template1:
[barry@cs barry]$ psql -U sqltest template1
Password:
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=>

Finally, create a table:
template1=> create table foo (
template1(> id_num int
template1(> );
CREATE TABLE
template1=> \dt foo
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | foo  | table | sqltest
(1 row)

I should not be able to do this.

I compiled PostgreSQL from the FC3 SRPM found on rpmfind.net. I had to
disable SSL, KRB, and Selftest in the spec file to make it compile.

"Barry Brown" <barry@cs.sierracollege.edu> writes:
> I should not be able to do this.

Not everyone would agree with you.  But if you want to work that way,
revoke public CREATE privileges on the public schema, and revoke public
CREATE privileges on the database so people can't create new schemas.

            regards, tom lane

Barry Brown wrote:
>
> The following bug has been logged online:
>
> Bug reference:      1497
> Logged by:          Barry Brown
> Email address:      barry@cs.sierracollege.edu
> PostgreSQL version: 8.0.1
> Operating system:   RHEL 3.0
> Description:        Default permissions allow any user to create objects in
> any database
> Details:
>
> The docs say that initially only the owner of a database may use the objects
> created in it. But I have found that ANY user can work with any object by
> default, even in the template1 database.

Uh, where did you see that in the docs?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

First paragraph of section 17.4 (Privileges):

"When a database object is created, it is assigned an owner. .... By
default, only an owner (or a superuser) can do anything with the
object. In order to allow other users to use it, privileges must be
granted."

To me, that paragraphs says that only the owner of a database can do
anything with it and all other privileges must be explicitly granted to
others.

-B

On Feb 23, 2005, at 11:10 PM, Bruce Momjian wrote:

> Barry Brown wrote:
>>
>> The following bug has been logged online:
>>
>> Bug reference:      1497
>> Logged by:          Barry Brown
>> Email address:      barry@cs.sierracollege.edu
>> PostgreSQL version: 8.0.1
>> Operating system:   RHEL 3.0
>> Description:        Default permissions allow any user to create
>> objects in
>> any database
>> Details:
>>
>> The docs say that initially only the owner of a database may use the
>> objects
>> created in it. But I have found that ANY user can work with any
>> object by
>> default, even in the template1 database.
>
> Uh, where did you see that in the docs?
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073