Tip:: PPAS 9.4 and Global Temporary Table

Customers who moved/migrated their database from Oracle to PPAS frequently ask for Global Temporary Table in PPAS.

Currently, PPAS doesn’t support Global Temporary tables. However, there is a way user can achieve this functionality in PPAS.

Before we continue with the implementation, lets first understand characteristics of Global Temporary Table. Following are the important characteristics of Global Temporary Table.
1. Global Temporary Table gives predefined structure for storing data.
2. It’s an unlogged table which means any activity on this table will not be logged.
3. The data in a global temporary table are private, such that data inserted by a session can only be accessed by that session.

Based on the above characteristics of Global Temporary Table AKA GTT, we can define similar kind of work by using the following method:
1. Create UNLOGGED TABLE in PPAS, which activity won’t be logged.
2. Create Row Level Security in such a way that session should be able to see their information (based on PID).
3. Create a process which can cleanup data from GTT based on pids which are not active in database.

Lets see how we can implement it in Adavanced Server.

1. Create an UNLOGGED table with all columns required and extra column of Pid.

CREATE UNLOGGED TABLE test_global_temporary_table(id numeric, col text, pid bigint default pg_backend_pid());

2. Create a function to restrict the visibility of data.

CREATE OR REPLACE FUNCTION verify_pid_context (
    p_schema       TEXT,
    p_object       TEXT
)
RETURN VARCHAR2
IS
DECLARE
   predicate TEXT;
BEGIN
    IF ( current_setting('is_superuser') = 'on')
    THEN
      predicate = 'true';
    ELSE
      predicate := format('pid = %s',pg_backend_pid());
    END IF;
    RETURN predicate;
END;

3. Apply the security policy based on above function.

DECLARE
  v_object_schema VARCHAR2(30)   := 'public';
  v_object_name VARCHAR2(30)     := 'test_global_temporary_table';
  v_policy_name VARCHAR2(30)     := 'secure_by_pid';
  v_function_schema VARCHAR2(30) := 'public';
  v_policy_function VARCHAR2(30) := 'verify_pid_context';
  v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
  v_update_check BOOLEAN         := TRUE;
  v_enable BOOLEAN               := TRUE;
BEGIN
  DBMS_RLS.ADD_POLICY( v_object_schema,
                       v_object_name,
                       v_policy_name,
                       v_function_schema,
                       v_policy_function,
                       v_statement_types,
                       v_update_check,
                       v_enable
                     );
END;

4. Create UPDATABLE view which can hide pid column. All sessions will be using this view as GTT.

   CREATE OR REPLACE VIEW test_global_temporary AS SELECT id, col FROM test_global_temporary_table;

5. Create a backend job, which can cleanup Table based on stale/old sessions.
For job, user/developer can do following:
a. use superuser and execute DELETE command on table:
DELETE FROM test_global_temporary WHERE pid NOT in (SELECT pid FROM pg_stat_activity);
b. To Schedule above DELETE command, user can use one of the following:
i. Crontab
ii. Or PPAS DBMS_SCHEDULE Package.

6. GRANT ALL privileges to database user who can access Global Temporary Table.

    GRANT ALL on test_global_temporary TO testdbuser;
    GRANT ALL on test_global_temporary_table To testdbuser;

Now, lets try above implementation of Global Temporary Table.

Open two sessions as a normal user (testdbuser) as given below:

[vibhorkumar@localhost ~]$ psql -U testdbuser edb
psql.bin (9.4.4.9)
Type "help" for help.

edb=> 
edb=> select pg_backend_pid();
 pg_backend_pid 
----------------
          32722
(1 row)


edb=> select pg_backend_pid();
 pg_backend_pid 
----------------
          32729
(1 row)

Now from both session insert some records:
From first session:

edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32722');
INSERT 0 1

From Second session:

edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32729');
INSERT 0 1

From First Session:

edb=> SELECT * FROM test_global_temporary;
 id |      col       
----+----------------
  1 | FROM pid 32722
  2 | FROM pid 32722
  3 | FROM pid 32722
(3 rows)

From Second Session:

edb=> SELECT * FROm test_global_temporary;
 id |      col       
----+----------------
  1 | FROM pid 32729
  2 | FROM pid 32729
  3 | FROM pid 32729
(3 rows)

which shows that unlogged table with right RLS policy and backend job, can be a potential solution for Global Temporary Tables.

Virtual Private Database (VPD) in PPAS 9.1

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1

Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below:
1. Create an Application Context
2. Create security policies functions
3. Apply security policies to tables

Lets see how user can implement it in Advanced Server.
1. Setup an environment as given below:

 CREATE user merry identified by edb;
 CREATE user john identified by edb;
 CREATE TABLE public.john_merry(userid varchar2(200),val numeric);
 grant select,update,delete,insert on john_merry to john;
 grant select,update,delete,insert on john_merry to merry;

2. Now create a Policy Function as given below:

 CREATE OR REPLACE FUNCTION verify_user (
 p_schema VARCHAR2,
 p_object VARCHAR2
 )
 RETURN VARCHAR2
 IS
 BEGIN
 RETURN 'userid = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
 END;

3. Apply Security Policy using Policy Functions as given below:

 DECLARE
 v_object_schema VARCHAR2(30) := 'public';
 v_object_name VARCHAR2(30) := 'john_merry';
 v_policy_name VARCHAR2(30) := 'secure_data';
 v_function_schema VARCHAR2(30) := 'enterprisedb';
 v_policy_function VARCHAR2(30) := 'verify_user';
 v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
 v_update_check BOOLEAN := TRUE;
 v_enable BOOLEAN := TRUE;
 BEGIN
 DBMS_RLS.ADD_POLICY(
 v_object_schema,
 v_object_name,
 v_policy_name,
 v_function_schema,
 v_policy_function,
 v_statement_types,
 v_update_check,
 v_enable
 );
 END;

Now, lets see how it works.
First insert some records for John and Merry as given below:
1. Connect as John user and do some insert as john, as given below:

 edb=> insert into john_merry values('john',1);
 INSERT 0 1
 edb=> insert into john_merry values('john',2);
 INSERT 0 1
 edb=> insert into john_merry values('john',3);
 INSERT 0 1

2. Now connect as merry and insert some records as merry:

 edb=> insert into john_merry values('merry',1);
 INSERT 0 1
 edb=> insert into john_merry values('merry',2);
 INSERT 0 1
 edb=> insert into john_merry values('merry',3);
 INSERT 0 1

Ok. Now we have some data for both users. lets see how VPD works:
1. Connect as John User and try to insert some record for merry:

 edb=> insert into john_merry values('merry',4);
 ERROR: policy with check option violation
 DETAIL: Policy predicate was evaluated to FALSE with the updated values

hmm this is expected, VPD security policy won’t allow me to do this activity.

2. Now try to update Merry data as John User:

 edb=> update john_merry set val=4 where val=3 and userid='merry';
 UPDATE 0

No rows updated. This is expected since as per security policy merry’s data is not visible to john. However, john can update his record as given below:

 edb=> update john_merry set val=4 where val=3 and userid='john';
 UPDATE 1

3. Now, lets execute SELECT query on table to view data:

 edb=> select current_user;
 current_user
 --------------
 john
 (1 row)

edb=> select * from john_merry ;
 userid | val
 --------+-----
 john | 1
 john | 2
 john | 4
 (3 rows)

As you can see, john can see his records not merry’s. Similary user can try for Merry. Below is a snapshot of SELECT query executed by merry:
using dumb terminal settings.

 edb=> select current_user;
 current_user
 --------------
 merry
 (1 row)

edb=> select * from john_merry ;
 userid | val
 --------+-----
 merry | 1
 merry | 2
 merry | 3
 (3 rows)

Interesting!!