DB Link Test on Local Cluster across Two DBs with Non Postgres Owner and Non Public Schema

Test Purpose :

DB Link Test on Local Cluster across Two DBs with Non  Postgres Owner and  Non Public Schema
So that I have revoked public access and included search path replacement of my own schemas.


Scenario :
Same Local Cluster -> Different Database -> Both Users are having Own different Schema and Both DB Owners are different


DB :   dvdrental, adventureworks.

schema : dvd_schema,adv_schema
users : dvd_user, adv_user
tablespaces : dvd_ts, adv_ts


Test : Access dvd_t1 from advuser@adventureworks

Steps :

psql >
postgres=# revoke all on schema public from public;
postgres=#  revoke all on schema public from dvduser;
REVOKE
postgres=#  revoke all on schema public from advuser;
REVOKE
postgres=#


bash-4.2$ psql -d  dvdrental -U dvduser
psql (11.4)
Type "help" for help.

dvdrental=# create table dvd_t1(name varchar(10),id int2);
CREATE TABLE
dvdrental=# \d
           List of relations
   Schema   |  Name  | Type  |  Owner
------------+--------+-------+---------
 dvd_schema | dvd_t1 | table | dvduser
(1 row)

dvdrental=# insert into dvd_t1 values ('film1',10);
INSERT 0 1
dvdrental=# insert into dvd_t1 values ('film2',20);
INSERT 0 1
dvdrental=# insert into dvd_t1 values ('film3',30);
INSERT 0 1
dvdrental=#


Execute the below command whether db_link ext is already installed or not.

SELECT pg_namespace.nspname, pg_proc.proname  FROM pg_proc, pg_namespace
WHERE pg_proc.pronamespace=pg_namespace.oid  AND pg_proc.proname LIKE '%dblink%';

If not then, it will return 0 Rows.

Test the DBlink connection. Since I revoked public access, I have mentioned externally in the statement.

Adventureworks=# select public.dblink_connect('host=pg1 user=dvduser password=dvduser123 dbname=dvdrental');
 dblink_connect
----------------
 OK
(1 row)

Adventureworks=#


check postgress_fdw Ext is already installed or not.


Adventureworks=# \dx
                                    List of installed extensions
     Name     | Version |   Schema   |                         Description
--------------+---------+------------+--------------------------------------------------------------
 dblink       | 1.2     | public     | connect to other PostgreSQL databases from within a database
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)


Adventureworks=# CREATE FOREIGN DATA WRAPPER adv_to_dvd_fdw VALIDATOR postgresql_fdw_validator;
CREATE FOREIGN DATA WRAPPER

Adventureworks=# create server adv_to_dvd_server FOREIGN DATA WRAPPER adv_to_dvd_fdw OPTIONS (hostaddr '192.168.56.101',dbname 'dvdrental');
CREATE SERVER

Adventureworks=# CREATE USER MAPPING FOR advuser SERVER adv_to_dvd_server OPTIONS (user 'dvduser',password 'dvduser123');
CREATE USER MAPPING

Adventureworks=# SELECT public.dblink_connect('adv_to_dvd_server');

 dblink_connect
----------------
 OK
(1 row)

Adventureworks=# GRANT USAGE ON FOREIGN SERVER adv_to_dvd_server to advuser;
GRANT

Adventureworks=# 

Test 1 with Exact Cols as per original table :
**********************************************
select * from public.dblink('adv_to_dvd_server','select name,id from dvdrental.dvd_schema.dvd_t1') AS DATA(CNAME varchar(10), CID int2);

 cname | cid
-------+-----
 film1 |  10
 film2 |  20
 film3 |  30
(3 rows)


Test 2 with only one col :
**********************************************

select CID from public.dblink('adv_to_dvd_server','select id from dvdrental.dvd_schema.dvd_t1') AS DATA(CID int8);
 cid
-----
  10
  20
  30
(3 rows)


Test 1 with Relevant Data columns change
**********************************************

Adventureworks=# select CID from public.dblink('adv_to_dvd_server','select id from dvdrental.dvd_schema.dvd_t1') AS DATA(CID int8);
 cid
-----
  10
  20
  30
(3 rows)

Adventureworks=# select * from public.dblink('adv_to_dvd_server','select name,id from dvdrental.dvd_schema.dvd_t1') AS DATA(CNAME varchar(20), CID int8);
 cname | cid
-------+-----
 film1 |  10
 film2 |  20
 film3 |  30
(3 rows)

Adventureworks=# select * from public.dblink('adv_to_dvd_server','select name,id from dvdrental.dvd_schema.dvd_t1') AS DATA(CNAME text, CID int8);
 cname | cid
-------+-----
 film1 |  10
 film2 |  20
 film3 |  30
(3 rows)

Adventureworks=#