BACKGROUND We are trying to make an effort of using unprivileged user access to database. CURRENT IMPLEMENTATION All objects are created within public schema. During engine-cleanup we use privileged account to drop and create the database. During upgrade we iterate through all view and procedures and drop them. PROBLEMS In order to clean up a database using unprivileged user we should enumerate all objects, determine if these belongs to us, and if so cascade delete it. Using naming convention or iterating of objects as we do when we drop views and procedure is risky and quite dirty. SUGGESTED SOLUTION Use our own schema to store objects, this will allow our unprivileged user to drop the schema in order to drop all our objects. Optionally, we can create another schema for the views and procedures so dropping these during upgrade can be replaced with dropping a schema. SIDE EFFECTS Upgrade should move objects from public schema to the private schema.
Please see comment 4 , I don't think that we can provide that for 3.4 due to luck of PG support in connecting to a schema ... There is a patch contributed by the community to PG in 2008 !!! , however this patch was never merged into the product See http://www.postgresql.org/message-id/4873F034.8010301@scharp.org Barak , I would recommend to target to rhev-future since we have no idea when this will be supported by PG
I agree, we tried to do this properly :) Sad that we cannot.
Can be done by: change all our SPs/Views and dynamic queries in the search engine from <table> to <schema>.<table> Oved, please consider for 4.0
Eli - has the situation changed with postgresql 9.x?
(In reply to Yaniv Kaul from comment #8) > Eli - has the situation changed with postgresql 9.x? In 9.x you can define the schema in the connection string, for example : jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema But this is actually ignored and your connection will fail to use the schema. A workaround I just saw now that was reported to work with 9.x is to send a "SET SCHEMA <schema>" command from the engine code using the opened connection, that might be much easier than applying comment 7
currently we don't see the customer use case need for this support. if needed please reopen with specific use case.