CVE-2022-24128: Incorrect Authorization (CWE-863)
value: Timescale TimescaleDB 1.x and 2.x before 2.5.2 may allow privilege escalation during extension installation. The installation process uses commands such as CREATE x IF NOT EXIST that allow an unprivileged user to precreate objects. These objects will be used by the installer (which executes as Superuser), leading to privilege escalation. In order to be able to take advantage of this, an unprivileged user would need to be able to create objects in a database and then get a Superuser to install TimescaleDB into their database. (In the fixed versions, the installation aborts when it finds that an object already exists.)
CWE description: The product performs an authorization check when an actor attempts to access a resource or perform an action, but it does not correctly perform the check. This allows attackers to bypass intended access restrictions.
CVSS v3 base score: 8
Published: 2022-03-13T18:15Z
Repository: https://github.com/timescale/timescaledb
Language: SQL
Files changed: .github/workflows/code_style.yaml, cmake/GenerateScripts.cmake, cmake/ScriptFiles.cmake, scripts/check_update_scripts.sh, sql/CMakeLists.txt, sql/bgw_startup.sql, sql/cat.cmake, sql/pre_install/cache.sql, sql/pre_install/schemas.sql, sql/pre_install/tables.sql, sql/updates/1.7.5--2.0.0-rc1.sql, sql/updates/2.0.0-rc3--2.0.0-rc4.sql, sql/updates/2.2.1--2.3.0.sql, sql/updates/2.3.1--2.4.0.sql, sql/updates/latest-dev.sql, sql/updates/reverse-dev.sql, sql/views.sql, test/expected/extension_scripts.out, test/sql/CMakeLists.txt, test/sql/extension_scripts.sql, tsl/test/expected/data_node_bootstrap.out, tsl/test/sql/data_node_bootstrap.sql
Commit date: 2022-02-09 17:53:20 +0100
Commit message: Fix extension installation privilege escalation

TimescaleDB was vulnerable to a privilege escalation attack in
the extension installation script. An attacker could precreate
objects normally owned by the extension and get those objects
used in the installation script since the script would only try
to create them if they did not already exist. Thanks to Pedro
Gallegos for reporting the problem.

This patch changes the schema, table and function creation to fail
and abort the installation when the object already exists instead
of using the existing object.

Security: CVE-2022-24128

Diff: diff --git a/.github/workflows/code_style.yaml b/.github/workflows/code_style.yaml
index 6df45773..64d701e5 100644
--- a/.github/workflows/code_style.yaml
+++ b/.github/workflows/code_style.yaml
@@ -33,11 +33,11 @@ jobs:
       - name: Format Perl files, if needed
         run: find . -name '*.p[lm]' -exec perltidy --profile=scripts/perltidyrc {} +
       - name: Check for diff
         run: git diff --exit-code
   misc_checks:
-    name: Check formatting, license and git hooks
+    name: Check formatting, license, update scripts and git hooks
     runs-on: ubuntu-20.04
     strategy:
       fail-fast: false
     steps:
     - name: Checkout source
@@ -60,5 +60,8 @@ jobs:
         sudo apt install clang-format-8
         sudo update-alternatives --install /usr/bin/clang-format clang-format /usr/bin/clang-format-8 100
         sudo update-alternatives --set clang-format /usr/bin/clang-format-8
         ./scripts/clang_format_all.sh
         git diff --exit-code
+    - name: Check update scripts
+      if: always()
+      run: ./scripts/check_update_scripts.sh
diff --git a/cmake/GenerateScripts.cmake b/cmake/GenerateScripts.cmake
index 5b527164..73180edc 100644
--- a/cmake/GenerateScripts.cmake
+++ b/cmake/GenerateScripts.cmake
@@ -157,11 +157,11 @@ function(generate_downgrade_script)
   # so we can read them locally without affecting the parent scope.
   include(
     ${CMAKE_BINARY_DIR}/v${_downgrade_TARGET_VERSION}/cmake/ScriptFiles.cmake)
 
   set(_downgrade_PRE_FILES ${PRE_UPDATE_FILES})
-  set(_downgrade_POST_FILES ${SOURCE_FILES} ${SET_POST_UPDATE_STAGE}
+  set(_downgrade_POST_FILES "${PRE_INSTALL_FUNCTION_FILES};${SOURCE_FILES}" ${SET_POST_UPDATE_STAGE}
                             ${POST_UPDATE_FILES} ${UNSET_UPDATE_STAGE})
 
   # Fetch prolog and epilog from target version.
   git_versioned_get(
     VERSION
diff --git a/cmake/ScriptFiles.cmake b/cmake/ScriptFiles.cmake
index eb09cdf0..6d33ece7 100644
--- a/cmake/ScriptFiles.cmake
+++ b/cmake/ScriptFiles.cmake
@@ -13,19 +13,22 @@ set(PRE_INSTALL_SOURCE_FILES
     pre_install/schemas.sql # Must be first
     pre_install/types.pre.sql
     pre_install/types.functions.sql
     pre_install/types.post.sql # Must be before tables.sql
     pre_install/tables.sql
+    pre_install/cache.sql
     pre_install/insert_data.sql
-    pre_install/bgw_scheduler_startup.sql
     pre_install/fdw_functions.sql
     pre_install/timescaledb_fdw.sql)
 
+# Source files that define functions and need to be rerun in update
+set(PRE_INSTALL_FUNCTION_FILES
+    pre_install/types.functions.sql
+    pre_install/fdw_functions.sql)
+
 # The rest of the source files defining mostly functions
 set(SOURCE_FILES
-    pre_install/types.functions.sql
-    pre_install/fdw_functions.sql
     hypertable.sql
     chunk.sql
     data_node.sql
     ddl_internal.sql
     ddl_experimental.sql
@@ -41,11 +44,10 @@ set(SOURCE_FILES
     time_bucket.sql
     time_bucket_ng.sql
     version.sql
     size_utils.sql
     histogram.sql
-    cache.sql
     bgw_scheduler.sql
     metadata.sql
     dist_internal.sql
     views.sql
     views_experimental.sql
@@ -55,11 +57,12 @@ set(SOURCE_FILES
     restoring.sql
     job_api.sql
     policy_api.sql
     policy_internal.sql
     cagg_utils.sql
-	telemetry.sql)
+	  telemetry.sql
+    bgw_startup.sql)
 
 # These files should be pre-pended to update scripts so that they are executed
 # before anything else during updates
 set(PRE_UPDATE_FILES updates/pre-update.sql)
 
diff --git a/scripts/check_update_scripts.sh b/scripts/check_update_scripts.sh
new file mode 100755
index 00000000..f0daab9a
--- /dev/null
+++ b/scripts/check_update_scripts.sh
@@ -0,0 +1,16 @@
+#! /bin/bash
+
+SCRIPT_DIR=$(dirname ${0})
+SRC_DIR=$(dirname ${SCRIPT_DIR})
+
+if grep -ir "IF NOT EXISTS" ${SRC_DIR}/sql; then
+  cat <<EOF
+
+Update scripts must unconditionally add new objects and fail when the object
+already exists otherwise this might enable privilege escalation attacks where
+an attacker can precreate objects that get used in later parts of the scripts
+instead of the objects created by timescaledb.
+
+EOF
+  exit 1
+fi
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 2984d8ff..2cd817fc 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -105,37 +105,63 @@ endfunction()
 # Create versioned files (replacing MODULE_PATHNAME) in the build directory of
 # all our source files
 version_files("${PRE_UPDATE_FILES}" PRE_UPDATE_FILES_VERSIONED)
 version_files("${POST_UPDATE_FILES}" POST_UPDATE_FILES_VERSIONED)
 version_files("${PRE_INSTALL_SOURCE_FILES}" PRE_INSTALL_SOURCE_FILES_VERSIONED)
+version_files("${PRE_INSTALL_FUNCTION_FILES}"
+              PRE_INSTALL_FUNCTION_FILES_VERSIONED)
 version_files("${SOURCE_FILES}" SOURCE_FILES_VERSIONED)
 version_files("${MOD_FILES}" MOD_FILES_VERSIONED)
 version_files("updates/latest-dev.sql" LASTEST_MOD_VERSIONED)
 version_files("notice.sql" NOTICE_FILE)
 
 # Function to concatenate all files in SRC_FILE_LIST into file OUTPUT_FILE
 function(cat_files SRC_FILE_LIST OUTPUT_FILE)
   if(WIN32)
-    # Make list of files into string of files separated by "+" to make Windows
-    # copy concatenate them
-    file(TO_NATIVE_PATH "${SRC_FILE_LIST}" SRC_FILE_LIST_NATIVE)
-    string(REPLACE ";" ";+;" SQL_LIST_JOINED "${SRC_FILE_LIST_NATIVE}")
-    file(TO_NATIVE_PATH "${OUTPUT_FILE}" OUTPUT_FILE_NATIVE)
-    set(CAT_CMD copy /B /y ${SQL_LIST_JOINED} "\"${OUTPUT_FILE_NATIVE}\"" >NUL)
+    set("SRC_ARG" "-DSRC_FILE_LIST=${SRC_FILE_LIST}")
   else()
-    set(CAT_CMD cat ${SRC_FILE_LIST} > ${OUTPUT_FILE})
+    set("SRC_ARG" "'-DSRC_FILE_LIST=${SRC_FILE_LIST}'")
   endif()
   add_custom_command(
     OUTPUT ${OUTPUT_FILE}
     DEPENDS ${SRC_FILE_LIST}
     WORKING_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}
-    COMMAND ${CAT_CMD}
+    COMMAND ${CMAKE_COMMAND} "${SRC_ARG}" "-DOUTPUT_FILE=${OUTPUT_FILE}" -P
+            cat.cmake
     COMMENT "Generating ${OUTPUT_FILE}")
 endfunction()
 
+# Concatenate all files in SRC_FILE_LIST into file OUTPUT_FILE Additionally
+# strips out `OR REPLACE` from the generated file as this would allow a
+# privilege escalation attack when present in the CREATE script
+function(cat_create_files SRC_FILE_LIST OUTPUT_FILE)
+  if(WIN32)
+    set("SRC_ARG" "-DSRC_FILE_LIST=${SRC_FILE_LIST}")
+  else()
+    set("SRC_ARG" "'-DSRC_FILE_LIST=${SRC_FILE_LIST}'")
+  endif()
+  if(WIN32)
+    add_custom_command(
+      OUTPUT ${OUTPUT_FILE}
+      DEPENDS ${SRC_FILE_LIST}
+      WORKING_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}
+      COMMAND ${CMAKE_COMMAND} "${SRC_ARG}" "-DOUTPUT_FILE=${OUTPUT_FILE}"
+              "-DSTRIP_REPLACE=ON" -P cat.cmake
+      COMMENT "Generating ${OUTPUT_FILE}")
+  else()
+    add_custom_command(
+      OUTPUT ${OUTPUT_FILE}
+      DEPENDS ${SRC_FILE_LIST}
+      WORKING_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}
+      COMMAND ${CMAKE_COMMAND} "'-DSRC_FILE_LIST=${SRC_FILE_LIST}'"
+              "-DOUTPUT_FILE=${OUTPUT_FILE}" "-DSTRIP_REPLACE=ON" -P cat.cmake
+      COMMENT "Generating ${OUTPUT_FILE}")
+  endif()
+endfunction()
+
 # Generate the extension file used with CREATE EXTENSION
-cat_files(
+cat_create_files(
   "${PRE_INSTALL_SOURCE_FILES_VERSIONED};${SOURCE_FILES_VERSIONED};${NOTICE_FILE}"
   ${CMAKE_CURRENT_BINARY_DIR}/${INSTALL_FILE})
 add_custom_target(sqlfile ALL
                   DEPENDS ${CMAKE_CURRENT_BINARY_DIR}/${INSTALL_FILE})
 
@@ -198,15 +224,16 @@ foreach(transition_mod_file ${MOD_FILES_LIST})
       ${CMAKE_CURRENT_BINARY_DIR}/timescaledb--${START_VERSION}--${PROJECT_VERSION_MOD}.sql
   )
   list(APPEND UPDATE_SCRIPTS ${UPDATE_SCRIPT})
   if(CURR_MOD_FILES)
     cat_files(
-      "${PRE_FILES};${CURR_MOD_FILES};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
+      "${PRE_FILES};${CURR_MOD_FILES};${PRE_INSTALL_FUNCTION_FILES_VERSIONED};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
       ${UPDATE_SCRIPT})
   else()
-    cat_files("${PRE_FILES};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
-              ${UPDATE_SCRIPT})
+    cat_files(
+      "${PRE_FILES};${PRE_INSTALL_FUNCTION_FILES_VERSIONED};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
+      ${UPDATE_SCRIPT})
   endif()
 endforeach(transition_mod_file)
 
 add_custom_target(sqlupdatescripts ALL DEPENDS ${UPDATE_SCRIPTS})
 
diff --git a/sql/pre_install/bgw_scheduler_startup.sql b/sql/bgw_startup.sql
similarity index 59%
rename from sql/pre_install/bgw_scheduler_startup.sql
rename to sql/bgw_startup.sql
index 89dfbf32..372e5f42 100644
--- a/sql/pre_install/bgw_scheduler_startup.sql
+++ b/sql/bgw_startup.sql
@@ -1,10 +1,5 @@
 -- This file and its contents are licensed under the Apache License 2.0.
 -- Please see the included NOTICE for copyright information and
 -- LICENSE-APACHE for a copy of the license.
 
-CREATE OR REPLACE FUNCTION _timescaledb_internal.restart_background_workers()
-RETURNS BOOL 
-AS '@LOADER_PATHNAME@', 'ts_bgw_db_workers_restart'
-LANGUAGE C VOLATILE;
-
 SELECT _timescaledb_internal.restart_background_workers();
diff --git a/sql/cat.cmake b/sql/cat.cmake
new file mode 100644
index 00000000..b62d45ff
--- /dev/null
+++ b/sql/cat.cmake
@@ -0,0 +1,26 @@
+
+IF(POLICY CMP0012)
+  CMAKE_POLICY(SET CMP0012 NEW)
+ENDIF()
+
+if (NOT DEFINED STRIP_REPLACE)
+  set(STRIP_REPLACE OFF)
+endif()
+
+function(append_file IN_FILE OUT_FILE STRIP_REPLACE)
+  file(READ ${IN_FILE} CONTENTS)
+  if (${STRIP_REPLACE})
+    string(REPLACE " OR REPLACE " " " CONTENTS "${CONTENTS}")
+  endif()
+  file(APPEND ${OUT_FILE} "${CONTENTS}")
+endfunction()
+
+# Function to concatenate all files in SRC_FILE_LIST into file OUTPUT_FILE
+function(cat SRC_FILE_LIST OUTPUT_FILE STRIP_REPLACE)
+  file(WRITE ${OUTPUT_FILE} "")
+  foreach(SRC_FILE ${SRC_FILE_LIST})
+    append_file(${SRC_FILE} ${OUTPUT_FILE} ${STRIP_REPLACE})
+  endforeach()
+endfunction()
+
+cat("${SRC_FILE_LIST}" "${OUTPUT_FILE}" "${STRIP_REPLACE}")
diff --git a/sql/cache.sql b/sql/pre_install/cache.sql
similarity index 85%
rename from sql/cache.sql
rename to sql/pre_install/cache.sql
index 28e865b2..e401ad72 100644
--- a/sql/cache.sql
+++ b/sql/pre_install/cache.sql
@@ -3,22 +3,22 @@
 -- LICENSE-APACHE for a copy of the license.
 
 -- This file contains infrastructure for cache invalidation of TimescaleDB
 -- metadata caches kept in C. Please look at cache_invalidate.c for a
 -- description of how this works.
-CREATE TABLE IF NOT EXISTS  _timescaledb_cache.cache_inval_hypertable();
+CREATE TABLE _timescaledb_cache.cache_inval_hypertable();
 
 -- For notifying the scheduler of changes to the bgw_job table.
-CREATE TABLE IF NOT EXISTS  _timescaledb_cache.cache_inval_bgw_job();
+CREATE TABLE _timescaledb_cache.cache_inval_bgw_job();
 
 -- This is pretty subtle. We create this dummy cache_inval_extension table
 -- solely for the purpose of getting a relcache invalidation event when it is
 -- deleted on DROP extension. It has no related triggers. When the table is
 -- invalidated, all backends will be notified and will know that they must
 -- invalidate all cached information, including catalog table and index OIDs,
 -- etc.
-CREATE TABLE IF NOT EXISTS  _timescaledb_cache.cache_inval_extension();
+CREATE TABLE _timescaledb_cache.cache_inval_extension();
 
 -- not actually strictly needed but good for sanity as all tables should be dumped.
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_cache.cache_inval_hypertable', '');
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_cache.cache_inval_extension', '');
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_cache.cache_inval_bgw_job', '');
diff --git a/sql/pre_install/schemas.sql b/sql/pre_install/schemas.sql
index d43ae038..18fbd0b1 100644
--- a/sql/pre_install/schemas.sql
+++ b/sql/pre_install/schemas.sql
@@ -1,12 +1,13 @@
 -- This file and its contents are licensed under the Apache License 2.0.
 -- Please see the included NOTICE for copyright information and
 -- LICENSE-APACHE for a copy of the license.
 
-CREATE SCHEMA IF NOT EXISTS _timescaledb_catalog;
-CREATE SCHEMA IF NOT EXISTS _timescaledb_internal;
-CREATE SCHEMA IF NOT EXISTS _timescaledb_cache;
-CREATE SCHEMA IF NOT EXISTS _timescaledb_config;
-CREATE SCHEMA IF NOT EXISTS timescaledb_experimental;
-GRANT USAGE ON SCHEMA _timescaledb_cache, _timescaledb_catalog, _timescaledb_internal, _timescaledb_config TO PUBLIC;
-GRANT USAGE ON SCHEMA timescaledb_experimental TO PUBLIC;
+CREATE SCHEMA _timescaledb_catalog;
+CREATE SCHEMA _timescaledb_internal;
+CREATE SCHEMA _timescaledb_cache;
+CREATE SCHEMA _timescaledb_config;
+CREATE SCHEMA timescaledb_experimental;
+CREATE SCHEMA timescaledb_information;
+
+GRANT USAGE ON SCHEMA _timescaledb_cache, _timescaledb_catalog, _timescaledb_internal, _timescaledb_config, timescaledb_information, timescaledb_experimental TO PUBLIC;
 
diff --git a/sql/pre_install/tables.sql b/sql/pre_install/tables.sql
index 6121160d..4ba4571c 100644
--- a/sql/pre_install/tables.sql
+++ b/sql/pre_install/tables.sql
@@ -35,13 +35,13 @@
 -- configurable number of slices. The number of slices can be
 -- reconfigured, but the new partitioning only affects newly created
 -- chunks.
 -- The unique constraint is table_name +schema_name. The ordering is
 -- important as we want index access when we filter by table_name
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
+CREATE SEQUENCE _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable (
+CREATE TABLE _timescaledb_catalog.hypertable (
   id INTEGER PRIMARY KEY DEFAULT nextval('_timescaledb_catalog.hypertable_id_seq'), 
   schema_name name NOT NULL CHECK (schema_name != '_timescaledb_catalog'),
   table_name name NOT NULL,
   associated_schema_name name NOT NULL,
   associated_table_prefix name NOT NULL,
@@ -65,11 +65,11 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable (
 ALTER SEQUENCE _timescaledb_catalog.hypertable_id_seq OWNED BY _timescaledb_catalog.hypertable.id;
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.hypertable_id_seq', '');
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.hypertable', '');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_data_node (
+CREATE TABLE _timescaledb_catalog.hypertable_data_node (
   hypertable_id integer NOT NULL REFERENCES _timescaledb_catalog.hypertable (id),
   node_hypertable_id integer NULL,
   node_name name NOT NULL,
   block_chunks boolean NOT NULL,
   UNIQUE (node_hypertable_id, node_name),
@@ -78,21 +78,21 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_data_node (
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.hypertable_data_node', '');
 
 -- The tablespace table maps tablespaces to hypertables.
 -- This allows spreading a hypertable's chunks across multiple disks.
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.tablespace (
+CREATE TABLE _timescaledb_catalog.tablespace (
   id serial PRIMARY KEY,
   hypertable_id int NOT NULL REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   tablespace_name name NOT NULL,
   UNIQUE (hypertable_id, tablespace_name)
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.tablespace', '');
 
 -- A dimension represents an axis along which data is partitioned.
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.dimension (
+CREATE TABLE _timescaledb_catalog.dimension (
   id serial NOT NULL PRIMARY KEY,
   hypertable_id integer NOT NULL REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   column_name name NOT NULL,
   column_type REGTYPE NOT NULL,
   aligned boolean NOT NULL,
@@ -113,11 +113,11 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.dimension (
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.dimension', '');
 
 SELECT pg_catalog.pg_extension_config_dump(pg_get_serial_sequence('_timescaledb_catalog.dimension', 'id'), '');
 
 -- A dimension slice defines a keyspace range along a dimension axis.
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.dimension_slice (
+CREATE TABLE _timescaledb_catalog.dimension_slice (
   id serial NOT NULL PRIMARY KEY,
   dimension_id integer NOT NULL REFERENCES _timescaledb_catalog.dimension (id) ON DELETE CASCADE,
   range_start bigint NOT NULL,
   range_end bigint NOT NULL,
   CHECK (range_start <= range_end),
@@ -131,13 +131,13 @@ SELECT pg_catalog.pg_extension_config_dump(pg_get_serial_sequence('_timescaledb_
 -- A chunk is a partition (hypercube) in an N-dimensional
 -- hyperspace. Each chunk is associated with N constraints that define
 -- the chunk's hypercube. Tuples that fall within the chunk's
 -- hypercube are stored in the chunk's data table, as given by
 -- 'schema_name' and 'table_name'.
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_id_seq MINVALUE 1;
+CREATE SEQUENCE _timescaledb_catalog.chunk_id_seq MINVALUE 1;
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk (
+CREATE TABLE _timescaledb_catalog.chunk (
   id integer PRIMARY KEY DEFAULT nextval('_timescaledb_catalog.chunk_id_seq'),
   hypertable_id int NOT NULL REFERENCES _timescaledb_catalog.hypertable (id),
   schema_name name NOT NULL,
   table_name name NOT NULL,
   compressed_chunk_id integer REFERENCES _timescaledb_catalog.chunk (id),
@@ -145,49 +145,49 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk (
   status integer NOT NULL DEFAULT 0,
   UNIQUE (schema_name, table_name)
 );
 ALTER SEQUENCE _timescaledb_catalog.chunk_id_seq OWNED BY _timescaledb_catalog.chunk.id;
 
-CREATE INDEX IF NOT EXISTS chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
+CREATE INDEX chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
 
-CREATE INDEX IF NOT EXISTS chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
+CREATE INDEX chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk', '');
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk_id_seq', '');
 
 -- A chunk constraint maps a dimension slice to a chunk. Each
 -- constraint associated with a chunk will also be a table constraint
 -- on the chunk's data table.
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_constraint (
+CREATE TABLE _timescaledb_catalog.chunk_constraint (
   chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk (id),
   dimension_slice_id integer NULL REFERENCES _timescaledb_catalog.dimension_slice (id),
   constraint_name name NOT NULL,
   hypertable_constraint_name name NULL,
   UNIQUE (chunk_id, constraint_name)
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk_constraint', '');
 
-CREATE INDEX IF NOT EXISTS chunk_constraint_chunk_id_dimension_slice_id_idx ON _timescaledb_catalog.chunk_constraint (chunk_id, dimension_slice_id);
+CREATE INDEX chunk_constraint_chunk_id_dimension_slice_id_idx ON _timescaledb_catalog.chunk_constraint (chunk_id, dimension_slice_id);
 
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_constraint_name;
+CREATE SEQUENCE _timescaledb_catalog.chunk_constraint_name;
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk_constraint_name', '');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_index (
+CREATE TABLE _timescaledb_catalog.chunk_index (
   chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk (id) ON DELETE CASCADE,
   index_name name NOT NULL,
   hypertable_id integer NOT NULL REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   hypertable_index_name name NOT NULL,
   UNIQUE (chunk_id, index_name)
 );
 
-CREATE INDEX IF NOT EXISTS chunk_index_hypertable_id_hypertable_index_name_idx ON _timescaledb_catalog.chunk_index (hypertable_id, hypertable_index_name);
+CREATE INDEX chunk_index_hypertable_id_hypertable_index_name_idx ON _timescaledb_catalog.chunk_index (hypertable_id, hypertable_index_name);
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk_index', '');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_data_node (
+CREATE TABLE _timescaledb_catalog.chunk_data_node (
   chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk (id),
   node_chunk_id integer NOT NULL,
   node_name name NOT NULL,
   UNIQUE (node_chunk_id, node_name),
   UNIQUE (chunk_id, node_name)
@@ -196,16 +196,16 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_data_node (
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.chunk_data_node', '');
 
 -- Default jobs are given the id space [1,1000). User-installed jobs and any jobs created inside tests
 -- are given the id space [1000, INT_MAX). That way, we do not pg_dump jobs that are always default-installed
 -- inside other .sql scripts. This avoids insertion conflicts during pg_restore.
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_config.bgw_job_id_seq
+CREATE SEQUENCE _timescaledb_config.bgw_job_id_seq
 MINVALUE 1000;
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job_id_seq', '');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
+CREATE TABLE _timescaledb_config.bgw_job (
   id integer PRIMARY KEY DEFAULT nextval('_timescaledb_config.bgw_job_id_seq'),
   application_name name NOT NULL,
   schedule_interval interval NOT NULL,
   max_runtime interval NOT NULL,
   max_retries integer NOT NULL,
@@ -218,15 +218,15 @@ CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
   config jsonb
 );
 
 ALTER SEQUENCE _timescaledb_config.bgw_job_id_seq OWNED BY _timescaledb_config.bgw_job.id;
 
-CREATE INDEX IF NOT EXISTS bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job (proc_schema, proc_name, hypertable_id);
+CREATE INDEX bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job (proc_schema, proc_name, hypertable_id);
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job', 'WHERE id >= 1000');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_job_stat (
+CREATE TABLE _timescaledb_internal.bgw_job_stat (
   job_id integer PRIMARY KEY REFERENCES _timescaledb_config.bgw_job (id) ON DELETE CASCADE,
   last_start timestamptz NOT NULL DEFAULT NOW(),
   last_finish timestamptz NOT NULL,
   next_start timestamptz NOT NULL,
   last_successful_finish timestamptz NOT NULL,
@@ -242,28 +242,28 @@ CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_job_stat (
 
 --The job_stat table is not dumped by pg_dump on purpose because
 --the statistics probably aren't very meaningful across instances.
 -- Now we define a special stats table for each job/chunk pair. This will be used by the scheduler
 -- to determine whether to run a specific job on a specific chunk.
-CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_policy_chunk_stats (
+CREATE TABLE _timescaledb_internal.bgw_policy_chunk_stats (
   job_id integer NOT NULL REFERENCES _timescaledb_config.bgw_job (id) ON DELETE CASCADE,
   chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk (id) ON DELETE CASCADE,
   num_times_job_run integer,
   last_time_job_run timestamptz,
   UNIQUE (job_id, chunk_id)
 );
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.metadata (
+CREATE TABLE _timescaledb_catalog.metadata (
   key NAME NOT NULL PRIMARY KEY,
   value text NOT NULL,
   include_in_telemetry boolean NOT NULL
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.metadata', $$
   WHERE KEY = 'exported_uuid' $$);
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
+CREATE TABLE _timescaledb_catalog.continuous_agg (
   mat_hypertable_id integer PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   raw_hypertable_id integer NOT NULL REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   user_view_schema name NOT NULL,
   user_view_name name NOT NULL,
   partial_view_schema name NOT NULL,
@@ -274,16 +274,16 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
   materialized_only bool NOT NULL DEFAULT FALSE,
   UNIQUE (user_view_schema, user_view_name),
   UNIQUE (partial_view_schema, partial_view_name)
 );
 
-CREATE INDEX IF NOT EXISTS continuous_agg_raw_hypertable_id_idx ON _timescaledb_catalog.continuous_agg (raw_hypertable_id);
+CREATE INDEX continuous_agg_raw_hypertable_id_idx ON _timescaledb_catalog.continuous_agg (raw_hypertable_id);
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_agg', '');
 
 -- See the comments for ContinuousAggsBucketFunction structure.
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_bucket_function(
+CREATE TABLE _timescaledb_catalog.continuous_aggs_bucket_function(
   mat_hypertable_id integer PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   -- The schema of the function. Equals TRUE for "timescaledb_experimental", FALSE otherwise.
   experimental bool NOT NULL,
   -- Name of the bucketing function, e.g. "time_bucket" or "time_bucket_ng"
   name text NOT NULL,
@@ -295,31 +295,31 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_bucket_function(
   timezone text NOT NULL
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_bucket_function', '');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_invalidation_threshold (
+CREATE TABLE _timescaledb_catalog.continuous_aggs_invalidation_threshold (
   hypertable_id integer PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   watermark bigint NOT NULL
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_invalidation_threshold', '');
 
 -- this does not have an FK on the materialization table since INSERTs to this
 -- table are performance critical
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (
+CREATE TABLE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (
   hypertable_id integer NOT NULL,
   lowest_modified_value bigint NOT NULL,
   greatest_modified_value bigint NOT NULL
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_aggs_hypertable_invalidation_log', '');
 
 CREATE INDEX continuous_aggs_hypertable_invalidation_log_idx ON _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (hypertable_id, lowest_modified_value ASC);
 
 -- per cagg copy of invalidation log
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_materialization_invalidation_log (
+CREATE TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log (
   materialization_id integer REFERENCES _timescaledb_catalog.continuous_agg (mat_hypertable_id) ON DELETE CASCADE,
   lowest_modified_value bigint NOT NULL,
   greatest_modified_value bigint NOT NULL
 );
 
@@ -329,18 +329,18 @@ CREATE INDEX continuous_aggs_materialization_invalidation_log_idx ON _timescaled
 
 
 /* the source of this data is the enum from the source code that lists
  *  the algorithms. This table is NOT dumped.
  */
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.compression_algorithm (
+CREATE TABLE _timescaledb_catalog.compression_algorithm (
   id smallint PRIMARY KEY,
   version smallint NOT NULL,
   name name NOT NULL,
   description text
 );
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_compression (
+CREATE TABLE _timescaledb_catalog.hypertable_compression (
   hypertable_id integer REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   attname name NOT NULL,
   compression_algorithm_id smallint REFERENCES _timescaledb_catalog.compression_algorithm (id),
   segmentby_column_index smallint,
   orderby_column_index smallint,
@@ -351,11 +351,11 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_compression (
   UNIQUE (hypertable_id, orderby_column_index)
 );
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.hypertable_compression', '');
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.compression_chunk_size (
+CREATE TABLE _timescaledb_catalog.compression_chunk_size (
   chunk_id integer REFERENCES _timescaledb_catalog.chunk (id) ON DELETE CASCADE,
   compressed_chunk_id integer REFERENCES _timescaledb_catalog.chunk (id) ON DELETE CASCADE,
   uncompressed_heap_size bigint NOT NULL,
   uncompressed_toast_size bigint NOT NULL,
   uncompressed_index_size bigint NOT NULL,
@@ -378,11 +378,11 @@ CREATE TABLE _timescaledb_catalog.remote_txn (
   data_node_name name, --this is really only to allow us to cleanup stuff on a per-node basis.
   remote_transaction_id text CHECK (remote_transaction_id::rxid IS NOT NULL),
   PRIMARY KEY (remote_transaction_id)
 );
 
-CREATE INDEX IF NOT EXISTS remote_txn_data_node_name_idx ON _timescaledb_catalog.remote_txn (data_node_name);
+CREATE INDEX remote_txn_data_node_name_idx ON _timescaledb_catalog.remote_txn (data_node_name);
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.remote_txn', '');
 
 -- This table stores information about the stage that has been completed of a
 -- chunk move/copy activity
@@ -400,13 +400,13 @@ SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.remote_txn', ''
 -- We don't want to pg_dump this table's contents. A node restored using it
 -- could be part of a totally different multinode setup and we don't want to
 -- carry over chunk copy/move operations from earlier (if it makes sense at all)
 --
 
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
+CREATE SEQUENCE _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation (
+CREATE TABLE _timescaledb_catalog.chunk_copy_operation (
   operation_id name PRIMARY KEY, -- the publisher/subscriber identifier used
   backend_pid integer NOT NULL, -- the pid of the backend running this activity
   completed_stage name NOT NULL, -- the completed stage/step
   time_start timestamptz NOT NULL DEFAULT NOW(), -- start time of the activity
   chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk (id) ON DELETE CASCADE,
diff --git a/sql/updates/1.7.5--2.0.0-rc1.sql b/sql/updates/1.7.5--2.0.0-rc1.sql
index 8b0cff98..f66b1d89 100644
--- a/sql/updates/1.7.5--2.0.0-rc1.sql
+++ b/sql/updates/1.7.5--2.0.0-rc1.sql
@@ -39,11 +39,11 @@ DROP VIEW IF EXISTS timescaledb_information.drop_chunks_policies;
 DROP VIEW IF EXISTS timescaledb_information.reorder_policies;
 
 ALTER TABLE _timescaledb_catalog.hypertable ADD COLUMN replication_factor SMALLINT NULL CHECK (replication_factor > 0);
 
 -- Table for hypertable -> node mappings
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_data_node (
+CREATE TABLE _timescaledb_catalog.hypertable_data_node (
     hypertable_id          INTEGER NOT NULL     REFERENCES _timescaledb_catalog.hypertable(id),
     node_hypertable_id   INTEGER NULL,
     node_name            NAME NOT NULL,
     block_chunks           BOOLEAN NOT NULL,
     UNIQUE(node_hypertable_id, node_name),
@@ -52,11 +52,11 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_data_node (
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.hypertable_data_node', '');
 
 GRANT SELECT ON _timescaledb_catalog.hypertable_data_node TO PUBLIC;
 
 -- Table for chunk -> nodes mappings
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_data_node (
+CREATE TABLE _timescaledb_catalog.chunk_data_node (
     chunk_id               INTEGER NOT NULL     REFERENCES _timescaledb_catalog.chunk(id),
     node_chunk_id        INTEGER NOT NULL,
     node_name            NAME NOT NULL,
     UNIQUE(node_chunk_id, node_name),
     UNIQUE(chunk_id, node_name)
@@ -83,22 +83,22 @@ CREATE TYPE rxid (
 CREATE TABLE _timescaledb_catalog.remote_txn (
     data_node_name              NAME, --this is really only to allow us to cleanup stuff on a per-node basis.
     remote_transaction_id    TEXT CHECK (remote_transaction_id::rxid is not null),
     PRIMARY KEY (remote_transaction_id)
 );
-CREATE INDEX IF NOT EXISTS remote_txn_data_node_name_idx
+CREATE INDEX remote_txn_data_node_name_idx
 ON _timescaledb_catalog.remote_txn(data_node_name);
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.remote_txn', '');
 
 GRANT SELECT ON _timescaledb_catalog.remote_txn TO PUBLIC;
 
 DROP VIEW IF EXISTS timescaledb_information.compressed_hypertable_stats;
 DROP VIEW IF EXISTS timescaledb_information.compressed_chunk_stats;
 
 -- all existing compressed chunks have NULL value for the new columns
-ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN IF NOT EXISTS numrows_pre_compression BIGINT;
-ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN IF NOT EXISTS numrows_post_compression BIGINT;
+ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN numrows_pre_compression BIGINT;
+ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN numrows_post_compression BIGINT;
 
 --rewrite catalog table to not break catalog scans on tables with missingval optimization
 CLUSTER  _timescaledb_catalog.compression_chunk_size USING compression_chunk_size_pkey;
 ALTER TABLE _timescaledb_catalog.compression_chunk_size SET WITHOUT CLUSTER;
 
@@ -274,16 +274,16 @@ ALTER TABLE _timescaledb_internal.bgw_policy_chunk_stats DROP CONSTRAINT IF EXIS
 -- remember sequence values so they can be restored in new sequence
 CREATE TABLE tmp_bgw_job_seq_value AS SELECT last_value, is_called FROM _timescaledb_config.bgw_job_id_seq;
 
 DROP TABLE _timescaledb_config.bgw_job;
 
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_config.bgw_job_id_seq MINVALUE 1000;
+CREATE SEQUENCE _timescaledb_config.bgw_job_id_seq MINVALUE 1000;
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job_id_seq', '');
 SELECT setval('_timescaledb_config.bgw_job_id_seq', last_value, is_called) FROM tmp_bgw_job_seq_value;
 DROP TABLE tmp_bgw_job_seq_value;
 
-CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
+CREATE TABLE _timescaledb_config.bgw_job (
     id                  INTEGER PRIMARY KEY DEFAULT nextval('_timescaledb_config.bgw_job_id_seq'),
     application_name    NAME        NOT NULL,
     schedule_interval   INTERVAL    NOT NULL,
     max_runtime         INTERVAL    NOT NULL,
     max_retries         INTEGER     NOT NULL,
@@ -295,11 +295,11 @@ CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
     hypertable_id       INTEGER REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
     config              JSONB
 );
 
 ALTER SEQUENCE _timescaledb_config.bgw_job_id_seq OWNED BY _timescaledb_config.bgw_job.id;
-CREATE INDEX IF NOT EXISTS bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job(proc_schema,proc_name,hypertable_id);
+CREATE INDEX bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job(proc_schema,proc_name,hypertable_id);
 
 INSERT INTO _timescaledb_config.bgw_job SELECT id, application_name, schedule_interval, max_runtime, max_retries, retry_period, proc_schema, proc_name, owner, scheduled, hypertable_id, config FROM _timescaledb_config.bgw_job_tmp ORDER BY id;
 DROP TABLE _timescaledb_config.bgw_job_tmp;
 ALTER TABLE _timescaledb_internal.bgw_job_stat ADD CONSTRAINT bgw_job_stat_job_id_fkey FOREIGN KEY(job_id) REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE;
 ALTER TABLE _timescaledb_internal.bgw_policy_chunk_stats ADD CONSTRAINT bgw_policy_chunk_stats_job_id_fkey FOREIGN KEY(job_id) REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE;
@@ -365,11 +365,11 @@ CREATE TABLE _timescaledb_catalog.continuous_agg_tmp AS SELECT * FROM _timescale
 ALTER TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log DROP CONSTRAINT continuous_aggs_materialization_invalid_materialization_id_fkey;
 
 ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_agg;
 DROP TABLE _timescaledb_catalog.continuous_agg;
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
+CREATE TABLE _timescaledb_catalog.continuous_agg (
     mat_hypertable_id INTEGER PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
     raw_hypertable_id INTEGER NOT NULL REFERENCES  _timescaledb_catalog.hypertable(id) ON DELETE CASCADE,
     user_view_schema NAME NOT NULL,
     user_view_name NAME NOT NULL,
     partial_view_schema NAME NOT NULL,
@@ -380,11 +380,11 @@ CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
     materialized_only BOOL NOT NULL DEFAULT false,
     UNIQUE(user_view_schema, user_view_name),
     UNIQUE(partial_view_schema, partial_view_name)
 );
 
-CREATE INDEX IF NOT EXISTS continuous_agg_raw_hypertable_id_idx
+CREATE INDEX continuous_agg_raw_hypertable_id_idx
     ON _timescaledb_catalog.continuous_agg(raw_hypertable_id);
 
 SELECT pg_catalog.pg_extension_config_dump('_timescaledb_catalog.continuous_agg', '');
 GRANT SELECT ON _timescaledb_catalog.continuous_agg TO PUBLIC;
 
diff --git a/sql/updates/2.0.0-rc3--2.0.0-rc4.sql b/sql/updates/2.0.0-rc3--2.0.0-rc4.sql
index f3710714..2aec6c8b 100644
--- a/sql/updates/2.0.0-rc3--2.0.0-rc4.sql
+++ b/sql/updates/2.0.0-rc3--2.0.0-rc4.sql
@@ -33,14 +33,14 @@ CREATE TABLE tmp_hypertable_seq_value AS
 SELECT last_value, is_called FROM _timescaledb_catalog.hypertable_id_seq;
 ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.hypertable;
 ALTER EXTENSION timescaledb DROP SEQUENCE _timescaledb_catalog.hypertable_id_seq;
 DROP TABLE _timescaledb_catalog.hypertable;
 
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
+CREATE SEQUENCE _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
 
 -- now create table without self referential foreign key
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable(
+CREATE TABLE _timescaledb_catalog.hypertable(
   id INTEGER PRIMARY KEY DEFAULT nextval('_timescaledb_catalog.hypertable_id_seq'),
   schema_name name NOT NULL CHECK (schema_name != '_timescaledb_catalog'),
   table_name name NOT NULL,
   associated_schema_name name NOT NULL,
   associated_table_prefix name NOT NULL,
diff --git a/sql/updates/2.2.1--2.3.0.sql b/sql/updates/2.2.1--2.3.0.sql
index 2598b564..cf97950f 100644
--- a/sql/updates/2.2.1--2.3.0.sql
+++ b/sql/updates/2.2.1--2.3.0.sql
@@ -27,14 +27,14 @@ DROP VIEW IF EXISTS _timescaledb_internal.compressed_chunk_stats;
 
 ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.chunk;
 ALTER EXTENSION timescaledb DROP SEQUENCE _timescaledb_catalog.chunk_id_seq;
 DROP TABLE _timescaledb_catalog.chunk;
 
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_id_seq MINVALUE 1;
+CREATE SEQUENCE _timescaledb_catalog.chunk_id_seq MINVALUE 1;
 
 -- now create table without self referential foreign key
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk (
+CREATE TABLE _timescaledb_catalog.chunk (
   id integer PRIMARY KEY DEFAULT nextval('_timescaledb_catalog.chunk_id_seq'),
   hypertable_id int NOT NULL REFERENCES _timescaledb_catalog.hypertable (id),
   schema_name name NOT NULL,
   table_name name NOT NULL,
   compressed_chunk_id integer ,
@@ -54,12 +54,12 @@ SELECT id, hypertable_id, schema_name, table_name,
     END
   )
 FROM _timescaledb_catalog.chunk_tmp;
 
 --add indexes to the chunk table
-CREATE INDEX IF NOT EXISTS chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
-CREATE INDEX IF NOT EXISTS chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
+CREATE INDEX chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
+CREATE INDEX chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
 
 ALTER SEQUENCE _timescaledb_catalog.chunk_id_seq OWNED BY _timescaledb_catalog.chunk.id;
 SELECT setval('_timescaledb_catalog.chunk_id_seq', last_value, is_called) FROM tmp_chunk_seq_value;
 
 -- add self referential foreign key
diff --git a/sql/updates/2.3.1--2.4.0.sql b/sql/updates/2.3.1--2.4.0.sql
index 98596cdf..4c00d560 100644
--- a/sql/updates/2.3.1--2.4.0.sql
+++ b/sql/updates/2.3.1--2.4.0.sql
@@ -1,15 +1,15 @@
-CREATE SCHEMA IF NOT EXISTS timescaledb_experimental;
+CREATE SCHEMA timescaledb_experimental;
 GRANT USAGE ON SCHEMA timescaledb_experimental TO PUBLIC;
 DROP FUNCTION IF EXISTS _timescaledb_internal.block_new_chunks;
 DROP FUNCTION IF EXISTS _timescaledb_internal.allow_new_chunks;
 DROP FUNCTION IF EXISTS _timescaledb_internal.refresh_continuous_aggregate;
 DROP FUNCTION IF EXISTS _timescaledb_internal.create_chunk;
 
-CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
+CREATE SEQUENCE _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
 
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation (
+CREATE TABLE _timescaledb_catalog.chunk_copy_operation (
   operation_id name PRIMARY KEY, -- the publisher/subscriber identifier used
   backend_pid integer NOT NULL, -- the pid of the backend running this activity
   completed_stage name NOT NULL, -- the completed stage/step
   time_start timestamptz NOT NULL DEFAULT NOW(), -- start time of the activity
   chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk (id) ON DELETE CASCADE,
diff --git a/sql/updates/latest-dev.sql b/sql/updates/latest-dev.sql
index 817e0eb6..0b4a6396 100644
--- a/sql/updates/latest-dev.sql
+++ b/sql/updates/latest-dev.sql
@@ -1,11 +1,11 @@
 DROP FUNCTION IF EXISTS recompress_chunk;
 DROP FUNCTION IF EXISTS delete_data_node;
 DROP FUNCTION IF EXISTS get_telemetry_report;
 
 -- Also see the comments for ContinuousAggsBucketFunction structure.
-CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_bucket_function(
+CREATE TABLE _timescaledb_catalog.continuous_aggs_bucket_function(
   mat_hypertable_id integer PRIMARY KEY REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
   -- The schema of the function. Equals TRUE for "timescaledb_experimental", FALSE otherwise.
   experimental bool NOT NULL,
   -- Name of the bucketing function, e.g. "time_bucket" or "time_bucket_ng"
   name text NOT NULL,
diff --git a/sql/updates/reverse-dev.sql b/sql/updates/reverse-dev.sql
index 7709d6fd..7995a233 100644
--- a/sql/updates/reverse-dev.sql
+++ b/sql/updates/reverse-dev.sql
@@ -13,72 +13,72 @@ BEGIN
     SELECT array_length(caggs, 1) INTO caggs_nr;
     IF caggs_nr > 0 THEN
         RAISE EXCEPTION 'Downgrade is impossible since % continuous aggregates exist which use variable buckets: %', caggs_nr, caggs
               USING HINT = 'Remove the corresponding continuous aggregates manually before downgrading';
     END IF;
+END
+$$ LANGUAGE 'plpgsql';
 
-    -- It's safe to drop the table.
-    -- ALTER EXTENSION is required to revert the effect of pg_extension_config_dump()
-    -- See "The list of tables configured to be dumped" test in test/sql/updates/post.catalog.sql
-    ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_aggs_bucket_function;
+-- It's safe to drop the table.
+-- ALTER EXTENSION is required to revert the effect of pg_extension_config_dump()
+-- See "The list of tables configured to be dumped" test in test/sql/updates/post.catalog.sql
+ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_aggs_bucket_function;
 
-    -- Actually drop the table.
-    -- ALTER EXTENSION only removes the table from the extension but doesn't drop it.
-    DROP TABLE IF EXISTS _timescaledb_catalog.continuous_aggs_bucket_function;
+-- Actually drop the table.
+-- ALTER EXTENSION only removes the table from the extension but doesn't drop it.
+DROP TABLE _timescaledb_catalog.continuous_aggs_bucket_function;
 
-    -- Drop overloaded versions of invalidation_process_hypertable_log() and invalidation_process_cagg_log()
-    -- with bucket_functions argument.
+-- Drop overloaded versions of invalidation_process_hypertable_log() and invalidation_process_cagg_log()
+-- with bucket_functions argument.
 
-    ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_hypertable_log(
-        mat_hypertable_id INTEGER,
-        raw_hypertable_id INTEGER,
-        dimtype REGTYPE,
-        mat_hypertable_ids INTEGER[],
-        bucket_widths BIGINT[],
-        max_bucket_widths BIGINT[],
-        bucket_functions TEXT[]
-    );
+ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_hypertable_log(
+    mat_hypertable_id INTEGER,
+    raw_hypertable_id INTEGER,
+    dimtype REGTYPE,
+    mat_hypertable_ids INTEGER[],
+    bucket_widths BIGINT[],
+    max_bucket_widths BIGINT[],
+    bucket_functions TEXT[]
+);
 
-    DROP FUNCTION IF EXISTS _timescaledb_internal.invalidation_process_hypertable_log(
-        mat_hypertable_id INTEGER,
-        raw_hypertable_id INTEGER,
-        dimtype REGTYPE,
-        mat_hypertable_ids INTEGER[],
-        bucket_widths BIGINT[],
-        max_bucket_widths BIGINT[],
-        bucket_functions TEXT[]
-    );
+DROP FUNCTION _timescaledb_internal.invalidation_process_hypertable_log(
+    mat_hypertable_id INTEGER,
+    raw_hypertable_id INTEGER,
+    dimtype REGTYPE,
+    mat_hypertable_ids INTEGER[],
+    bucket_widths BIGINT[],
+    max_bucket_widths BIGINT[],
+    bucket_functions TEXT[]
+);
 
-    ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_cagg_log(
-        mat_hypertable_id INTEGER,
-        raw_hypertable_id INTEGER,
-        dimtype REGTYPE,
-        window_start BIGINT,
-        window_end BIGINT,
-        mat_hypertable_ids INTEGER[],
-        bucket_widths BIGINT[],
-        max_bucket_widths BIGINT[],
-        bucket_functions TEXT[],
-        OUT ret_window_start BIGINT,
-        OUT ret_window_end BIGINT
-    );
+ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_cagg_log(
+    mat_hypertable_id INTEGER,
+    raw_hypertable_id INTEGER,
+    dimtype REGTYPE,
+    window_start BIGINT,
+    window_end BIGINT,
+    mat_hypertable_ids INTEGER[],
+    bucket_widths BIGINT[],
+    max_bucket_widths BIGINT[],
+    bucket_functions TEXT[],
+    OUT ret_window_start BIGINT,
+    OUT ret_window_end BIGINT
+);
 
-    DROP FUNCTION IF EXISTS _timescaledb_internal.invalidation_process_cagg_log(
-        mat_hypertable_id INTEGER,
-        raw_hypertable_id INTEGER,
-        dimtype REGTYPE,
-        window_start BIGINT,
-        window_end BIGINT,
-        mat_hypertable_ids INTEGER[],
-        bucket_widths BIGINT[],
-        max_bucket_widths BIGINT[],
-        bucket_functions TEXT[],
-        OUT ret_window_start BIGINT,
-        OUT ret_window_end BIGINT
-    );
-END
-$$ LANGUAGE 'plpgsql';
+DROP FUNCTION _timescaledb_internal.invalidation_process_cagg_log(
+    mat_hypertable_id INTEGER,
+    raw_hypertable_id INTEGER,
+    dimtype REGTYPE,
+    window_start BIGINT,
+    window_end BIGINT,
+    mat_hypertable_ids INTEGER[],
+    bucket_widths BIGINT[],
+    max_bucket_widths BIGINT[],
+    bucket_functions TEXT[],
+    OUT ret_window_start BIGINT,
+    OUT ret_window_end BIGINT
+);
 
 --undo compression feature for caggs
 --check that all continuous aggregates have compression disabled
 --this check is sufficient as we cannot have compressed chunks, if
 -- compression is disabled
@@ -86,23 +86,23 @@ DO $$
 DECLARE
   cagg_name NAME;
   cnt INTEGER := 0;
 BEGIN
     FOR cagg_name IN
-        SELECT view_name, 
-               materialization_hypertable_schema ,
-               materialization_hypertable_name   
+        SELECT view_name,
+               materialization_hypertable_schema,
+               materialization_hypertable_name
         FROM timescaledb_information.continuous_aggregates
         WHERE compression_enabled is TRUE
     LOOP
         RAISE NOTICE 'Compression is enabled for continuous aggregate: %', cagg_name;
-        cnt := cnt + 1; 
+        cnt := cnt + 1;
     END LOOP;
     IF cnt > 0 THEN
        RAISE EXCEPTION 'Cannot downgrade as compression is enabled for continuous aggregates' 
             USING DETAIL = 'Please disable compression on all continuous aggregates before downgrading',
-            HINT = ('To disable compression, call decompress_chunk to decompress chunks, then drop any existing compression policy on the continuous aggregate, and finally run ALTER MATERIALIZED VIEW % SET timescaledb.compress = ''false'' ');
+            HINT = 'To disable compression, call decompress_chunk to decompress chunks, then drop any existing compression policy on the continuous aggregate, and finally run ALTER MATERIALIZED VIEW % SET timescaledb.compress = ''false'' ';
     END IF;
 END $$;
 
 -- revert changes to continuous aggregates view definition
-DROP VIEW IF EXISTS timescaledb_information.continuous_aggregates;
+DROP VIEW timescaledb_information.continuous_aggregates;
diff --git a/sql/views.sql b/sql/views.sql
index bd00b4c0..469eb63d 100644
--- a/sql/views.sql
+++ b/sql/views.sql
@@ -1,11 +1,9 @@
 -- This file and its contents are licensed under the Apache License 2.0.
 -- Please see the included NOTICE for copyright information and
 -- LICENSE-APACHE for a copy of the license.
 
-CREATE SCHEMA IF NOT EXISTS timescaledb_information;
-
 -- Convenience view to list all hypertables
 CREATE OR REPLACE VIEW timescaledb_information.hypertables AS
 SELECT ht.schema_name AS hypertable_schema,
   ht.table_name AS hypertable_name,
   t.tableowner AS owner,
@@ -296,8 +294,6 @@ WHERE segq.hypertable_id = ht.id
     OR segq.orderby_column_index IS NOT NULL)
 ORDER BY table_name,
   segmentby_column_index,
   orderby_column_index;
 
-GRANT USAGE ON SCHEMA timescaledb_information TO PUBLIC;
-
 GRANT SELECT ON ALL TABLES IN SCHEMA timescaledb_information TO PUBLIC;
diff --git a/test/expected/extension_scripts.out b/test/expected/extension_scripts.out
new file mode 100644
index 00000000..f1c35d8a
--- /dev/null
+++ b/test/expected/extension_scripts.out
@@ -0,0 +1,56 @@
+-- This file and its contents are licensed under the Apache License 2.0.
+-- Please see the included NOTICE for copyright information and
+-- LICENSE-APACHE for a copy of the license.
+\c :TEST_DBNAME :ROLE_SUPERUSER
+DROP EXTENSION timescaledb;
+-- test that installation script errors when any of our internal schemas already exists
+\set ON_ERROR_STOP 0
+CREATE SCHEMA _timescaledb_catalog;
+CREATE EXTENSION timescaledb;
+ERROR:  schema "_timescaledb_catalog" already exists
+DROP SCHEMA _timescaledb_catalog;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA _timescaledb_internal;
+CREATE EXTENSION timescaledb;
+ERROR:  schema "_timescaledb_internal" already exists
+DROP SCHEMA _timescaledb_internal;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA _timescaledb_cache;
+CREATE EXTENSION timescaledb;
+ERROR:  schema "_timescaledb_cache" already exists
+DROP SCHEMA _timescaledb_cache;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA _timescaledb_config;
+CREATE EXTENSION timescaledb;
+ERROR:  schema "_timescaledb_config" already exists
+DROP SCHEMA _timescaledb_config;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA timescaledb_experimental;
+CREATE EXTENSION timescaledb;
+ERROR:  schema "timescaledb_experimental" already exists
+DROP SCHEMA timescaledb_experimental;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA timescaledb_information;
+CREATE EXTENSION timescaledb;
+ERROR:  schema "timescaledb_information" already exists
+DROP SCHEMA timescaledb_information;
+-- test that installation script errors when any of the function in public schema already exists
+-- we don't test every public function but just a few common ones
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE FUNCTION get_telemetry_report() RETURNS JSONB LANGUAGE SQL AS $$ SELECT NULL::jsonb; $$;
+CREATE EXTENSION timescaledb;
+ERROR:  function "get_telemetry_report" already exists with same argument types
+DROP FUNCTION get_telemetry_report;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE FUNCTION time_bucket(int,int) RETURNS int LANGUAGE SQL AS $$ SELECT 1::int; $$;
+CREATE EXTENSION timescaledb;
+ERROR:  function "time_bucket" already exists with same argument types
+DROP FUNCTION time_bucket;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE OR REPLACE FUNCTION show_chunks(relation regclass, older_than "any" DEFAULT NULL, newer_than "any" DEFAULT NULL) RETURNS SETOF regclass language internal as 'pg_partition_ancestors';
+CREATE EXTENSION timescaledb;
+ERROR:  function "show_chunks" already exists with same argument types
+DROP FUNCTION show_chunks;
+\c :TEST_DBNAME :ROLE_SUPERUSER
+SET client_min_messages TO ERROR;
+CREATE EXTENSION timescaledb;
diff --git a/test/sql/CMakeLists.txt b/test/sql/CMakeLists.txt
index 6dfdd771..4fae5d6d 100644
--- a/test/sql/CMakeLists.txt
+++ b/test/sql/CMakeLists.txt
@@ -17,10 +17,11 @@ set(TEST_FILES
     drop_owned.sql
     drop_rename_hypertable.sql
     drop_schema.sql
     dump_meta.sql
     extension.sql
+    extension_scripts.sql
     generated_as_identity.sql
     grant_hypertable.sql
     hash.sql
     histogram_test.sql
     index.sql
diff --git a/test/sql/extension_scripts.sql b/test/sql/extension_scripts.sql
new file mode 100644
index 00000000..bf4f6eee
--- /dev/null
+++ b/test/sql/extension_scripts.sql
@@ -0,0 +1,59 @@
+-- This file and its contents are licensed under the Apache License 2.0.
+-- Please see the included NOTICE for copyright information and
+-- LICENSE-APACHE for a copy of the license.
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+DROP EXTENSION timescaledb;
+
+-- test that installation script errors when any of our internal schemas already exists
+\set ON_ERROR_STOP 0
+CREATE SCHEMA _timescaledb_catalog;
+CREATE EXTENSION timescaledb;
+DROP SCHEMA _timescaledb_catalog;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA _timescaledb_internal;
+CREATE EXTENSION timescaledb;
+DROP SCHEMA _timescaledb_internal;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA _timescaledb_cache;
+CREATE EXTENSION timescaledb;
+DROP SCHEMA _timescaledb_cache;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA _timescaledb_config;
+CREATE EXTENSION timescaledb;
+DROP SCHEMA _timescaledb_config;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA timescaledb_experimental;
+CREATE EXTENSION timescaledb;
+DROP SCHEMA timescaledb_experimental;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE SCHEMA timescaledb_information;
+CREATE EXTENSION timescaledb;
+DROP SCHEMA timescaledb_information;
+
+-- test that installation script errors when any of the function in public schema already exists
+-- we don't test every public function but just a few common ones
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE FUNCTION get_telemetry_report() RETURNS JSONB LANGUAGE SQL AS $$ SELECT NULL::jsonb; $$;
+CREATE EXTENSION timescaledb;
+DROP FUNCTION get_telemetry_report;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE FUNCTION time_bucket(int,int) RETURNS int LANGUAGE SQL AS $$ SELECT 1::int; $$;
+CREATE EXTENSION timescaledb;
+DROP FUNCTION time_bucket;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+CREATE OR REPLACE FUNCTION show_chunks(relation regclass, older_than "any" DEFAULT NULL, newer_than "any" DEFAULT NULL) RETURNS SETOF regclass language internal as 'pg_partition_ancestors';
+CREATE EXTENSION timescaledb;
+DROP FUNCTION show_chunks;
+
+\c :TEST_DBNAME :ROLE_SUPERUSER
+SET client_min_messages TO ERROR;
+CREATE EXTENSION timescaledb;
+
diff --git a/tsl/test/expected/data_node_bootstrap.out b/tsl/test/expected/data_node_bootstrap.out
index 40f2d277..1e77181a 100644
--- a/tsl/test/expected/data_node_bootstrap.out
+++ b/tsl/test/expected/data_node_bootstrap.out
@@ -186,12 +186,11 @@ SELECT * FROM delete_data_node('bootstrap_test');
 -- added.
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 CREATE DATABASE bootstrap_test OWNER :ROLE_CLUSTER_SUPERUSER;
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => false);
    node_name    |   host    | port  |    database    | node_created | database_created | extension_created 
@@ -212,12 +211,11 @@ DROP DATABASE bootstrap_test;
 -- bootstrap a database with an extension already installed.
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 CREATE DATABASE bootstrap_test OWNER :ROLE_CLUSTER_SUPERUSER;
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => true);
 NOTICE:  database "bootstrap_test" already exists on data node, skipping
@@ -273,12 +271,11 @@ CREATE DATABASE bootstrap_test
    LC_CTYPE 'C'
    TEMPLATE template0
       OWNER :ROLE_CLUSTER_SUPERUSER;
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 \set ON_ERROR_STOP 0
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => false);
@@ -291,12 +288,11 @@ CREATE DATABASE bootstrap_test
    LC_CTYPE :ctype
    TEMPLATE template0
       OWNER :ROLE_CLUSTER_SUPERUSER;
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 \set ON_ERROR_STOP 0
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => false);
@@ -309,12 +305,11 @@ CREATE DATABASE bootstrap_test
    LC_CTYPE :other_locale
    TEMPLATE template0
       OWNER :ROLE_CLUSTER_SUPERUSER;
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 \set ON_ERROR_STOP 0
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => false);
@@ -396,12 +391,12 @@ DROP DATABASE bootstrap_test;
 -- generated.
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 CREATE DATABASE access_node OWNER :ROLE_CLUSTER_SUPERUSER;
 \c access_node :ROLE_CLUSTER_SUPERUSER
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
+CREATE EXTENSION timescaledb WITH SCHEMA ts_non_default CASCADE;
 SET client_min_messages TO NOTICE;
 -- Show the schema for the extension to verify that it is not public.
 SELECT extname FROM pg_extension WHERE extname = 'timescaledb';
    extname   
 -------------
@@ -410,22 +405,22 @@ SELECT extname FROM pg_extension WHERE extname = 'timescaledb';
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 CREATE DATABASE bootstrap_test OWNER :ROLE_CLUSTER_SUPERUSER;
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
+CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO NOTICE;
 \c access_node :ROLE_CLUSTER_SUPERUSER
 -- Add data node and delete it under error suppression. We want to
 -- avoid later tests to have random failures because the add succeeds.
 \set ON_ERROR_STOP 0
-SELECT * FROM _timescaledb_catalog.add_data_node(
+SELECT * FROM ts_non_default.add_data_node(
        'bootstrap_test', host => 'localhost',
        database => 'bootstrap_test', bootstrap => true);
 NOTICE:  database "bootstrap_test" already exists on data node, skipping
-ERROR:  schema "_timescaledb_catalog" already exists in database, aborting
-SELECT * FROM _timescaledb_catalog.delete_data_node('bootstrap_test');
+ERROR:  schema "ts_non_default" already exists in database, aborting
+SELECT * FROM ts_non_default.delete_data_node('bootstrap_test');
 ERROR:  server "bootstrap_test" does not exist
 \set ON_ERROR_STOP 1
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 DROP DATABASE bootstrap_test;
 DROP DATABASE access_node;
diff --git a/tsl/test/sql/data_node_bootstrap.sql b/tsl/test/sql/data_node_bootstrap.sql
index 42871b6d..331f8090 100644
--- a/tsl/test/sql/data_node_bootstrap.sql
+++ b/tsl/test/sql/data_node_bootstrap.sql
@@ -126,12 +126,11 @@ SELECT * FROM delete_data_node('bootstrap_test');
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 CREATE DATABASE bootstrap_test OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => false);
@@ -146,12 +145,11 @@ DROP DATABASE bootstrap_test;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 CREATE DATABASE bootstrap_test OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                             database => 'bootstrap_test', bootstrap => true);
@@ -204,12 +202,11 @@ CREATE DATABASE bootstrap_test
    TEMPLATE template0
       OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 \set ON_ERROR_STOP 0
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
@@ -225,12 +222,11 @@ CREATE DATABASE bootstrap_test
    TEMPLATE template0
       OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 \set ON_ERROR_STOP 0
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
@@ -246,12 +242,11 @@ CREATE DATABASE bootstrap_test
    TEMPLATE template0
       OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE EXTENSION timescaledb;
 SET client_min_messages TO NOTICE;
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
 \set ON_ERROR_STOP 0
 SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
@@ -306,34 +301,34 @@ DROP DATABASE bootstrap_test;
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 CREATE DATABASE access_node OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c access_node :ROLE_CLUSTER_SUPERUSER
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
-CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
+CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
+CREATE EXTENSION timescaledb WITH SCHEMA ts_non_default CASCADE;
 SET client_min_messages TO NOTICE;
 
 -- Show the schema for the extension to verify that it is not public.
 SELECT extname FROM pg_extension WHERE extname = 'timescaledb';
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 CREATE DATABASE bootstrap_test OWNER :ROLE_CLUSTER_SUPERUSER;
 
 \c bootstrap_test :ROLE_CLUSTER_SUPERUSER
 SET client_min_messages TO ERROR;
-CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
+CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
 SET client_min_messages TO NOTICE;
 
 \c access_node :ROLE_CLUSTER_SUPERUSER
 
 -- Add data node and delete it under error suppression. We want to
 -- avoid later tests to have random failures because the add succeeds.
 \set ON_ERROR_STOP 0
-SELECT * FROM _timescaledb_catalog.add_data_node(
+SELECT * FROM ts_non_default.add_data_node(
        'bootstrap_test', host => 'localhost',
        database => 'bootstrap_test', bootstrap => true);
-SELECT * FROM _timescaledb_catalog.delete_data_node('bootstrap_test');
+SELECT * FROM ts_non_default.delete_data_node('bootstrap_test');
 \set ON_ERROR_STOP 1
 
 \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
 DROP DATABASE bootstrap_test;
 DROP DATABASE access_node;

Vulnerable code:     name: Check formatting, license and git hooks
  set(_downgrade_POST_FILES ${SOURCE_FILES} ${SET_POST_UPDATE_STAGE}
    pre_install/bgw_scheduler_startup.sql
    pre_install/types.functions.sql
    pre_install/fdw_functions.sql
    cache.sql
	telemetry.sql)
    # Make list of files into string of files separated by "+" to make Windows
    # copy concatenate them
    file(TO_NATIVE_PATH "${SRC_FILE_LIST}" SRC_FILE_LIST_NATIVE)
    string(REPLACE ";" ";+;" SQL_LIST_JOINED "${SRC_FILE_LIST_NATIVE}")
    file(TO_NATIVE_PATH "${OUTPUT_FILE}" OUTPUT_FILE_NATIVE)
    set(CAT_CMD copy /B /y ${SQL_LIST_JOINED} "\"${OUTPUT_FILE_NATIVE}\"" >NUL)
    set(CAT_CMD cat ${SRC_FILE_LIST} > ${OUTPUT_FILE})
    COMMAND ${CAT_CMD}
cat_files(
      "${PRE_FILES};${CURR_MOD_FILES};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
    cat_files("${PRE_FILES};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
              ${UPDATE_SCRIPT})
CREATE OR REPLACE FUNCTION _timescaledb_internal.restart_background_workers()
RETURNS BOOL 
AS '@LOADER_PATHNAME@', 'ts_bgw_db_workers_restart'
LANGUAGE C VOLATILE;

CREATE TABLE IF NOT EXISTS  _timescaledb_cache.cache_inval_hypertable();
CREATE TABLE IF NOT EXISTS  _timescaledb_cache.cache_inval_bgw_job();
CREATE TABLE IF NOT EXISTS  _timescaledb_cache.cache_inval_extension();
CREATE SCHEMA IF NOT EXISTS _timescaledb_catalog;
CREATE SCHEMA IF NOT EXISTS _timescaledb_internal;
CREATE SCHEMA IF NOT EXISTS _timescaledb_cache;
CREATE SCHEMA IF NOT EXISTS _timescaledb_config;
CREATE SCHEMA IF NOT EXISTS timescaledb_experimental;
GRANT USAGE ON SCHEMA _timescaledb_cache, _timescaledb_catalog, _timescaledb_internal, _timescaledb_config TO PUBLIC;
GRANT USAGE ON SCHEMA timescaledb_experimental TO PUBLIC;
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_data_node (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.tablespace (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.dimension (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.dimension_slice (
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_id_seq MINVALUE 1;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk (
CREATE INDEX IF NOT EXISTS chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
CREATE INDEX IF NOT EXISTS chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_constraint (
CREATE INDEX IF NOT EXISTS chunk_constraint_chunk_id_dimension_slice_id_idx ON _timescaledb_catalog.chunk_constraint (chunk_id, dimension_slice_id);
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_constraint_name;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_index (
CREATE INDEX IF NOT EXISTS chunk_index_hypertable_id_hypertable_index_name_idx ON _timescaledb_catalog.chunk_index (hypertable_id, hypertable_index_name);
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_data_node (
CREATE SEQUENCE IF NOT EXISTS _timescaledb_config.bgw_job_id_seq
CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
CREATE INDEX IF NOT EXISTS bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job (proc_schema, proc_name, hypertable_id);
CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_job_stat (
CREATE TABLE IF NOT EXISTS _timescaledb_internal.bgw_policy_chunk_stats (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.metadata (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
CREATE INDEX IF NOT EXISTS continuous_agg_raw_hypertable_id_idx ON _timescaledb_catalog.continuous_agg (raw_hypertable_id);
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_bucket_function(
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_invalidation_threshold (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_materialization_invalidation_log (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.compression_algorithm (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_compression (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.compression_chunk_size (
CREATE INDEX IF NOT EXISTS remote_txn_data_node_name_idx ON _timescaledb_catalog.remote_txn (data_node_name);
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable_data_node (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_data_node (
CREATE INDEX IF NOT EXISTS remote_txn_data_node_name_idx
ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN IF NOT EXISTS numrows_pre_compression BIGINT;
ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN IF NOT EXISTS numrows_post_compression BIGINT;
CREATE SEQUENCE IF NOT EXISTS _timescaledb_config.bgw_job_id_seq MINVALUE 1000;
CREATE TABLE IF NOT EXISTS _timescaledb_config.bgw_job (
CREATE INDEX IF NOT EXISTS bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job(proc_schema,proc_name,hypertable_id);
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_agg (
CREATE INDEX IF NOT EXISTS continuous_agg_raw_hypertable_id_idx
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.hypertable(
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_id_seq MINVALUE 1;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk (
CREATE INDEX IF NOT EXISTS chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
CREATE INDEX IF NOT EXISTS chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
CREATE SCHEMA IF NOT EXISTS timescaledb_experimental;
CREATE SEQUENCE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.chunk_copy_operation (
CREATE TABLE IF NOT EXISTS _timescaledb_catalog.continuous_aggs_bucket_function(
    -- It's safe to drop the table.
    -- ALTER EXTENSION is required to revert the effect of pg_extension_config_dump()
    -- See "The list of tables configured to be dumped" test in test/sql/updates/post.catalog.sql
    ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_aggs_bucket_function;
    -- Actually drop the table.
    -- ALTER EXTENSION only removes the table from the extension but doesn't drop it.
    DROP TABLE IF EXISTS _timescaledb_catalog.continuous_aggs_bucket_function;
    -- Drop overloaded versions of invalidation_process_hypertable_log() and invalidation_process_cagg_log()
    -- with bucket_functions argument.
    ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_hypertable_log(
        mat_hypertable_id INTEGER,
        raw_hypertable_id INTEGER,
        dimtype REGTYPE,
        mat_hypertable_ids INTEGER[],
        bucket_widths BIGINT[],
        max_bucket_widths BIGINT[],
        bucket_functions TEXT[]
    );
    DROP FUNCTION IF EXISTS _timescaledb_internal.invalidation_process_hypertable_log(
        mat_hypertable_id INTEGER,
        raw_hypertable_id INTEGER,
        dimtype REGTYPE,
        mat_hypertable_ids INTEGER[],
        bucket_widths BIGINT[],
        max_bucket_widths BIGINT[],
        bucket_functions TEXT[]
    );
    ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_cagg_log(
        mat_hypertable_id INTEGER,
        raw_hypertable_id INTEGER,
        dimtype REGTYPE,
        window_start BIGINT,
        window_end BIGINT,
        mat_hypertable_ids INTEGER[],
        bucket_widths BIGINT[],
        max_bucket_widths BIGINT[],
        bucket_functions TEXT[],
        OUT ret_window_start BIGINT,
        OUT ret_window_end BIGINT
    );
    DROP FUNCTION IF EXISTS _timescaledb_internal.invalidation_process_cagg_log(
        mat_hypertable_id INTEGER,
        raw_hypertable_id INTEGER,
        dimtype REGTYPE,
        window_start BIGINT,
        window_end BIGINT,
        mat_hypertable_ids INTEGER[],
        bucket_widths BIGINT[],
        max_bucket_widths BIGINT[],
        bucket_functions TEXT[],
        OUT ret_window_start BIGINT,
        OUT ret_window_end BIGINT
    );
END
$$ LANGUAGE 'plpgsql';
        SELECT view_name, 
               materialization_hypertable_schema ,
               materialization_hypertable_name   
        cnt := cnt + 1; 
            HINT = ('To disable compression, call decompress_chunk to decompress chunks, then drop any existing compression policy on the continuous aggregate, and finally run ALTER MATERIALIZED VIEW % SET timescaledb.compress = ''false'' ');
DROP VIEW IF EXISTS timescaledb_information.continuous_aggregates;
CREATE SCHEMA IF NOT EXISTS timescaledb_information;

GRANT USAGE ON SCHEMA timescaledb_information TO PUBLIC;

CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
SELECT * FROM _timescaledb_catalog.add_data_node(
ERROR:  schema "_timescaledb_catalog" already exists in database, aborting
SELECT * FROM _timescaledb_catalog.delete_data_node('bootstrap_test');
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA _timescaledb_catalog CASCADE;
CREATE SCHEMA _timescaledb_catalog AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
SELECT * FROM _timescaledb_catalog.add_data_node(
SELECT * FROM _timescaledb_catalog.delete_data_node('bootstrap_test');
Fixed code:     name: Check formatting, license, update scripts and git hooks
    - name: Check update scripts
      if: always()
      run: ./scripts/check_update_scripts.sh
  set(_downgrade_POST_FILES "${PRE_INSTALL_FUNCTION_FILES};${SOURCE_FILES}" ${SET_POST_UPDATE_STAGE}
    pre_install/cache.sql
# Source files that define functions and need to be rerun in update
set(PRE_INSTALL_FUNCTION_FILES
    pre_install/types.functions.sql
    pre_install/fdw_functions.sql)

	  telemetry.sql
    bgw_startup.sql)
#! /bin/bash

SCRIPT_DIR=$(dirname ${0})
SRC_DIR=$(dirname ${SCRIPT_DIR})

if grep -ir "IF NOT EXISTS" ${SRC_DIR}/sql; then
  cat <<EOF

Update scripts must unconditionally add new objects and fail when the object
already exists otherwise this might enable privilege escalation attacks where
an attacker can precreate objects that get used in later parts of the scripts
instead of the objects created by timescaledb.

EOF
  exit 1
fi
version_files("${PRE_INSTALL_FUNCTION_FILES}"
              PRE_INSTALL_FUNCTION_FILES_VERSIONED)
    set("SRC_ARG" "-DSRC_FILE_LIST=${SRC_FILE_LIST}")
    set("SRC_ARG" "'-DSRC_FILE_LIST=${SRC_FILE_LIST}'")
    COMMAND ${CMAKE_COMMAND} "${SRC_ARG}" "-DOUTPUT_FILE=${OUTPUT_FILE}" -P
            cat.cmake
# Concatenate all files in SRC_FILE_LIST into file OUTPUT_FILE Additionally
# strips out `OR REPLACE` from the generated file as this would allow a
# privilege escalation attack when present in the CREATE script
function(cat_create_files SRC_FILE_LIST OUTPUT_FILE)
  if(WIN32)
    set("SRC_ARG" "-DSRC_FILE_LIST=${SRC_FILE_LIST}")
  else()
    set("SRC_ARG" "'-DSRC_FILE_LIST=${SRC_FILE_LIST}'")
  endif()
  if(WIN32)
    add_custom_command(
      OUTPUT ${OUTPUT_FILE}
      DEPENDS ${SRC_FILE_LIST}
      WORKING_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}
      COMMAND ${CMAKE_COMMAND} "${SRC_ARG}" "-DOUTPUT_FILE=${OUTPUT_FILE}"
              "-DSTRIP_REPLACE=ON" -P cat.cmake
      COMMENT "Generating ${OUTPUT_FILE}")
  else()
    add_custom_command(
      OUTPUT ${OUTPUT_FILE}
      DEPENDS ${SRC_FILE_LIST}
      WORKING_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}
      COMMAND ${CMAKE_COMMAND} "'-DSRC_FILE_LIST=${SRC_FILE_LIST}'"
              "-DOUTPUT_FILE=${OUTPUT_FILE}" "-DSTRIP_REPLACE=ON" -P cat.cmake
      COMMENT "Generating ${OUTPUT_FILE}")
  endif()
endfunction()

cat_create_files(
      "${PRE_FILES};${CURR_MOD_FILES};${PRE_INSTALL_FUNCTION_FILES_VERSIONED};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
    cat_files(
      "${PRE_FILES};${PRE_INSTALL_FUNCTION_FILES_VERSIONED};${SOURCE_FILES_VERSIONED};${POST_FILES_PROCESSED}"
      ${UPDATE_SCRIPT})

IF(POLICY CMP0012)
  CMAKE_POLICY(SET CMP0012 NEW)
ENDIF()

if (NOT DEFINED STRIP_REPLACE)
  set(STRIP_REPLACE OFF)
endif()

function(append_file IN_FILE OUT_FILE STRIP_REPLACE)
  file(READ ${IN_FILE} CONTENTS)
  if (${STRIP_REPLACE})
    string(REPLACE " OR REPLACE " " " CONTENTS "${CONTENTS}")
  endif()
  file(APPEND ${OUT_FILE} "${CONTENTS}")
endfunction()

# Function to concatenate all files in SRC_FILE_LIST into file OUTPUT_FILE
function(cat SRC_FILE_LIST OUTPUT_FILE STRIP_REPLACE)
  file(WRITE ${OUTPUT_FILE} "")
  foreach(SRC_FILE ${SRC_FILE_LIST})
    append_file(${SRC_FILE} ${OUTPUT_FILE} ${STRIP_REPLACE})
  endforeach()
endfunction()

cat("${SRC_FILE_LIST}" "${OUTPUT_FILE}" "${STRIP_REPLACE}")
CREATE TABLE _timescaledb_cache.cache_inval_hypertable();
CREATE TABLE _timescaledb_cache.cache_inval_bgw_job();
CREATE TABLE _timescaledb_cache.cache_inval_extension();
CREATE SCHEMA _timescaledb_catalog;
CREATE SCHEMA _timescaledb_internal;
CREATE SCHEMA _timescaledb_cache;
CREATE SCHEMA _timescaledb_config;
CREATE SCHEMA timescaledb_experimental;
CREATE SCHEMA timescaledb_information;

GRANT USAGE ON SCHEMA _timescaledb_cache, _timescaledb_catalog, _timescaledb_internal, _timescaledb_config, timescaledb_information, timescaledb_experimental TO PUBLIC;
CREATE SEQUENCE _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
CREATE TABLE _timescaledb_catalog.hypertable (
CREATE TABLE _timescaledb_catalog.hypertable_data_node (
CREATE TABLE _timescaledb_catalog.tablespace (
CREATE TABLE _timescaledb_catalog.dimension (
CREATE TABLE _timescaledb_catalog.dimension_slice (
CREATE SEQUENCE _timescaledb_catalog.chunk_id_seq MINVALUE 1;
CREATE TABLE _timescaledb_catalog.chunk (
CREATE INDEX chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
CREATE INDEX chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
CREATE TABLE _timescaledb_catalog.chunk_constraint (
CREATE INDEX chunk_constraint_chunk_id_dimension_slice_id_idx ON _timescaledb_catalog.chunk_constraint (chunk_id, dimension_slice_id);
CREATE SEQUENCE _timescaledb_catalog.chunk_constraint_name;
CREATE TABLE _timescaledb_catalog.chunk_index (
CREATE INDEX chunk_index_hypertable_id_hypertable_index_name_idx ON _timescaledb_catalog.chunk_index (hypertable_id, hypertable_index_name);
CREATE TABLE _timescaledb_catalog.chunk_data_node (
CREATE SEQUENCE _timescaledb_config.bgw_job_id_seq
CREATE TABLE _timescaledb_config.bgw_job (
CREATE INDEX bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job (proc_schema, proc_name, hypertable_id);
CREATE TABLE _timescaledb_internal.bgw_job_stat (
CREATE TABLE _timescaledb_internal.bgw_policy_chunk_stats (
CREATE TABLE _timescaledb_catalog.metadata (
CREATE TABLE _timescaledb_catalog.continuous_agg (
CREATE INDEX continuous_agg_raw_hypertable_id_idx ON _timescaledb_catalog.continuous_agg (raw_hypertable_id);
CREATE TABLE _timescaledb_catalog.continuous_aggs_bucket_function(
CREATE TABLE _timescaledb_catalog.continuous_aggs_invalidation_threshold (
CREATE TABLE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log (
CREATE TABLE _timescaledb_catalog.continuous_aggs_materialization_invalidation_log (
CREATE TABLE _timescaledb_catalog.compression_algorithm (
CREATE TABLE _timescaledb_catalog.hypertable_compression (
CREATE TABLE _timescaledb_catalog.compression_chunk_size (
CREATE INDEX remote_txn_data_node_name_idx ON _timescaledb_catalog.remote_txn (data_node_name);
CREATE SEQUENCE _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
CREATE TABLE _timescaledb_catalog.chunk_copy_operation (
CREATE TABLE _timescaledb_catalog.hypertable_data_node (
CREATE TABLE _timescaledb_catalog.chunk_data_node (
CREATE INDEX remote_txn_data_node_name_idx
ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN numrows_pre_compression BIGINT;
ALTER TABLE IF EXISTS _timescaledb_catalog.compression_chunk_size ADD COLUMN numrows_post_compression BIGINT;
CREATE SEQUENCE _timescaledb_config.bgw_job_id_seq MINVALUE 1000;
CREATE TABLE _timescaledb_config.bgw_job (
CREATE INDEX bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job(proc_schema,proc_name,hypertable_id);
CREATE TABLE _timescaledb_catalog.continuous_agg (
CREATE INDEX continuous_agg_raw_hypertable_id_idx
CREATE SEQUENCE _timescaledb_catalog.hypertable_id_seq MINVALUE 1;
CREATE TABLE _timescaledb_catalog.hypertable(
CREATE SEQUENCE _timescaledb_catalog.chunk_id_seq MINVALUE 1;
CREATE TABLE _timescaledb_catalog.chunk (
CREATE INDEX chunk_hypertable_id_idx ON _timescaledb_catalog.chunk (hypertable_id);
CREATE INDEX chunk_compressed_chunk_id_idx ON _timescaledb_catalog.chunk (compressed_chunk_id);
CREATE SCHEMA timescaledb_experimental;
CREATE SEQUENCE _timescaledb_catalog.chunk_copy_operation_id_seq MINVALUE 1;
CREATE TABLE _timescaledb_catalog.chunk_copy_operation (
CREATE TABLE _timescaledb_catalog.continuous_aggs_bucket_function(
END
$$ LANGUAGE 'plpgsql';
-- It's safe to drop the table.
-- ALTER EXTENSION is required to revert the effect of pg_extension_config_dump()
-- See "The list of tables configured to be dumped" test in test/sql/updates/post.catalog.sql
ALTER EXTENSION timescaledb DROP TABLE _timescaledb_catalog.continuous_aggs_bucket_function;
-- Actually drop the table.
-- ALTER EXTENSION only removes the table from the extension but doesn't drop it.
DROP TABLE _timescaledb_catalog.continuous_aggs_bucket_function;
-- Drop overloaded versions of invalidation_process_hypertable_log() and invalidation_process_cagg_log()
-- with bucket_functions argument.
ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_hypertable_log(
    mat_hypertable_id INTEGER,
    raw_hypertable_id INTEGER,
    dimtype REGTYPE,
    mat_hypertable_ids INTEGER[],
    bucket_widths BIGINT[],
    max_bucket_widths BIGINT[],
    bucket_functions TEXT[]
);
DROP FUNCTION _timescaledb_internal.invalidation_process_hypertable_log(
    mat_hypertable_id INTEGER,
    raw_hypertable_id INTEGER,
    dimtype REGTYPE,
    mat_hypertable_ids INTEGER[],
    bucket_widths BIGINT[],
    max_bucket_widths BIGINT[],
    bucket_functions TEXT[]
);
ALTER EXTENSION timescaledb DROP FUNCTION _timescaledb_internal.invalidation_process_cagg_log(
    mat_hypertable_id INTEGER,
    raw_hypertable_id INTEGER,
    dimtype REGTYPE,
    window_start BIGINT,
    window_end BIGINT,
    mat_hypertable_ids INTEGER[],
    bucket_widths BIGINT[],
    max_bucket_widths BIGINT[],
    bucket_functions TEXT[],
    OUT ret_window_start BIGINT,
    OUT ret_window_end BIGINT
);
DROP FUNCTION _timescaledb_internal.invalidation_process_cagg_log(
    mat_hypertable_id INTEGER,
    raw_hypertable_id INTEGER,
    dimtype REGTYPE,
    window_start BIGINT,
    window_end BIGINT,
    mat_hypertable_ids INTEGER[],
    bucket_widths BIGINT[],
    max_bucket_widths BIGINT[],
    bucket_functions TEXT[],
    OUT ret_window_start BIGINT,
    OUT ret_window_end BIGINT
);
        SELECT view_name,
               materialization_hypertable_schema,
               materialization_hypertable_name
        cnt := cnt + 1;
            HINT = 'To disable compression, call decompress_chunk to decompress chunks, then drop any existing compression policy on the continuous aggregate, and finally run ALTER MATERIALIZED VIEW % SET timescaledb.compress = ''false'' ';
DROP VIEW timescaledb_information.continuous_aggregates;
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
\c :TEST_DBNAME :ROLE_SUPERUSER
DROP EXTENSION timescaledb;
-- test that installation script errors when any of our internal schemas already exists
\set ON_ERROR_STOP 0
CREATE SCHEMA _timescaledb_catalog;
CREATE EXTENSION timescaledb;
ERROR:  schema "_timescaledb_catalog" already exists
DROP SCHEMA _timescaledb_catalog;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA _timescaledb_internal;
CREATE EXTENSION timescaledb;
ERROR:  schema "_timescaledb_internal" already exists
DROP SCHEMA _timescaledb_internal;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA _timescaledb_cache;
CREATE EXTENSION timescaledb;
ERROR:  schema "_timescaledb_cache" already exists
DROP SCHEMA _timescaledb_cache;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA _timescaledb_config;
CREATE EXTENSION timescaledb;
ERROR:  schema "_timescaledb_config" already exists
DROP SCHEMA _timescaledb_config;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA timescaledb_experimental;
CREATE EXTENSION timescaledb;
ERROR:  schema "timescaledb_experimental" already exists
DROP SCHEMA timescaledb_experimental;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA timescaledb_information;
CREATE EXTENSION timescaledb;
ERROR:  schema "timescaledb_information" already exists
DROP SCHEMA timescaledb_information;
-- test that installation script errors when any of the function in public schema already exists
-- we don't test every public function but just a few common ones
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE FUNCTION get_telemetry_report() RETURNS JSONB LANGUAGE SQL AS $$ SELECT NULL::jsonb; $$;
CREATE EXTENSION timescaledb;
ERROR:  function "get_telemetry_report" already exists with same argument types
DROP FUNCTION get_telemetry_report;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE FUNCTION time_bucket(int,int) RETURNS int LANGUAGE SQL AS $$ SELECT 1::int; $$;
CREATE EXTENSION timescaledb;
ERROR:  function "time_bucket" already exists with same argument types
DROP FUNCTION time_bucket;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION show_chunks(relation regclass, older_than "any" DEFAULT NULL, newer_than "any" DEFAULT NULL) RETURNS SETOF regclass language internal as 'pg_partition_ancestors';
CREATE EXTENSION timescaledb;
ERROR:  function "show_chunks" already exists with same argument types
DROP FUNCTION show_chunks;
\c :TEST_DBNAME :ROLE_SUPERUSER
SET client_min_messages TO ERROR;
CREATE EXTENSION timescaledb;
    extension_scripts.sql
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.

\c :TEST_DBNAME :ROLE_SUPERUSER
DROP EXTENSION timescaledb;

-- test that installation script errors when any of our internal schemas already exists
\set ON_ERROR_STOP 0
CREATE SCHEMA _timescaledb_catalog;
CREATE EXTENSION timescaledb;
DROP SCHEMA _timescaledb_catalog;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA _timescaledb_internal;
CREATE EXTENSION timescaledb;
DROP SCHEMA _timescaledb_internal;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA _timescaledb_cache;
CREATE EXTENSION timescaledb;
DROP SCHEMA _timescaledb_cache;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA _timescaledb_config;
CREATE EXTENSION timescaledb;
DROP SCHEMA _timescaledb_config;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA timescaledb_experimental;
CREATE EXTENSION timescaledb;
DROP SCHEMA timescaledb_experimental;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA timescaledb_information;
CREATE EXTENSION timescaledb;
DROP SCHEMA timescaledb_information;

-- test that installation script errors when any of the function in public schema already exists
-- we don't test every public function but just a few common ones
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE FUNCTION get_telemetry_report() RETURNS JSONB LANGUAGE SQL AS $$ SELECT NULL::jsonb; $$;
CREATE EXTENSION timescaledb;
DROP FUNCTION get_telemetry_report;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE FUNCTION time_bucket(int,int) RETURNS int LANGUAGE SQL AS $$ SELECT 1::int; $$;
CREATE EXTENSION timescaledb;
DROP FUNCTION time_bucket;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION show_chunks(relation regclass, older_than "any" DEFAULT NULL, newer_than "any" DEFAULT NULL) RETURNS SETOF regclass language internal as 'pg_partition_ancestors';
CREATE EXTENSION timescaledb;
DROP FUNCTION show_chunks;

\c :TEST_DBNAME :ROLE_SUPERUSER
SET client_min_messages TO ERROR;
CREATE EXTENSION timescaledb;

CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA ts_non_default CASCADE;
CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
SELECT * FROM ts_non_default.add_data_node(
ERROR:  schema "ts_non_default" already exists in database, aborting
SELECT * FROM ts_non_default.delete_data_node('bootstrap_test');
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE EXTENSION timescaledb;
CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
CREATE EXTENSION timescaledb WITH SCHEMA ts_non_default CASCADE;
CREATE SCHEMA ts_non_default AUTHORIZATION :ROLE_CLUSTER_SUPERUSER;
SELECT * FROM ts_non_default.add_data_node(
SELECT * FROM ts_non_default.delete_data_node('bootstrap_test');