http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [195]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2917]
PostgreSQL [103]
기타정보 [151]
운영체제 [584]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: PostgreSQL

 회원가입

ERROR: cross-database references are not implemented / postgres_fdw / psql dblink
최길호 [LIST]   2021-11-15 00:46:01, 조회 : 185


https://towardsdatascience.com/how-to-set-up-a-foreign-data-wrapper-in-postgresql-ebec152827f3
https://www.postgresql.org/docs/13/postgres-fdw.html

psql (13.4)
postgres=# select * from cell_000.public.zipcode limit 5;
ERROR:  cross-database references are not implemented: "cell_000.public.zipcode"

postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
postgres=#  CREATE SERVER cell_000
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5413', dbname 'cell_000');

postgres=# \des
          List of foreign servers
   Name   |  Owner   | Foreign-data wrapper
----------+----------+----------------------
cell_000 | postgres | postgres_fdw
(1 row)

postgres=# select * from pg_foreign_server;
  oid  | srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                 srvoptions                
-------+----------+----------+--------+---------+------------+--------+--------------------------------------------
16399 | cell_000 |       10 |  16398 |         |            |        | {host=localhost,port=5413,dbname=cell_000}
(1 row)

postgres=# alter user segio Superuser;
ALTER ROLE
postgres=# create user localuser password 'localuser';
CREATE ROLE
postgres=# \du
                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
localuser |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
segio     | Superuser                                                  | {}

postgres=# CREATE USER MAPPING FOR localuser SERVER cell_000 OPTIONS (user 'segio', password 'segio');
CREATE USER MAPPING
postgres=#


cell_000=# \d zipcode
                      Table "public.zipcode"
Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
zipcode | character varying(20)  |           |          |
sido    | character varying(30)  |           |          |
gugun   | character varying(30)  |           |          |
dong    | character varying(100) |           |          |
bunji   | character varying(30)  |           |          |

cell_000=#

postgres=# \c postgres localuser
You are now connected to database "postgres" as user "localuser".
postgres=> CREATE FOREIGN TABLE fzipcode (
zipcode  character varying(20) ,
sido     character varying(30) ,
gugun    character varying(30) ,
dong     character varying(100),
bunji    character varying(30)
)
        SERVER cell_000
        OPTIONS (schema_name 'public', table_name 'zipcode');
ERROR:  permission denied for foreign server cell_000
postgres=# alter user localuser Superuser;
ALTER ROLE

postgres=> CREATE FOREIGN TABLE fzipcode ....

postgres=> select * from fzipcode limit 6;
zipcode | sido | gugun  |         dong          |    bunji    
---------+------+--------+-----------------------+-------------
135-807 | 서울 | 강남구 | 개포1동 우성3차아파트 | (1∼6동)
135-806 | 서울 | 강남구 | 개포1동 우성9차아파트 | (901∼902동)
135-770 | 서울 | 강남구 | 개포1동 주공아파트    | (1∼16동)
135-805 | 서울 | 강남구 | 개포1동 주공아파트    | (17∼40동)
135-966 | 서울 | 강남구 | 개포1동 주공아파트    | (41∼85동)
135-807 | 서울 | 강남구 | 개포1동 주공아파트    | (86∼103동)
(6 rows)



권한 제거하면 아래와 같이 에러 난다.
postgres=# alter user localuser NOSUPERUSER;
ALTER ROLE
postgres=# \c postgres localuser
postgres=> select * from fzipcode limit 5;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server's authentication method must be changed or password_required=false set in the user mapping attributes.


3.239.112.140


  LIST

제목 작성자 작성일 조회
Admin  odbc_fdw test    최길호 2021/11/22 139
Admin  ERROR: cross-database references are not implemented / postgres_fdw / psql dblink    최길호 2021/11/15 185
Admin  CREATE EXTENSION postgres_fdw; [ source install ]    최길호 2021/11/15 292
Admin  psql [ 8.4.20 , 8.4.22 ] 여러 Version 운영, upgrade 8.4.20 to 8.4.22 [ 뒤 Version 다르면 그냥 시작 ]    최길호 2021/11/12 117
Admin  source make install [ 8 ~ 14 ]    최길호 2021/11/11 171
Admin  psql odbc make install    최길호 2021/11/09 135
Admin  oracle to psql dblink    최길호 2021/11/09 155
Admin  /usr/bin/initdb -D /var/lib/pgsql/test [ Instance 추가 ]    최길호 2021/11/05 107
Troubleshoot  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?    최길호 2021/11/04 168
Troubleshoot  ERROR: current transaction is aborted, commands ignored until end of transaction block; [ postgresql-odbc upgrade ]    최길호 2021/11/04 183
Troubleshoot  pg_ctl start $PGDATA/pg_log/ 한글/영어 메세지 [ $PGDATA/postgresql.conf lc_messages ]    최길호 2021/10/29 206
Admin  pg_dumpall pg_upgrade    최길호 2021/10/21 149
Admin  통계정보    최길호 2021/10/21 124
Admin  pg_dump / pg_restore[ psql (8.4.20) to psql (14.0) ]    최길호 2021/10/20 170
Admin  PostgreSQL 14 / pgadmin Install [ lc_collate 'ko_KR.UTF-8' order by 정상 ]    최길호 2021/10/17 235
Admin  psql dblink to oracle [ odbc test ] 한글    최길호 2021/04/19 2499
Admin  postgres , enterprisedb(EDB) / [ ERROR: type "number" does not exist ]    최길호 2020/09/01 273
Admin  install postgresql on docker centos:7    최길호 2020/08/31 382
Troubleshoot  psql: server closed the connection unexpectedly [ vi $PGDATA/postgresql.conf / listen_addresses='*' ]    최길호 2020/08/31 413

    목록보기   다음페이지 1 [2][3][4][5][6]
       

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
11/23
[ORACLE]
acfsutil size: ACFS-03006: sma....
by 최길호
11/07
[운영체제]
Job for sshd.service failed be....
by 최길호
11/04
[ORACLE]
jsp error 위치 확인 [ debug ].
by 최길호