MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Logging Table Function

with one comment

It is interesting when somebody remembers a presentation from 10 years ago. They asked if it was possible in PL/pgSQL to write an autonomous procedure to log data when calling a table view function. The answer is two fold. PL/pgSQL doesn’t support autonomous functions or procedures like the Oracle database but it doesn’t need to because unless you invoke a transaction it auto commits writes.

Logging table functions are important for security auditing and compliance management against laws, like SOX, HIPAA, and FERPA. All too many systems lack the basic ability to audit who queries records without raising an error and blocking the access. That means the bad actor or actress gains the ability to probe the system for weaknesses before determining an attack vector. It’s often better to capture the unauthorized access and take direct action to protect both the the data and systems.

While the example lets an unauthorized person access the information in the first version of the student_query, it blocks access by reporting no rows returned in the latter. Both versions of the query log the data and thereby collect the evidence necessary to act against the hack.

This blog post shows you how to write it and test it. Follow the following steps:

  1. Create the necessary tables and data to work with a logging PL/pgSQL table view function: 

    /* Conditionally drop and create table. */
DROP TABLE IF EXISTS student;
CREATE TABLE student
( student_id      SERIAL
, first_name      VARCHAR(20)
, last_name       VARCHAR(20)
, hogwarts_house  VARCHAR(10));
 
/* Conditionally drop and create table. */
DROP TABLE IF EXISTS logger;
CREATE TABLE logger
( logger_id        SERIAL
, app_user         VARCHAR(30)
, queried_student  VARCHAR(30)
, query_time       TIMESTAMP );
 
/* Insert one record into table. */
INSERT INTO student
( first_name, last_name, hogwarts_house )
VALUES
 ( 'Harry', 'Potter', 'Gryffindor' )
,( 'Hermione', 'Granger', 'Gryffindor' )
,( 'Ronald', 'Weasily', 'Gryffindor' )
,( 'Draco', 'Malfoy', 'Slytherin' )
,( 'Vincent', 'Crabbe', 'Slytherin' )
,( 'Susan', 'Bones', 'Hufflepuff' )
,( 'Hannah', 'Abbott', 'Hufflepuff' )
,( 'Luna', 'Lovegood', 'Ravenclaw' )
,( 'Cho', 'Chang', 'Ravenclaw' )
,( 'Gilderoy', 'Lockhart', 'Ravenclaw' );
  2. While not necessary if you’re very familiar with PL/pgSQL, it may be helpful to review:

    • The SET command that lets you assign a value to a session-level variable, which you can later use in a PL/pgSQL block.
    • The SELECT-INTO statement in a DO-block.

    Here’s a test script that demonstrates both:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

    /* Set a session-level variable. */
SET credential.app_user = 'Draco Malfoy';
 
/* Secure the value from a session-level variable. */
SELECT current_setting('credential.app_user');
 
/* 
DO
$$
DECLARE
  input   VARCHAR(30) := 'Hermione';
  output  VARCHAR(30);
BEGIN
  /* Sample for partial name construction of full name. */
  SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name
  INTO   output
  FROM   student s
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||input||'%';
 
  /* Show result of local assignment via a query. */
  RAISE NOTICE '[%][%]', current_setting('credential.app_user'), output;
END;
$$;

    There’s an important parsing trick to this sample program. It uses the LIKE operator rather than the SIMILAR TO operator because the parser fails to recognize the SIMILAR TO operator.

    The DO-block returns the following output:

    NOTICE:  [Draco Malfoy][Hermione Granger]
  3. This creates the student_query logging table function, which takes a partial portion of a students first and last name to return the student information. While the example only returns the name and the Hogwarts House it lays a foundation for a more complete solution. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

    CREATE OR REPLACE
  FUNCTION student_query (partial_name  VARCHAR)
  RETURNS TABLE ( first_naem      VARCHAR(20)
                , last_name       VARCHAR(20)
                , hogwarts_house  VARCHAR(10) ) AS
$$
DECLARE
  queried   VARCHAR;
  by_whome  VARCHAR;
BEGIN
  /* Query separately because embedding in insert statement fails. */
  SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name
  FROM   student s INTO queried
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';
 
  /* Log the query with the credentials of the user. */  
  INSERT INTO logger
  ( app_user
  , queried_student
  , query_time )
  VALUES
  ( current_setting('credential.app_user')
  , queried
  , NOW());
 
  /* Return the result set without disclosing the query was recorded. */
  RETURN QUERY
  SELECT s.first_name
  ,      s.last_name
  ,      s.hogwarts_house
  FROM   student s
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';
END;
$$ LANGUAGE plpgsql;
  4. You can test the function by calling it, like this: 

    SELECT * FROM student_query('Hermione');

    It displays:

     first_naem | last_name | hogwarts_house
------------+-----------+----------------
 Hermione   | Granger   | Gryffindor
(1 row)

    You can check the logging table and discover who looked up another student’s records.

    SELECT * FROM logger;

    It displays:

     logger_id |   app_user   | queried_student  |         query_time
-----------+--------------+------------------+----------------------------
         1 | Draco Malfoy | Hermione Granger | 2022-05-29 22:51:50.398987
(1 row)
  5. Assuming you’ve built an authorized_user function that returns a Boolean, you can add a call to it in the WHERE clause. For simplicity, let’s implement the function to deny all users, like: 

    1
2
3
4
5
6
7
8
9
10

    CREATE OR REPLACE
  FUNCTION authorized_user
  (user_name  VARCHAR) RETURNS BOOLEAN AS
$$
DECLARE
  lv_retval  BOOLEAN := FALSE;
BEGIN
  RETURN lv_retval;
END;
$$  LANGUAGE plpgsql;

    You can now replace the query on lines 28 through 32 with the new one below. The added clause on line 33 denies access to unauthorized users because there aren’t any.

    28
29
30
31
32
33

      SELECT s.first_name
  ,      s.last_name
  ,      s.hogwarts_house
  FROM   student s
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'
  AND    authorized_user(current_setting('credential.app_user'));

    While it returns:

     first_naem | last_name | hogwarts_house
------------+-----------+----------------
(0 rows)

    The logger table shows two entries. One for the query that returned a value and one for the version that didn’t.

     logger_id |   app_user   | queried_student  |         query_time
-----------+--------------+------------------+----------------------------
         1 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:39.82063
         2 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:40.736945
(2 rows)

    In both cases the bad actor Draco Malfoy’s unauthorized access is captured and he was denied any information without alerting him to the security precaution in a logging table function.

As always, I hope this helps those looking for this type of solution.

Written by maclochlainn

May 29th, 2022 at 11:16 pm

Posted in Database,Database Design,DBA,DevOps,Linux,Microsoft Windows 10,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,Unix,Windows 10,Windows OS

«
»