phpPgAdmin.patch - Patch for phpPgAdmin which enables compatibility with security hardened postgreSQL server
phpPgAdmin is a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies and hosting services. It has all the basic functionality you need to completely administer a PosgreSQL server and/or database, including the ability to administer views, sequences, stored procedures, and triggers. Features include the ability to create and drop databases; create, copy, drop, and alter tables/views/sequences/functions/triggers etc. For more details about phpPgAdmin visit it's home page.
Default PostgresSQL installation in majority of Linux distributions comes with configuration which is not acceptable for security reasons for data stored into database or even whole system itself. Default installation assumes existence of system user named postgres and database user with the same name which have superuser privilege on the postgreSQL database. Default installation also requires that postgres user does not have password set or if there is password set, it should not be asked for local connections of that user. This means that if there is exploit which is able to execute commands as postgres system user, attacker will be able to read any data stored into database such as credit card numbers. Also, in multiuser environment sometimes, it is not acceptable that one postgreSQL user can see other existing databases or users which by default possible.
It is possible to run postgreSQL server with unprivileged user which is able to connect to postgreSQL without password authorization. It is also possible to configure postgreSQL server that way that users cannot see other existing databases or users other than themselves but if postgreSQL is configured that way, functionality of one such a useful application like phpPgAdmin will be lost. This is why this patch exist. It can make phpPgAdmin compatible with somewhat complicated configuration of postgreSQL server where SELECT from pg_user table gives only relevant rows to unprivileged users and where access to pg_database and pg_group tables can be achieved only trough pg_db and pg_grp views respectively.
Concretely, four options has been added to configuration file:
$conf['servers']['enhanced_security'] = false; - turns on/off all other options, phpPgAdmin will work as patch have never been applied
$conf['servers']['pg_database_view'] = 'pg_db'; - name of the view which will be used for assess to pg_database table
$conf['servers']['pg_group_view'] = 'pg_grp'; - name of the view which will be used for assess to pg_group table
$conf['show_system_admin'] = true; - if option show_system is turned off, privileged users will be able to see system tables
So, this patch is useful when unprivileged users does not have access to pg_database and pg_group tables. To make phpPgAdmin functional anyway, you have to create views which unprivileged users can use to get information about their database objects.
Example for creating views which will change pg_database and pg_group tables:
create view pg_db as select oid, * from pg_database where datname=(select current_user);
grant select on pg_db to public;
revoke select on pg_database from public;
create view pg_grp as select * from pg_group where groname=(select current_user);
grant select on pg_grp to public;
revoke select on pg_group from public;
Also, unprivileged user should be able to get only rows relevant for specific user from pg_user table. Here's the example but note that I have used privileged user root which does not have to be same with your configuration:
grant rule on pg_user to root;
create or replace view pg_user as select usename, usesysid, usecreatedb, usesuper, usecatupd, '********'::text as passwd, valuntil, useconfig FROM pg_shadow where usename=(select current_user);
revoke rule on pg_user from root;
grant select on pg_user to public;
grant select on public.pg_user to public;
First of all, download adequate version of phpPgAdmin from it's home page. After that, download matching version of phpPgAdmin.patch package from the DOWNLOAD section. Unpack the phpPgAdmin archive and cd into it's directory where you can issue this command:
patch -p 1 < ../phpPgAdmin.patch
With this procedure you created patched version of phpPgAdmin which should be moved somewhere where web server will have access to serve this directory to clients.
Current version of phpPgAdmin.patch does not contain known bugs.
phpPgAdmin homepage, PostgreSQL homepage
Josip Deanovic firstname.lastname@example.org