Mengelola keamanan data aplikasi menggunakan tampilan aman berparameter AlloyDB

Tampilan aman berparameter di AlloyDB untuk PostgreSQL memungkinkan Anda membatasi akses baris berdasarkan parameter bernama khusus aplikasi, seperti kredensial pengguna aplikasi. Tabel virtual ini memperluas tabel virtual PostgreSQL untuk melindungi database Anda saat menjalankan kueri yang tidak tepercaya dengan menerapkan batasan secara otomatis pada setiap kueri.

Untuk mengetahui informasi selengkapnya, lihat Ringkasan tampilan aman berparameter dan Mengamankan dan mengontrol akses ke data aplikasi menggunakan tampilan aman berparameter.

Sebelum memulai

Halaman ini mengasumsikan bahwa Anda telah membuat cluster dan instance AlloyDB. Untuk mengetahui informasi selengkapnya, lihat Membuat database.

Sebelum menggunakan tampilan aman berparameter, Anda harus melakukan hal berikut:

  1. Aktifkan flag database parameterized_views.enabled, yang memuat library ekstensi yang diperlukan. Anda harus mengaktifkan flag ini, meskipun sebelumnya telah diaktifkan oleh tim AlloyDB. Untuk mengetahui informasi selengkapnya tentang cara mengaktifkan flag database, lihat Mengonfigurasi flag database instance.

  2. Gunakan AlloyDB Studio atau psql untuk membuat ekstensi parameterized_views di database mana pun tempat Anda ingin membuat tampilan berparameter:

    -- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;

    Saat ekstensi dibuat, skema bernama parameterized_views juga dibuat oleh sistem sehingga API dimuat dalam namespace skema tersebut, dan agar API tidak bertentangan dengan API yang ada.

Membuat tampilan aman berparameter

Untuk membuat tampilan aman berparameter, ikuti langkah-langkah berikut:

  1. Jalankan perintah DDL CREATE VIEW, seperti yang ditunjukkan dalam contoh berikut:

    CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;

    Dalam contoh sebelumnya, tampilan aman berparameter mengizinkan akses ke tiga kolom dari tabel bernama checked_items. Tampilan membatasi hasil ke baris tempat checked_items.customer_id cocok dengan parameter yang diperlukan.

    Gunakan atribut berikut:

    • Buat tampilan menggunakan opsi security_barrier.
    • Untuk membatasi pengguna aplikasi agar hanya dapat melihat baris yang diizinkan untuk diakses, tambahkan parameter yang diperlukan dalam definisi tampilan menggunakan sintaksis $@PARAMETER_NAME. Kasus penggunaan yang umum adalah memeriksa nilai kolom dalam klausa WHERE menggunakan COLUMN = $@PARAMETER_NAME.
    • $@PARAMETER_NAME menunjukkan parameter tampilan bernama. Nilainya diberikan saat Anda menggunakan execute_parameterized_query API. Parameter tampilan bernama memiliki persyaratan berikut:
      • Parameter tampilan bernama harus diawali dengan huruf (a-z).
      • Anda dapat menggunakan huruf dengan tanda diakritik dan huruf non-Latin, serta dapat menggunakan garis bawah (_).
      • Karakter berikutnya dapat berupa huruf, garis bawah, atau digit (0-9).
      • Parameter tampilan bernama tidak boleh berisi $.
      • Parameter tampilan bernama peka huruf besar/kecil. Misalnya, $@PARAMETER_NAME ditafsirkan secara berbeda dengan $@parameter_name.

  2. Berikan SELECT pada tabel virtual ke pengguna database mana pun yang diizinkan untuk membuat kueri tabel virtual.

  3. Berikan USAGE pada skema yang berisi tabel yang ditentukan dalam tampilan kepada pengguna database mana pun yang diizinkan untuk mengkueri tampilan.

Untuk mengetahui informasi selengkapnya, lihat Mengamankan dan mengontrol akses ke data aplikasi menggunakan tampilan aman berparameter.

Mengonfigurasi keamanan untuk aplikasi Anda

Untuk mengonfigurasi keamanan aplikasi Anda menggunakan tampilan aman berparameter, ikuti langkah-langkah berikut:

  1. Buat tampilan berparameter yang aman sebagai pengguna administratif. Pengguna ini adalah pengguna database AlloyDB yang melakukan operasi administratif untuk aplikasi, termasuk penyiapan database dan administrasi keamanan.

  2. Buat peran database baru untuk menjalankan kueri terhadap tampilan aman berparameter. Ini adalah peran database AlloyDB yang digunakan aplikasi untuk terhubung dan login ke database, serta untuk menjalankan kueri terhadap tabel virtual berparameter.

    1. Berikan izin peran baru ke tampilan aman, yang biasanya mencakup hak istimewa SELECT ke tampilan dan USAGE pada skema.

    2. Batasi objek yang dapat diakses oleh peran ini ke kumpulan fungsi dan objek publik minimum yang diperlukan oleh aplikasi. Hindari memberikan akses ke skema dan tabel yang tidak bersifat publik.

    3. Untuk mengurangi risiko keamanan, cabut akses dari peran ini pada skema atau objek sensitif apa pun yang tidak benar-benar diperlukan oleh peran tersebut.

    Saat Anda membuat kueri tampilan, aplikasi akan memberikan nilai parameter tampilan yang diperlukan, yang terkait dengan identitas pengguna aplikasi.

    Untuk mengetahui informasi selengkapnya tentang cara membuat pengguna database, lihat Membuat pengguna database.

Membuat kueri tampilan aman berparameter

Untuk membuat kueri tampilan aman berparameter, gunakan salah satu opsi berikut yang paling mendukung kasus penggunaan Anda:

  • Berbasis JSON: gunakan API ini untuk menjalankan kueri dalam satu kali dan menampilkan baris JSON.

  • Berbasis CURSOR: gunakan API ini saat Anda memiliki kueri yang berjalan lebih lama atau saat Anda memiliki kueri besar dan ingin mengambil hasilnya dalam batch. Fungsi execute_parameterized_query yang disediakan oleh ekstensi parameterized_views menerima nama kursor.

  • Pernyataan PREPARE EXECUTE: gunakan ini untuk pernyataan yang disiapkan yang dapat dieksekusi beberapa kali dengan parameter value yang berbeda.

Untuk mengkueri tampilan aman berparameter, gunakan fungsi execute_parameterized_query() yang disediakan oleh ekstensi parameterized_views.

JSON API

Jalankan fungsi execute_parameterized_query(), yang memiliki sintaksis berikut:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Ganti kode berikut:

  • SQL_QUERY: kueri SQL yang klausa FROM-nya merujuk ke satu atau beberapa tampilan aman berparameter.

  • PARAMETER_NAMES: daftar nama parameter yang akan diteruskan sebagai string.

  • PARAMETER_VALUES: daftar nilai parameter yang akan diteruskan.

    • Daftar ini harus memiliki ukuran yang sama dengan daftar param_names, dengan urutan nilai yang cocok dengan urutan nama.

    • Jenis nilai yang tepat disimpulkan dari kueri dan definisi tampilan berparameter. Konversi jenis dilakukan saat diperlukan dan jika memungkinkan untuk nilai parameter yang diberikan. Jika terjadi ketidakcocokan jenis, error akan ditampilkan.

Fungsi ini menampilkan tabel objek JSON. Setiap baris dalam tabel setara dengan nilai ROW_TO_JSON() dari baris hasil kueri asli.

Gunakan contoh berikut untuk membuat kueri tampilan aman berparameter:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

Penggunaan API ini membatasi ukuran set hasil berdasarkan ukuran yang dinyatakan dalam kilobyte (kB) hasil dan jumlah baris. Anda dapat mengonfigurasi batas ini menggunakan flag parameterized_views.json_results_max_size dan parameterized_views.json_results_max_rows.

CURSOR API

API ini memiliki batasan karena mendeklarasikan kursor untuk kueri yang diberikan. Oleh karena itu, kueri harus kompatibel dengan kursor PostgreSQL. Misalnya, CURSOR API tidak mendukung pernyataan DO atau SHOW.

API ini juga tidak membatasi hasil berdasarkan ukuran atau jumlah baris yang ditampilkan.

Jalankan fungsi execute_parameterized_query(), yang membuat dan menampilkan CURSOR cakupan transaksi yang Anda gunakan untuk mengambil hasil kueri:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Ganti kode berikut:

  • SQL_QUERY: kueri SQL yang klausa FROM-nya merujuk ke satu atau beberapa tampilan aman berparameter.

  • CURSOR_NAME: nama kursor yang akan dideklarasikan.

  • PARAMETER_NAMES: daftar nama parameter yang akan diteruskan sebagai string.

  • PARAMETER_VALUES: daftar nilai parameter yang akan diteruskan. Daftar ini harus berukuran sama dengan daftar param_names, dengan urutan nilai yang cocok dengan urutan nama. Jenis nilai yang tepat disimpulkan dari kueri dan definisi tampilan berparameter. Konversi jenis dilakukan saat diperlukan dan jika memungkinkan untuk nilai parameter yang diberikan. Jika terjadi ketidakcocokan jenis, error akan ditampilkan.

Gunakan contoh berikut untuk membuat kueri tampilan aman berparameter:

  -- Start a transaction, which is the default lifetime of a CURSOR
  BEGIN;
  -- Create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor',
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- Fetch the results
  FETCH ALL FROM mycursor;
  -- End the transaction, which cleans up the cursor
  END;

Kursor yang ditampilkan adalah kursor NO SCROLL WITHOUT HOLD. Anda tidak dapat menggunakan kursor untuk mengambil baris secara tidak berurutan, misalnya, dalam arah mundur. Anda tidak dapat menggunakan kursor di luar transaksi yang membuatnya.

Pernyataan PREPARE

Gunakan perintah PREPARE .. AS RESTRICTED untuk membuat pernyataan yang disiapkan yang mereferensikan tampilan berparameter. Pernyataan yang disiapkan ini mendukung parameter posisi dan menerapkan berbagai batasan saat Anda menjalankannya. Untuk mengetahui informasi selengkapnya tentang cara tampilan aman berparameter menerapkan keamanan, lihat Mekanisme keamanan.

Fitur ini memperluas perintah PREPARE dan EXECUTE untuk mendukung parameter tampilan bernama. Gunakan pernyataan yang disiapkan untuk menghindari overhead penguraian, analisis, dan penulisan ulang setiap kali pernyataan dijalankan, yang dapat menghasilkan peningkatan performa yang signifikan, terutama untuk kueri yang sering dijalankan atau kompleks. Pernyataan yang disiapkan adalah objek sisi server yang dapat mengoptimalkan performa dengan melakukan pra-kompilasi dan menyimpan pernyataan SQL berparameter untuk dieksekusi nanti.

API ini memiliki batasan karena pernyataan harus diizinkan dalam pernyataan PREPARE, yang berarti hanya pernyataan SELECT dan VALUES yang didukung.

API ini juga tidak membatasi hasil berdasarkan ukuran atau jumlah baris yang ditampilkan.

Untuk membuat pernyataan yang disiapkan yang merujuk ke tampilan berparameter, jalankan perintah PREPARE .. AS RESTRICTED:

PREPARE pquery (POSITIONAL_PARAM_TYPES)
        AS RESTRICTED SQL_QUERY;
EXECUTE pquery (POSITIONAL_PARAM_VALUES)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Ganti kode berikut:

  • POSITIONAL_PARAM_TYPES: satu atau beberapa jenis data parameter posisi yang digunakan dalam kueri RESTRICTED.

  • SQL_QUERY: kueri SQL yang dapat merujuk ke tampilan berparameter.

  • POSITIONAL_PARAM_VALUES: nilai sebenarnya yang menggantikan parameter posisional yang ditentukan dalam pernyataan PREPARE.

  • VIEW_PARAM_NAME: nama parameter yang diharapkan oleh tampilan berparameter yang direferensikan dalam kueri RESTRICTED.

  • VIEW_PARAM_VALUE: nilai sebenarnya yang diteruskan ke parameter VIEW_PARAM_NAME yang sesuai dari tampilan berparameter.

Untuk menyertakan parameter dalam pernyataan yang disiapkan, Anda memberikan daftar jenis data dalam pernyataan PREPARE. Dalam pernyataan yang Anda siapkan, Anda merujuk ke parameter berdasarkan posisi menggunakan, misalnya, $1 dan $2.

Gunakan perintah EXECUTE .. WITH VIEW PARAMETERS untuk mengeksekusi pernyataan yang telah disiapkan sebelumnya yang Anda buat menggunakan perintah PREPARE .. AS RESTRICTED. Jika pernyataan PREPARE yang membuat pernyataan menentukan parameter posisi, Anda harus meneruskan kumpulan parameter yang kompatibel ke pernyataan EXECUTE. Anda harus meneruskan parameter tampilan bernama yang diperlukan oleh tampilan berparameter dalam klausa WITH VIEW PARAMETERS.

Gunakan contoh berikut untuk membuat kueri tampilan aman berparameter:

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

Pembatasan yang diterapkan pada kueri

Berikut adalah daftar kumpulan operasi yang dibatasi untuk kueri yang Anda jalankan menggunakan opsi yang dijelaskan dalam Membuat kueri tampilan aman berparameter:

  • Setiap pemanggilan rekursif API apa pun—execute_parameterized_query, atau dengan menggunakan EXECUTE .. WITH VIEW PARAMETERS—dilarang, sehingga hanya nilai yang ditentukan oleh aplikasi yang digunakan. Batasan ini juga mencegah kueri digunakan untuk mengakali batasan keamanan dari kumpulan parameter value yang diberikan.

  • Beberapa ekstensi yang memulai sesi latar belakang baru tidak diizinkan, termasuk ekstensi dblink, pg_cron, dan pg_background.

  • Konstruksi kueri berikut diizinkan atau dibatasi:

    • Pernyataan SELECT hanya baca diizinkan.

    • Pernyataan SHOW hanya baca, pernyataan CALL, dan pernyataan DO diizinkan.

    • Pernyataan DML seperti INSERT, UPDATE, dan DELETE tidak diizinkan.

    • Pernyataan DDL seperti CREATE TABLE dan ALTER TABLE tidak diizinkan.

    • Jenis pernyataan lain seperti LOAD, SET, CLUSTER, LOCK, CHECKPOINT, dan EXPLAIN tidak diizinkan.

  • Pernyataan EXPLAIN tidak diizinkan secara default untuk menghindari kemungkinan serangan saluran terselubung menggunakan rencana kueri. Untuk mengetahui informasi selengkapnya, lihat Saluran terselubung. Namun, untuk menjalankan pernyataan EXPLAIN, setiap pengguna database dengan hak istimewa superuser AlloyDB dapat menetapkan GUC tingkat sesi parameterized_views.enable_explain ke on.

  • Tampilan aman berparameter menyediakan setelan untuk membantu Anda mengelola resource yang digunakan oleh API untuk mengkueri tampilan berparameter, seperti parameterized_views.statement_timeout. Untuk mengetahui informasi selengkapnya, lihat Flag database yang didukung.

Mencantumkan semua tampilan berparameter

Gunakan ekstensi parameterized_views untuk mencantumkan semua tabel virtual berparameter dalam database menggunakan tabel virtual all_parameterized_views. Output tampilan ini sama dengan tampilan katalog pg_views, tetapi all_parameterized_views hanya mencantumkan tampilan dengan parameter tampilan bernama.

Untuk mencantumkan tampilan berparameter, jalankan kueri berikut:

SELECT * FROM parameterized_views.all_parameterized_views;

Outputnya mirip dengan hal berikut ini:

schemaname |       viewname       | viewowner |                       definition
-----------+----------------------+-----------+---------------------------------------------------------
public     | secure_checked_items | postgres  |  SELECT checked_items.bag_id,                          +
           |                      |           |     checked_items."timestamp",                         +
           |                      |           |     checked_items.location                             +
           |                      |           |    FROM checked_items                                  +
           |                      |           |   WHERE (checked_items.customer_id = $@app_end_userid);

Untuk mencantumkan tampilan berparameter di all_parameterized_views, pastikan tampilan berparameter berisi setidaknya satu parameter tampilan bernama dalam definisinya.

Langkah berikutnya