PostgreSQL で読み取り専用ユーザーを作成する

1. Select/Usage を単一のテーブルに付与する
2. PostgreSQL バージョン 9.0+ 用の複数の Tables/Views
3. 読み取り専用ユーザーの作成
4. Postgres を Redash に接続する

読み取り専用ユーザー データベースは、データベースとそのテーブルおよびフィールドへの読み取り専用アクセスが制限されているため、レポート作成に役立ちます。

Postgres Ready-Only ユーザーとは、ユーザーがデータベースから情報を読み取ることしかできず、書き込むことを許可されていないことを意味します。 まず、Postgres 読み取り専用ユーザーを作成する方法を学びましょう。

Select/Usage を単一のテーブルに付与する

Postgres のユーザーとの履歴がある場合は、おそらく GRANT コマンドと REVOKE コマンドを見たことがあるでしょう。 これらは、Postgres におけるアクセス制御の基本的な基盤であることが証明されています。

それらを扱うのは紛らわしいですが、Postgres にはさまざまなアクセス制御設定が用意されており、慎重にナビゲートできます。 これらのコマンドについて注意すべきもう 1つの点は、データベース内に導入されたテーブルなどの新しいオブジェクトには適用されないことです。

問題になるとデフォルトの権限があり、これらの権限は、新しいオブジェクトが作成されるときに Postgres によってロールに適用されます。 ただし、ALTER DEFAULT PRIVILEGES コマンドを使用して、これらのデフォルト権限を変更できます。

したがって、データベースに CONNECT のみを付与すると、ユーザーはデータベースへの接続を許可されますが、他の権限はありません。 ここでは、さらに一歩進んで、namespaces/schemas に USAGE を付与し、テーブルとビューに個別に SELECT を付与する必要があります。

次のように実行できます。

GRANT CONNECT ON DATABASE databasename TO user; -- databasename = your database name
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON tablename TO user;

PostgreSQL バージョン 9.0+ 用の複数の Tables/Views

最新バージョンの PostgreSQL がダウンロードされているとします。 その場合、次のように 1つずつ入力する代わりに、1つのコマンドだけを使用して、テーブル/ビューなどのスキーマ内のオブジェクトに対するアクセス許可を簡単に付与できます。

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

この行は、既に作成されているテーブルのみに影響を与えるように機能します。 PostgreSQL では、次のように、将来的にデフォルトのロールを新しいオブジェクトに自動的に割り当てる機能も提供されます。

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO user;

最初にこのコマンドを発行したユーザーだけが、これらのデフォルト ロールをオブジェクトとテーブルに割り当てることができることに注意してください。

ただし、発行ユーザーがメンバーである任意のロールに設定できます。 同時に、新しいオブジェクトを作成するときに、自分がメンバーになっているすべてのロールのデフォルトの権限を取得するわけではありません。

データベースに所有ロールがあるアプローチを採用できるため、スキーマの変更はその特定の所有ロールで実行されるため、その所有ロールにデフォルトの権限を割り当てます。

9.0 より前の PostgreSQL バージョンの複数の Tables/Views

これらの機能は PostgreSQL の以前のバージョンではすぐには利用できませんが、Postgres 読み取り専用ユーザーを作成することは引き続き可能です。

長さの複数テーブルの変更では、エラーを回避するために、各 ビュー/テーブル に必要な GRANT SELECT を生成する 自動 プロセスを使用することをお勧めします。

これは次の方法で行われます。

SELECT 'GRANT SELECT ON ' || relname || 'TO user; '
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r' ,'v' , 'S');

このコマンドラインは、実行されると、関連する GRANT コマンドを、公開されているすべてのシーケンス、テーブル、およびビューの GRANT SELECT に出力し、コピー アンド ペーストする必要があります。 ただし、すでに作成されたテーブルにのみ適用することに注意する必要があります。

読み取り専用ユーザーの作成

次のコードを実行して、読み取り専用ユーザーを作成します。

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'pass321.'
SUPERUSER INHERIT
NOCREATEDB NOCREATEROLE NOREPLICATION
VALID UNTIL
'infinity';

作成したばかりの Postgres 読み取り専用ユーザーにアクセス許可を付与することができます。

GRANT CONNECTION ON DATABASE databasename TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

ただし、これらのコマンドは、データベースに既に作成されているテーブルに対してのみ機能します。 今後作成されるすべての新しいテーブルにアクセス許可を割り当てるには:

ALTER DEFAULT PRIVILEGES IN SCHEMA
public GRANT SELECT ON TABLES TO Read_Only_user;

GRANT USAGE 以降のテーブルに対する権限を付与する命令は、目的のデータベースで実行されることに注意することが重要です。 そうしないと、権限が機能しません。

これを行うには、\connect databasename; と入力します。 データベースに接続します。

Postgres を Redash に接続する

GRANT/USAGE コマンドがなければ、パーミッションは機能しません。 ここで、USAGE は、オブジェクトの権限要件が満たされている限り、特定のスキーマに含まれるオブジェクトへのアクセスを許可します。 まず、読み取り専用ロールを作成します。

CREATE ROLE myapp_readonly; 
GRANT CONNECT ON DATABASE database default TO myapp_readonly;
GRANT USAGE ON SCHEMA myapp TO myapp_readonly;

次に、新しく作成されたロールの権限を付与します。 最後に、新しい読み取り専用ユーザーで Postgres データベースへの接続が完了したら、redash でユーザーを作成し、それに読み取り専用ロールを割り当ててください。

Postgres 読み取り専用ユーザーを作成すると、データベースを処理する際に非常に役立ちます。 ただし、インストールされているバージョンによっては、タスクの難易度が異なる場合があります。

この記事では、Postgres の読み取り専用ユーザーを適切に作成できるように、さまざまなバージョンの Postgres のすべての基礎を取り上げます。 これにより、データを読み取るアクセス権を持つユーザーを作成できるだけでなく、ユーザーが編集を実行できなくなります。