security issue - database user
security issue - database user
От:
"John Madden" <jmadden@ivytech.edu>
Дата:
(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
Re: security issue - database user
От:
Tim Ellis <Tim.Ellis@gamet.com>
Дата:
On Mon, 5 Aug 2002 11:01:34 -0500 (EST) "John Madden" 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.