Database Support Blog

  • EDB
2021.08.26

EDBのDBMS_SCHEDULERでセキュアなジョブをスケジューリングする方法

当記事はエンタープライズDB社のブログ記事の翻訳の紹介および弊社考察を加えています。

Raghavendra Rao. (2020, Aug, 24). How to secure Job scheduling with DBMS_SCHEDULER in EDB Postgres Advanced Server.
The EDB Blog.
https://www.enterprisedb.com/postgres-tutorials/how-secure-job-scheduling-dbmsscheduler-edb-postgres-advanced-server


EPAS データベースの互換性は、Oracle と同様の機能を提供しているため、開発者や運用担当者は既存の PL/SQL の開発や運用のスキルの多くを引き続き活用できます。EPAS でサポートされている ORACLE 準拠の機能の一つに、DBMS_SCHEDULER パッケージを使用したジョブがあります。

EPAS の DBMS_SCHEDULER パッケージは、Oracle と比較して部分的な実装になっています。DBMS_SCHEDULER はスケジュールされたジョブを実行するために pgAgent サービスを使用します。pgAgent は PostgreSQL のジョブスケジューリングエージェントであり、ユーザがデータベースから PL/SQL や OS シェルスクリプトを定期的に実行することができます。

Linux OS のプロセス(pgAgent)が OS ユーザーによって実行され、定期的にデータベースへの接続を行い、カタログ内に実行すべきジョブがあるかどうかを確認するアーキテクチャになります。

DBMS_SCHEDULER

ジョブをスケジュールするコンポーネント:

  • ユニークなジョブ名 - DBMS_SCHEDULER.CREATE_JOB
  • プログラム(”何を”実行するか) - DBMS_SCHEDULER.CREATE_PROGRAM
  • スケジュール("いつ”実行するか) - DBMS_SCHEDULER.CREATE_SCHEDULE
  • カタログビューの監視

前提条件

Linux OS で EPAS 12 のジョブをスケジュールするために必要なソフトウェアは次の通りです。

  • EPAS 12
  • Extensions
  • pgAgent
  • DBMS_SCHEDULER

以下の例では、サービスの管理に systemd を使用している Linux システムを想定しています。


インストール

前提要件に挙げたソフトウェアをインストールします。

  • EPAS 12 をインストール(手順はドキュメントを参照してください)
  • edb-pgagent-12 をインストール(手順はドキュメントを参照してください)
  • Extensions をインストール
 
 --pgAgent インストール
 edb=# create extension pgagent;
 CREATE EXTENSION
  
  
 --dbms_scheduler インストール
 edb=# CREATE EXTENSION dbms_scheduler;
 CREATE EXTENSION
  
  
 edb=#\dx
  
                                   List of installed extensions
        Name       | Version |   Schema   |                     Description                      
 ------------------+---------+------------+------------------------------------------------------
  dbms_scheduler   | 1.1     | sys        | Creates catalog objects for DBMS_SCHEDULER package
  edb_dblink_libpq | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
  edb_dblink_oci   | 1.0     | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
  edbspl           | 1.0     | pg_catalog | EDB-SPL procedural language
  pgagent          | 4.0     | pgagent    | A PostgreSQL job scheduler
  pldbgapi         | 1.1     | pg_catalog | server-side support for debugging PL/pgSQL functions
  plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
  
 (7 rows)
 

スーパーユーザーでジョブをスケジュールする方法

このセクションでは、データベースのスーパーユーザーとしてジョブをスケジュールする方法を見ていきます。

・スーパーユーザーとして pgAgentサービスを開始する

 
 --Start the pgagent
 
 # systemctl start edb-pgagent-12.service
 

・まず、テーブルに行を INSERT するサンプルPLSQLプログラムを作成します。
 1分毎にデータベースに接続されているユーザー数を収集したいとします。
 最初にテーブルを作成し、次に統計情報(pg_stat_activity)を収集して
 テーブルに挿入するプログラムを作成します。

 
 --テーブルを作成
 edb=# create table userinfo(at_time timestamp, total_users_in_db number(10));
 CREATE TABLE
  
  
 --ジョブ/スケジュールから呼びだされるプログラムを定義
 CREATE OR REPLACE PROCEDURE public.get_user_info ()
 IS
 DECLARE
 BEGIN
    EXECUTE IMMEDIATE 'insert into public.userinfo select now(),count(*) from pg_stat_activity ;';
 END;
 

・次に、ジョブとプログラム名を作成して、1分ごとに実行するようにスケジュールする必要があります。 まず、実行されるプログラム名を作成します。

 
 -- プログラム名を作成
  
 EXEC DBMS_SCHEDULER.CREATE_PROGRAM('program_logusers',
                                     'STORED_PROCEDURE',
                                     '"public"."get_user_info"',
                                     0,
                                     true,
                                     'Program that runs every minute to insert number of users in db');
  
 -- 有効化
 EXEC DBMS_SCHEDULER.enable (name => 'program_logusers');
  
  
  
  
 -- プログラムが作成されているか確認
  
 edb=# SELECT owner, program_name, enabled FROM dba_scheduler_programs;
  
     owner     |   program_name   | enabled 
 --------------+------------------+---------
  enterprisedb | program_logusers | t
  
 (1 row)
 

・次に、いつ実行されるかを定義するスケジュールを作成します。

  
 EXEC
    DBMS_SCHEDULER.CREATE_SCHEDULE (
                                     schedule_name    => 'every_minute',  
                                     start_date       => now, 
                                     repeat_interval  => 'FREQ=MINUTELY;',  
                                     comments         => 'This schedule executes every minute');
  
  
 --スケジュールが作成されているか確認
  
 edb=# SELECT owner, schedule_name FROM dba_scheduler_schedules;
  
     owner     | schedule_name 
 --------------+---------------
  enterprisedb | every_minute
  
 (1 row)
 

・最後に、プログラムを実行するジョブを作成し、1分ごとに実行されるようにスケジュールします。

 
 EXEC
    DBMS_SCHEDULER.CREATE_JOB (
                 job_name       => 'job_loguserinfo',
                 program_name   => 'program_logusers',
                 schedule_name  => 'every_minute',
                 enabled        => true,
                 comments       => 'Job runs every minute to store users in db into a table');
  
  
  
 -- ジョブが作成されているか確認
  
 edb=# SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;
  
     owner     |    job_name     |     job_class     | enabled 
 --------------+-----------------+-------------------+---------
  enterprisedb | job_loguserinfo | DEFAULT_JOB_CLASS | t
  
 (1 row)
 

・以上でジョブは1分毎に実行されます。ログには実行された各ジョブのエントリが表示されます。

 
 DEBUG: Creating DB connection: user=enterprisedb  hostaddr=127.0.0.1 port=5444 dbname=edb
 DEBUG: Parsing connection information...
 DEBUG: Allocating new connection for the database with connection string: user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444...
 DEBUG: Starting job: 2
 DEBUG: Creating DB connection: user=enterprisedb  hostaddr=127.0.0.1 port=5444 dbname=edb
 DEBUG: Parsing connection information...
 DEBUG: Allocating new connection for the database with connection string: user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444...
 DEBUG: Executing SQL step 2(part of job 2)
 DEBUG: Parsing connection information...
 DEBUG: Returning the connection to the connection pool: 'user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444'...
 DEBUG: Parsing connection information...
 DEBUG: Returning the connection to the connection pool: 'user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444'...
 DEBUG: Completed job: 2
 

・テーブルの状態を確認すると1分毎に新しい行が追加されています。

 
 edb=# select * from userinfo ;
  
           at_time          | total_users_in_db 
 ---------------------------+-------------------
  04-JUN-20 01:46:03.651164 |                10
  04-JUN-20 01:47:03.677979 |                10
  04-JUN-20 01:48:03.687966 |                10
  
 (3 rows)
 

非スーパーユーザーでジョブをスケジュールするセキュアな方法

ほとんどの手順はスーパーユーザーの場合と同じですが、非スーパーユーザーがジョブをスケジューリングするためには、スーパーユーザーと競合しないように、そのユーザーの OS レベルで pgAgent デーモンを実行する必要があります。

非スーパーユーザーのジョブスケジューリングについては次の手順に従ってください。(「USER1」が非スーパーユーザーの名前になります)

・非スーパーユーザーでジョブを実行するための pgAgent デーモンプロセスを実行するOSユーザーを作成します。

 
 # useradd user1
 

・OSユーザと同じ名前のデータベースユーザーを作成し、
 pgAgent および dbms_scheduler 関連の関数/ビュー/テーブルが存在する
 「PGAGENT」および「SYS」スキーマに対する権限を付与し、
 非スーパーユーザーが独自のジョブ/プログラム/スケジュールを作成できるようにします。

 
 CREATE ROLE user1 LOGIN  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
 GRANT ALL ON SCHEMA pgagent to user1;
 GRANT ALL ON ALL TABLES IN SCHEMA pgagent to user1;
 GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO user1;
 GRANT ALL ON SCHEMA sys TO user1 ;
 GRANT ALL ON ALL TABLES IN SCHEMA sys TO user1;
 GRANT SELECT on sys.dba_scheduler_schedules to user1 ;
 

・「USER1」がテーブルとプログラムを所有しており、それを1分ごとに実行したいとします。

 
 edb=> create table conninfo(at_time timestamp,total_conn number(3));
  
     CREATE TABLE
  
  
 -- program
 CREATE OR REPLACE PROCEDURE public.get_conn_info ()
 IS
 DECLARE
 BEGIN
    EXECUTE IMMEDIATE 'insert into public.conninfo select now(),count(*) from pg_stat_activity ;';
 END;
 

・「USER1」のジョブ/プログラム/スケジュールを作成してみましょう。

 
 -- プログラム名を作成
 EXEC DBMS_SCHEDULER.CREATE_PROGRAM('program_logconn',
                                     'STORED_PROCEDURE',
                                     '"public"."get_conn_info"',
                                     0,
                                     true,
                                     'Program that runs every minute to insert number of users in db');
  
  
  
 -- 有効化
 EXEC DBMS_SCHEDULER.enable (name => 'program_logconn');
  
  
  
 -- スケジュールを作成
 EXEC DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name    => 'every_minute_user1',  
                                     start_date       => now, 
                                     repeat_interval  => 'FREQ=MINUTELY;',  
                                     comments         => 'This schedule executes every minute');
  
  
  
 -- ジョブを作成
 EXEC DBMS_SCHEDULER.CREATE_JOB (job_name       => 'job_logconninfo',
                                 program_name   => 'program_logconn',
                                 schedule_name  => 'every_minute_user1',
                                 enabled        => true,
                                 comments       => 'Job runs every minute to store users in db into a table');
 

・最後のステップとして、OSレベルでpgagentデーモンを実行し、「USER1」のジョブスケジュールを実行します。

 
 # su - user1 -c '/usr/edb/as12/bin/pgagent hostaddr=127.0.0.1 dbname=edb user=user1 port=5444 -l 2 -s /tmp/pgagent.log  &'
 

これで、「USER1」のジョブは、OSレベルで起動された独自のデーモン・プロセスによって実行されるようになります。これで非スーパーユーザーによるセキュアなジョブ・スケジューリング方法を実現することができます。

(以上、EDB社ブログ記事より翻訳)


まとめ

DBMS_SCHEDULER によるジョブのスケジュール方法および非スーパーユーザーによるセキュアなジョブのスケジュール方法をご紹介しました。これによりサードパーティ製のスケジューラーに頼ることなく、DBのネイティブな機能だけで、非スーパーユーザーによるジョブをセキュアにスケジュールすることができます。

もし、タスクのスケジュール実行の実装方法をお悩みでしたら、DBMS_SCHEDULER の利用をご検討されてみてはいかがでしょうか。


執筆者情報

やすだこうき プロフィール画像

2017年に中途入社。Oracle Database、EDB Postgres/PostgreSQL のサポート経験を経て、2020年からバックサポートを担当。DBとアプリケーションを繋ぐミドルウェア製品のスペシャリスト。トレンドな技術は積極的に触れるほど好奇心旺盛。最近はプロアクティブなサポートを目指して粉骨砕身。趣味はボードゲーム。...show more


■本記事の内容について
 本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。

■商標に関して
 ・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
 ・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
  文中の社名、商品名等は各社の商標または登録商標である場合があります。

関連している記事

  • EDB
  • PostgreSQL
2024.01.16

EDBがもたらすデータベースの新たな価値 ~ EDB社Field CTO Ajit Gadge氏来日、セミナー講演レポート ~

EDB社のAjit Gadge氏を招き「PostgreSQLユーザーに捧ぐ、EDBを使ったDB機能向上とコスト削減の両立」セミナーを開催しました。DB市場の現状やトレンド、EDBの最新動向について紹介しております。アシストセッションのアーカイブ配信の視聴申し込みも可能です。ぜひご覧ください。

  • PostgreSQL
  • EDB
2023.12.20

PostgreSQLのSQLチューニングを体験してみよう!

35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載最終回となる9回目の記事では、「PostgreSQL SQLチューニング実践」のワークショップ主管である 田中 健一朗 にインタビューしました。

  • PostgreSQL
  • EDB
2023.10.30

データベースの健康診断! ~ PostgreSQL DB稼働分析体験 ~

35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載8回目となる今回の記事では、PostgreSQL DB稼働分析ワークショップの主管である保田 公貴にインタービューしました。

ページの先頭へ戻る