Saturday, September 27, 2008

[pgsql-jobs] Pg server hacks needed: alter catalogs to only show permitted/owned objects

Please contact me via email off-list. This phase is getting
quotes/estimates for the work, which will lead to the client's decision
as to whether to move forward.

We have a system where many users share the same database, with varying
permissions to tables. We also host multiple databases, named after the
customer for administrative purposes. We want various pg_catalog tables
modified to only show "appropriate" objects, so as to preserve the
privacy of our customers and to simplify the view to show only items to
which the user has access. While some system views do this (eg
pg_catalog.tables) many do not (eg pg_catalog.tablespace) and it's these
latter which are used by most clients.

Specifics so far, and additional suggestions are welcome along these veins:

* These changes cannot be made just to the psql client; we need them
made at the server level so they cannot be bypassed simply by switching
clients! Still, I'll use the psql \ commands for brevity.

* Superusers should see all tables and databases, the current behavior.

* \dt and \ds et al should only show items to which the user has access.

* \l should only show the existing database, not others.

* Having a postgresql.conf option to toggle these "simplifications" may
be appropriate.

These changes must be contributed back to the PgSQL project if the PgSQL
project will accept them (I believe them to be of great applicability in
a shared-hosting environment), with credits to yourself for the code and
to our client for the funding.

Gregor Mosheh / Greg Allensworth BS, A+, Network+, Security+, Server+
System Administrator, Lead Programmer
HostGIS development & hosting services,

"Remember that no one cares if you can back up,
only if you can restore." - AMANDA

Sent via pgsql-jobs mailing list (
To make changes to your subscription:

No comments: