Postgres CGI Security Problem

Thus spake Chris Hardie

The situation: I have one machine with general user access. Some users
(including myself) own a postgres database. Some users (including myself)
use postgres as a back-end for CGI applications, using the Postgres.pm
module for Perl. This requires that user "nobody" (or www, or whomever)
have read/write access to my database.

The problem: While it's very handy that I can write CGI scripts that can
read/write my database, it's a security problem. Other users` CGI scripts
will also make use of the "nobody" identity to access the database, which
means they can potentially read/write the data in my database if they
wanted to.

The fix: You tell me. It would seem to involve a "setuid" of sorts for
how the httpd process accesses the postgres database.

Here's how I handle it. Run a separate web server as the database
owner. Put all the scripts into a separate directory and make it
the document root. If running Apache, put the following directive
into srm.conf.

UserDir disabled

Otherwise other users can write their own scripts and access your
database. You might also consider creating a user especially for
running this app. That way you can grant just enough privileges
to them to make the page work.

I also use .htaccess files to allow admin and user access by putting
the admin and user files into separate directories.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

What web server are you on. I know that when i install apache 1.3
there was something about running cgi scripts as the owner rather
then the "nobody" id.

Scott

Chris Hardie wrote:

The situation: I have one machine with general user access. Some users
(including myself) own a postgres database. Some users (including myself)
use postgres as a back-end for CGI applications, using the Postgres.pm
module for Perl. This requires that user "nobody" (or www, or whomever)
have read/write access to my database.

The problem: While it's very handy that I can write CGI scripts that can
read/write my database, it's a security problem. Other users` CGI scripts
will also make use of the "nobody" identity to access the database, which
means they can potentially read/write the data in my database if they
wanted to.

The fix: You tell me. It would seem to involve a "setuid" of sorts for

^^^^^^

how the httpd process accesses the postgres database.

Apache has suexec program ro run user' CGI and SSI under
user' privileges...

Vadim

On Sat, 8 Aug 1998, Vadim Mikheev wrote:

Chris Hardie wrote:

The situation: I have one machine with general user access. Some users
(including myself) own a postgres database. Some users (including myself)
use postgres as a back-end for CGI applications, using the Postgres.pm
module for Perl. This requires that user "nobody" (or www, or whomever)
have read/write access to my database.

The problem: While it's very handy that I can write CGI scripts that can
read/write my database, it's a security problem. Other users` CGI scripts
will also make use of the "nobody" identity to access the database, which
means they can potentially read/write the data in my database if they
wanted to.

The fix: You tell me. It would seem to involve a "setuid" of sorts for

^^^^^^

how the httpd process accesses the postgres database.

Apache has suexec program ro run user' CGI and SSI under
user' privileges...

And you could ofcourse always use password authenication for those
databases....

Maarten

ps. only problem is that those passwords have to be in your perl-script,
and that script has to be world-readable, unless you have a system that
supports ACL's, then you can set it only readable to user nobody or www
or whatever

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------

Another way to handle it is if you are using perl is to create a seperate user
(ie. Xdf83sr)or some other impossible to guess name, and have it in the actual
Perl Script as $<=<userid of the fake user> and then grant access to that user
rather than the 'nobody' or 'www' user.. Of course, using things like inetd to
prevent access from any other machines than from your own local network goes a
long way to handling ne'er do wells. Other wise if they get a copy of your passwd
file, they just have to try the names one at a time.
IP authentication at the postmaster level would be a much better solution, because
the web server inherently doesn't use passwds..

Chris Hardie wrote:

The situation: I have one machine with general user access. Some users
(including myself) own a postgres database. Some users (including myself)
use postgres as a back-end for CGI applications, using the Postgres.pm
module for Perl. This requires that user "nobody" (or www, or whomever)
have read/write access to my database.

The problem: While it's very handy that I can write CGI scripts that can
read/write my database, it's a security problem. Other users` CGI scripts
will also make use of the "nobody" identity to access the database, which
means they can potentially read/write the data in my database if they
wanted to.

--
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael - System Administrator              Working in Cheap Canadian Dollars
Unix Administration - WebSite Hosting - Network Services - Programming
Wizard Internet Services - TechnoWizard Computers - Wizard Tower TechnoServices
------------------------------------------------------------------------------
(604) 589-0037          Beautiful British Columbia, Canada
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The Apache suexec solution sounds like my quickest fix yet.

The solutions involving setting up a httpd server running as a user that
can access my particular database still leaves my users` databases open to
write by other users, and seems kind of messy (but effective).

Several folks mentioned supplying a password to the database through the
CGI script. I had a hard time finding good documentation on this scheme;
psql apparently supports the "-u" option that prompts for a password (and
I assume you're prompted anyway when you have the "crypt" option set for a
user/database in pg_dba.conf). But if you look in the source code for
psql, it seems there are two methods to connect to a database, PQconnectdb
which does allow for username/password, and PQsetdb, which *does not*.
This means that someone could theoretically write a PERL module that uses
the latter method to connect and bypass the password scheme.

In anycase, I'm using the Postgres.pm module with PERL, and it doesn't
seem to support the passing of a username/password pair (correct me if I'm
wrong) from a CGI script. I'll attempt to code that, unless someone has
done it already.

Thanks for all your help!

Chris

On Sat, 8 Aug 1998, Maarten Boekhold wrote:

On Sat, 8 Aug 1998, Vadim Mikheev wrote:

Chris Hardie wrote:

The situation: I have one machine with general user access. Some users
(including myself) own a postgres database. Some users (including myself)
use postgres as a back-end for CGI applications, using the Postgres.pm
module for Perl. This requires that user "nobody" (or www, or whomever)
have read/write access to my database.

The problem: While it's very handy that I can write CGI scripts that can
read/write my database, it's a security problem. Other users` CGI scripts
will also make use of the "nobody" identity to access the database, which
means they can potentially read/write the data in my database if they
wanted to.

The fix: You tell me. It would seem to involve a "setuid" of sorts for

^^^^^^

how the httpd process accesses the postgres database.

Apache has suexec program ro run user' CGI and SSI under
user' privileges...

And you could ofcourse always use password authenication for those
databases....

Maarten

ps. only problem is that those passwords have to be in your perl-script,
and that script has to be world-readable, unless you have a system that
supports ACL's, then you can set it only readable to user nobody or www
or whatever

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------

---------------------------------------------
Chris Hardie chris@summersault.com
http://www.summersault.com/chris
vincendum est
---------------------------------------------

On Sat, 8 Aug 1998, Chris Hardie wrote:

The Apache suexec solution sounds like my quickest fix yet.

The solutions involving setting up a httpd server running as a user that
can access my particular database still leaves my users` databases open to
write by other users, and seems kind of messy (but effective).

Several folks mentioned supplying a password to the database through the
CGI script. I had a hard time finding good documentation on this scheme;
psql apparently supports the "-u" option that prompts for a password (and
I assume you're prompted anyway when you have the "crypt" option set for a
user/database in pg_dba.conf). But if you look in the source code for
psql, it seems there are two methods to connect to a database, PQconnectdb
which does allow for username/password, and PQsetdb, which *does not*.
This means that someone could theoretically write a PERL module that uses
the latter method to connect and bypass the password scheme.

No, because the requirement to ocnnect with a password is not made by
libpq (or psql), but by the postmaster. If you have configured your
database to only allow access with username/password, and you do not
supply them, access is refused.

To try this out:

create a user (psql: CREATE USER username WITH PASSWORD mypassword)
stop the postmaster
edit $PGDATA/pg_hba.conf:
make sure that the lines describing access to your databases
have 'password' or 'crypt' at the end of the line (read the
comments in this file)
start the postmaster
try to connect with 'psql database', it'll fail (if you have editted
pg_hba.conf correctly).
try to connect using 'psql -u database'. it'll prompt for a username and
password. give those, and you have access.

Note that you have 2 ways passwords are supported. The best one (in my
opinion) is the one that stores passwords in the table pg_shadow (only
readable by the postgres superuser, other users can only use pg_user,
which is a view on pg_shadow that hides the password field). The other
way is to store passwords in a seperate file. Passwords can be added to
this file with the pg_passwd program). The first method is used by
default, the second if you *explicitly* specify a password file for each
line in pg_hba.conf).

In anycase, I'm using the Postgres.pm module with PERL, and it doesn't
seem to support the passing of a username/password pair (correct me if I'm
wrong) from a CGI script. I'll attempt to code that, unless someone has
done it already.

Thanks for all your help!

Chris

On Sat, 8 Aug 1998, Maarten Boekhold wrote:

On Sat, 8 Aug 1998, Vadim Mikheev wrote:

Chris Hardie wrote:

The situation: I have one machine with general user access. Some users
(including myself) own a postgres database. Some users (including myself)
use postgres as a back-end for CGI applications, using the Postgres.pm
module for Perl. This requires that user "nobody" (or www, or whomever)
have read/write access to my database.

The problem: While it's very handy that I can write CGI scripts that can
read/write my database, it's a security problem. Other users` CGI scripts
will also make use of the "nobody" identity to access the database, which
means they can potentially read/write the data in my database if they
wanted to.

The fix: You tell me. It would seem to involve a "setuid" of sorts for

^^^^^^

how the httpd process accesses the postgres database.

Apache has suexec program ro run user' CGI and SSI under
user' privileges...

And you could ofcourse always use password authenication for those
databases....

Maarten

ps. only problem is that those passwords have to be in your perl-script,
and that script has to be world-readable, unless you have a system that
supports ACL's, then you can set it only readable to user nobody or www
or whatever

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------

---------------------------------------------
Chris Hardie chris@summersault.com
http://www.summersault.com/chris
vincendum est
---------------------------------------------

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------

Chris,

Have you considered using the Perl DBI module. It will let
you connect to a database with a username and a password.

Example:

#!/usr/local/bin/perl

user DBI;

# Connect To Database
$conn= DBI->connect("dbi:Pg:dbname=$dbname",$dbuser, $dbpassword) || die("connect_database: Could Not Connect To Database $dbname AS $dbuser");

I hope this helps you out. If you need any more info then feel free
to ask.

Shawn T. Walker swalker@iac.net
Internet Access Cincinnati
http://www.iac.net

"Running enterprise applications on NT? Let the torture begin."
- A Sun Microsystems Inc. banner ad

Chris Hardie writes:

On Sat, 8 Aug 1998, Shawn T. Walker wrote:

Chris,

Have you considered using the Perl DBI module. It will let
you connect to a database with a username and a password.

Chris Hardie writes:

In anycase, I'm using the Postgres.pm module with PERL, and it doesn't
seem to support the passing of a username/password pair (correct me if I'm
wrong) from a CGI script. I'll attempt to code that, unless someone has
done it already.

Actually Pg.pm *does* allow you to connect with a username/password. If
you use the 'new-style' methods of Pg.pm, do $db->connect("dbname=.. user=..
password=..");. If you use the 'old-style' (like I do), use PQconnectdb()
with the same arguments.

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------

Chris Hardie writes:

In anycase, I'm using the Postgres.pm module with PERL, and it doesn't
seem to support the passing of a username/password pair (correct me if
I'm wrong) from a CGI script. I'll attempt to code that, unless
someone has done it already.

Actually Pg.pm *does* allow you to connect with a username/password. If
you use the 'new-style' methods of Pg.pm, do $db->connect("dbname=..
user=.. password=..");. If you use the 'old-style' (like I do), use
PQconnectdb() with the same arguments.

He said Postgres.pm, not Pg.pm. There are three modules (that I've
used anyway) to do Postgres connectivity via perl:

Pg.pm (ugly, too C like for perl)
Postgres.pm (very perl like)
DBD::Pg (best way to go, very similar to Postgres.pm)

--
SA, software.net My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

On Sun, 9 Aug 1998, Dustin Sallings wrote:

Chris Hardie writes:

In anycase, I'm using the Postgres.pm module with PERL, and it doesn't
seem to support the passing of a username/password pair (correct me if
I'm wrong) from a CGI script. I'll attempt to code that, unless
someone has done it already.

Actually Pg.pm *does* allow you to connect with a username/password. If
you use the 'new-style' methods of Pg.pm, do $db->connect("dbname=..
user=.. password=..");. If you use the 'old-style' (like I do), use
PQconnectdb() with the same arguments.

He said Postgres.pm, not Pg.pm. There are three modules (that I've
used anyway) to do Postgres connectivity via perl:

Pg.pm (ugly, too C like for perl)
Postgres.pm (very perl like)
DBD::Pg (best way to go, very similar to Postgres.pm)

Ah, I didn't know if any Postgres.pm, so I assumed he ment Pg.pm. I've
seen ppl making that mistake several times (saying Postgres.pm when they
ment Pg.pm).

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------