Thread: PostgreSQL security concerns
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.
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. >
Ken Causey writes: > 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? You need to configure the pg_hba.conf entries so they only succeed for particular users. If the web server and the database server run on the same host then it might be easiest to connect through Unix domain sockets and restrict access by using the file permission bits. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
There are 2 different server systems, so IP sockets are being used. But, the only user processes on the webserver are CGI process which all run as the same user, the web server user (nobody). Even at that, my point is that I need to be able to setup databases for specific users which they can get into, but not allow them to get into other user's databases. As far as I can tell, any user settings in pg_hba.conf would apply to the ident user, which will always be 'nobody'. The only solution is to have permissions based on the postgresql user, and I can't find anyway to set that up. Ken Causey At 11:12 PM 5/31/01 +0200, you wrote: >Ken Causey writes: > >> 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? > >You need to configure the pg_hba.conf entries so they only succeed for >particular users. If the web server and the database server run on the >same host then it might be easiest to connect through Unix domain sockets >and restrict access by using the file permission bits. > >-- >Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter > >
Peter Eisentraut wrote: >Ken Causey writes: > >>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? >> > >You need to configure the pg_hba.conf entries so they only succeed for >particular users. If the web server and the database server run on the >same host then it might be easiest to connect through Unix domain sockets >and restrict access by using the file permission bits. > Besides that you can add all the users you need to pg_hba.conf and do the required grants to establish the proper permissions. And setup your script to connect using the proper username in the connection string. Or am I missing the point here? Rob > >
I have to apologize. Its clear that I did not sufficiently test
accessibility with a non-owner postgresql account. Since I was able to
see the list of relations I guess I just assumed that I had access to
them. This message got me looking a little deeper and I found that the
documentation for GRANT and REVOKE seemed to imply that only the owner
would have any real access. Further testing seems to indicate that this
is the case and so the default is exactly what I wanted. ;) I knew I
should think a couple of more times before posting. Thanks everyone for
your responses.
Ken Causey
>>>>
<excerpt>In article
<<3.0.1.32.20010531103344.0168f98c@pop3.premiernet.net>, "Ken
Causey" <<ken@ineffable.com> 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?
You can restrict access on a table-by-table basis using the SQL
GRANT command. For instance, for web access using Apache server
side includes, the user nobody must have the appropriate access. I
allow web users to read a database but not change it, so for any table
a web user might need to read I run the command:
GRANT SELECT ON <<table name> TO nobody ;
Of course, for this to work the user 'nobody' must be already a
PostgreSQL user. BTW, the opposite of GRANT is REVOKE, which
you can use to revoke a database privilege for a PostgreSQL user.
</excerpt><<<<<<<<
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
> 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!! createdb/dropdb are actually controlled by template0/1, not the database itself. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Francesco Casadei <f_casadei@libero.it> writes:
> # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP
> local template0 trust
> local template1 trust
> local funland password funland.pwd
If you're going to make template1 accessible via "trust" then you should
expect rather severe lack of security. Better put passwords on it too.
In a security-conscious setup, I don't see any good reason for anyone
but the DBA to be allowed to connect to template1.
BTW, there's no need to allow anyone to connect to template0 at all.
regards, tom lane
On Mon, Jun 04, 2001 at 09:51:24AM -0400, Bruce Momjian wrote: [snip] > > createdb/dropdb are actually controlled by template0/1, not the database > itself. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > end of the original message Restricting access to template[01] solved the problem, thanks a lot!! Francesco Casadei
In article <3.0.1.32.20010531103344.0168f98c@pop3.premiernet.net>, "Ken
Causey" <ken@ineffable.com> 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?
You can restrict access on a table-by-table basis using the SQL
GRANT command. For instance, for web access using Apache server
side includes, the user nobody must have the appropriate access. I
allow web users to read a database but not change it, so for any table
a web user might need to read I run the command:
GRANT SELECT ON <table name> TO nobody ;
Of course, for this to work the user 'nobody' must be already a
PostgreSQL user. BTW, the opposite of GRANT is REVOKE, which
you can use to revoke a database privilege for a PostgreSQL user.