On Thu, Sep 26, 2002 at 11:42:44PM -0400, Tom Lane wrote: > Jim Mercer <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] +1 416 410-5633 ] [ I want to live forever, or die trying. ] ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org