/*
*
* Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
 * SPDX-License-Identifier: Apache-2.0
*/
\pset pager off
-- ensure our current role is a superuser
SELECT rolsuper FROM pg_roles WHERE rolname = CURRENT_USER;
 rolsuper 
----------
 t
(1 row)

CREATE EXTENSION pg_tle;
-- create an unprivileged role that is attempting to elevate privileges
CREATE ROLE bad_actor NOSUPERUSER;
-- this would be a trojan attack through the comment argument. this should fail.
SELECT pgtle.install_extension
(
 'test_hax',
 '1.0',
 $$hax$_pgtle_i_$ $_pgtle_o_$ LANGUAGE SQL; ALTER ROLE bad_actor SUPERUSER; CREATE OR REPLACE FUNCTION haha() RETURNS TEXT AS $_pgtle_o_$ SELECT $_pgtle_i_$ $$,
$_pgtle_$
  CREATE OR REPLACE FUNCTION basic_func()
  RETURNS INT AS $$
    SELECT 1;
  $$ LANGUAGE SQL;
$_pgtle_$
);
ERROR:  invalid character in extension definition
DETAIL:  Use of string delimiters "$_pgtle_o_$" and "$_pgtle_i_$" are forbidden in extension definitions.
HINT:  This may be an attempt at a SQL injection attack. Please verify your installation file.
-- verify that the user did not elevate privileges
SELECT rolsuper FROM pg_roles WHERE rolname = 'bad_actor';
 rolsuper 
----------
 f
(1 row)

-- this would be a trojan attack through the ext argument. this should fail.
SELECT pgtle.install_extension
(
 'test_hax',
 '1.0',
 'hax',
$_pgtle_$ $_pgtle_i_$ $_pgtle_o_$ ALTER ROLE bad_actor SUPERUSER; $_pgtle_o_$ $_pgtle_i_$
  CREATE OR REPLACE FUNCTION basic_func()
  RETURNS INT AS $$
    SELECT 1;
  $$ LANGUAGE SQL;
$_pgtle_$
);
ERROR:  invalid character in extension definition
DETAIL:  Use of string delimiters "$_pgtle_o_$" and "$_pgtle_i_$" are forbidden in extension definitions.
HINT:  This may be an attempt at a SQL injection attack. Please verify your installation file.
-- verify that the user did not elevate privileges
SELECT rolsuper FROM pg_roles WHERE rolname = 'bad_actor';
 rolsuper 
----------
 f
(1 row)

-- install a legit extension. then try to create an update path that has
-- a trojan.
SELECT pgtle.install_extension
(
 'legit_100',
 '1.0',
 'legit',
$_pgtle_$
  CREATE FUNCTION basic_func()
  RETURNS INT AS $$
    SELECT 1;
  $$ LANGUAGE SQL;
$_pgtle_$
);
 install_extension 
-------------------
 t
(1 row)

SELECT pgtle.install_update_path
(
 'legit_100',
 '1.0',
 '1.1',
$_pgtle_$ $_pgtle_i_$ ; $_pgtle_o_$ LANGUAGE SQL; ALTER ROLE bad_actor SUPERUSER; CREATE FUNCTiON hax() RETURNS text AS $_pgtle_o_$ SELECT $_pgtle_i_$
 CREATE OR REPLACE FUNCTION basic_func()
 RETURNS INT AS $$
   SELECT 2;
 $$ LANGUAGE SQL;
$_pgtle_$
);
ERROR:  invalid character in extension update definition
DETAIL:  Use of string delimiters "$_pgtle_o_$" and "$_pgtle_i_$" are forbidden in extension definitions.
HINT:  This may be an attempt at a SQL injection attack. Please verify your installation file.
-- verify that the user did not elevate privileges
SELECT rolsuper FROM pg_roles WHERE rolname = 'bad_actor';
 rolsuper 
----------
 f
(1 row)

-- remove the legit extension
SELECT pgtle.uninstall_extension('legit_100');
 uninstall_extension 
---------------------
 t
(1 row)

-- grant the pgtle_admin role to the bad_actor and try to install the extension
GRANT pgtle_admin TO bad_actor;
-- become the bad_actor
SET SESSION AUTHORIZATION bad_actor;
-- attempt to install the extension with an injection in the comments and error
SELECT pgtle.install_extension
(
 'test_hax',
 '1.0',
 $$hax$_pgtle_i_$ $_pgtle_o_$ LANGUAGE SQL; ALTER ROLE bad_actor SUPERUSER; CREATE OR REPLACE FUNCTION haha() RETURNS TEXT AS $_pgtle_o_$ SELECT $_pgtle_i_$ $$,
$_pgtle_$
  CREATE OR REPLACE FUNCTION basic_func()
  RETURNS INT AS $$
    SELECT 1;
  $$ LANGUAGE SQL;
$_pgtle_$
);
ERROR:  invalid character in extension definition
DETAIL:  Use of string delimiters "$_pgtle_o_$" and "$_pgtle_i_$" are forbidden in extension definitions.
HINT:  This may be an attempt at a SQL injection attack. Please verify your installation file.
-- attempt to install the extension with an injection in the ext and error
SELECT pgtle.install_extension
(
 'test_hax',
 '1.0',
 'hax',
$_pgtle_$ $_pgtle_i_$ $_pgtle_o_$ ALTER ROLE bad_actor SUPERUSER; $_pgtle_o_$ $_pgtle_i_$
  CREATE OR REPLACE FUNCTION basic_func()
  RETURNS INT AS $$
    SELECT 1;
  $$ LANGUAGE SQL;
$_pgtle_$
);
ERROR:  invalid character in extension definition
DETAIL:  Use of string delimiters "$_pgtle_o_$" and "$_pgtle_i_$" are forbidden in extension definitions.
HINT:  This may be an attempt at a SQL injection attack. Please verify your installation file.
-- revert back to superuser
RESET SESSION AUTHORIZATION;
-- verify that the user did not elevate privileges
SELECT rolsuper FROM pg_roles WHERE rolname = 'bad_actor';
 rolsuper 
----------
 f
(1 row)

-- become the bad_actor
SET SESSION AUTHORIZATION bad_actor;
-- install a legit extension. then try to create an update path that has
-- a trojan.
SELECT pgtle.install_extension
(
 'legit_100',
 '1.0',
 'legit',
$_pgtle_$
  CREATE FUNCTION basic_func()
  RETURNS INT AS $$
    SELECT 1;
  $$ LANGUAGE SQL;
$_pgtle_$
);
 install_extension 
-------------------
 t
(1 row)

SELECT pgtle.install_update_path
(
 'legit_100',
 '1.0',
 '1.1',
$_pgtle_$ $_pgtle_i_$ ; $_pgtle_o_$ LANGUAGE SQL; ALTER ROLE bad_actor SUPERUSER; CREATE FUNCTiON hax() RETURNS text AS $_pgtle_o_$ SELECT $_pgtle_i_$
 CREATE OR REPLACE FUNCTION basic_func()
 RETURNS INT AS $$
   SELECT 2;
 $$ LANGUAGE SQL;
$_pgtle_$
);
ERROR:  invalid character in extension update definition
DETAIL:  Use of string delimiters "$_pgtle_o_$" and "$_pgtle_i_$" are forbidden in extension definitions.
HINT:  This may be an attempt at a SQL injection attack. Please verify your installation file.
-- revert back to superuser
RESET SESSION AUTHORIZATION;
-- verify that the user did not elevate privileges
SELECT rolsuper FROM pg_roles WHERE rolname = 'bad_actor';
 rolsuper 
----------
 f
(1 row)

-- remove the legit extension
SELECT pgtle.uninstall_extension('legit_100');
 uninstall_extension 
---------------------
 t
(1 row)

-- Attempt to install extension with invalid name
SELECT pgtle.install_extension
(
 'test9.control"(),pg_sleep(10),pgtle."test9',
 '0.1',
 'comment',
$_pg_tle_$
    CREATE FUNCTION dist(x1 numeric, y1 numeric, x2 numeric, y2 numeric, l numeric)
    RETURNS numeric
    AS $$
      SELECT ((x2 ^ l - x1 ^ l) ^ (1 / l)) + ((y2 ^ l - y1 ^ l) ^ (1 / l));
    $$ LANGUAGE SQL;
$_pg_tle_$
);
ERROR:  invalid extension name: "test9.control"(),pg_sleep(10),pgtle."test9"
DETAIL:  Extension names must only contain alphanumeric characters or valid separators.
-- cleanup
DROP EXTENSION pg_tle;
DROP SCHEMA pgtle;
DROP ROLE bad_actor;
DROP ROLE pgtle_admin;