Re: PostgreSQL security concerns - Mailing list pgsql-general

On Thu, May 31, 2001 at 10:33:44AM -0500, Ken Causey wrote:
> OK, I am aware of this file.  I need to provide a little more detail.
>
> The situation is that of a shared webserver and a shared SQL server.
> Access to the SQL server is limited to the webserver already.  Users can
> only run CGI scripts which will of course execute as the webserver user.
> What I'm looking for is restricting access by postgresql user.  All logins
> will be coming from the same host and same host user.  I don't
> see this capability as part of pg_hba.conf.  Did I miss it?
>
> Ken Causey
>
> At 07:41 AM 5/31/01 -0700, you wrote:
> >RTFM re: pg_hba.conf.
> >
>
> <snip some good stuff>
>
> >Ian A. Harding
> >Programmer/Analyst II
> >Tacoma-Pierce County Health Department
> >(253) 798-3549
> >mailto: ianh@tpchd.org
> >
> >>>> Ken Causey <ken@ineffable.com> 05/31/01 07:34AM >>>
> >I've been using PostgreSQL in a limited environment for a couple of years
> >now.  I'm in a position where I will soon need to be able to allow
> >multi-user access.  I'm concerned that, as far as I can tell, any user can
> >access any database with impunity.  Is this correct?  Have I missed some
> >configuration?
> >
> >Ken Causey
> >
> >P.S. I'm not currently on this list, so please reply to me directly.
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> end of the original message

Read section 4.2.1 of the PostgreSQL 7.1.2 Administrator's Guide.

The only problem I have is with createdb and dropdb. I only have two users:
pgsql and funland (created with CREATEDB option). The relevant lines of
pg_hba.conf are:

# TYPE       DATABASE    IP_ADDRESS    MASK               AUTHTYPE  MAP
local        template0                                    trust
local        template1                                    trust
local        funland                                      password  funland.pwd

psql prompts for a password when pgsql and funland connect to database funland
(as expected).
But anyone can create or destroy the database WITHOUT supplying a password. For
example casimiro is a UNIX user not registered in PostgreSQL. I can do:

casimiro@goku.kasby> createdb -U funland funland
CREATE DATABASE

casimiro@goku.kasby> dropdb -U funland funland
DROP DATABASE

I can use -W to force a password prompt, but a malicious user will not!!

    Francesco Casadei