dblink 测试

select * from pg_extension;

创建插件
create extension if not exists dblink;


postgres=> select * from pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+-------------+----------+--------------+----------------+------------+-----------+--------------
14173 | plpgsql | 10 | 11 | f | 1.0 | |
16567 | pgstattuple | 10 | 2200 | t | 1.5 | |
24759 | dblink | 10 | 2200 | t | 1.2 | |
(3 rows)

postgres=> \c
You are now connected to database "postgres" as user "tt".
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
tt | | {}
zc | Superuser | {}



开启dblink ：
postgres=# select dblink_connect('dblinktest','host=192.168.75.2 dbname=zc user=zc password=zc port=5432');
dblink_connect
----------------
OK
(1 row)

通过dblink 查询：
postgres=# select * from dblink('dblinktest', 'select * from t2') as t(id int, name varchar(32));
id | name
----+------
1 | zc
(1 row)

关闭dblink ：
postgres=# SELECT dblink_disconnect('dblinktest');
dblink_disconnect
-------------------
OK
(1 row)

再次查询
postgres=# select * from dblink('dblinktest', 'select * from t2') as t(id int, name varchar(32));
ERROR: could not establish connection
DETAIL: missing "=" after "dblinktest" in connection info string






授予tt用户创建不安全的链接权限
GRANT all ON FUNCTION dblink_connect_u(text) TO tt;
GRANT all ON FUNCTION dblink_connect_u(text, text) TO tt;
进程测试
postgres=> SELECT dblink_connect_u('myconn', 'dbname=zc options=-csearch_path=');
dblink_connect_u
------------------
OK
(1 row)

postgres=> select * from dblink('myconn', 'select * from t2') as t(id int, name varchar(32));
ERROR: relation "t2" does not exist
CONTEXT: while executing query on dblink connection named "myconn"
postgres=> select * from dblink('myconn', 'select * from public.t2') as t(id int, name varchar(32));
id | name
----+------
1 | zc
(1 row)

可以发现 不用密码就能查询了。


超级用户 使用安全连接 可以不用密码查询，但是普通用户不行
超级用户：
postgres=# SELECT dblink_connect('myconn1', 'dbname=zc options=-csearch_path=');
dblink_connect
----------------
OK
(1 row)

postgres=#
postgres=# select * from dblink('myconn1', 'select * from public.t2') as t(id int, name varchar(32));
id | name
----+------
1 | zc
(1 row)

普通用户：
无法创建无密码的链接。
postgres=> SELECT dblink_connect('myconn1', 'dbname=zc options=-csearch_path=');
ERROR: password is required
DETAIL: Non-superusers must provide a password in the connection string.
postgres=>




dblink — 在一个远程数据库中执行一个查询
当给定两个text参数时，第一个被首先作为一个持久连接的名称进行查找；如果找到，该命令会在该连接上被执行。
如果没有找到，第一个参数被视作一个用于dblink_connect的连接信息字符串，并且被指出的连接只是在这个命令的持续期间被建立。


普通用户需要输入密码，root可以不用密码：
普通用户：
postgres=> SELECT *
postgres-> FROM dblink('dbname=zc options=-csearch_path=',
postgres(> 'select proname, prosrc from pg_proc')
postgres-> AS t1(proname name, prosrc text)
postgres-> WHERE proname LIKE 'bytea%';
ERROR: password is required
DETAIL: Non-superusers must provide a password in the connection string.
postgres=>

超级用户：
zc=# SELECT *
zc-# FROM dblink('dbname=zc options=-csearch_path=',
zc(# 'select proname, prosrc from pg_proc')
zc-# AS t1(proname name, prosrc text)
zc-# WHERE proname LIKE 'bytea%';
proname | prosrc
--------------------------+--------------------------
byteain | byteain
byteaout | byteaout
bytea_string_agg_transfn | bytea_string_agg_transfn
bytea_string_agg_finalfn | bytea_string_agg_finalfn
byteaeq | byteaeq
bytealt | bytealt
byteale | byteale
byteagt | byteagt
byteage | byteage
byteane | byteane
byteacmp | byteacmp
bytea_sortsupport | bytea_sortsupport
bytealike | bytealike
byteanlike | byteanlike
byteacat | byteacat
bytearecv | bytearecv
byteasend | byteasend


普通用户使用dblink 命令的两个方式：

方式1：
SELECT *
FROM dblink('host=192.168.75.2 dbname=zc user=zc password=zc port=5432',
'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
普通用户使用上面的sql：
postgres=> SELECT *
postgres-> FROM dblink('host=192.168.75.2 dbname=zc user=zc password=zc port=5432',
postgres(> 'select proname, prosrc from pg_proc')
postgres-> AS t1(proname name, prosrc text)
postgres-> WHERE proname LIKE 'bytea%';
proname | prosrc
--------------------------+--------------------------
byteain | byteain
byteaout | byteaout
bytea_string_agg_transfn | bytea_string_agg_transfn
bytea_string_agg_finalfn | bytea_string_agg_finalfn
byteaeq | byteaeq
bytealt | bytealt
byteale | byteale
byteagt | byteagt
byteage | byteage
byteane | byteane

方式2：
SELECT *
FROM dblink('dblinktest',
'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';

postgres=> select dblink_connect('dblinktest','host=192.168.75.2 dbname=zc user=zc password=zc port=5432');
dblink_connect
----------------
OK
(1 row)

postgres=> SELECT *
postgres-> FROM dblink('dblinktest',
postgres(> 'select proname, prosrc from pg_proc')
postgres-> AS t1(proname name, prosrc text)
postgres-> WHERE proname LIKE 'bytea%';
proname | prosrc
--------------------------+--------------------------
byteain | byteain
byteaout | byteaout
bytea_string_agg_transfn | bytea_string_agg_transfn
bytea_string_agg_finalfn | bytea_string_agg_finalfn



dblink_exec 进行增删改

postgres=> select dblink_exec('dblinktest', 'insert into t1 (id) values (4)');
dblink_exec
-------------
INSERT 0 1
(1 row)

postgres=> select * from dblink('dblinktest', 'select * from t1') as t(id int);
id
----
4
2
3
(3 rows)


dblink_open 打开游标

SELECT dblink_open('dblinktest', 'select * from t1');

dblink_fetch从一个之前由dblink_open建立的游标中取得行。

SELECT dblink_open('dblinktest', 'select * from public.t1');
SELECT * FROM dblink_fetch('dblinktest', 3) AS (id int);

超级用户测试通过，
postgres=# SELECT dblink_open('dblinktest', 'select * from public.t1');
dblink_open
-------------
OK
(1 row)

postgres=# SELECT * FROM dblink_fetch('dblinktest', 3) AS (id int);
id
----
4
4
4
(3 rows)
关闭游标
SELECT dblink_close('dblinktest');


普通用户报错：
postgres=> SELECT dblink_open('dblinktest', 'select * from public.t1');
ERROR: connection not available
怀疑权限问题，没法打开游标

查询当前的dblink
SELECT dblink_get_connections();
postgres=# SELECT dblink_get_connections();
dblink_get_connections
----------------------------------
{myconn1,dblinktest,dblinktest1}




异步查询：
SELECT dblink_send_query('dblinktest', 'SELECT * FROM t1');

postgres=# SELECT dblink_send_query('dblinktest', 'SELECT * FROM t1');
dblink_send_query
-------------------
1
(1 row)
如果查询被成功地派送返回 1，否则返回 0。

dblink_is_busy — 检查连接是否正在忙于一个异步查询
SELECT dblink_is_busy('dblinktest');
postgres=# SELECT dblink_is_busy('dblinktest');
dblink_is_busy
----------------
0

dblink_get_result — 得到一个异步查询结果

SELECT * FROM dblink_get_result('dblinktest') AS t1(id int);

postgres=# SELECT * FROM dblink_get_result('dblinktest') AS t1(id int);
id
----
4
4
4
2
3



dblink_cancel_query — 在命名连接上取消任何活动查询

SELECT dblink_cancel_query('dblinktest');
postgres=# SELECT dblink_cancel_query('dblinktest');
dblink_cancel_query
---------------------
OK
(1 row)