Usare gli SQL hook Postgre con le tue estensioni TLE

Un hook è un meccanismo di callback disponibile in Postgre SQL che consente agli sviluppatori di richiamare funzioni personalizzate o altre routine durante le normali operazioni del database. Il kit di TLE sviluppo supporta gli SQL hook Postgre in modo da poter integrare funzioni personalizzate con il comportamento di Postgre in fase di esecuzione. SQL Ad esempio, puoi utilizzare un hook per associare il processo di autenticazione al codice personalizzato o per modificare il processo di pianificazione ed esecuzione delle query in base alle tue esigenze specifiche.

Le tue estensioni possono usare TLE gli hook. Se l'ambito dell'hook è globale, si applica a tutti i database. Pertanto, se l'TLEestensione utilizza un hook globale, è necessario creare TLE l'estensione in tutti i database a cui gli utenti possono accedere.

Quando si utilizza l'pg_tleestensione per creare le proprie estensioni linguistiche attendibili, è possibile utilizzare gli hook disponibili da SQL API a per sviluppare le funzioni dell'estensione. È necessario registrare gli hook con pg_tle. Per alcuni hook, potrebbe essere necessario impostare anche vari parametri di configurazione. Ad esempio, l'hook di controllo passcode può essere impostato su attivo, disattivo oppure obbligatorio. Per ulteriori informazioni sui requisiti specifici per gli hook pg_tle disponibili, consulta Guida di riferimento agli Hooks per Trusted Language Extensions for Postgre SQL.

Esempio: creazione di un'estensione che utilizza un hook SQL Postgre

L'esempio discusso in questa sezione utilizza un SQL hook Postgre per verificare la password fornita durante SQL operazioni specifiche e impedisce agli utenti del database di impostare le proprie password su quelle contenute nella tabella. password_check.bad_passwords La tabella contiene le prime dieci opzioni di password più utilizzate, ma facilmente violabili.

Per configurare questo esempio nel cluster Aurora Postgre SQL DB, RDS per l'istanza Postgre DB, Extensions. Per informazioni dettagliate, consultare .

L'hook di controllo della password è ora attivo. Puoi testarlo creando un nuovo ruolo e utilizzando una delle password errate, come illustrato nell'esempio seguente.

CREATE ROLE test_role PASSWORD 'password';
ERROR:  Cannot use passwords from the common password dictionary
CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 21 at RAISE
SQL statement "SELECT password_check.passcheck_hook(
    $1::pg_catalog.text, 
    $2::pg_catalog.text, 
    $3::pgtle.password_types, 
    $4::pg_catalog.timestamptz, 
    $5::pg_catalog.bool)"

L'output è stato formattato per ragioni di leggibilità.

L'esempio seguente mostra che il comportamento \password del metacomando interattivo pgsql è influenzato anche dall'hook di controllo della password.

postgres=> SET password_encryption TO 'md5';
SET
postgres=> \password
Enter new password for user "postgres":*****
Enter it again:*****
ERROR:  Cannot use passwords from the common password dictionary
CONTEXT:  PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 12 at RAISE
SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

Se lo desideri, puoi eliminare questa TLE estensione e disinstallarne i file sorgente. Per ulteriori informazioni, consulta Eliminare le TLE estensioni da un database.

Codice di hook di controllo della password

Il codice di esempio mostrato qui definisce le specifiche per l'my_password_check_rulesTLEestensione. Quando copi questo codice e lo incolli nel database, il codice dell'estensione my_password_check_rules viene caricato nel database e l'hook password_check viene registrato per essere utilizzato dall'estensione.

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 timestamptz, 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 'Cannot use passwords from the 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 'Cannot use passwords from the common 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_$
);