Modelling a web CMS in Postgres ... a little advice needed

[Laura Smith]

Hi

I'm looking at using pgsql as a backend to a web CMS but could do with a little 
advice from the crowd on the wiseness of my schema thinking.

TL;DR the design is centered around two tables "pages" and "page_content", 
where "pages" has a jsonb column that refers to "page_content" in a key-value 
style (key for content block location ID on the web page, value for the 
database lookup).  Probably both, but certainly "page_content" would need to be 
versioned.

My present thinking is along the following lines (table columns minimised for 
this post):

create table pages (
  page_id uuid primary key not null,
  page_metadata jsonb not null
);

create table page_content(
  content_id uuid not null,
  content_version_id uuid not null
  content_valid tstzrange not null default tstzrange(now(),'infinity'),
  content_data text,
EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE 
INITIALLY DEFERRED
);
create unique index if not exists on page_content(content_version_id);
CREATE OR REPLACE VIEW current_content AS select * from page_content where 
content_valid @> now();


An example "page_metadata" entry might look something like :
{
"page":"foo",
"description":"bar",
"content":[
"pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
"pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
]
}


So I guess my questions here are :
Am i nuts with this thinking ? Is there a saner way to do this ? Should I be 
using pgsql at all for this, e.g. the cool kids will probably say I should be 
using a graph database ?  (N.B. I did consider a pure old-school relational 
model with no jsonb, but I considered it too difficult to model the dynamic 
nature of the fields, i.e. unknown many-many relationship between page content 
locations and content ... but I'm willing to be proven wrong by wiser minds)

Then, on a more technical level  what would an optimal query for looping 
through the json content array look like ?  I have always been pretty useless 
when it comes to CTE expressions !

Thanks all

Aw: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Karsten Hilbert]

I'll be happy to make a smaller example. It will, however, need to create 
users, a database, schemas, and some number of triggers. Because the triggers, 
their functions, and everything else about them follow a pattern, I can use 
"format()" and dynamic SQL to generate them. I'll still need those three 
"security definer" procedures to make the table changes that I explained. And 
the code to call these procedures to implement the test. So the result won't be 
exactly small. But, while I'm generating the triggers, I may just as well 
generate all eight. After all, how would I know which of the eight to skip 
while I don't know the intended rules for the current_role?

= You'd certainly start out with all eight but then whittle down to what still 
exhibits the problem and post that.
= Karsten
 

Fwd: Modelling a web CMS in Postgres ... a little advice needed

[Tony Shelver]

From: Tony Shelver 
Date: Thu, 11 Aug 2022 at 11:47
Subject: Re: Modelling a web CMS in Postgres ... a little advice needed
To: Laura Smith 



On Thu, 11 Aug 2022 at 09:35, Laura Smith <
n5d9xq3ti233xiyif...@protonmail.ch> wrote:

> Hi
>
> I'm looking at using pgsql as a backend to a web CMS but could do with a
> little advice from the crowd on the wiseness of my schema thinking.
>
> TL;DR the design is centered around two tables "pages" and "page_content",
> where "pages" has a jsonb column that refers to "page_content" in a
> key-value style (key for content block location ID on the web page, value
> for the database lookup).  Probably both, but certainly "page_content"
> would need to be versioned.
>
> My present thinking is along the following lines (table columns minimised
> for this post):
>
> create table pages (
>   page_id uuid primary key not null,
>   page_metadata jsonb not null
> );
>
> create table page_content(
>   content_id uuid not null,
>   content_version_id uuid not null
>   content_valid tstzrange not null default tstzrange(now(),'infinity'),
>   content_data text,
> EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE
> INITIALLY DEFERRED
> );
> create unique index if not exists on page_content(content_version_id);
> CREATE OR REPLACE VIEW current_content AS select * from page_content where
> content_valid @> now();
>
>
> An example "page_metadata" entry might look something like :
> {
> "page":"foo",
> "description":"bar",
> "content":[
> "pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
> "pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
> ]
> }
>
>
> So I guess my questions here are :
> Am i nuts with this thinking ? Is there a saner way to do this ? Should I
> be using pgsql at all for this, e.g. the cool kids will probably say I
> should be using a graph database ?  (N.B. I did consider a pure old-school
> relational model with no jsonb, but I considered it too difficult to model
> the dynamic nature of the fields, i.e. unknown many-many relationship
> between page content locations and content ... but I'm willing to be proven
> wrong by wiser minds)
>
> Then, on a more technical level  what would an optimal query for
> looping through the json content array look like ?  I have always been
> pretty useless when it comes to CTE expressions !
>
> Thanks all
>
>
> Off the top of my head, it seems like you are reinventing the wheel.

There are some very good CMS systems out there, such as Strapi, which is an
open source, headless CMS, and which can use Postgresql as the underlying
DB.

It could be worth a while to install this and see how it generates the
underlying pages. As a benefit, it also generates the UI to maintain the
underlying data, and also provides a set of APIs that you can call to
access the data from many different front-ends.

Tony

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

[Laura Smith]

Hi Tony

The reason I'm looking to do it from scratch is that its a case of "once 
bitten, twice shy".

This CMS will be replacing a Joomla based CMS.

I can't quite say I'm enamoured by the option of trading one "baggage included" 
opinionated CMS for another.  Also in this day and age, removing the excess 
baggage means an improved security footprint.

Laura
--- Original Message ---
On Thursday, August 11th, 2022 at 10:49, Tony Shelver  
wrote:


> From: Tony Shelver 
> Date: Thu, 11 Aug 2022 at 11:47
> Subject: Re: Modelling a web CMS in Postgres ... a little advice needed
> To: Laura Smith 
>
>
>
> On Thu, 11 Aug 2022 at 09:35, Laura Smith 
>  wrote:
>
> > Hi
> >
> > I'm looking at using pgsql as a backend to a web CMS but could do with a 
> > little advice from the crowd on the wiseness of my schema thinking.
> >
> > TL;DR the design is centered around two tables "pages" and "page_content", 
> > where "pages" has a jsonb column that refers to "page_content" in a 
> > key-value style (key for content block location ID on the web page, value 
> > for the database lookup). Probably both, but certainly "page_content" would 
> > need to be versioned.
> >
> > My present thinking is along the following lines (table columns minimised 
> > for this post):
> >
> > create table pages (
> > page_id uuid primary key not null,
> > page_metadata jsonb not null
> > );
> >
> > create table page_content(
> > content_id uuid not null,
> > content_version_id uuid not null
> > content_valid tstzrange not null default tstzrange(now(),'infinity'),
> > content_data text,
> > EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE 
> > INITIALLY DEFERRED
> > );
> > create unique index if not exists on page_content(content_version_id);
> > CREATE OR REPLACE VIEW current_content AS select * from page_content where 
> > content_valid @> now();
> >
> >
> > An example "page_metadata" entry might look something like :
> > {
> > "page":"foo",
> > "description":"bar",
> > "content":[
> > "pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
> > "pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
> > ]
> > }
> >
> >
> > So I guess my questions here are :
> > Am i nuts with this thinking ? Is there a saner way to do this ? Should I 
> > be using pgsql at all for this, e.g. the cool kids will probably say I 
> > should be using a graph database ? (N.B. I did consider a pure old-school 
> > relational model with no jsonb, but I considered it too difficult to model 
> > the dynamic nature of the fields, i.e. unknown many-many relationship 
> > between page content locations and content ... but I'm willing to be proven 
> > wrong by wiser minds)
> >
> > Then, on a more technical level  what would an optimal query for 
> > looping through the json content array look like ? I have always been 
> > pretty useless when it comes to CTE expressions !
> >
> > Thanks all
> >
>
> Off the top of my head, it seems like you are reinventing the wheel.
>
> There are some very good CMS systems out there, such as Strapi, which is an 
> open source, headless CMS, and which can use Postgresql as the underlying DB.
>
> It could be worth a while to install this and see how it generates the 
> underlying pages. As a benefit, it also generates the UI to maintain the 
> underlying data, and also provides a set of APIs that you can call to access 
> the data from many different front-ends.
>
> Tony

Strategy for preparing a query containg dynamic case / when

[Theofilos Theofovos]

Hello,

just for the sake of the example  lets have this kind of table

CREATE TABLE experimentals (
  indy integer not null primary KEY,
  XXX1 integer NOT NULL,
  XXX2 integer NOT NULL,
  json_data jsonb
);

It emulates a case where an item has integer labels XXX1, and XXX2
and characteristics described as json Z1, Z2 (can be any number of them,
e.g. Z3, Z4 ...)
each having a value in [0,1).

A synthetic population of the table can take the form

insert into experimentals
select indy, max(XXX1), max(XXX2), json_object_agg(zval, tval) as json_data
from (
select
 (RANDOM() * 3)::INT as XXX1,
 (RANDOM() * 5)::INT XXX2,
 unnest(ARRAY['Z1', 'Z2']) as zval,
 unnest(ARRAY[RANDOM(), RANDOM()]) as tval,
 unnest(ARRAY[seq, seq]) as indy
   FROM GENERATE_SERIES(1, 1000) seq
) exploded_jsons
group by indy;


Now, for each pair of labels we define a partitioning of the
characteristics, two possible appear here

-- XXX1 = 1, XXX2 = 2 partition (Z1, Z2) => (0..1 , 0..0.5) -> F1 (0..1,
0.5..1) -> F2

-- XXX1 = 3, XXX2 = 1 partition (Z1, Z2) => (0..0.3 , 0..0.5) -> G1
(0..0.6, 0.5..1) -> G2, ELSE  G3


In concrete SQL they take the form:


-- Partitioning 1

select  XXX1, XXX2, indy, json_data,
case
  when
 (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
  then
'F1'
  else
'F2'
end as some_label
from experimentals
where XXX1 = 1 and XXX2 = 2 ;

-- Partitioning 2
select  XXX1, XXX2, indy, json_data,
case
   when
 (json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.3 and
 (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
   then
 'G1'
   when
 (json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.6 and
 (json_data->>'Z2')::float >= 0.5 and (json_data->>'Z2')::float < 1
   then
 'G2'
   else
 'G3'
  end as some_label
from experimentals
where XXX1 = 3 and XXX2 = 1 ;


the partitioning is an immutable function of a column and is given to me at
runtime in some format

Now, to the real question for preparing it.

I would expect something (pseudo sql) for partitioning1

PREPARE fooplan (int, int, immutable (jsonb -> varchar) ) AS
select XXX1, XXX2, indy, json_data, $x3(json_data) as some_label
from experimentals
where XXX1 = $1 and XXX2 = $2 ;
EXECUTE fooplan(1, 2, 't', json_data ->
case
  when
 (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
  then
'F1'
  else
'F2'
end
);

Is this something possible, is there any recommended strategy for these cases?

PS The real queries are containing joins and other filters, I just
include a simplified case.

Vasilis




-- 
Dr. Vasileios Anagnostopoulos (MSc,PhD)
Researcher/Developer

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

[Laura Smith]

Sent with Proton Mail secure email.

--- Original Message ---
On Thursday, August 11th, 2022 at 13:11, Ray O'Donnell  
wrote:


> On 11/08/2022 11:00, Laura Smith wrote:
>
> > Hi Tony
> >
> > The reason I'm looking to do it from scratch is that its a case of
> > "once bitten, twice shy".
> >
> > This CMS will be replacing a Joomla based CMS.
> >
> > I can't quite say I'm enamoured by the option of trading one "baggage
> > included" opinionated CMS for another. Also in this day and age,
> > removing the excess baggage means an improved security footprint.
>
>
> I would second what Tony Shelver said - it sounds as if what you're
> trying to do has already been done, and for the most part done well. I'd
> recommend Drupal, which works well with PostgreSQL, and which is very
> extensible and customisable.
>
> I'd agree with you that removing baggage can mean improved security -
> on the other hand, having a huge, security-conscious community behind an
> open-source project gets you there too.
>
> Ray.
>

Tony's suggestion also needed Node.js, and frankly I'm not even going to open 
that can of worms.

At this point I'm not interested in "very extensible and customisable".  Joomla 
fills those words too, and my present installation is full of all sorts of 
third-party extensions ... the thought of all the security vulnerabilities 
scares the bejeezus out of me !

Hence I want to get rid of bells, whistles and baggage.  Forget the fancy GUI, 
forget the "community" of random plugins.

I just want a solid Postgres, which exposes to the outside world via a limited 
number of Pl/pgsql functions.  The frontend can then pull the data and render 
it into a template.

I know I probably sound nuts. But as I said, I've spent too long in the hell of 
Joomla. I don't want to replace it with another thing that tries to be all 
things to all people ... I just want to follow the KISS principle now.

Oddity that I don't understand

[Perry Smith]

I’m tempted to ask “Is this a bug” but I predict there is an explanation.

I have a view:
find_dups=# \sv+ dateien
1   CREATE OR REPLACE VIEW public.dateien AS
2SELECT d.id,
3   d.basename,
4   d.parent_id,
5   d.ino,
6   d.ext,
7   i.ftype,
8   i.uid,
9   i.gid,
10  i.mode,
11  i.mtime,
12  i.nlink,
13  i.size,
14  i.sha1,
15  i.file_type
16 FROM dirents d
17   FULL JOIN inodes i USING (ino)

find_dups=# \d inodes
Table "public.inodes"
   Column   |  Type  | Collation | Nullable | Default
++---+--+-
 ino| bigint |   | not null |
 ftype  | character varying  |   | not null |
 uid| bigint |   | not null |
 gid| bigint |   | not null |
 mode   | bigint |   | not null |
 mtime  | timestamp without time zone|   | not null |
 nlink  | bigint |   | not null |
 size   | bigint |   | not null |
 sha1   | character varying  |   |  |
 created_at | timestamp(6) without time zone |   | not null |
 updated_at | timestamp(6) without time zone |   | not null |
 file_type  | character varying  |   |  |
Indexes:
"inodes_pkey" PRIMARY KEY, btree (ino)
"index_inodes_on_ftype_and_size_and_file_type_and_sha1_and_nlink" btree 
(ftype, size, file_type, sha1, nlink)
Referenced by:
TABLE "dirents" CONSTRAINT "fk_rails_f076303053" FOREIGN KEY (ino) 
REFERENCES inodes(ino)

find_dups=# \d dirents
  Table "public.dirents"
   Column   |  Type  | Collation | Nullable |   
Default
++---+--+-
 id | bigint |   | not null | 
nextval('dirents_id_seq'::regclass)
 basename   | character varying  |   | not null |
 parent_id  | bigint |   |  |
 ino| bigint |   | not null |
 created_at | timestamp(6) without time zone |   | not null |
 updated_at | timestamp(6) without time zone |   | not null |
 ext| character varying  |   |  |
Indexes:
"dirents_pkey" PRIMARY KEY, btree (id)
"index_dirents_on_basename" btree (basename)
"index_dirents_on_ext" btree (ext)
"index_dirents_on_ino" btree (ino)
"index_dirents_on_parent_id_and_basename" UNIQUE, btree (parent_id, 
basename)
Foreign-key constraints:
"fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) REFERENCES dirents(id) ON 
DELETE CASCADE
"fk_rails_f076303053" FOREIGN KEY (ino) REFERENCES inodes(ino)
Referenced by:
TABLE "dirents" CONSTRAINT "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) 
REFERENCES dirents(id) ON DELETE CASCADE

 I do a select and I get:

find_dups=# select id, basename, ext, parent_id, ino, sha1 from dateien where 
sha1 = '36f53d60353e0de6ed55d9da70a36b17559039f3' order by parent_id;
id|   basename   | ext | parent_id |   ino|   sha1
--+--+-+---+--+--
 85276821 | VC5Y8191.CR2 | CR2 |  85273064 | 70163023 | 
36f53d60353e0de6ed55d9da70a36b17559039f3
 85829158 | VC5Y8191.CR2 | CR2 |  85827904 |79366 | 
36f53d60353e0de6ed55d9da70a36b17559039f3
  |  | |   |  | 
36f53d60353e0de6ed55d9da70a36b17559039f3
(3 rows)

How can the third line exist?  Or, perhaps I should ask, what is the third line 
telling me?

Thank you,
Perry





signature.asc
Description: Message signed with OpenPGP

Re: Oddity that I don't understand

[Perry Smith]

I see why…  The select of the view is picking d.ino which is null because there 
is no match in the dirents table.

Thanks guys!

> On Aug 11, 2022, at 08:23, Perry Smith  wrote:
> 
> I’m tempted to ask “Is this a bug” but I predict there is an explanation.
> 
> I have a view:
> find_dups=# \sv+ dateien
> 1   CREATE OR REPLACE VIEW public.dateien AS
> 2SELECT d.id ,
> 3   d.basename,
> 4   d.parent_id,
> 5   d.ino,
> 6   d.ext,
> 7   i.ftype,
> 8   i.uid,
> 9   i.gid,
> 10  i.mode,
> 11  i.mtime,
> 12  i.nlink,
> 13  i.size,
> 14  i.sha1,
> 15  i.file_type
> 16 FROM dirents d
> 17   FULL JOIN inodes i USING (ino)
> 
> find_dups=# \d inodes
> Table "public.inodes"
>Column   |  Type  | Collation | Nullable | Default
> ++---+--+-
>  ino| bigint |   | not null |
>  ftype  | character varying  |   | not null |
>  uid| bigint |   | not null |
>  gid| bigint |   | not null |
>  mode   | bigint |   | not null |
>  mtime  | timestamp without time zone|   | not null |
>  nlink  | bigint |   | not null |
>  size   | bigint |   | not null |
>  sha1   | character varying  |   |  |
>  created_at | timestamp(6) without time zone |   | not null |
>  updated_at | timestamp(6) without time zone |   | not null |
>  file_type  | character varying  |   |  |
> Indexes:
> "inodes_pkey" PRIMARY KEY, btree (ino)
> "index_inodes_on_ftype_and_size_and_file_type_and_sha1_and_nlink" btree 
> (ftype, size, file_type, sha1, nlink)
> Referenced by:
> TABLE "dirents" CONSTRAINT "fk_rails_f076303053" FOREIGN KEY (ino) 
> REFERENCES inodes(ino)
> 
> find_dups=# \d dirents
>   Table "public.dirents"
>Column   |  Type  | Collation | Nullable | 
>   Default
> ++---+--+-
>  id | bigint |   | not null | 
> nextval('dirents_id_seq'::regclass)
>  basename   | character varying  |   | not null |
>  parent_id  | bigint |   |  |
>  ino| bigint |   | not null |
>  created_at | timestamp(6) without time zone |   | not null |
>  updated_at | timestamp(6) without time zone |   | not null |
>  ext| character varying  |   |  |
> Indexes:
> "dirents_pkey" PRIMARY KEY, btree (id)
> "index_dirents_on_basename" btree (basename)
> "index_dirents_on_ext" btree (ext)
> "index_dirents_on_ino" btree (ino)
> "index_dirents_on_parent_id_and_basename" UNIQUE, btree (parent_id, 
> basename)
> Foreign-key constraints:
> "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) REFERENCES dirents(id) ON 
> DELETE CASCADE
> "fk_rails_f076303053" FOREIGN KEY (ino) REFERENCES inodes(ino)
> Referenced by:
> TABLE "dirents" CONSTRAINT "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) 
> REFERENCES dirents(id) ON DELETE CASCADE
> 
>  I do a select and I get:
> 
> find_dups=# select id, basename, ext, parent_id, ino, sha1 from dateien where 
> sha1 = '36f53d60353e0de6ed55d9da70a36b17559039f3' order by parent_id;
> id|   basename   | ext | parent_id |   ino|   sha1
> --+--+-+---+--+--
>  85276821 | VC5Y8191.CR2 | CR2 |  85273064 | 70163023 | 
> 36f53d60353e0de6ed55d9da70a36b17559039f3
>  85829158 | VC5Y8191.CR2 | CR2 |  85827904 |79366 | 
> 36f53d60353e0de6ed55d9da70a36b17559039f3
>   |  | |   |  | 
> 36f53d60353e0de6ed55d9da70a36b17559039f3
> (3 rows)
> 
> How can the third line exist?  Or, perhaps I should ask, what is the third 
> line telling me?
> 
> Thank you,
> Perry
> 
> 
> 



signature.asc
Description: Message signed with OpenPGP

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

[Tony Shelver]

On Thu, 11 Aug 2022 at 12:00, Laura Smith <
n5d9xq3ti233xiyif...@protonmail.ch> wrote:

> Hi Tony
>
> The reason I'm looking to do it from scratch is that its a case of "once
> bitten, twice shy".
>
> This CMS will be replacing a Joomla based CMS.
>
> I can't quite say I'm enamoured by the option of trading one "baggage
> included" opinionated CMS for another.  Also in this day and age, removing
> the excess baggage means an improved security footprint.
>
>
>
Laura, fair enough.

At the time i used a static site generator (SSG), which was Jekyll at the
time, to generate our website.
I could have used Joomla or Wordpress as the CMS, but these are terrible
solutions as you mentioned, as they are not designed as pure CMS engines,
especially for use with a static site generator.

I went with my own rolled CMS as well.

One part of my requirement was for a product sales database, which ended up
being a lot more complex than your design, as I needed categories, product
groups, products, prices by area and more.  I did this in Postgresql, and
pulled the data via a GraphQL API implemented via Postgraphile, which
generates a GraphQL API from a Postgres schema automagically.

The other part was for the static web pages.  What I came up with was to
store pages and then a set of tags where I could assign content to each tag
for the page.
For example, you talked about Page Headers and PageMainLeft.  I stored
these tagged with the name of the page, and the tag name, plus the content.

The SSG then saw the tags in the HTML page template, and pulled in the
content via Postgraphile.

At the time I looked at several 'headless' CMS engines, and Strapi came
close to meeting requirements, but was still in early development stages.

Today, I would look at a different route, likely leveraging something like
Strapi.  I *may* still consider using native Postgres tables to manage the
product database, but there are a lot of other proven CMS options on the
market now without the baggage of Joomla, Wordpress et al.
Node.js I regard as a necessary evil if you are developing relatively
complex websites, integrating multiple APIs and other resources.

Very much depends on your environment though.
For example, one huge advantage of the headless CMS options is that the
whole user management and access is built in if you have a large user base
maintaining different parts of the website content.  Another is things like
formal APIs to get content, and access management / security of the content
itself.

There isn't really a one-solution-fits-all-requirements option when it
comes to content management, unfortunately.

Regards

Re: Modelling a web CMS in Postgres ... a little advice needed

[Rob Sargent]

> On Aug 11, 2022, at 5:30 AM, Laura Smith  
> wrote:
> 
> 
> 
> 
> 
> 
> Sent with Proton Mail secure email.
> 
> --- Original Message ---
> On Thursday, August 11th, 2022 at 13:11, Ray O'Donnell  
> wrote:
> 
> 
>> On 11/08/2022 11:00, Laura Smith wrote:
>> 
>>> Hi Tony
>>> 
>>> The reason I'm looking to do it from scratch is that its a case of
>>> "once bitten, twice shy".
>>> 
>>> This CMS will be replacing a Joomla based CMS.
>>> 
>>> I can't quite say I'm enamoured by the option of trading one "baggage
>>> included" opinionated CMS for another. Also in this day and age,
>>> removing the excess baggage means an improved security footprint.
>> 
>> 
>> I would second what Tony Shelver said - it sounds as if what you're
>> trying to do has already been done, and for the most part done well. I'd
>> recommend Drupal, which works well with PostgreSQL, and which is very
>> extensible and customisable.
>> 
>> I'd agree with you that removing baggage can mean improved security -
>> on the other hand, having a huge, security-conscious community behind an
>> open-source project gets you there too.
>> 
>> Ray.
>> 
> 
> Tony's suggestion also needed Node.js, and frankly I'm not even going to open 
> that can of worms.
> 
> At this point I'm not interested in "very extensible and customisable".  
> Joomla fills those words too, and my present installation is full of all 
> sorts of third-party extensions ... the thought of all the security 
> vulnerabilities scares the bejeezus out of me !
> 
> Hence I want to get rid of bells, whistles and baggage.  Forget the fancy 
> GUI, forget the "community" of random plugins.
> 
> I just want a solid Postgres, which exposes to the outside world via a 
> limited number of Pl/pgsql functions.  The frontend can then pull the data 
> and render it into a template.
> 
> I know I probably sound nuts. But as I said, I've spent too long in the hell 
> of Joomla. I don't want to replace it with another thing that tries to be all 
> things to all people ... I just want to follow the KISS principle now.
> 
> 
I once worked a company which produce reference text for radiologist.  The 
content was generated from an in-house app which wrote a form of mark up xml 
and store in postgres (version 8,9 no json).  That was hard copy though (but 
beautiful).

I would like to better understand your concept of “page”.  I’m not following 
how you’re managing layout and how variable that is in your world.

I would suggest getting as many of the things like page name (foo) and 
description(bar) as possible out of the json. How many tags (eg pageHeader, 
pageMainLeft) do you have?  Do queries ask for pages with pageMainLeft having 
some string value within their content?   Will you index the content separately?

Pictures as content?

Is ODBC list still alive?

[Igor Korot]

Thank you.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn]

> karsten.hilb...@gmx.net wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> I'll be happy to make a smaller example. It will, however, need to create… 
>> After all, how would I know which of the eight to skip while I don't know 
>> the intended rules for the current_role?
> 
> You'd certainly start out with all eight but then whittle down to what still 
> exhibits the problem and post that.

Do you know where I can read a statement of the intended rules here? I 
appreciate that one is doomed who tries to deduce the rules that govern a 
software system's behavior by using just empirical testing. (And reading source 
code hoping to deduce the behavior that the programmer intended is hardly 
better.)

I used the subject "surprising results" to mean "Results that surprise me, 
Bryn". The results might well not surprise somebody who knows the rules. 
Several cases that I've asked about before on this list were surprising for me 
because I was too dim-witted to find where, in the PG docs, the rules were 
stated. And in those cases, I was delighted to be pointed to the appropriate 
doc and to receive some helpful instruction. That's what I'm hoping for here.

Notice that I didn't consider "for insert" or "for update" triggers. But you 
can contrive a cascade effect with these, too. For example, table "t1" might 
have a trigger that inserts or updates a row in table "t2" for a purpose like 
maintaining a change history. And "t2" might, in turn, have a trigger for 
who-knows-what purpose (maybe to enforce a write-once-read-many regime for the 
values in certain columns).

This is why I'd very much like to start by studying a clear statement of the 
intention in scenarios in the same general class as the one that I showed.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[David G. Johnston]

On Thu, Aug 11, 2022 at 9:28 AM Bryn Llewellyn  wrote:

>
> *karsten.hilb...@gmx.net  wrote:*
>
> *b...@yugabyte.com  wrote:*
>
> I'll be happy to make a smaller example. It will, however, need to create…
> After all, how would I know which of the eight to skip while I don't know
> the intended rules for the current_role?
>
>
> You'd certainly start out with all eight but then whittle down to what
> still exhibits the problem and post that.
>
>
> Do you know where I can read a statement of the intended rules here?
>

You are correct that the behavior here is not explicitly documented (at
least not in the two sections I would expect to find it in - the SQL
Reference and the general Triggers section).

My expectation is that the trigger owner is the context in which the
trigger function is executed.  Table owners can grant the ability to other
roles to create triggers on the owned tables should the trigger owner and
table owner require different roles.  Triggers are like views in this
regard.

I don't expect cascade update/cascade delete to be a factor here at all,
other than making the process easier to perform.  This extends from the
prior expectation.

I expect a security definer function's contents to be executed as the role
of the owner of that function.  Because that is what that clause means and
there isn't an obvious reason to ignore it here.

I expect those because that is how the system usually works, not because I
can point to a place where all that is said.  I would for sure expect
deviations to be mentioned, and would find explicit documentation to be
reasonable to add if someone pushes forward such a change.

David J.

Re: Postgres NOT IN vs NOT EXISTS optimization

[Bruce Momjian]

On Tue, Jun 14, 2022 at 12:09:16PM -0400, Tom Lane wrote:
> "Dirschel, Steve"  writes:
> > Is Postgres able to drive the query the same way with the NOT IN as the
> > NOT EXISTS is doing or is that only available if the query has a NOT
> > EXISTS?
> 
> NOT IN is not optimized very well in PG, because of the strange
> semantics that the SQL spec demands when the sub-query produces any
> null values.  There's been some interest in detecting cases where
> we can prove that the subquery produces no nulls and then optimizing
> it into NOT EXISTS, but it seems like a lot of work for not-great
> return, so nothing's happened (yet).  Perhaps Oracle does something
> like that already, or perhaps they're just ignoring the semantics
> problem; they do not have a reputation for hewing closely to the
> spec on behavior regarding nulls.

I was just now researching NOT IN behavior and remembered this thread,
so wanted to give a simplified example.  If you set up tables like this:

CREATE TABLE small AS
SELECT * FROM generate_series(1, 10) AS t(x);

CREATE TABLE large AS SELECT small.x
FROM small CROSS JOIN generate_series(1, 1000) AS t(x);

INSERT INTO small VALUES (11), (12);

ANALYZE small, large;

These IN and EXISTS/NOT EXISTS queries look fine. using hash joins:

EXPLAIN SELECT small.x
FROM small
WHERE small.x IN (SELECT large.x FROM large);
 QUERY PLAN

-
 Hash Join  (cost=170.22..171.49 rows=10 width=4)
   Hash Cond: (small.x = large.x)
   ->  Seq Scan on small  (cost=0.00..1.12 rows=12 width=4)
   ->  Hash  (cost=170.10..170.10 rows=10 width=4)
 ->  HashAggregate  (cost=170.00..170.10 rows=10 width=4)
   Group Key: large.x
   ->  Seq Scan on large  (cost=0.00..145.00 rows=1 
width=4)

EXPLAIN SELECT small.x
FROM small
WHERE EXISTS (SELECT large.x FROM large WHERE large.x = small.x);
 QUERY PLAN

-
 Hash Join  (cost=170.22..171.49 rows=10 width=4)
   Hash Cond: (small.x = large.x)
   ->  Seq Scan on small  (cost=0.00..1.12 rows=12 width=4)
   ->  Hash  (cost=170.10..170.10 rows=10 width=4)
 ->  HashAggregate  (cost=170.00..170.10 rows=10 width=4)
   Group Key: large.x
   ->  Seq Scan on large  (cost=0.00..145.00 rows=1 
width=4)

EXPLAIN SELECT small.x
FROM small
WHERE NOT EXISTS (SELECT large.x FROM large WHERE large.x = small.x);
  QUERY PLAN
---
 Hash Anti Join  (cost=270.00..271.20 rows=2 width=4)
   Hash Cond: (small.x = large.x)
   ->  Seq Scan on small  (cost=0.00..1.12 rows=12 width=4)
   ->  Hash  (cost=145.00..145.00 rows=1 width=4)
 ->  Seq Scan on large  (cost=0.00..145.00 rows=1 width=4)

These NOT IN queries all use sequential scans, and IS NOT NULL does not help:

EXPLAIN SELECT small.x
FROM small
WHERE small.x NOT IN (SELECT large.x FROM large);
QUERY PLAN
---
 Seq Scan on small  (cost=170.00..171.15 rows=6 width=4)
   Filter: (NOT (hashed SubPlan 1))
   SubPlan 1
 ->  Seq Scan on large  (cost=0.00..145.00 rows=1 width=4)

EXPLAIN SELECT small.x
FROM small
WHERE small.x NOT IN (SELECT large.x FROM large WHERE large.x IS NOT 
NULL);
QUERY PLAN
---
 Seq Scan on small  (cost=170.00..171.15 rows=6 width=4)
   Filter: (NOT (hashed SubPlan 1))
   SubPlan 1
 ->  Seq Scan on large  (cost=0.00..145.00 rows=1 width=4)
   Filter: (x IS NOT NULL)

Is converting NOT IN to NOT EXISTS our only option?  Couldn't we start
to create the hash and just switch to always returning NULL if we see
any NULLs while we are creating the hash?

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: Strategy for preparing a query containg dynamic case / when

[Theofilos Theofovos]

Hi subin,

can you clarify please?

On Thu, Aug 11, 2022 at 3:42 PM subin  wrote:

> Please let me know if that is okay.
>
> On Thu, Aug 11, 2022 at 11:11 AM Theofilos Theofovos 
> wrote:
>
>> Hello,
>>
>> just for the sake of the example  lets have this kind of table
>>
>> CREATE TABLE experimentals (
>>   indy integer not null primary KEY,
>>   XXX1 integer NOT NULL,
>>   XXX2 integer NOT NULL,
>>   json_data jsonb
>> );
>>
>> It emulates a case where an item has integer labels XXX1, and XXX2
>> and characteristics described as json Z1, Z2 (can be any number of them,
>> e.g. Z3, Z4 ...)
>> each having a value in [0,1).
>>
>> A synthetic population of the table can take the form
>>
>> insert into experimentals
>> select indy, max(XXX1), max(XXX2), json_object_agg(zval, tval) as
>> json_data
>> from (
>> select
>>  (RANDOM() * 3)::INT as XXX1,
>>  (RANDOM() * 5)::INT XXX2,
>>  unnest(ARRAY['Z1', 'Z2']) as zval,
>>  unnest(ARRAY[RANDOM(), RANDOM()]) as tval,
>>  unnest(ARRAY[seq, seq]) as indy
>>FROM GENERATE_SERIES(1, 1000) seq
>> ) exploded_jsons
>> group by indy;
>>
>>
>> Now, for each pair of labels we define a partitioning of the
>> characteristics, two possible appear here
>>
>> -- XXX1 = 1, XXX2 = 2 partition (Z1, Z2) => (0..1 , 0..0.5) -> F1 (0..1,
>> 0.5..1) -> F2
>>
>> -- XXX1 = 3, XXX2 = 1 partition (Z1, Z2) => (0..0.3 , 0..0.5) -> G1
>> (0..0.6, 0.5..1) -> G2, ELSE  G3
>>
>>
>> In concrete SQL they take the form:
>>
>>
>> -- Partitioning 1
>>
>> select  XXX1, XXX2, indy, json_data,
>> case
>>   when
>>  (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
>>   then
>> 'F1'
>>   else
>> 'F2'
>> end as some_label
>> from experimentals
>> where XXX1 = 1 and XXX2 = 2 ;
>>
>> -- Partitioning 2
>> select  XXX1, XXX2, indy, json_data,
>> case
>>when
>>  (json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.3
>> and
>>  (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
>>then
>>  'G1'
>>when
>>  (json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.6
>> and
>>  (json_data->>'Z2')::float >= 0.5 and (json_data->>'Z2')::float < 1
>>then
>>  'G2'
>>else
>>  'G3'
>>   end as some_label
>> from experimentals
>> where XXX1 = 3 and XXX2 = 1 ;
>>
>>
>> the partitioning is an immutable function of a column and is given to me
>> at runtime in some format
>>
>> Now, to the real question for preparing it.
>>
>> I would expect something (pseudo sql) for partitioning1
>>
>> PREPARE fooplan (int, int, immutable (jsonb -> varchar) ) AS
>> select XXX1, XXX2, indy, json_data, $x3(json_data) as some_label
>> from experimentals
>> where XXX1 = $1 and XXX2 = $2 ;
>> EXECUTE fooplan(1, 2, 't', json_data ->
>> case
>>   when
>>  (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
>>   then
>> 'F1'
>>   else
>> 'F2'
>> end
>> );
>>
>> Is this something possible, is there any recommended strategy for these 
>> cases?
>>
>> PS The real queries are containing joins and other filters, I just include a 
>> simplified case.
>>
>> Vasilis
>>
>>
>>
>>
>> --
>> Dr. Vasileios Anagnostopoulos (MSc,PhD)
>> Researcher/Developer
>>
>

-- 
Dr. Vasileios Anagnostopoulos (MSc,PhD)
Researcher/Developer

Re: Strategy for preparing a query containg dynamic case / when

[David G. Johnston]

On Thu, Aug 11, 2022 at 1:19 PM Theofilos Theofovos 
wrote:

> Hi subin,
>
> can you clarify please?
>
>
I got a random nonsensical reply from this sender earlier today
too...spam/hack/whatever, probably best to just ignore.

David J.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
> You are correct that the behavior here is not explicitly documented [where] I 
> would expect to find it.
> 
> My expectation is that the trigger owner is the context in which the trigger 
> function is executed. Table owners can grant the ability to other roles to 
> create triggers on the owned tables should the trigger owner and table owner 
> require different roles. Triggers are like views in this regard.
> 
> I don't expect cascade update/cascade delete to be a factor here at all, 
> other than making the process easier to perform. This extends from the prior 
> expectation.
> 
> I expect [all this] not because I can point to a place where all that is said.
> 
> I would for sure expect deviations to be mentioned, and would find explicit 
> documentation to be reasonable to add if someone pushes forward such a change.

Good. We're converging. Thanks, David. I think that this  is a fair summary:

1. The PG doc very likely has no clear statement, anywhere, of the rules that 
govern the behavior in the class of trigger scenarios under discussion.

2. An expectation of what the rules are has emerged:

> the invoking role for a trigger's function is the role[*] that owns the 
> trigger.

Thereafter, the privilege domain in which the function executes is governed by 
the ordinary, separable, rules about "security definer" versus "security 
invoker".

I mentioned this from the "CREATE TRIGGER" section:

> https://www.postgresql.org/docs/current/sql-createtrigger.html#SQL-CREATETRIGGER-NOTES
>  
> 
> To create or replace a trigger on a table, the user must have the TRIGGER 
> privilege on the table. The user must also have EXECUTE privilege on the 
> trigger function.

It gives a strong hint that David's expectations are sound.

3. The outcomes with the eight triggers that I tested show straight buggy 
behavior in six cases. Moreover, because David said "don't expect cascade… to 
[matter]", the outcomes in the other two cases might show correct behavior only 
by accident.

It seems to me, therefore, that a carefully constructed, "single click", 
reproducible testcase is needed. I have this on my laptop. But, of course, I 
need to refine it a bit and review it thoroughly. It ended up in several .sql 
scripts called by a master script. This naturally implies a .zip file as the 
delivery vehicle.

Nobody has told me how an outsider like me can deliver such a .zip file, 
together with its typographically nuanced external documentation, to readers of 
plsql-general. So this is what I'll do:

I'll create a placeholder GitHub issue in "yugabyte/yugabyte-db" and send you 
the URL. Anybody can access this repo, read the account of the issues, and 
download an attached .zip of a testcase. I'll mention in my account that the 
behavior that I observe in YugabyteDB reproduces exactly in PG 14.4, that the 
YugabyteDB issue is filed for tracking purposes, and that I'll update the 
account with more information from the PG folks in due course.

Please bear with me. It might be a few days before I'm able to send you all the 
promised URL.


[*] The PG doc favors using "role" in sentences like this rather than 
"user"—even though a role might have child rows in an indefinitely deep 
hierarchy. I trust that this point is fully separable from what matters in the 
present triggers scenario.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Julien Rouhaud]

On Thu, Aug 11, 2022 at 04:20:20PM -0700, Bryn Llewellyn wrote:
>
> Nobody has told me how an outsider like me can deliver such a .zip file,
> together with its typographically nuanced external documentation, to readers
> of plsql-general. So this is what I'll do:

You mentioned previously that "Email attachments don't make it to the archive
for posts to this list", but they should.  It seems that you're using apple
mail, which is famous for having such problems, see [1] for instance.

Using a different MUA, or configuring apple mail to correctly put attachment as
attachment will solve this problem.

[1]: 
https://www.postgresql.org/message-id/CABUevEwEw35g7n3peoqmpWraQuRvounck7puDUWU-S-%3DyfsoEA%40mail.gmail.com

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn]

> rjuju...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Nobody has told me how an outsider like me can deliver such a .zip file, 
>> together with its typographically nuanced external documentation…
> 
> You mentioned previously that "Email attachments don't make it to the archive 
> for posts to this list", but they should. It seems that you're using apple 
> mail, which is famous for having such problems, see [1] for instance.
> 
> Using a different MUA, or configuring apple mail to correctly put attachment 
> as attachment will solve this problem.
> 
> [1]: 
> https://www.postgresql.org/message-id/CABUevEwEw35g7n3peoqmpWraQuRvounck7puDUWU-S-%3DyfsoEA%40mail.gmail.com


Hmm… I use a modern MacBook with the always current macOS Big Sur. (One day 
I'll pluck up courage and get to Monterey.) I use the native "Mail.app" email 
client (a.k.a. Mail User Agent) at whatever version comes with the native macOS 
upgrade process. I use this to send attachments all the time to no end of 
friends and colleagues—without issue. So how can there be anything wrong with 
how my "Mail.app" is configured? Is the configuration a pairwise notion so that 
I need dedicated settings to send to the pgsql-general list?

Yugabyte uses an email service from Google. I suppose that I could use their 
ordinary browser-based interface to send emails to the pgsql-general list. It 
would certainly be good to fix this for future exchanges. But for now, I'll 
stick to my plan. This will make it easy for me to draw this issue to the 
attention of colleagues and to give me a place where I add updates about 
progress on the issue.

Moreover, GitHub allows Markdown formatting. And the ability to format even a 
shortish essay with ordinary modern devices like heading levels, bullet lists, 
italics, and especially code blocks makes an enormous difference to readability.

The conventions that this list's archive imposes (only plain text, quoted 
content indicated with successively deep chevron-style marks, explicit URLs 
twice as long as your arm, and baked-in hard line breaks at about a dozen 
words) makes comprehension quite hard—and structuring an account well-nigh 
impossible.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[David G. Johnston]

On Thu, Aug 11, 2022 at 8:41 PM Bryn Llewellyn  wrote:

> The conventions that this list's archive imposes (only plain text, quoted
> content indicated with successively deep chevron-style marks, explicit URLs
> twice as long as your arm, and baked-in hard line breaks at about a dozen
> words) makes comprehension quite hard—and structuring an account well-nigh
> impossible.


It encourages brevity so we consider it a feature ;)  Usually, but not
here, the complaint is that it seems to do that too well...

David J.
p.s. just for testing I've included the original email as both zip and text
here.
My code example ended up quite big—so I'll show it to you all only if you ask. 
But it's easy to describe. My script does this:

1. It creates three non-superuser roles: "data", "code", and "client".

2. It creates a text-book masters-and-details table pair with owner "data".

Each table has the obvious PK column. The "details" table has the obvious FK 
column. And each table has a payload column "v". The FK constraint is defined 
thus:

  constraint details_fk foreign key(mk)
references masters(mk)
match full
on delete cascade
on update restrict
initially deferred

3. It creates a DELETE trigger at each timing point on each table.

That's eight in all: [before|after] * [row|statement] * [masters|details].

The trigger functions have the same names as the triggers. And each name pair 
reflects the three degrees of freedom—for example "before_statement_masters".

Each trigger function simply does a "raise info" to report its name, the return 
value from "current_role",  (and, in the "row" case, the value of "old.v"). And 
then it does "return null".

The trigger functions are owned by "data" and are explicitly marked "security 
invoker". (The results are the same without this marking—as expected.)

(In my real use case, the trigger functions are marked "security definer". But 
I did this test in order to understand the rules.)

"data" grants appropriate privileges to "code" to let its functions succeed.

4. It creates three "security definer" procedures with owner "code"

"cr_m_and_ds()" inserts specified "details" rows and then their specified 
"masters" row. (That's why the FK constraint is "initially deferred". The 
use-case from which this test is derived needs this.)

"del_m()" deletes a specified "masters" row—which implies the cascade-delete of 
its details.

"del_ds()" deletes all the "details" rows for a specified master.

"code" grants "execute" on these to "client". ("client " owns no objects.)

5. "client" invokes "code.cr_m_and_ds()".

It's called to insert a single "masters" row with "masters.v" set to 'Mary' and 
a single "details" row with "details.v" set to 'shampoo'.

6. "client" invokes "code.del_m()" on 'Mary'.

Here's what I see:

before_statement_masters: [code]
before_row_masters: [code] Mary
before_statement_details: [data]
before_row_details: [data] shampoo
after_row_masters: [code] Mary
after_statement_masters: [code]
after_row_details: [code] shampoo
after_statement_details: [code]

(I stripped the noisy "psql:0.sql:32: INFO:" preamble for each output line by 
hand.)

I was surprised that the value from "current_role" is *not* the table owner, 
"data", in all cases. (This is how triggers behave in Oracle database.) Rather, 
it's mainly (but not always) "code". I could be persuaded that, in the 
cascade-delete case, the invoking role is the owner of the "masters" table 
rather than the role, "code" that performs the "delete" from "masters"—but that 
would maybe be a stretch. Anyway, if this is the intention, why is it like this 
only for the "before" timing points for the triggers on "details"?

7. Starting with the same 'Mary'-'shampoo' pair, client invokes "code.del_ds()" 
on 'Mary'

With the same set-up, and using this instead of "del_m()", this is the result:

before_statement_details: [code]
before_row_details: [code] shampoo
after_row_details: [code] shampoo
after_statement_details: [code]

Here the value for "current_role" from each trigger is the same. These results 
are in line with the common case in the first test.

I read the section "Triggers on Data Changes" 
(https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER).
 But there's no hit on the page for any of "security", "invoker", or "definer". 
And I couldn't find wording without these terms that addresses what I describe 
here.
<>

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn]

david.g.johns...@gmail.com wrote:My code example ended up quite big—so I'll show it to you all only if you 
ask. But it's easy to describe. My script does this:

1. It creates three non-superuser roles: "data", "code", and "client".

2. It creates a text-book masters-and-details table pair with owner "data".

Each table has the obvious PK column. The "details" table has the obvious FK 
column. And each table has a payload column "v". The FK constraint is defined 
thus:

  constraint details_fk foreign key(mk)
references masters(mk)
match full
on delete cascade
on update restrict
initially deferred

3. It creates a DELETE trigger at each timing point on each table.

That's eight in all: [before|after] * [row|statement] * [masters|details].

The trigger functions have the same names as the triggers. And each name pair 
reflects the three degrees of freedom—for example "before_statement_masters".

Each trigger function simply does a "raise info" to report its name, the return 
value from "current_role",  (and, in the "row" case, the value of "old.v"). And 
then it does "return null".

The trigger functions are owned by "data" and are explicitly marked "security 
invoker". (The results are the same without this marking—as expected.)

(In my real use case, the trigger functions are marked "security definer". But 
I did this test in order to understand the rules.)

"data" grants appropriate privileges to "code" to let its functions succeed.

4. It creates three "security definer" procedures with owner "code"

"cr_m_and_ds()" inserts specified "details" rows and then their specified 
"masters" row. (That's why the FK constraint is "initially deferred". The 
use-case from which this test is derived needs this.)

"del_m()" deletes a specified "masters" row—which implies the cascade-delete 
of its details.

"del_ds()" deletes all the "details" rows for a specified master.

"code" grants "execute" on these to "client". ("client " owns no objects.)

5. "client" invokes "code.cr_m_and_ds()".

It's called to insert a single "masters" row with "masters.v" set to 'Mary' and 
a single "details" row with "details.v" set to 'shampoo'.

6. "client" invokes "code.del_m()" on 'Mary'.

Here's what I see:

before_statement_masters: [code]
before_row_masters: [code] Mary
before_statement_details: [data]
before_row_details: [data] shampoo
after_row_masters: [code] Mary
after_statement_masters: [code]
after_row_details: [code] shampoo
after_statement_details: [code]

(I stripped the noisy "psql:0.sql:32: INFO:" preamble for each output line by 
hand.)

I was surprised that the value from "current_role" is *not* the table owner, 
"data", in all cases. (This is how triggers behave in Oracle database.) Rather, 
it's mainly (but not always) "code". I could be persuaded that, in the 
cascade-delete case, the invoking role is the owner of the "masters" table 
rather than the role, "code" that performs the "delete" from "masters"—but 
that would maybe be a stretch. Anyway, if this is the intention, why is it like 
this only for the "before" timing points for the triggers on "details"?

7. Starting with the same 'Mary'-'shampoo' pair, client invokes "code.del_ds()" 
on 'Mary'

With the same set-up, and using this instead of "del_m()", this is the result:

before_statement_details: [code]
before_row_details: [code] shampoo
after_row_details: [code] shampoo
after_statement_details: [code]

Here the value for "current_role" from each trigger is the same. These results 
are in line with the common case in the first test.

I read the section "Triggers on Data Changes" 
(https://www.google.com/url?q=https://www.postgresql.org/docs/current/plpgsql-trigger.html%23PLPGSQL-DML-TRIGGER&source=gmail-imap&ust=166088155600&usg=AOvVaw2IjBK9LWeL4u9d1epMjuMk).
 But there's no hit on the page for any of "security", "invoker", or "definer". 
And I couldn't find wording without these terms that addresses what I describe 
here.
<>
Good. I can see the attachments here too:https://www.postgresql.org/message-id/CAKFQuwaNcC2NPtwNY%2BNvbbHFQuphfkicvLxcnCbnwTTwKf%3DGdw%40mail.gmail.comAnd I can download via the links with no problem. I'll aim to work out what's wrong my end as soon as I can.