The following bug has been logged online:
Bug reference: 1498
Logged by: Barry Brown
Email address: barry@cs.sierracollege.edu
PostgreSQL version: 8.0.1
Operating system: RHEL 3.0 (kernel 2.4.21-27.0.2.ELsmp)
Description: Default permissions allow any user to create objects in
any database (revised)
Details:
I just submitted bug 1497. I did some more tests and have made it easier to
reproduce the bug by compiling from scratch instead of using an SRPM. I have
included all steps, starting from compilation.
Building:
postgres user is UID 26, GID 26
Unpacked -base and -opt.
% ./configure --prefix=/usr/local
% make
# make install
# mkdir /var/lib/pgsql
# chown postgres:postgres /var/lib/pgsql
# chmod 700 /var/lib/pgsql
Changed the last lines of pg_hba.conf to this:
# "local" is for Unix domain socket connections only
#local all all ident sameuser
# IPv4 local connections:
#host all all 127.0.0.1/32 ident sameuser
# IPv6 local connections:
#host all all ::1/128 ident sameuser
local template1 postgres ident sameuser
local all all md5
Starting:
I have an /etc/init.d/postgresql script left over from the FC3 build. I
changed PCENGINE to point to /usr/local/bin (instead of /usr/bin) so it will
run the correct binaries. PGDATA is /var/lib/pgsql
As root:
[root@cs postgresql-8.0.1]# /etc/init.d/postgresql.local start
Initializing database: [ OK ]
Starting postgresql.local service: [ OK ]
Create users and databases:
[root@cs postgresql-8.0.1]# su - postgres
-bash-2.05b$ /usr/local/bin/psql template1
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=# create user sqltest1 password 'foobar1';
CREATE USER
template1=# create user sqltest2 password 'foobar2';
CREATE USER
template1=# create database sqltest1db owner sqltest1;
CREATE DATABASE
Create a table in someone else's database.
First, connect as user sqltest2 to the sqltest1db database:
[barry@cs barry]$ /usr/local/bin/psql -U sqltest2 sqltest1db
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
sqltest1db=>
Then create a table:
sqltest1db=> create table foo ( id int );
CREATE TABLE
sqltest1db=> \dt foo
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | foo | table | sqltest2
(1 row)
This is the bug. The user sqltest2 should not be able to create a table in
sqltest1's database with being given permissions to do so. Worse yet, any
valid database user can connect to the template1 database and create tables
in it.
