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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings