Mengamankan dan mengontrol akses ke data aplikasi menggunakan tampilan aman berparameter

Pilih versi dokumentasi:

Tutorial ini menjelaskan cara menggunakan tampilan aman yang diberi parameter di AlloyDB Omni untuk membatasi akses pengguna ke tampilan yang diberi parameter menggunakan AlloyDB Omni Studio atau psql.

Contoh disertakan untuk mengilustrasikan kemampuan tampilan aman yang diberi parameter. Contoh ini hanya ditujukan untuk tujuan demonstrasi.

Tujuan

  • Membuat tampilan aman yang diberi parameter dengan parameter tampilan bernama.
  • Membuat peran database yang digunakan oleh aplikasi untuk terhubung ke database dan mengakses tampilan aman yang diberi parameter.
  • Memberikan izin peran baru ke tampilan aman dan mencabut akses ke tabel dasar.
  • Menghubungkan menggunakan peran baru dan memverifikasi bahwa tabel yang dibatasi tidak dapat diakses.
  • Menjalankan kueri pada tampilan aman yang diberi parameter menggunakan fungsi execute_parameterized_query.

Menginstal dan terhubung ke database

  1. Menginstal AlloyDB Omni menggunakan orchestrator container.

  2. Membuat cluster dan instance utamanya.

  3. Menghubungkan ke instance Anda menggunakan orchestrator container.

Menyiapkan lingkungan Anda

Untuk bersiap menjalankan kueri pada tampilan aman yang diberi parameter, Anda harus menyiapkan tampilan yang diberi parameter, database dan peran database, ekstensi parameterized_view, dan skema aplikasi terlebih dahulu.

Sebelum menggunakan tampilan aman yang diberi parameter, Anda harus melakukan hal berikut sekali di setiap container postgres baru. Setiap setelan dapat diterapkan menggunakan ALTER SYSTEM atau dengan mengedit postgresql.conf secara langsung.:

  1. Menambahkan "parameterized_views" ke shared_preload_libraries.
  2. Mengaktifkan fitur dengan menetapkan parameterized_views.enabled=on.
  3. Memulai ulang postgres agar perubahan dapat diterapkan.

Menyiapkan database

  • Membuat database bernama database untuk data aplikasi dan tampilan yang diberi parameter.

Membuat peran database, ekstensi, dan skema aplikasi

  1. Menggunakan psql, hubungkan ke database sebagai pengguna postgres atau sebagai pengguna dengan hak istimewa superuser AlloyDB Omni.

    psql database -U postgres

  2. Membuat ekstensi parameterized_views di database.

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

    Saat ekstensi dibuat, sistem juga membuat skema bernama parameterized_views sehingga API berada dalam namespace skema tersebut dan API tersebut tidak berkonflik dengan API yang ada.

  3. Membuat peran administratif AlloyDB Omni, yang memiliki dan mengelola database.

    CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;

    Untuk mengetahui informasi selengkapnya, lihat CREATE USER.

  4. Membuat peran database baru untuk menjalankan kueri terhadap tampilan aman yang diberi parameter. Ini adalah peran AlloyDB Omni yang digunakan aplikasi untuk terhubung dan login ke database untuk menjalankan kueri dengan akses terbatas ke fungsi atau objek publik ke kumpulan minimum yang diperlukan.

    Untuk mengetahui informasi selengkapnya, lihat CREATE USER.

  5. Menghubungkan sebagai pengguna administratif.

    SET role TO admin_user;

  6. Membuat skema yang berisi tabel.

    CREATE SCHEMA schema;

  7. Membuat tabel dan menyisipkan data.

    CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO schema.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Membuat tampilan aman yang diberi parameter dan menyiapkan hak akses

Untuk membuat tampilan aman yang diberi parameter dan menyiapkan hak akses yang sesuai untuk tabel dan tampilan dasar, ikuti langkah-langkah berikut:

  1. Menggunakan psql, hubungkan ke database sebagai admin_user.

    psql database -U admin_user

  2. Untuk memberikan akses terbatas ke tampilan, buat tampilan aman yang diberi parameter.

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

  3. Memberikan akses ke tampilan.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. Untuk mengakses tampilan, berikan akses ke skema.

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. Mencabut akses langsung ke tabel dasar.

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

Memverifikasi keamanan data

Untuk memverifikasi bahwa tampilan aman yang diberi parameter membatasi akses ke tampilan yang ditentukan, jalankan perintah berikut sebagai psv_user. Ini adalah peran database AlloyDB Omni yang digunakan aplikasi untuk terhubung dan login ke database untuk menjalankan kueri.

  1. Menghubungkan sebagai pengguna tampilan aman yang diberi parameter.

    sql psql database -U psv_user

  2. Memverifikasi bahwa tabel dasar tidak dapat diakses.

    SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

  3. Mengakses tampilan aman yang diberi parameter menggunakan fungsi execute_parameterized_query.

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

  4. Untuk meningkatkan keamanan kueri yang dibuat dengan bahasa alami, integrasikan tampilan aman yang diberi parameter menggunakan bahasa alami.

Pembersihan

Untuk membersihkan, Anda dapat meng-uninstal instance AlloyDB Omni atau mempertahankan instance dan menghapus objek satu per satu.

Langkah berikutnya