Search Postgresql Archives

What to expect when mixing inherited tables and different schemas while dealing with functions and views?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi!


As I said in other messages, I'm planning with a partner of mine to use 
inheritance and schemas to separate access and data from several sources 
(companies) for a client we have (accounting office).

The idea is:

	- 1 base schema with all table definitions plus functions plus views
	- 1 schema per client he (our client) have where all tables would be
	  inherited from the base schema
	- Using views and functions declared on base schema to also manipulate
	  and retrieve data from each schema


(Before starting, I've made a dump available at 
http://godoy.homeip.net/~godoy/hydrogen.sql containing basic data for 
testing, if you're interested you'd only need to change some usernames for 
permissions in there.)


But we're facing some problems and before doing anything more serious we'd 
like to know what to expect when:

a) We are with some restricted search_path set (e.g. after "SET search_path TO 
   schema_1") and we make a "SELECT * FROM base.view".  What we're seeing is 
   that views are tied to the schema where they were created, no matter if 
   they are or not fully qualified in their definition.  Is this correct?  I'd 
   expect views to respect the search_path if they aren't fully qualified 
   (i.e. if I created them as "SELECT something FROM table" instead of "SELECT 
   something FROM schema.table").

b) We are seeing a really weird behaviour when we use functions.  It appears 
   that it disregards the search_path from the second run and on.  If I SELECT 
   from a function with the search_path set to, e.g., schema_1, then when I 
   set it to schema_2 then I'll still see data from schema_1.  Note, here, 
   that even the function being created on the base schema results were 
   correctly retrieved at first execution.  (You can repeat that use the above
   dump by connecting, setting the search path to any of three schemas, 
   selecting from the function, changing to other schema and then selecting
   again from the same function -- you'll see the same result --; then, if you
   reconnect and do a first select in another schema and change your 
   search_path you'll see a different result from the previous connection but 
   it will be the same result for both search_paths.)


Are these expected behaviors?  Is there any way to make both views and 
functions work with search_path?  It would be really great to be able to 
define them only once and use them on all "1k" schemas unchanged.

I believe that the behavior with functions is "a bit" dangerous since it 
allows leaking information from one schema to another after a context change.  
(These tests aren't taking permissions into account yet, so after applying 
those this problem could be reduced but...)



TIA,
-- 
Jorge Godoy      <jgodoy@xxxxxxxxx>



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux