Project

General

Profile

Actions

Bug #7605

closed

Migration 4a172cd00ef0 silently uses plpgsql extension

Added by Pavel Kácha almost 2 years ago. Updated about 1 year ago.

Status:
Closed
Priority:
Normal
Category:
Development - Core
Target version:
Start date:
10/13/2022
Due date:
% Done:

100%

Estimated time:
To be discussed:

Description

Migration 4a172cd00ef0_clean_mail_to_and_add_not_null_.py uses plpqsql and silently expects it to exist and be active.

This is intrinsic extension, it needs no additional installation, but it has to be activated for database.

Migration (and possible others in the future) thus should use "create extension" and "drop extension", possibly with "on conflict ignore" or something like that to not drop extension if it is alreade enabled and has some other uses by administrator.

Actions #1

Updated by Rajmund Hruška over 1 year ago

  • To be discussed changed from No to Yes

What needs to be done here?

Shall I add the "create extension" and "drop extension" to the mentioned migration script?

I guess we should also add this to documentation - https://alchemist.cesnet.cz/mentat/doc/development/html/_doclib/development.html#database-schema-migrations

Actions #2

Updated by Rajmund Hruška over 1 year ago

I am actually confused.

Migration 3fb6b209a5cd for mentat_events is also using plpgsql. I wonder why this issue wasn't discovered with that migration.

Locally, when I create my virtual machine from scratch using vagrant up, I have plpgsql in the list of extensions (\dx). And when I create a test function

CREATE OR REPLACE FUNCTION Sum(a int, b int) 
RETURNS int AS $$
BEGIN
  /*
   *  My first trivial PL/pgSQL function.
   */
  RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

there is no error when executing SELECT Sum(14,13);. I don't have to 'enable' the extension using createlong or anything. But I haven't seen anything regarding plpgsql anywhere in the codebase.

Actions #3

Updated by Rajmund Hruška over 1 year ago

  • To be discussed deleted (Yes)
Actions #4

Updated by Pavel Kácha about 1 year ago

This is maybe question to Radko? Aren't some extensions on by default? (Or possibly only on some platforms?)

Actions #5

Updated by Radko Krkoš about 1 year ago

  • To be discussed set to Yes

As far as I know, no extensions are enabled by default. Some extensions are built-in, such as plpgsql (and many more), so they need no installation, just enabling for target database(s) through CREATE EXTENSION. On the other end, another extension we use heavily, ip4r, must be installed explicitly (there is a .deb package) and also activated for select database(s) by CREATE EXTENSION.
As for plpgsql, we have it explicitly enabled on production, because we use it sometimes. For example in the two migrations mentioned, but also for some testing analytics (which are not part of production Mentat). Therefore this issue does not affect our systems, but would most probably fail for other users.
Simply adding CREATE and DROP to a migration would disable the extension for users which have it enabled without explicit dependencies, such as permanently defined functions. So, also us. The correct approach would be detecting the availability of extension in the beginning and in case of it missing, creating it before functions and their usage, and dropping it at the end. Otherwise, no modification to the extension state should be done. There is also the question of extension versioning (what to do if the installed version is different from then-current/tested for migration?). Solving this right is complex, the impact is low, so this issue was created as a reminder and a riddle for less interesting times.
Declaring plpgsql a requirement and managing its upgrades just like in case of ip4r is also a viable path. It would also allow us to use plpgsql functions as part of Mentat which might make some operations easier or cleaner (data integrity checks for arrays, timeline queries, ...).

I am afraid that that is all I can recall. Is it of any help?

Actions #6

Updated by Rajmund Hruška about 1 year ago

Radko Krkoš wrote in #note-5:

As far as I know, no extensions are enabled by default. Some extensions are built-in, such as plpgsql (and many more), so they need no installation, just enabling for target database(s) through CREATE EXTENSION. On the other end, another extension we use heavily, ip4r, must be installed explicitly (there is a .deb package) and also activated for select database(s) by CREATE EXTENSION.
As for plpgsql, we have it explicitly enabled on production, because we use it sometimes. For example in the two migrations mentioned, but also for some testing analytics (which are not part of production Mentat). Therefore this issue does not affect our systems, but would most probably fail for other users.
Simply adding CREATE and DROP to a migration would disable the extension for users which have it enabled without explicit dependencies, such as permanently defined functions. So, also us. The correct approach would be detecting the availability of extension in the beginning and in case of it missing, creating it before functions and their usage, and dropping it at the end. Otherwise, no modification to the extension state should be done. There is also the question of extension versioning (what to do if the installed version is different from then-current/tested for migration?). Solving this right is complex, the impact is low, so this issue was created as a reminder and a riddle for less interesting times.
Declaring plpgsql a requirement and managing its upgrades just like in case of ip4r is also a viable path. It would also allow us to use plpgsql functions as part of Mentat which might make some operations easier or cleaner (data integrity checks for arrays, timeline queries, ...).

I am afraid that that is all I can recall. Is it of any help?

Yes, I understand the part about detecting the extension and enabling/disabling it if not found. What confuses me is that when I created a virtual machine using vagrant, the extension was already enabled and I don't know why.

Actions #7

Updated by Radko Krkoš about 1 year ago

Rajmund Hruška wrote in #note-6:

Yes, I understand the part about detecting the extension and enabling/disabling it if not found. What confuses me is that when I created a virtual machine using vagrant, the extension was already enabled and I don't know why.

I see.
According to documentation, section "43.1. Overview" [1]:
"In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it."
So, plpgsql seems to be enabled by default. My bad, sorry for the confusion.

[1] https://www.postgresql.org/docs/15/plpgsql-overview.html

Actions #8

Updated by Radko Krkoš about 1 year ago

Radko Krkoš wrote in #note-7:

Rajmund Hruška wrote in #note-6:

Yes, I understand the part about detecting the extension and enabling/disabling it if not found. What confuses me is that when I created a virtual machine using vagrant, the extension was already enabled and I don't know why.

I see.
According to documentation, section "43.1. Overview" [1]:
"In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it."
So, plpgsql seems to be enabled by default. My bad, sorry for the confusion.

[1] https://www.postgresql.org/docs/15/plpgsql-overview.html

Also, according to documentation, section "42.1. Installing Procedural Languages" [2]:
"In a default PostgreSQL installation, the handler for the PL/pgSQL language is built and installed into the “library” directory; furthermore, the PL/pgSQL language itself is installed in all databases." What is quite explicit.
In line with [1], the change came in version 9.0, see difference between [3] (9.0) and [4] (8.4 - last 8.x version), where the cited sentence reads: "In a default PostgreSQL installation, the handler for the PL/pgSQL language is built and installed into the "library" directory."
PostgreSQL version 9.0 was released on September 20th 2010 [5], so the change is by no means recent and predates this issue substantially.

[2] https://www.postgresql.org/docs/15/xplang-install.html
[3] https://www.postgresql.org/docs/9.0/xplang-install.html
[4] https://www.postgresql.org/docs/8.4/xplang-install.html
[5] https://www.postgresql.org/support/versioning/

Actions #9

Updated by Rajmund Hruška about 1 year ago

  • Status changed from New to Closed
  • Assignee set to Rajmund Hruška
  • Target version changed from Backlog to 2.11
  • % Done changed from 0 to 100
  • To be discussed deleted (Yes)

I've added the note about using extensions to the documentation. As plpgsql should be enabled by default, I will not modify the existing migrations.

Actions

Also available in: Atom PDF