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とアプリケーションを繋ぐミドルウェア製品のスペシャリスト。トレンドな技術は積極的に触れるほど好奇心旺盛。最近はプロアクティブなサポートを目指して粉骨砕身。趣味はボードゲーム。

アシスト データベース ウェビナー


■商標に関して
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。

関連している記事

  • EDB
  • PostgreSQL
2021.09.09

WordPress のデータベースをPostgreSQLに変更する方法

WordPressはデータベース管理システムとしてMySQLを使用していますが、PostgreSQLを好むユーザーからは「MySQLではなくPostgreSQLでWordPressを使用する方法はありませんか?」といった声をよく聞きます。そこで今回はPostgreSQLでWordPress をデプロイする方法をご紹介いたします。

  • EDB
2021.07.02

PostgreSQL/EPAS13新機能:パラレルVacuumは高速に処理できるのか?

PostgreSQL管理者を悩ませる「肥大化」は可能な限り迅速にバキューム処理で対応する必要があります。PostgreSQL13では、単一テーブルに作成された複数のインデックスのバキューム並列を実行可能にする Parallel Vacuum機能が導入されました。本記事では検証結果をご紹介します。

  • EDB
  • PostgreSQL
2021.07.01

Postgres Vision 2021 に登壇しました!

毎年恒例のEnterpriseDB社主催イベント「Postgres Vision」が2021年6月22日・23日にバーチャル形式で開催。「アジアにおけるPostgresの採用と利用に関するパネルディスカッション」に日本PostgreSQLユーザ会(JPUG)理事長も務めるアシストの喜田紘介が登壇しました。

ページの先頭へ戻る