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 19073First 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:
Show quoted text
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
Barry Brown wrote:
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?
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.
Yea, that is confusing. When they say "database object", the don't mean
database, but object created in the database, like a table or view.
I modified the text to not mention "database":
When an object is created, it is assigned an owner. The
--
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