PostgreSQL Functions – How Could a ‘SECURITY DEFINER’ Function Be Insecure with Improper search_path?

functionspostgresql

The PG docs provides this example of a properly written security definer function with a search_path:

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

I don't understand this part:

But without the SET clause, or with a SET clause mentioning only
admin, the function could be subverted by creating a temporary table
named pwds.

If admin.pwds exists and search_path is set to admin only, how is it even possible to create a temporary table, pwds, maliciously?

Best Answer

If pg_temp is not present in search_path at all, then the temp scheme is always searched first, i.e. as if it were implicitly prepended to search_path.

The only way to prevent it from being searched first is to give it an explicit presence in search_path.

Related Solutions

Postgresql – I can’t figure out how to call a function with a trigger, keeps saying function doesn’t exist

Before creating trigger, you need to create a trigger function, which has to be (quoting the manual):

A user-supplied function that is declared as taking no arguments and returning type trigger

Like:

CREATE OR REPLACE FUNCTION calculate_t_f()
  RETURNS trigger AS
$$
BEGIN
...
-- do something
-- maybe with with calculate(char)...
...
END
$$ LANGUAGE plpgsql;

Then you can create your trigger with that trigger function (not your original function):

CREATE TRIGGER trigger_calculate
AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate_t_f();

PostgreSQL Function – Return Table with Extra Column

Just add the variable to the SELECT list:

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address varchar)
  RETURNS TABLE (credential_id integer
               , user_id integer
               , password_hash varchar
               , password_salt varchar
               , created_at timestamp
               , last_updated_at timestamp
               , logical_value_return smallint) AS
$func$
DECLARE
   sproc_logical_value_return SMALLINT := 2;  -- you can assign at declaration time
BEGIN
   RETURN QUERY
      SELECT uc.credential_id,
             uc.user_id,
             uc.password_hash,
             uc.password_salt,
             uc.created_at,
             uc.last_updated_at
             sproc_logical_value_return  -- just put it in the SELECT list
      FROM   myapp_users_credentials uc
      JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
      JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
      WHERE  c.value = $1
      AND    c.type  = 1
      AND    c.is_primary = 1
      LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;
END
$func$  LANGUAGE plpgsql ROWS 1;

I simplified with table aliases.

Since your function returns a single row by definition, the value is also returned only once.

You might use RETURNS RECORD combined with OUT parameters instead of RETURNS TABLE since you only return exactly 1 row anyway.

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(
                 IN  email_address varchar
               , OUT credential_id integer
               , OUT user_id integer
               , OUT password_hash varchar
               , OUT password_salt varchar
               , OUT created_at timestamp
               , OUT last_updated_at timestamp
               , OUT logical_value_return smallint) AS
$func$
BEGIN

   logical_value_return := 2;  -- assign separately or with SELECT list

   SELECT uc.credential_id, uc.user_id, uc.password_hash, uc.password_salt, uc.created_at, uc.last_updated_at
   INTO      credential_id,    user_id,    password_hash,    password_salt,    created_at,    last_updated_at
   FROM   myapp_users_credentials uc
   JOIN   myapp_contacts_assoc    ca ON ca.user_id = uc.user_id      AND uc.expired_at IS NULL
   JOIN   myapp_contacts          c  ON c.contact_id = ca.contact_id AND uc.expired_at IS NULL
   WHERE  c.value = $1
   AND    c.type = 1
   AND    c.is_primary = 1
   LIMIT  1;

   IF NOT FOUND THEN
      RAISE EXCEPTION 'Credentials not found';
   END IF;

END
$func$  LANGUAGE plpgsql;

RETURNS RECORD is redundant noise which can be omitted in this case.

The subtle difference: You could still return the logical constant, even when no row is found. Other columns would be NULL in this case. Details:

Best Answer

Related Solutions

Postgresql – I can’t figure out how to call a function with a trigger, keeps saying function doesn’t exist

PostgreSQL Function – Return Table with Extra Column

Related Question