PostgreSQL security concerns

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>

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:

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.

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

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

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