PostgreSQLでリード(SELECT)権限ユーザーの作り方 | フルスタックエンジニアライフ

PostgreSQLでSELECTのみを使えるユーザーをサクッと作っていきます。

作成するユーザーの概要は下記のものとします。

・データベースにログインができる
・指定のスキーマのみアクセスを許可
・指定のスキーマ内の全テーブルへのSELECT権限を許可

注意点としてPostgreSQLの場合リード権限を部分的に付与してもスキーマへの参照権限を付与した時点で、そのスキーマの全テーブルの存在自体は表示可能となってしまいます。

１．ロールの作成

PostgreSQLではデータベースを利用するユーザーのことを「ロール」と呼び、ユーザを作成する場合も「CREATE ROLE」コマンドで作成します。

CREATE ROLE readonly_user LOGIN PASSWORD 'input_your_password';

２．各種権限の付与

データベースへのログイン権限の付与
作成したロールがデータベースにログインをできるようにするための権限を付与します。

/* ロールのログインの許可 */
ALTER ROLE readonly_user WITH LOGIN;

スキーマへのアクセス権限付与
データベースのスキーマへのアクセス権限を付与します。
このスキーマへのアクセス権限が無いと後のSELECTの権限を付与してもそもそものスキーマにアクセスができないので、付与し忘れないように注意してください。

/* スキーマのアクセス許可 */
GRANT USAGE ON SCHEMA public To readonly_user;

テーブルへのアクセス（SELECT)権限付与

/* スキーマ内の全テーブルのアクセス(SELECT)を許可 */
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

テーブル個別にアクセス権限を付与する場合はテーブル名を指定します

/* スキーマ内の指定したテーブルのアクセス(SELECT)を許可 */
GRANT SELECT ON table_name TO readonly_user;

３．作成したユーザーの確認

ユーザ表示コマンド、SQLで必要の無い権限が付与されていないかを確認します。

\duコマンドで確認してみます。

\du
                                                       List of roles
   Role name    |              Attributes                    |               Member of                          
----------------+--------------------------------------------+----------------------------------------
 readonly_user  |                                            | {}

pg_rolesテーブルをSELECTで確認してみます。

SELECT * FROM pg_roles where rolname = 'readonly_user';

      rolname      | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid   
-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+--------
 readonly_user     | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 150705
(1 row)                                      | {}

４．作成したユーザーでログイン＆動作確認

対象スキーマにログイン＆SELECTコマンドでテーブルのデータが表示できればOKです。
一応下記以外にINSERT、UPDATE、DELETEが出来ないかを確認したほうがいいです。

#psql -h {接続先ホストIPアドレスorドメイン} {データベース名} -Ureadonly_user

SELECT * FROM {テーブルネーム}

ユーザーから権限を削除

もし権限を間違って付与した場合は下記のコマンドで権限を削除することができます。

/* hogeテーブルからSELECT権限のみ削除 */
REVOKE SELECT ON hoge FROM readonly_user;

/* publicスキーマの全テーブルから全権限を削除 */
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM readonly_user;

以上。PostgreSQLでリード権限（SELECTのみ）のユーザーを作成する方法でした。

おつかれさまでした！！