経緯
PostgreSQLを使用する機会があり、要件としてはテーブルのアクセス権限を
ユーザ事に分ける必要がありました。
例：
Aユーザは読み取りのみ
Bユーザは読み取り、書き込み、削除
これがMySQLであればユーザ事に以下の設定で行けます。（たぶん）
一括の場合
GRANT 権限 ON DB名.* TO user;
テーブルごとの場合
GRANT 権限 ON DB名.テーブル名 TO user;
ちなみに現状は・・・
ググってみた
MySQLは触ったことがある(一般的なSQL分を投げる程度)が、
PostgreSQLは未知の世界だったので調べてみた。
すると大体以下のコマンドを記載している記事がヒットします。
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
実行してみた
dbtest=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
ERROR: "ALL"またはその近辺で構文エラー
行 1: GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
ALLなんてないけどと怒られる・・・
更にググってみた
どうやらALLはPostgreSQL9以降で使えるらしい？
19.2.6. 履歴データベースへの読み取り専用アクセスの許可
更に実行してみた
ユーザ作成
# psql -U postgres -c "CREATE ROLE ユーザ名 WITH LOGIN;" DB名ユーザへDBへの接続権限付与
# psql -U postgres -c "GRANT CONNECT ON DATABASE DB名 TO ユーザ名; DB名public スキーマの使用をユーザーに許可
psql -U postgres -c "GRANT USAGE ON SCHEMA public TO ユーザ名;" DB名ユーザーに許可するパーミッションと全テーブルを対象とするSQL文を生成して、ファイルに保存
# psql -U postgres -c "SELECT 'GRANT 付与したい権限 ON ' || relname || ' TO ユーザ名;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v');" --pset=tuples_only=on DB名 > grant.sql作成されたgrant.sqlを実行
psql -U postgres -f grant.sql DB名
GRANTの表示が出ればOK
権限を今回SELECTのみ付与した為、user01にrが追加されました。
(postgresアカウントはMySQLのrootの様な管理者アカウントなのでマスター権限が自動で付与されました。)
以下引用元DBOnline
=xxxx -- PUBLICに与えられた権限
uname=xxxx -- 1人のユーザに与えられた権限
group gname=xxxx -- グループに与えられた権限
r -- SELECT（読み取り（read））
w -- UPDATE（書き込み（write））
a -- INSERT （追加（append））
d -- DELETE
R -- RULE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
T -- TEMPORARY
arwdRxt -- 全ての権限（テーブル用）
* -- 直前の権限に関するグラントオプション
/yyyy -- この権限を付与したユーザ
感想
調べ方が悪いのか古いバージョンだからなのかわかりませんが、対応方法がなかなか見つかりませんでした。
全テーブルに権限付与という操作はレアケースではないと思うのですが、初PostgreSQLということもあり苦戦しました。
誰かのお役に立てれば幸いです。