DROP USER user1;
DROP DATABASE if exists fdw;
DROP DATABASE if exists pgbench;
CREATE USER user1 SUPERUSER;
CREATE DATABASE pgbench;
CREATE DATABASE fdw;
\! pgbench -i -s 5 pgbench

\c fdw -
CREATE EXTENSION postgres_fdw;

-- Objects of server pgbench
CREATE SERVER pgbench FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'pgbench');
CREATE USER MAPPING FOR public SERVER pgbench OPTIONS (user 'postgres');
CREATE USER MAPPING FOR user1 SERVER pgbench OPTIONS (user 'user1');
CREATE FOREIGN TABLE pgbench_accounts (
	aid int not null,
	bid int,
	abalance int,
	filler char(84)
) SERVER pgbench;

CREATE FOREIGN TABLE pgbench_branches (
	bid int not null,
	bbalance int,
	filler char(88)
) SERVER pgbench;

CREATE FOREIGN TABLE pgbench_tellers (
	tid int not null,
	bid int,
	tbalance int,
	filler char(84)
) SERVER pgbench;

CREATE FOREIGN TABLE pgbench_history (
	tid int not null,
	bid int,
	aid int,
	delta int,
	mtime timestamptz,
	filler char(22)
) SERVER pgbench;
;


-- Objects of server pgbench2
CREATE SERVER pgbench2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'pgbench');
CREATE USER MAPPING FOR public SERVER pgbench2 OPTIONS (user 'postgres');
CREATE FOREIGN TABLE pgbench2_accounts (
	aid int not null,
	bid int,
	abalance int,
	filler char(84)
) SERVER pgbench2
OPTIONS (table_name 'pgbench_accounts');

CREATE FOREIGN TABLE pgbench2_branches (
	bid int not null,
	bbalance int,
	filler char(88)
) SERVER pgbench2
OPTIONS (table_name 'pgbench_branches');

CREATE FOREIGN TABLE pgbench2_tellers (
	tid int not null,
	bid int,
	tbalance int,
	filler char(84)
) SERVER pgbench2
OPTIONS (table_name 'pgbench_tellers');

CREATE FOREIGN TABLE pgbench2_history (
	tid int not null,
	bid int,
	aid int,
	delta int,
	mtime timestamptz,
	filler char(22)
) SERVER pgbench2
OPTIONS (table_name 'pgbench_history');

grant all on pgbench_branches to public;
grant all on pgbench_accounts to public;
grant all on pgbench_tellers to public;
grant all on pgbench_history to public;

-- CREATE VIEWs to check multiple user mapping behavior
create view v_pgbench_branches as select * from pgbench_branches;
create view v_pgbench_accounts as select * from pgbench_accounts;
create view v_pgbench_tellers as select * from pgbench_tellers;
create view v_pgbench_history as select * from pgbench_history;
alter view v_pgbench_branches owner to user1;
alter view v_pgbench_accounts owner to user1;
alter view v_pgbench_tellers owner to user1;
alter view v_pgbench_history owner to user1;

grant all on v_pgbench_branches to public;
grant all on v_pgbench_accounts to public;
grant all on v_pgbench_tellers to public;
grant all on v_pgbench_history to public;

