Unprivileged user without pgtle_staff cannot run passcheck hook.

[jkatz]

REPRO

1. Create a passcheck hook:

SELECT pgtle.install_extension (
  'my_password_check_rules',
  '1.0',
  'Do not let users use the 10 most commonly used passwords',
$_pgtle_$
  CREATE SCHEMA password_check;
  REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
  GRANT USAGE ON SCHEMA password_check TO PUBLIC;

  CREATE TABLE password_check.bad_passwords (plaintext) AS
  VALUES
    ('123456'),
    ('password'),
    ('12345678'),
    ('qwerty'),
    ('123456789'),
    ('12345'),
    ('1234'),
    ('111111'),
    ('1234567'),
    ('dragon');
  CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext);

  CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamp, valid_null boolean)
  RETURNS void AS $$
    DECLARE
      invalid bool := false;
    BEGIN
      IF password_type = 'PASSWORD_TYPE_MD5' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE ('md5' || md5(bp.plaintext || username)) = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN
        SELECT EXISTS(
          SELECT 1
          FROM password_check.bad_passwords bp
          WHERE bp.plaintext = password
        ) INTO invalid;
        IF invalid THEN
          RAISE EXCEPTION 'password must not be found on a common password dictionary';
        END IF;
      END IF;
    END
  $$ LANGUAGE plpgsql SECURITY DEFINER;

  GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;

  SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
);

2. Create the extension:

CREATE EXTENSION my_password_check_rules;

3. Create an user without pgtle_admin role.

CREATE ROLE test_role;

4. Become that user. Set the password using \password to the value password

SET SESSION AUTHORIZATION test_role;
\password

EXPECTED

ERROR:  password must not be found on a common password dictionary

ACTUAL

ERROR:  permission denied for schema pgtle at character 34

NOTES

This is failing when pg_tle is trying to load the hook function via the pgtle.feature_info table:

pg_tle/src/passcheck.c

Lines 152 to 157 in 68d2bfa

	/*
	* Assume function accepts the proper argument, it'll error when we
	* call out to SPI_exec if it doesn't anyway
	*/
	query = psprintf("SELECT schema_name, proname FROM %s.%s WHERE feature = '%s' ORDER BY proname",
	schema_name, feature_table_name, password_check_feature);

[jkatz]

🤔 Haven't tested this yet, but this may be an ALTER DEFAULT PRIVILEGES situation. We may need to set that on the GRANT USAGE ON SCHEMA pgtle TO PUBLIC;

[jkatz]

This is now resolved with the changes from #77