Re: PostgreSQL security concerns

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(at)tpchd(dot)org
> >
> >>>> Ken Causey <ken(at)ineffable(dot)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(at)postgresql(dot)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(at)goku(dot)kasby> createdb -U funland funland
CREATE DATABASE

casimiro(at)goku(dot)kasby> dropdb -U funland funland
DROP DATABASE

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

Francesco Casadei