I've recently migrated one of my databases to using veil. This involved creating a 'private' schema and moving all tables to it. Functions remain in public, and secured views are created there which can be accessed by normal users.
In doing so, I found to my extreme displeasure that although the database continues to function flawlessly, I can no longer restore dumps produced by pg_dump even after hours of manual tweaking. In all cases, this is due to search_path being frobbed during the restore. CASE 1: serial column not in the same schema as associated table create table a(i serial primary key); create schema notpublic; alter SEQUENCE a_i_seq set schema notpublic; Attempting to restore the output of pg_dump on a database in which the above has been executed will result in the error: SET search_path = notpublic, pg_catalog; SET -- -- Name: a_i_seq; Type: SEQUENCE SET; Schema: notpublic; Owner: pfrost -- SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('a', 'i'), 1, false); ERROR: relation "a" does not exist CASE 2: default set to the serial sequence of another table create schema private; create table private.t(i serial primary key); alter sequence private.t_i_seq set schema public; create table public.t(i integer primary key default nextval('t_i_seq')); This is similar to case 1, and will encounter the same error first. However, if that error is manually corrected, restoring the dump will yield: SET search_path = public, pg_catalog; SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('private.t', 'i'), 1, false); -- manually fixed CREATE TABLE t ( i integer DEFAULT nextval('t_i_seq'::regclass) NOT NULL ); ERROR: relation "t_i_seq" does not exist The problem here seems to be that although the sequence t_i_seq is in schema public in the dumped database, restoring the dump places it in schema private. CASE 3: functions containing unqualified function references create schema private; create function private.a(text) returns text language sql immutable as $$ select $1 || 'a'; $$; set search_path = public, private; create function public.b(text) returns text language sql immutable as $$ select a($1); $$; create table foo(t text); insert into foo values ('foo'); create index foo_idx on foo ((b(t))); Restoring the dump of this database yields: ... CREATE INDEX foo_idx ON foo USING btree (b(t)); ERROR: function a(text) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. CONTEXT: SQL function "b" during startup The way I encountered this actually has little to do with veil. The function involved in my case takes as parameters some values from columns of a table and returns a tsvector to be indexed by tsearch2. I suspect this would be common practice if the tsearch2 documentation did not store the vector in an additional column. CASE 4: functions using extension operators Essentially the same as above, but the body of a function contains a reference to an operator without specifying the schema with the operator(schema.name) syntax. Again, contrib modules like tsearch2 are a great way to encounter this problem. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org