【PostgresSQL】ロールの作成

はじめに

PostgresSQLのロールを作成する機会があったので、その際の作業記録を残します。

やった事

  • ロールの作成
  • SQLファイルから実行
  • ロールの削除

ロールの作成

以下のパターンのユーザを作成しました。

  • 読み取り専用
  • 読み取り＋書き込み
  • 読み取り＋特定テーブルの書き込み
  • 読み取り＋書き込み可＋DB作成権限 ※ログイン不可

GRANT：既存のDBに権限を付与します。
ALTER DEFAULT PRIVILEGES：デフォルトのアクセス権限を定義します。作成済のDBに権限は付与されません。

読み取り専用

CREATE ROLE db_readerWITH LOGIN PASSWORD 'db_reader';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO db_reader;

読み取り＋書き込み

CREATE ROLE db_writerWITH LOGIN PASSWORD 'db_writer';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_writer;

読み取り＋特定テーブルの書き込み

CREATE ROLE db_specific_writer WITH LOGIN PASSWORD 'db_specific_writer';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_specific_writer;
GRANT UPDATE ON something_table TO db_specific_writer;

読み取り＋書き込み＋DB作成

CREATE USER db_user WITH LOGIN CREATEDB PASSWORD 'db_user';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_user;

SQLファイルから実行

cd C:\Program Files\PostgreSQL
psql -U postgres -h localhost -f "C:\Temp\test.sql"

と実行すればsqlファイルの内容を実行できるのですが、パスワードを求められてしまいますため、バッチファイルにする際に不便です。
Windowsの場合、以下でパスワード無しでpsqlを実行できます。

pgpass.confの作成

C:\Users\myuser\AppData\Roaming\postgresql にpgpass.conf を作成します。

pgpass.confの中身は以下のようになります。

localhost:5432:database:postgres_user:postgres_password

これでpsql時にパスワードを求められなくなります。

ロールの削除

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM db_user;
DROP ROLE db_user;

ロールを削除する前に、REVOKEで権限を取り消しておかないと削除できません。

ALTER DEFAULT PRIVILEGESを付与している場合は以下のようになります。

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM db_user;
DROP OWNED BY db_user;
DROP ROLE db_user;

2行目のDROP OWNED BY db_user;を実行する際は、BY句の後に指定しているユーザ（この例だとdb_user）で実行する必要があります。

未分類PostgresSQL

Posted by ababa