Mengamankan dan mengontrol akses ke data aplikasi menggunakan tampilan aman berparameter

Tutorial ini menjelaskan cara menggunakan tampilan aman yang diberi parameter di Cloud SQL untuk PostgreSQL guna membatasi akses pengguna ke tampilan yang diberi parameter menggunakan Cloud SQL Studio atau psql.

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.

Menyiapkan lingkungan Anda

  1. Aktifkan flag database cloudsql.enable_parameterized_views untuk instance Cloud SQL Anda. Perubahan flag ini memerlukan restart database.

  2. Hubungkan ke database Anda sebagai pengguna postgres.

    psql -U postgres

  3. Buat ekstensi parameterized_views di database.

    CREATE EXTENSION parameterized_views;

  4. Buat peran database baru untuk menjalankan kueri.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. Buat skema dan tabel untuk data aplikasi.

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

Membuat tampilan aman yang diberi parameter dan menyiapkan hak akses

  1. Buat tampilan aman yang diberi parameter:

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. Berikan akses ke tampilan dan skema ke peran aplikasi.

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

  3. Cabut akses langsung ke tabel dasar.

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

Memverifikasi keamanan data

  1. Hubungkan sebagai psv_user.

    psql -U psv_user -d postgres

  2. Verifikasi bahwa tabel dasar tidak dapat diakses.

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table items

  3. Akses tampilan aman yang diberi parameter menggunakan fungsi execute_parameterized_query:

    SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    Hasilnya hanya boleh menyertakan item dengan owner_id adalah 123.

Langkah berikutnya