Simple validation for tuple modification (insert/update/delete) is by applying constraints. More complex validation can be forced through trigger which is considered expensive by database experts.

The more efficient approach is by implementing validation in user defined function. Superuser prevents regular users from issuing INSERT/UPDATE/DELETE directly to table by revoking their privileges. They must invoke provided function to perform those tasks.

But, if you are the function programmer or superuser, the biggest enemy is yourself. You can easily bypass the function, modify table directly and break validation rule that you have set.

I have written additional functionality in PostgreSQL version 12.1 version backend to prevent regular user and even superuser from modifying directly table created with “private_modify” option. He or she should call SQL, PLPGSQL or other SPI-based function to do that.

CREATE TABLE public.regular (id integer NOT NULL, label text NOT NULL);
CREATE TABLE public.test (id integer NOT NULL, label text NOT NULL)
  WITH(private_modify=true);

Check table options in system table:

SELECT n.nspname, relname, reloptions
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname in('regular','test') AND n.nspname='public';

nspname | relname | reloptions
---------+---------+-----------------------
public | regular |
public | test | {private_modify=true}

Insert into public.regular directly and it works as usual.

INSERT INTO public.regular VALUES (1, 'abc');

Now, insert into public.test directly and you will have error message.

INSERT INTO public.test VALUES (1, 'abc');
ERROR: do not modify table with "private modify" option outside SQL, PLPGSQL or other SPI-based function

Anonymous block does not work too

DO $$
BEGIN
    INSERT INTO public.test VALUES (1, 'abc');
END$$;
ERROR: do not modify table with "private modify" option outside SQL, PLPGSQL or other SPI-based function

Update or delete will have the same error message.

So let us create function in SQL language and PLPGSQL language:

CREATE OR REPLACE FUNCTION public.testinsert_sql(i_id integer, t_label text)
RETURNS void AS
$BODY$
    /*do necessary validation*/
    INSERT INTO public.test (id, "label") VALUES ($1, $2);
$BODY$
LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.testinsert_plpgsql(i_id integer, t_label text)
RETURNS void AS
$BODY$
BEGIN
    /*do necessary validation*/
    INSERT INTO public.test (id, "label") VALUES ($1, $2);
RETURN;
END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

And use those functions for table modifications without error:

SELECT public.testinsert_sql(1, 'abc');
SELECT public.testinsert_plpgsql(2, 'def');

Check the result:

SELECT * FROM public.test;
id | label
----+-------
1 | abc
2 | def

But, to not interrupt replication agents which set session_replication_role to ‘replica’ prior to table modifications (e.g. Slony and Bucardo), such restrictions should be relaxed:

SET session_replication_role TO 'replica';
INSERT INTO public.test VALUES (3, 'ghi');

How to apply patch and build PostgreSQL

  1. Clone or download patch from https://github.com/AbdulYadi/postgresql-private. Extract as necessary.
  2. Download PostgreSQL version 12.1 from https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.bz2 or https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.gz. Extract to your preferred directory.
  3. Apply patch: change directory to the extracted PostgreSQL source code root and run patch -p0 < path-to-downloaded-patch-file.
  4. Build as instructed in PostgreSQL’s INSTALL file.

Warning

This patch is still an experiment so do not put into production server.

Example

A zoo can only breed maximum of 5 elephants (‘e’) and 3 tigers (‘t’).

CREATE TABLE public.zoo
(
    id serial NOT NULL,
    animal char NOT NULL,
    "name" text NOT NULL,
    CONSTRAINT zoo_pkey PRIMARY KEY (id),
    CONSTRAINT animal_kind CHECK (animal IN ('e','t'))
)
WITH (private_modify=true);

Animal kind can be simply validated by CHECK constraint. Other then ‘e’ or ‘t’ will be rejected. How about the maximum number constrained without additional book-keeping table. Option “private_modify” come to the rescue. Regular user and superuser can no longer directly INSERT/UPDATE/DELETE to public.zoo table. Following user defined function is needed:

CREATE OR REPLACE FUNCTION public.zoo_insert(c_animal char, t_name text)
  RETURNS void AS
$BODY$
DECLARE
    _count integer;
BEGIN
    PERFORM * FROM public.zoo WHERE animal = c_animal FOR UPDATE;--prevent race condition
    SELECT COUNT(*) INTO _count FROM public.zoo WHERE animal = c_animal;
    IF c_animal = 'e' AND _count >= 5 THEN
        RAISE EXCEPTION '5 elephants maximum';
    ELSIF c_animal = 't' AND _count >= 3 THEN
        RAISE EXCEPTION '3 tigers maximum';
    END IF;
    INSERT INTO public.zoo (animal, "name") VALUES (c_animal, t_name);
    RETURN;
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.zoo_insert(char, text) TO public;

Run following command one row at a time to add elephant. Notice that “ERROR: 5 elephants maximum” reported on sixth row:

SELECT public.zoo_insert('e', 'el-1');
SELECT public.zoo_insert('e', 'el-2');
SELECT public.zoo_insert('e', 'el-3');
SELECT public.zoo_insert('e', 'el-4');
SELECT public.zoo_insert('e', 'el-5');
SELECT public.zoo_insert('e', 'el-6');/*failing row*/

The same thing for tiger. Fourth row will fail with “ERROR: 3 tigers maximum”:

SELECT public.zoo_insert('t', 'ti-1');
SELECT public.zoo_insert('t', 'ti-2');
SELECT public.zoo_insert('t', 'ti-3');
SELECT public.zoo_insert('t', 'ti-4');/*failing row*/

Why do I still need pgAdmin3? As of now, pgAdmin4 does not show nested partition table in its object tree view. Since pgAdmin3 LTS repository in bitbucket by BigSQL Development Team is no longer available, I republish it in github: https://github.com/AbdulYadi/pgadmin3 with code fixes for PostgreSQL 12 internal relation field changes:

  1. No more relhasoids in pg_class.
  2. No more cache_value, is_cycled, is_called in sequence object (since PostgreSQL 11).
  3. No more adsrc in pg_attrdef, it should be calculated as pg_catalog.pg_get_expr(adbin, adrelid) instead.

Related with my post: https://abdulyadi.wordpress.com/2015/11/14/extension-for-qr-code-bitmap/. I have repackage the module and available on github: https://github.com/AbdulYadi/pgqr.

This project adds 2 functionality to QR code generator from repository https://github.com/swex/QR-Image-embedded:

  1. In-memory monochrome bitmap construction (1 bit per pixel).
  2. Wrap the whole package as PostgreSQL extension.

This project has been compiled successfully in Linux against PostgreSQL version 11.
$ make clean
$ make
$ make install

On successful compilation, install this extension in PostgreSQL environment
$ create extension pgqr

Function pgqr has 4 parameters:

  1. t text: text to be encoded.
  2. correction_level integer: 0 to 3.
  3. model_number integer: 0 to 2.
  4. scale integer: pixels for each dot.

Let us create a QR Code
$ select pgqr(‘QR Code with PostgreSQL’, 0, 0, 4);
The output is a monochrome bitmap ready for display.

image

 

 

What is my favourite PostgreSQL GUI-admin tool? pgAdmin3. I love its light weight user interface and simple navigation. Thanks to BigSQL Development Team for surviving the tool from freeze.

With PostgreSQL release 11.0, here is my patch file corresponding catalog table changes: pgadmin3-patch-text-file

First, clone pgAdmin3 project: clone git clone https://bitbucket.org/openscg/pgadmin3-lts.git

Then, apply the patch: patch -p0 -i [patch-text-file]

Oldies but goldies.

pgsocket is an extension for PostgreSQL server to send bytes to remote TCP/IP socket server. For the first version only single function provided for one way data send in bytearray.

This extension is compiled in Linux against PostgreSQL version 10.

Download source code from https://github.com/AbdulYadi/pgsocket. Build in Linux as usual:
$ make clean
$ make
$ make install

On successful compilation, install this extension in PostgreSQL environment
$ create extension pgsocket

Let us send bytes to –for example– host with IP address nnn.nnn.nnn.nnn, port 9090, send time out 30 seconds, messages “Hello”
$ select pgsocketsend(‘nnn.nnn.nnn.nnn’, 9090, 30, (E’\\x’ || encode(‘Hello’, ‘hex’))::bytea);

Or using address host name instead of IP address
$ select pgsocketsend(‘thesocketserver’, 9090, 30, (E’\\x’ || encode(‘Hello’, ‘hex’))::bytea);

Now, sending text from a table to remote TCP/IP socket server is easy. Assuming there is a table words:
id    txt
——————–
1    Life is easy
2    with PostgreSQL
just do:
$ select pgsocketsend(‘thesocketserver’, 9090, 30, (E’\\x’ || encode(t.txt, ‘hex’))::bytea)
from words t WHERE t.id = 1;

Upsert feature in PostgreSQL drove me to shorten SQL statement in my user defined functions. But somehow I overlook PostgreSQL version 10 documentation explaining trigger behavior:

“…If an INSERT contains an ON CONFLICT DO UPDATE clause, it is possible that the effects of row-level BEFORE INSERT triggers and row-level BEFORE UPDATE triggers can both be applied in a way that is apparent from the final state of the updated row, if an EXCLUDED column is referenced…”

For example, I have 2 tables:

CREATE TABLE public.sample
(
      id integer NOT NULL,
      code text NOT NULL,
      CONSTRAINT “sample: id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE public.audit
(
      id integer NOT NULL,
      codechanges integer NOT NULL DEFAULT 0,
      CONSTRAINT “audit: id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

Any record inserted into public.sample will be inserted into public.audit with codechanges 0. Any code changes to existing record in public.sample will increment codechanges in public.audit. For any reason, I implement the behavior in trigger:

CREATE OR REPLACE FUNCTION public.trig_sample() RETURNS trigger AS
$BODY$
BEGIN
      IF TG_WHEN=’BEFORE’ THEN
            IF TG_OP=’INSERT’ THEN
                  INSERT INTO audit (id) VALUES (NEW.id);
            ELSIF TG_OP=’UPDATE’ AND OLD.code IS DISTINCT FROM NEW.code THEN
                  UPDATE audit SET codechanges=codechanges+1 WHERE id = OLD.id;
            END IF;
      END IF;
      RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_sample_before BEFORE INSERT OR UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE public.trig_sample();

And a user defined function to encapsulate the complexity:

Old Style Update/Insert

CREATE OR REPLACE FUNCTION public.sample_update(IN i_id integer, IN t_code text)
RETURNS void AS
$BODY$
BEGIN
      UPDATE public.sample SET code = t_code WHERE id = i_id;
      IF NOT FOUND THEN
            INSERT INTO public.sample (id, code) VALUES (i_id, t_code);
      END IF;
      RETURN;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.sample_update(integer, text) TO public;

Let us give it a try:
SELECT public.sample_update(1, ‘TEST-1’);
SELECT * FROM public.sample;
      id      code
      1      “TEST-1”
SELECT * FROM public.audit;
      id      codechanges
      1      0

SELECT public.sample_update(1, ‘TEST-2’);
SELECT * FROM public.sample;
      id      code
      1      “TEST-2”
SELECT * FROM public.audit;
      id      codechanges
      1      1

Go Shorter with Upsert Version

Now, I modify user defined function with Upsert feature:

CREATE OR REPLACE FUNCTION public.sample_update(IN i_id integer, IN t_code text) RETURNS void AS
$BODY$
      INSERT INTO public.sample (id, code) VALUES ($1, $2)
      ON CONFLICT (id) DO UPDATE SET code = EXCLUDED.code;
$BODY$ LANGUAGE sql VOLATILE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.sample_update(integer, text) TO public;

And repeat the test:
SELECT public.sample_update(1, ‘TEST-3’);

But PostgreSQL complains:

ERROR: duplicate key value violates unique constraint “audit: id must be unique”
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement “INSERT INTO audit (id) VALUES (NEW.id)”
PL/pgSQL function trig_sample() line 5 at SQL statement
SQL function “sample_update” statement 1

I realize that I overlook the Overview of Trigger Behavior clause in PostgreSQL documentation. So, I modify my trigger, move the logic from BEFORE to AFTER:

CREATE OR REPLACE FUNCTION public.trig_sample() RETURNS trigger AS
$BODY$
BEGIN
      /*IF TG_WHEN=’BEFORE’ THEN*/
      IF TG_WHEN=’AFTER’ THEN
            IF TG_OP=’INSERT’ THEN
                  INSERT INTO audit (id) VALUES (NEW.id);
            ELSIF TG_OP=’UPDATE’ AND OLD.code IS DISTINCT FROM NEW.code THEN
                  UPDATE audit SET codechanges=codechanges+1 WHERE id = OLD.id;
            END IF;
      END IF;
      RETURN NEW;
END$BODY$ LANGUAGE plpgsql VOLATILE;

DROP TRIGGER trig_sample_before ON public.sample;

CREATE TRIGGER trig_sample_after AFTER INSERT OR UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE public.trig_sample();

Try the test once more:
SELECT public.sample_update(1, ‘TEST-3’);

And no more complains:
SELECT * FROM public.sample;
      id      code
      1      “TEST-3”
SELECT * FROM public.audit;
      id      codechanges
      1      2

A lesson learned.

For those who interested in cash spelling in Indonesian, here I share PLPGSQL code:

Usage:
SELECT cash_spell(1200000000.45, true);
'Satu Milyar Dua Ratus Juta Rupiah Empat Puluh Lima Sen'

Code:
CREATE OR REPLACE FUNCTION int_spell(i_val integer)
RETURNS text LANGUAGE 'plpgsql' IMMUTABLE STRICT
AS $BODY$
DECLARE
   _t text:=''::text;
   _SYMBOL text[]:=ARRAY['satu', 'dua', 'tiga', 'empat', 'lima', 'enam', 'tujuh', 'delapan', 'sembilan', 'sepuluh'
, 'sebelas', 'dua belas', 'tiga belas', 'empat belas', 'lima belas', 'enam belas', 'tujuh belas'
, 'delapan belas', 'sembilan belas'];
   _val integer:=i_val;
   _i integer;
BEGIN
   IF _val<1 OR _val>999 THEN
      RAISE EXCEPTION 'outside boundaries';
   END IF;

   _i:=(_val - (_val % 100)) / 100;
   IF _i>0 THEN
      IF _i>9 THEN
         RAISE EXCEPTION 'hundreds failed';
      END IF;
      IF _i = 1 THEN
         _t:='seratus';
      ELSE
         _t:=_SYMBOL[_i] || ' ratus';
      END IF;
      _val:=_val - (_i*100);
   END IF;

   IF _val>0 AND _val<20 THEN
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      _t:=_t || _SYMBOL[_val];
      _val:=0;
   END IF;

   _i:=(_val - (_val % 10)) / 10;
   IF _i>0 THEN
      IF _i>9 THEN
         RAISE EXCEPTION 'tenth failed';
      END IF;
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      IF _i = 1 THEN
         _t:= _t || 'sepuluh';
      ELSE
         _t:= _t || _SYMBOL[_i] || ' puluh';
      END IF;
      _val:=_val - (_i*10);
   END IF;

   IF _val>0 THEN
      IF LENGTH(_t) > 0 THEN
         _t:= _t || ' ';
      END IF;
      _t:=_t || _SYMBOL[_val];
   END IF;

   RETURN _t;
END;
$BODY$;
GRANT EXECUTE ON FUNCTION int_spell(integer) TO public;

CREATE OR REPLACE FUNCTION cash_spell(n numeric, b_initcap boolean)
RETURNS text LANGUAGE 'plpgsql' IMMUTABLE STRICT
AS $BODY$
DECLARE
   _t text:=''::text;
   _n numeric:=n;
   _frac numeric;
   _cent integer;
   _val integer;
   _suffix text[];
   _s text;
   _unit numeric;
BEGIN
   IF _n > 9999999999999999999999999999999999::numeric THEN
      RAISE EXCEPTION 'outside boundaries';
   END IF;

   IF _n < 0::numeric THEN
      RAISE EXCEPTION 'negative value';
   END IF;

   IF _n = 0::numeric THEN
      _t:='nol rupiah';
      IF b_initcap THEN
         _t:=initcap(_t);
      END IF;
      RETURN _t;
   END IF;

   _frac:= _n - floor(_n);
   _cent:= floor(_frac*100)::integer;

   _n:= _n - _frac; --remove fraction

   _suffix:=ARRAY['desiliun', 'noniliun', 'oktiliun', 'septiliun', 'sekstiliun', 'kuantiliun', 'kuadriliun',
'triliun', 'milyar', 'juta', 'ribu'];
   _unit:=1000000000000000000000000000000000::numeric;
   FOREACH _s IN ARRAY _suffix LOOP
      _val:=((_n - (_n % _unit)) / _unit)::integer;
      IF _val > 0 THEN
         IF LENGTH(_t)>0 THEN
            _t:=_t || ' ';
         END IF;
         IF _s = 'ribu' AND _val=1 THEN
            _t:= _t || 'seribu';
         ELSE
            _t:=_t || int_spell(_val) || ' ' || _s;
         END IF;
         _n:=_n - (_val * _unit);
      END IF;
      _unit:=_unit/1000;
   END LOOP;

   IF _n>0 THEN
      IF LENGTH(_t)>0 THEN
         _t:=_t || ' ';
      END IF;
      _t:=_t || int_spell(_n::integer);
   END IF;

   IF _cent>0 AND LENGTH(_t)=0 THEN
      _t:='nol';
   END IF;

   _t:=_t || ' rupiah';

   IF _cent>0 THEN
      _t:=_t || ' ';
      _t:=_t || int_spell(_cent) || ' sen';
   END IF;

   IF b_initcap THEN
      _t:=initcap(_t);
   END IF;

   RETURN _t;
END;
$BODY$;
GRANT EXECUTE ON FUNCTION cash_spell(numeric, boolean) TO public;