How to secure Job scheduling with DBMS_SCHEDULER in EDB Postgres Advanced Server

EDB Postgres Advanced Server (EPAS), a component of the EDB Postgres Platform, provides native database compatibility with Oracle. EPAS database compatibility provides similar Oracle-like features so developers and operations staff can continue to leverage many existing PL/SQL development and operations skills. One such feature supported in EPAS is an Oracle-style of jobs using the DBMS_SCHEDULER package. 

The DBMS_SCHEDULER package in EPAS is a partial implementation when compared to the Oracle version. The DBMS_SCHEDULER package is dependent on the pgAgent service to run the database scheduled jobs. pgAgent is a job scheduling agent in Postgres that allows users to run PL/SQL block or OS shell scripts periodically from the database. 

Conceptually, a Linux operating system daemon process(pgAgent) will be running by an OS user and it makes connections to the database periodically to check if there are any jobs to execute in the catalogs. 

 

Components to schedule jobs

 

• A unique job name - DBMS_SCHEDULER.CREATE_JOB
• A program("what" should be executed) - DBMS_SCHEDULER.CREATE_PROGRAM
• A schedule("when" it should run)  - DBMS_SCHEDULER.CREATE_SCHEDULE
• Monitoring catalog views 

 

Prerequisites

Following are the mandatory softwares installed to schedule a job in EPAS 12 on any Linux server.

• EPAS 12
• Extensions
  • pgAgent
  • Dbms_scheduler

The examples that follow assume that the user is on a Linux system that uses systemd for service management.

 

Installation

In this section, we will install the prerequisites mentioned in the documentation. 

• Install EPAS 12. Refer to the documentation
• Install edb-pgagent-12. Refer to the documentation
• Install 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)

 

Scheduling jobs as database Superuser

In this section, we see how to schedule jobs as a database superuser. 

• Start the pgAgent service as superuser

-- Start the pgagent

# systemctl start edb-pgagent-12.service

 

• First, create a sample SQL program to INSERT a row in a table. Assume we want to collect the total number of users in the database for every minute. First we need to create a table and then a program to collect stats and insert them into the table. 

-- Below steps should be performed as superuser

edb=# create table userinfo(at_time timestamp, total_users_in_db number(10));

CREATE TABLE



-- A program that will be called as per job/schedule



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;

 

• Now, we need to create job and program and schedule it to run every minute.  First, we create the program that will be run.

-- create program name and enable it.



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');

-- enable the program



EXEC DBMS_SCHEDULER.enable (name => 'program_logusers');



-- Check the program is created or not



edb=# SELECT owner, program_name, enabled FROM dba_scheduler_programs;

    owner     |   program_name   | enabled 

--------------+------------------+---------

 enterprisedb | program_logusers | t

(1 row)

 

• Next, create the schedule defining when it will be run

EXEC

   DBMS_SCHEDULER.CREATE_SCHEDULE (

                                    schedule_name    => 'every_minute',  

                                    start_date       => now, 

                                    repeat_interval  => 'FREQ=MINUTELY;',  

                                    comments         => 'This schedule executes every minute');



--check the schedule is created or not

edb=# SELECT owner, schedule_name FROM dba_scheduler_schedules;

    owner     | schedule_name 

--------------+---------------

 enterprisedb | every_minute

(1 row)

 

• Finally, you need to create the job that takes the program and schedule to run for every minute.

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');



-- check the job is created or not



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)

 

• Now, the job will be running every minute. The logs will have an entry for each job executed.

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

 

• You can now verify the status of the table, a new row will be added every minute. 

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)

 

Secure way of scheduling jobs as Non-Superuser

Most of the steps are the same as we did for database superuser, however, for database non-superuser scheduling jobs we need a pgagent daemon running at OS level for that user, so it won't conflict with superuser. 

Follow below steps for database non-superuser(“USER1” will be the name)  jobs scheduling: 

•  Create a user at OS level who will run the pgagent daemon process to run jobs for that user in Database.

# useradd user1

 

• Create database user with same name as OS user and give grants/privileges on "PGAGENT" & "SYS" schemas where pgagent and dbms_scheduler related functions/views/tables are resided, so database non-superuser can create his own Jobs/Program/Schedule.

    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 ;

 

• Assume, "USER1" owns a table/program and he wants it to execute for every minute.

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;

 

• Now, let's create job/program/schedule for "USER1"

-- create program name and enable it.

    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');



    -- Enable it

    EXEC DBMS_SCHEDULER.enable (name => 'program_logconn');



    -- schedule

    EXEC DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name    => 'every_minute_user1',  

                                        start_date       => now, 

                                        repeat_interval  => 'FREQ=MINUTELY;',  

                                        comments         => 'This schedule executes every minute');

    

    -- job



    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');

 

• Final step, now run pgagent daemon at OS level to run the jobs schedule for "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  &'

 

Now, "USER1" jobs are executed by his own daemon process started at OS level, this way you can achieve a secure way of job scheduling for database non-superuser.