Bug 951923

Summary:	[RFE] [db] create application objects within own schema
Product:	[oVirt] ovirt-engine	Reporter:	Alon Bar-Lev <alonbl>
Component:	RFEs	Assignee:	Eli Mesika <emesika>
Status:	CLOSED WONTFIX	QA Contact:	Pavel Stehlik <pstehlik>
Severity:	low	Docs Contact:
Priority:	unspecified
Version:	---	CC:	bazulay, bsettle, bugs, emesika, iheim, lpeer, masayag, mgoldboi, pstehlik, rbalakri, Rhev-m-bugs, sbonazzo, srevivo, zdover
Target Milestone:	---	Keywords:	FutureFeature, Improvement
Target Release:	---	Flags:	ylavi: ovirt-future? rule-engine: planning_ack? rule-engine: devel_ack? rule-engine: testing_ack?
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Enhancement
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2016-11-29 09:32:02 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	Infra	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	911191

Description Alon Bar-Lev 2013-04-14 12:50:50 UTC

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.

Comment 5 Eli Mesika 2013-11-05 12:45:24 UTC

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

Comment 6 Alon Bar-Lev 2013-11-05 12:52:54 UTC

I agree, we tried to do this properly :)
Sad that we cannot.

Comment 7 Eli Mesika 2015-09-06 09:43:30 UTC

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

Comment 8 Yaniv Kaul 2015-11-11 10:02:32 UTC

Eli - has the situation changed with postgresql 9.x?

Comment 9 Eli Mesika 2015-11-11 10:17:37 UTC

(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

Comment 11 Moran Goldboim 2016-11-29 09:32:02 UTC

currently we don't see the customer use case need for this support. if needed please reopen with specific use case.