Обсуждение: security issue - database user

(My apologies if this has been posted before - as you'll see, this isn't
the easiest thing to find in the archives.)

The scenario: We want to allow common users access to their own databases
for development.

The issue: As a user (not a superuser), I can modify any database on the
system, albeit only through CREATE TABLE.

CREATE USER myuser WITH PASSWORD 'blah' CREATEDB;
psql template1 -h db -U myuser
  (password auth)
template1 => CREATE DATABASE myuser;


(re-auth as user postgres, drop createdb privs)
ALTER USER myuser NOCREATEDB;


Now, we connect to our database (myuser) as myuser and go about our
business.  However, I can connect to any other database I've got access to
through pg_hba.conf:

psql somedb -h db -U myuser
  (password auth)
somedb => \d


I can't modify/etc. any existing tables, as one would expect, but:


somedb=> CREATE TABLE mytable(test text);
CREATE


Why is this allowed?  Any way to prevent it?  We've got a lot of users
working on a development server that obviously has hba to quite a few
databases...

Thanks,
  John




--
John Madden
UNIX Systems Engineer
Ivy Tech State College
jmadden@ivytech.edu

On Mon, 5 Aug 2002 11:01:34 -0500 (EST)
"John Madden" <jmadden@ivytech.edu> wrote:

> (My apologies if this has been posted before - as you'll see, this isn't
> the easiest thing to find in the archives.)
> [SNIP]
> I can't modify/etc. any existing tables, as one would expect, but:
>
> somedb=> CREATE TABLE mytable(test text);
> CREATE
>
> Why is this allowed?  Any way to prevent it?  We've got a lot of users
> working on a development server that obviously has hba to quite a few
> databases...

I've seen it asked a few times on this list. Answer: future version of
Postgres will allow permissions to create tables to be defined. Current
versions of Postgres do not, so you can't curtail this behaviour.

If it were me, and it was REALLY IMPORTANT to disallow unauthorised
tables, I'd create a new table with a list of legit tables and have a
cronjob run every say 5 minutes that DROPS tables that don't belong in the
database.

--
Tim Ellis
Senior Database Architect
Gamet, Inc.