FDWをCREATE EXTENSIONしてから、アプリ専用ユーザーでSELECTできるまでの備忘録

9996 ワード
FDW PostgreSQL PostgreSQL テキストリンク

環境

  • VirtualBox 6.1.14
  • Ubuntu 20.04.4
  • PostgreSQL 14.2
  • tds_fdw 2.0.2
  • わりとアホな脳みそ

状況

  • SQLServer上にある複数のデータベースをPostgresから参照したい！
  • postgresユーザーじゃないユーザーで参照したい！（WEBアプリゆえ…）
  • やり方を教えてくれる先輩などいない！！

そしてやりたい事をそのまんま解説している記事も見つからない…っ！
(つд⊂)ﾋﾞｴｰﾝ

参照させて頂いた先人の知恵

ありがたや～！

-おにぃにぃのITブログ
　わりとここだけでどうにかなる。神さま～～！
-gitHUBのtdsのページ
　なんだかんだすごく親切にインストール方法を教えてくれてる公式様。神。
-ICHI.PRO
　だいたいここのマネでいける！でも私のようなアホにはちょっと何言ってるかよくわからないところもある！神。

postgreのインストールとかtds_fdwのインストールとかは、のサイトとか公式とか。
あとは他にも素晴らしい記事がたくさんあるので、そちらを頼ってください。
悲しいことに、もう忘れちゃったんだ…

というわけで、ここが今回のスタート地点

  • postgresインストール済
  • postgresユーザーをパスワードでログインできるように設定済
  • アプリ用ユーザー作成済
  • アプリ用ユーザーでアプリ用ＤＢ作成済
  • アプリ用ユーザーログインのためにpg_hba.conf編集済

本当にtds_fdwの事だけ！

いっこ注意

このtds_fdw、2022年3月現在、なんとWindows版はありません！！！がーん…！
なので、Windowsで開発している私みたいな人たちは、仮想環境作ってがんばりませう…
どうせ本番環境でLinux使う事になるんだし、まぁ…あとが楽なんだと思って！

ややこしくないように、正解の手順をまず書くよ！

まずはpotsgresユーザーに切り替えて…

さらアプリ用のＤＢに接続してからやってまーす！

CREATE EXTENSION tds_fdw;
create server [外部サーバー名] foreign data wrapper tds_fdw;
options (servername '[サーバー名とかIPアドレスとか]', port '[ポート番号]', database '[ＤＢ名]');

外部サーバー名のところは好きな名前でＯＫ！
今回みたいに複数作る時は、いい感じにわかりやすい名前がいいかも。
ちなみにtds_fdwをDROP EXTENSIONする時、アプリ用のＤＢに接続して作ったやつは、アプリ用のＤＢに接続してからやらないと「所有者である必要があります」的なのが出て消せないです
postgresユーザーでＤＢ指定せずにログインした状態だとダメって事です～！
なんでかは説明できない……なんでなんだ…
そもそもpostgresの事からよくわかってない……。

次にユーザーマッピングと、権限はいどうぞする。

create user mapping for [アプリ用のPostgresユーザー名] server [外部サーバー名] 
options (username '[SQLServerのユーザー名]', password '[SQLServerのパスワード]');
GRANT USAGE ON FOREIGN SERVER [外部サーバー名] TO [アプリ用のPostgresユーザー名];
grant all on foreign server [外部サーバー名] to [アプリ用のPostgresユーザー名];

GRANT ALLは本当に必要なのかどうか、ちょっとわからない…。
悪戦苦闘している中で一応必要っぽかったからやってる、程度の感じで本当はいらないのかもしれないけど、それを検証するヤル気が今はナッシング～

次に、アプリ用のユーザーに切り替える

テーブルをつくるよ！！

create foreign table [postgres側用のテーブル名] (
　　"[カラム名１]" [データ型１],
　　"[カラム名]" [データ型]
) 
server [外部サーバー名] options (table '[SQLServer側のテーブル名]');

NOT NULLとか指定したい時はな感じで。

"[カラム名１]" [データ型１] NOT NULL,

とりあえずこれでSELECT文流してみたらデータが見られた～～！おめでとう私！！
ちなみに、Postgres側のテーブルを参照先のテーブルと同じ構成にしなさいっていろんなところに書いてあったけど、同じ構成じゃなくても大丈夫だった！
だって参照先のテーブル、たくさんカラムあって大変なんだもん…

同じSQLServer内の複数ＤＢ参照したいよ！って時は…

外部サーバーをもういっこつくるよ！！
postgresユーザーで、アプリ用ＤＢに接続して作業。

create server [外部サーバー名２] foreign data wrapper tds_fdw;
options (servername '[サーバー名とかIPアドレスとか]', port '[ポート番号]', database '[ＤＢ名]');

外部サーバー名は、さっきのやつとは違う名前で！
そのあとはと全く同じ手順
ユーザーマッピングと権限はいどうぞして、アプリ用ユーザーに切り替えてテーブル作って完成～
おめでとう私～！！

正解に辿り着くまでの紆余曲折

ここからは正解に辿り着くまでに泣きながら歩いた道程オブＦＤＷ。
反省の記録。

参考サイトの神々のお陰で流したコマンド自体はと変わらないんだけど、最初はアプリ用のユーザーに切り替えないでpostgresユーザーでテーブル作成までやっておりました…。
するとあら不思議。
GRANTしたはずなのにアプリ用ユーザーからはSELECTできないぞ～！！という展開に
何の権限が足りないのか分からなかったのでここを参考にしながら、一個ずつ権限与えちゃ削除してSELECT流して…っていうのを繰り返してみた。
結果、superuserの場合は結果が返ってくるのに、そのほかは全部「アクセスが拒否されました」。

Σ(ﾟДﾟ；ｴｰｯ!

さらにGRANT ALLしてみても「アクセスが拒否されました」なわけで…
superuser権限だとOKって事は何らかの権限が足りないのだと思ったのだけど……そしてその原因が知りたくて頑張ってみたけど、いい加減時間勿体ないし！という事で、一旦全部消してやり直すことに。
よしよし、外部サーバー消すじょ。

DROP SERVER [外部サーバー名] CASCADE;

それから外部サーバーを作り直して……。
って思ったら、何故か拒否

じゃあtds_fdwからDROP EXTENSIONしちゃうもんね！

DROP EXTENSION IF EXISTS tds_fdw CASCADE;
ERROR:  機能拡張 tds_fdw の所有者である必要があります

(つд⊂)

その後、アプリ用のＤＢに繋いで無事消せたんですが、なんていうかこう……用意されている落とし穴に次々ハマっていく感覚…
本当は原因が分かった方がいいし、一旦消してもう一回はそこそこ悪手だとは思ってるんだけど、いかんせんわかんなすぎた