Re: hacker help: PHP-4.2.3 patch to allow restriction of database access

On Thu, Sep 26, 2002 at 11:42:44PM -0400, Tom Lane wrote:
> Jim Mercer <jim(at)reptiles(dot)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 if dbName 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(at)reptiles(dot)org +1 416 410-5633 ]
[ I want to live forever, or die trying. ]