CREATE USER db1;
CREATE USER db2;
CREATE USER db3;
CREATE USER dbx;

--
-- PostgreSQL database dump
--


--
-- TOC entry 4 (OID 31388)
-- Name: db1; Type: SCHEMA; Schema: -; Owner: db1
--

CREATE SCHEMA db1 AUTHORIZATION db1;



--
-- TOC entry 2 (OID 31389)
-- Name: db2; Type: SCHEMA; Schema: -; Owner: db2
--

CREATE SCHEMA db2 AUTHORIZATION db2;



--
-- TOC entry 3 (OID 31390)
-- Name: db3; Type: SCHEMA; Schema: -; Owner: db3
--

CREATE SCHEMA db3 AUTHORIZATION db3;


\connect - db1

--
-- TOC entry 5 (OID 31388)
-- Name: db1; Type: ACL; Schema: -; Owner: db1
--

REVOKE ALL ON SCHEMA db1 FROM PUBLIC;
GRANT USAGE ON SCHEMA db1 TO dbx;


SET search_path = db1, pg_catalog;

--
-- TOC entry 6 (OID 31393)
-- Name: nav1; Type: TABLE; Schema: db1; Owner: db1
--

CREATE TABLE nav1 (
    id_nav1 serial NOT NULL,
    txt text,
    pos integer NOT NULL
);


--
-- TOC entry 7 (OID 31393)
-- Name: nav1; Type: ACL; Schema: db1; Owner: db1
--

REVOKE ALL ON TABLE nav1 FROM PUBLIC;
GRANT SELECT ON TABLE nav1 TO dbx;


\connect - db2

REVOKE ALL ON SCHEMA db2 FROM PUBLIC;
GRANT USAGE ON SCHEMA db2 TO dbx;


SET search_path = db2, pg_catalog;

--
-- TOC entry 10 (OID 31409)
-- Name: nav1; Type: TABLE; Schema: db2; Owner: db2
--

CREATE TABLE nav1 (
    id_nav1 serial NOT NULL,
    txt text,
    pos integer NOT NULL
);

REVOKE ALL ON TABLE nav1 FROM PUBLIC;
GRANT SELECT ON TABLE nav1 TO dbx;


\connect - db3

SET search_path = db3, pg_catalog;

--
-- TOC entry 12 (OID 31425)
-- Name: nav1; Type: TABLE; Schema: db3; Owner: db3
--

CREATE TABLE nav1 (
    id_nav1 serial NOT NULL,
    txt text,
    pos integer NOT NULL
);


\connect - db1

SET search_path = db1, pg_catalog;

--
-- Data for TOC entry 26 (OID 31393)
-- Name: nav1; Type: TABLE DATA; Schema: db1; Owner: db1
--

COPY nav1 (id_nav1, txt, pos) FROM stdin;
1	db1_Home	1
2	db1_Products	2
3	db1_Service	3
4	db1_Imprint	4
\.


\connect - db2

SET search_path = db2, pg_catalog;

--
-- Data for TOC entry 28 (OID 31409)
-- Name: nav1; Type: TABLE DATA; Schema: db2; Owner: db2
--

COPY nav1 (id_nav1, txt, pos) FROM stdin;
1	db2_Home	1
2	db2_Products	2
3	db2_Service	3
4	db2_Imprint	4
\.


\connect - db3

SET search_path = db3, pg_catalog;

--
-- Data for TOC entry 30 (OID 31425)
-- Name: nav1; Type: TABLE DATA; Schema: db3; Owner: db3
--

COPY nav1 (id_nav1, txt, pos) FROM stdin;
1	db3_Home	1
2	db3_Products	2
3	db3_Service	3
4	db3_Imprint	4
\.


\connect - db1

SET search_path = db1, pg_catalog;

--
-- TOC entry 20 (OID 31463)
-- Name: nav1_pkey; Type: CONSTRAINT; Schema: db1; Owner: db1
--

ALTER TABLE ONLY nav1
    ADD CONSTRAINT nav1_pkey PRIMARY KEY (id_nav1);


\connect - db2

SET search_path = db2, pg_catalog;

--
-- TOC entry 22 (OID 31471)
-- Name: nav1_pkey; Type: CONSTRAINT; Schema: db2; Owner: db2
--

ALTER TABLE ONLY nav1
    ADD CONSTRAINT nav1_pkey PRIMARY KEY (id_nav1);


\connect - db3

SET search_path = db3, pg_catalog;

--
-- TOC entry 24 (OID 31479)
-- Name: nav1_pkey; Type: CONSTRAINT; Schema: db3; Owner: db3
--

ALTER TABLE ONLY nav1
    ADD CONSTRAINT nav1_pkey PRIMARY KEY (id_nav1);


\connect - db1

SET search_path = db1, pg_catalog;

--
-- TOC entry 14 (OID 31391)
-- Name: nav1_id_nav1_seq; Type: SEQUENCE SET; Schema: db1; Owner: db1
--

SELECT pg_catalog.setval ('nav1_id_nav1_seq', 8, true);


\connect - db2

SET search_path = db2, pg_catalog;

--
-- TOC entry 16 (OID 31407)
-- Name: nav1_id_nav1_seq; Type: SEQUENCE SET; Schema: db2; Owner: db2
--

SELECT pg_catalog.setval ('nav1_id_nav1_seq', 8, true);


\connect - db3

SET search_path = db3, pg_catalog;

--
-- TOC entry 18 (OID 31423)
-- Name: nav1_id_nav1_seq; Type: SEQUENCE SET; Schema: db3; Owner: db3
--

SELECT pg_catalog.setval ('nav1_id_nav1_seq', 8, true);


\connect - dbx

-- TEST1: This should work
(SELECT 'this is from db1' as dbname, * FROM db1.nav1) UNION ALL (SELECT 'this is from db2' as dbname, * FROM db2.nav1) ORDER BY pos;

-- TEST2: This is forbidden
(SELECT 'this is from db1' as dbname, * FROM db1.nav1) UNION ALL (SELECT 'this is from db3' as dbname, * FROM db3.nav1) ORDER BY pos;
