Thread: hacker help: PHP-4.2.3 patch to allow restriction of database access
the following was sent to the php developer's list, and they came back with: > > Isn't it generally better (where "better" means more secure, > > efficient, and easily maintained) to handle database access > > control using PostgreSQL's native access mappings? > > I would think so, and IMHO, that's where pgsql access control > belongs, with pgsql. as best i can understand, there is no way to get apach/php/pgsql configured (using "PostgreSQL's native access mappings") that would disallow php code in one virtual host from connecting to any database on the system. i understand that on a user level, we can control which tables they have access to (disregarding that almost all access will be by the "web" user). can anyone make some valid arguments for/against the patch in php? or any suggestions on how to do it in pgsql? ----- Forwarded message from Jim Mercer <jim@reptiles.org> ----- Date: Thu, 26 Sep 2002 14:54:45 -0400 From: Jim Mercer <jim@reptiles.org> To: pgsql-general@postgreSQL.org Subject: PHP-4.2.3 patch to allow restriction of database access the patch is at: ftp://ftp.reptiles.org/pub/php/php-pgsql.patch this patch adds the config variable pgsql.allowed_dblist by default it has no value, meaning all databases are accessible it can contain a colon delimited list of databases that are accessible. if the database accessed is not in the list, and the list is not null, then an error is returned as if the database did not exist this patch is relative to php-4.2.3 this function would be very useful to apache/virtual hosting. i have tested with the following in my apache httpd.conf: <Directory /home/www/htdocs/jim> php_admin_value pgsql.allowed_dblist "jim:billing" </Directory> although it can be accomplished by other means, setting the variable to a value of ":" effectively locks the code out of pgsql. also, a special tag of "-all-" will allow access to all databases. -- [ Jim Mercer jim@reptiles.org +1 416 410-5633 ] [ I want to live forever, or die trying. ] ----- End forwarded message ----- -- [ Jim Mercer jim@reptiles.org +1 416 410-5633 ] [ I want to live forever, or die trying. ]
On Thu, 26 Sep 2002, Jim Mercer wrote:
>
> the following was sent to the php developer's list, and they came back with:
>
> > > Isn't it generally better (where "better" means more secure,
> > > efficient, and easily maintained) to handle database access
> > > control using PostgreSQL's native access mappings?
> >
> > I would think so, and IMHO, that's where pgsql access control
> > belongs, with pgsql.
I totally disagree. It is a language level restriction, not a database
level one, so why back it into Postgres? Just parse 'conninfo' when it is
pg_(p)connect() and check it against the configuration setting.
The patch seems fine. I am unsure as to how useful it is.
system("/usr/local/pgsql/bin/psql -U jim -c \"select 'i got in';\" template1");
Gavin
On Fri, Sep 27, 2002 at 11:15:35AM +1000, Gavin Sherry wrote:
> On Thu, 26 Sep 2002, Jim Mercer wrote:
> > > I would think so, and IMHO, that's where pgsql access control
> > > belongs, with pgsql.
>
> I totally disagree. It is a language level restriction, not a database
> level one, so why back it into Postgres? Just parse 'conninfo' when it is
> pg_(p)connect() and check it against the configuration setting.
which is effectively what my code does, except i was lazy, and i let the
connection proceed, then check if PQdb() is in the auth list, and fail
if it isn't. (i figured that way if there was any silliness in the conninfo
string, PQconnect would figure it out).
> The patch seems fine. I am unsure as to how useful it is.
>
> system("/usr/local/pgsql/bin/psql -U jim -c \"select 'i got
> in';\" template1");
that wouldn't work so well in safe_mode. which is the area i'm playing with.
maybe not _totally_ secure, but much moreso than nothing.
and retricting virtual hosts to their own data sets relieves me of worry
about "GRANT all ON blah TO public;".
--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ I want to live forever, or die trying. ]
On Thu, 26 Sep 2002, Jim Mercer wrote: > On Fri, Sep 27, 2002 at 11:15:35AM +1000, Gavin Sherry wrote: > > On Thu, 26 Sep 2002, Jim Mercer wrote: > > > > I would think so, and IMHO, that's where pgsql access control > > > > belongs, with pgsql. > > > > I totally disagree. It is a language level restriction, not a database > > level one, so why back it into Postgres? Just parse 'conninfo' when it is > > pg_(p)connect() and check it against the configuration setting. > > which is effectively what my code does, except i was lazy, and i let the > connection proceed, then check if PQdb() is in the auth list, and fail Ahh yes. I meant to say this. No point being lazy when it comes to security. > maybe not _totally_ secure, but much moreso than nothing. > I was basically just suggesting that its effect needs to be documented. "This needs to be used in conjunction with other forms of security...." Gavin
On Fri, Sep 27, 2002 at 12:06:43PM +1000, Gavin Sherry wrote: > On Thu, 26 Sep 2002, Jim Mercer wrote: > > maybe not _totally_ secure, but much moreso than nothing. > > I was basically just suggesting that its effect needs to be > documented. "This needs to be used in conjunction with other forms of > security...." documentation? what's that? 8^) -- [ Jim Mercer jim@reptiles.org +1 416 410-5633 ] [ I want to live forever, or die trying. ]
Jim Mercer <jim@reptiles.org> writes:
> as best i can understand, there is no way to get apach/php/pgsql configured
> (using "PostgreSQL's native access mappings") that would disallow php code
> in one virtual host from connecting to any database on the system.
Betraying my ignorance of PHP here: what does a server supporting
multiple virtual hosts look like from the database's end? Can we
tell the difference at all between connections initiated on behalf
of one virtual host from those initiated on behalf of another?
If we can tell 'em apart (for instance, if they differ in apparent
client IP address) then it'd make sense to put enforcement on the
database side. If we can't tell 'em apart, then we need some help
from the PHP interface code so that we can tell 'em apart.
Proceeding on the assumption that we do need some help ...
> this patch adds the config variable pgsql.allowed_dblist
> by default it has no value, meaning all databases are accessible
> it can contain a colon delimited list of databases that are accessible.
Seems like this hard-wires a rather narrow view of what sorts of
protection restrictions you need. Might I suggest instead that
an appropriate config variable would be a list of Postgres user ids
that the virtual host is allowed to connect as? Then the database's
usual protection mechanisms could be used to allow/disallow connection
to particular databases, if that's what you want. But this does more:
it lets different virtual hosts connect to the same database as
different users, and then access within that DB can be controlled using
the regular Postgres access-control mechanisms.
Essentially, the idea here is to ensure that the DB can tell virtual
hosts apart as different users.
regards, tom lane
On Thu, Sep 26, 2002 at 11:42:44PM -0400, Tom Lane wrote:
> Jim Mercer <jim@reptiles.org> writes:
> > as best i can understand, there is no way to get apach/php/pgsql configured
> > (using "PostgreSQL's native access mappings") that would disallow php code
> > in one virtual host from connecting to any database on the system.
>
> Betraying my ignorance of PHP here: what does a server supporting
> multiple virtual hosts look like from the database's end? Can we
> tell the difference at all between connections initiated on behalf
> of one virtual host from those initiated on behalf of another?
normally (in my experience) php is linked into apache, and pgsql is linked into
php.
apache runs as the same user (unless you use suexec and a cgi version of php).
pgsql's knowledge of the php process is only what is passed on by the user.
since there is no IP addr specific to the process, we can't easily use
host-based authentication.
for domain sockets, pgsql only gets the UID of the httpd process.
since all of the virtual hosts are run by the same uid, there is no way
to differentiate between the virtual hosts.
one could attempt to use a specific username and hardcoded password, but
that leaves the password in plain text in the php code.
and that does not stop someone from writing code to browse the available
databases for tables set with "GRANT ALL ON blah to PUBLIC;".
my patch is an attempt to put an immutable list of databases in the apache
config (safe from modification by normal users). and to have PQconnect()
check against that list before allowing access. the list would be specific
to a virtual host (and/or the directort hierarchy of the pages).
it is possible to pass such a list to pgsql through environment variables,
but those can be overridden by users.
the php-dev people are giving me a hard time saying that this level of
security should be managed internally by pgsql.
i'm trying to explain to them that it isn't, and that my patch allows this
security to happen.
if libpq had an additional facility where PQconnect checked against a list
passed to it in some fashion, then we could probably just pass that through
in the php modules, and they'd probably be more content with that as it is
just part of the pgsql API.
i'm thinking something like a wrapper function like:
PGconn *PQconnect_restricted(char *conninfo, char *restricted_list)
{// break out conninfo...
if (restricted_list != NULL) { // check to see if dbName is in the list .... if (not in list) { fail as
ifdbName did not exist }}return(PQconnect(conninfo);
}
(i'm sure someone more familiar with the code could come up with a more refined way of doing this)
> > this patch adds the config variable pgsql.allowed_dblist
> > by default it has no value, meaning all databases are accessible
> > it can contain a colon delimited list of databases that are accessible.
>
> Seems like this hard-wires a rather narrow view of what sorts of
> protection restrictions you need. Might I suggest instead that
> an appropriate config variable would be a list of Postgres user ids
> that the virtual host is allowed to connect as? Then the database's
> usual protection mechanisms could be used to allow/disallow connection
> to particular databases, if that's what you want. But this does more:
> it lets different virtual hosts connect to the same database as
> different users, and then access within that DB can be controlled using
> the regular Postgres access-control mechanisms.
ideally, i'd like to have users-per-database, as opposed to the global
model we have now. i'm tired of maintaining seperate pgsql userlists and
application userlists. probably a pipe dream, but, well, there you are. 8^)
if we are willing to modify libpq to support a "white-list", then what you
are suggesting is quite possible.
to satisfy the php-dev people, we just need to extend the API to require/allow
such a white-list to be processed.
passing the white-list from httpd.conf -> php -> libpq is an easy enough
tweak.
i suspect the php-dev people are unhappy with my patch because it is including
logic (ie. parsing the white-list) which they don't think php should be
responsible for.
personally, i think such an attitude is too rigid, but i'm also thinking a
white-list mechanism would be useful in other contexts as well.
--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ I want to live forever, or die trying. ]
On Thu, 2002-09-26 at 22:42, Tom Lane wrote: > Jim Mercer <jim@reptiles.org> writes: > > as best i can understand, there is no way to get apach/php/pgsql configured > > (using "PostgreSQL's native access mappings") that would disallow php code > > in one virtual host from connecting to any database on the system. > > Betraying my ignorance of PHP here: what does a server supporting > multiple virtual hosts look like from the database's end? Can we > tell the difference at all between connections initiated on behalf > of one virtual host from those initiated on behalf of another? Nope, you can't to the best of my knowledge. You just get a standard connect string. (Assuming NAME based vHosts here, which is what most should be, modulo SSL based ones). [snip] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Jim Mercer writes: > ideally, i'd like to have users-per-database, as opposed to the global > model we have now. That's in the works. Some form of this will be in 7.3. > if we are willing to modify libpq to support a "white-list", then what you > are suggesting is quite possible. How would you store such a list and prevent users from simply unsetting it? > i suspect the php-dev people are unhappy with my patch because it is including > logic (ie. parsing the white-list) which they don't think php should be > responsible for. From my reading of the discussion, I think they have not understood that the PostgreSQL server has no way to distinguish different virtual host identities. I think your feature is quite reasonable, if you list users instead of databases. -- Peter Eisentraut peter_e@gmx.net
On Sat, Sep 28, 2002 at 01:08:36PM +0200, Peter Eisentraut wrote: > Jim Mercer writes: > > ideally, i'd like to have users-per-database, as opposed to the global > > model we have now. > > That's in the works. Some form of this will be in 7.3. cool! > > if we are willing to modify libpq to support a "white-list", then what you > > are suggesting is quite possible. > > How would you store such a list and prevent users from simply unsetting > it? the list is something determined by the client, effectively, in this scenario. basically, i'm just looking at a libpq function that will take a white-list, and only allow connections through PQconnect() based on that list. the reasoning for this is that postmaster has no ability to differentiate between incoming sessions, and as such, storing the list in the server makes no sense, the server won't know how to apply the list. in the scenario i'm working with, apache/php/libpq are safe from change by the users. apache has the ability to pass values through php to libpq which the user cannot change. so apache would tell libpq what tables _this_ instance of apache/php/libpq can access. simply using environment variables is not good enough, as the user can change their values in their php scripts. > > i suspect the php-dev people are unhappy with my patch because it is including > > logic (ie. parsing the white-list) which they don't think php should be > > responsible for. > > From my reading of the discussion, I think they have not understood that > the PostgreSQL server has no way to distinguish different virtual host > identities. I think your feature is quite reasonable, if you list users > instead of databases. well, for my purposes, it is _databases_ i'm more concerned about. each virtual host should be restricted to specific databases. this way each user is entitled to mess up their own world, but not mess with other people's. as it currently stands, virtual hosts can trample all over other databases, and with the nature of a single uid for all apache/php/libpq proceses, they are generally doing it with the same pgsql user. vigilience over the user-level permissions is not something i trust the users to do. 8^( -- [ Jim Mercer jim@reptiles.org +1 416 410-5633 ] [ I want to live forever, or die trying. ]
Re: hacker help: PHP-4.2.3 patch to allow restriction of database access
From
"Michael Paesold"
Date:
Jim Mercer <jim@reptiles.org> wrote: > as it currently stands, virtual hosts can trample all over other databases, > and with the nature of a single uid for all apache/php/libpq proceses, > they are generally doing it with the same pgsql user. I haven't followed the whole thread, so perhaps I missed something. But why not just use password authentication to the database with a different user for each database? Ok, one has to store the plain-text passwords in the php files. You have to protect your users from reading each others files anyway; this can be done. At least you can set up different users per database, so that it doesn't matter if the proposed restriction setting is by database or by user. Regards, Michael Paesold
On Sat, Sep 28, 2002 at 03:57:27PM +0200, Michael Paesold wrote: > Jim Mercer <jim@reptiles.org> wrote: > > as it currently stands, virtual hosts can trample all over other > databases, > > and with the nature of a single uid for all apache/php/libpq proceses, > > they are generally doing it with the same pgsql user. > > I haven't followed the whole thread, so perhaps I missed something. But why > not just use password authentication to the database with a different user > for each database? Ok, one has to store the plain-text passwords in the php > files. You have to protect your users from reading each others files anyway; > this can be done. that can be done, but plain-text passwords are not good. also, it doesn't stop users from cruising other databases for unprotected data. my patch will control that, at least in the context of apach/php/libpq. > At least you can set up different users per database, so that it doesn't > matter if the proposed restriction setting is by database or by user. most of the databases have one user, that of the httpd process. from what i've seen, this is fairly standard with virtual hosting. until we have per-database users, generally what you end up doing is creating a per-database user/password table, and then write applications that control things based on that table, as opposed to the system table. this means that all of the tables in a database need to be read/write by one central user. i've always found this hokey, but necessary. the up-coming per-table userlist will help this alot. -- [ Jim Mercer jim@reptiles.org +1 416 410-5633 ] [ I want to live forever, or die trying. ]
Jim Mercer writes: > the reasoning for this is that postmaster has no ability to differentiate > between incoming sessions, and as such, storing the list in the server makes > no sense, the server won't know how to apply the list. Right, but libpq also has no concept of what you call "incoming session". PHP is the first interface that came up with that notion. If we have more clients requesting that kind of support, we can think about it, but for now you should think of putting it into PHP first. > well, for my purposes, it is _databases_ i'm more concerned about. OK, so *you* put local sameuser ... into pg_hba.conf and be done. The rest of the user community can decide for themselves. This is especially important since with the arrival of schemas there is a whole new way to manage multiple users on a server, which other users might be interested in. -- Peter Eisentraut peter_e@gmx.net