Contoh: Membuat ekstensi yang menggunakan hook Postgre SQL

Menggunakan SQL kait Postgre dengan ekstensi Anda TLE

Hook adalah mekanisme callback yang tersedia di Postgre SQL yang memungkinkan pengembang untuk memanggil fungsi kustom atau rutinitas lainnya selama operasi database reguler. Kit TLE pengembangan mendukung SQL kait Postgre sehingga Anda dapat mengintegrasikan fungsi kustom dengan perilaku SQL Postgre saat runtime. Misalnya, Anda dapat menggunakan hook untuk mengaitkan proses autentikasi dengan kode kustom Anda sendiri, atau mengubah proses perencanaan dan eksekusi kueri untuk kebutuhan spesifik Anda.

TLEEkstensi Anda dapat menggunakan kait. Jika hook cakupannya global, ini berlaku di semua basis data. Oleh karena itu, jika TLE ekstensi Anda menggunakan hook global, maka Anda perlu membuat TLE ekstensi Anda di semua database yang dapat diakses pengguna Anda.

Saat Anda menggunakan pg_tle ekstensi untuk membuat Ekstensi Bahasa Tepercaya Anda sendiri, Anda dapat menggunakan kait yang tersedia dari a SQL API untuk membangun fungsi ekstensi Anda. Anda harus mendaftarkan hook apa pun di pg_tle. Untuk beberapa hook, Anda mungkin juga perlu mengatur berbagai parameter konfigurasi. Misalnya, hook pemeriksaan passcode dapat diatur ke aktif, nonaktif, wajib. Untuk informasi selengkapnya tentang persyaratan khusus untuk hook pg_tle yang tersedia, lihat Referensi kait untuk Ekstensi Bahasa Tepercaya untuk Postgre SQL.

Contoh: Membuat ekstensi yang menggunakan hook Postgre SQL

Contoh yang dibahas di bagian ini menggunakan SQL hook Postgre untuk memeriksa kata sandi yang diberikan selama SQL operasi tertentu dan mencegah pengguna database mengatur kata sandi mereka ke salah satu yang terkandung dalam tabel. password_check.bad_passwords Tabel berisi sepuluh besar pilihan kata sandi yang paling umum digunakan, tetapi mudah dipecahkan.

Untuk mengatur contoh ini di Anda harus sudah menginstal Ekstensi Bahasa Tepercaya. Untuk detailnya, lihat Menyiapkan Ekstensi Bahasa Tepercaya di SQL.

Untuk menyiapkan contoh hook pemeriksaan kata sandi

Gunakan psql untuk terhubung ke . RDSuntuk contoh Postgre SQL DB.


psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
--port=5432 --username=postgres --password --dbname=labdb

Salin kode dari Daftar kode hook pemeriksaan kata sandi dan tempel ke basis data Anda.


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_$
);

Setelah ekstensi dimuat ke basis data, Anda akan melihat output seperti berikut.


 install_extension
-------------------
 t
(1 row)

Jika masih terhubung ke basis data, Anda kini dapat membuat ekstensi.
```
CREATE EXTENSION my_password_check_rules;
```

Anda dapat mengonfirmasi bahwa ekstensi telah dibuat dalam basis data dengan menggunakan metacommand psql berikut.


\dx
                        List of installed extensions
          Name           | Version |   Schema   |                         Description
-------------------------+---------+------------+-------------------------------------------------------------
 my_password_check_rules | 1.0     | public     | Prevent use of any of the top-ten most common bad passwords
 pg_tle                  | 1.0.1   | pgtle      | Trusted-Language Extensions for PostgreSQL
 plpgsql                 | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

Buka sesi terminal lain untuk bekerja dengan AWS CLI. Anda perlu memodifikasi grup parameter DB kustom Anda untuk mengaktifkan hook pemeriksaan kata sandi. Untuk melakukannya, gunakan modify-db-parameter-groupCLIperintah seperti yang ditunjukkan pada contoh berikut.
```
aws rds modify-db-parameter-group \
    --region aws-region \
    --db-parameter-group-name your-custom-parameter-group \
    --parameters "ParameterName=pgtle.enable_password_check,ParameterValue=on,ApplyMethod=immediate"
```
Jika parameter berhasil dihidupkan, Anda akan melihat output seperti berikut.
```
(
    "DBParameterGroupName": "docs-lab-parameters-for-tle"
}
```
Mungkin diperlukan waktu beberapa menit agar perubahan pada pengaturan grup parameter diterapkan. Parameter ini dinamis, bagaimanapun, jadi Anda tidak perlu me-restart DB agar pengaturan diterapkan.

Buka sesi psql dan kirim kueri ke basis data untuk memverifikasi bahwa hook password_check telah diaktifkan.


labdb=> SHOW pgtle.enable_password_check;
pgtle.enable_password_check
-----------------------------
on
(1 row)

Hook password_check kini aktif. Anda dapat mengujinya dengan membuat peran baru dan menggunakan salah satu kata sandi yang buruk, seperti pada contoh berikut.


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)"

Output ini telah diformat agar mudah dibaca.

Contoh berikut menunjukkan bahwa perilaku \password metacommand interaktif pgsql juga dipengaruhi oleh hook password_check.


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)"

Anda dapat menghapus TLE ekstensi ini dan menghapus file sumbernya jika Anda mau. Untuk informasi selengkapnya, lihat Menjatuhkan TLE ekstensi Anda dari database.

Daftar kode hook pemeriksaan kata sandi

Kode contoh yang ditampilkan di sini mendefinisikan spesifikasi untuk my_password_check_rules TLE ekstensi. Jika kode ini disalin dan ditempelkan ke basis data, kode untuk ekstensi my_password_check_rules akan dimuat ke dalam basis data, dan hook password_check akan didaftarkan untuk digunakan oleh ekstensi.


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_$
);

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Menghapus instalan Ekstensi Bahasa Tepercaya

Menggunakan Jenis Data Kustom di Ekstensi Bahasa Tepercaya