دیتابیس و SQLدیتابیس در کار واقعیدرس ۸ از ۱۰

کاربر، دسترسی و امنیت دیتابیس

دیتابیس را امن کن: نقش (role) و کاربر جدا برای برنامه و گزارش، اصل کمترین دسترسی با GRANT و REVOKE، دسترسی به sequence، نقش گروهی، دسترسی ستونی، ALTER DEFAULT PRIVILEGES، احراز هویت peer و رمز در pg_hba.conf، فایل ‎.pgpass، باز نکردن پورت 5432 به اینترنت، و تزریق SQL و جلوگیری از آن.

حدود ۳۱ دقیقه مطالعه
۹ مثال اجراشده
۳ تمرین با جواب
مقدماتی تا متوسط
به‌روزرسانی: ۲۹ مهر ۱۴۰۵

توی این درس یاد می‌گیری چه کسی به دیتابیس دسترسی دارد و به چه چیزی. برای برنامه و برای گزارش‌گیری کاربر جدا می‌سازی، با GRANT به هر کدام فقط همان دسترسی‌ای را می‌دهی که لازم دارد (اصل کمترین دسترسی)، می‌بینی PostgreSQL چطور تصمیم می‌گیرد چه کسی اصلاً می‌تواند وصل شود (pg_hba.conf)، رمز را امن در اسکریپت‌ها استفاده می‌کنی (‎.pgpass)، و با چشم خودت تزریق SQL را می‌بینی (یک ورودی که قیمت همه‌ی محصول‌ها را یک تومان می‌کند) و یاد می‌گیری چطور جلویش را بگیری.

مسئله: همه با یک کاربر، با همه‌ی اختیارات

تا اینجا همه‌چیز را با کاربر خودت (ali) انجام دادی که صاحب جدول‌هاست و هر کاری می‌تواند بکند، حتی DROP TABLE. اگر برنامه‌ی وب هم با همین کاربر وصل شود، یک باگ یا یک حمله‌ی موفق به برنامه یعنی همه‌چیز در دست مهاجم است: خواندن همه‌ی داده‌ها، تغییر قیمت‌ها، پاک کردن جدول‌ها. اگر گزارش‌ساز هم با همین کاربر کار کند، یک UPDATE اشتباه در اسکریپت گزارش، داده‌ی واقعی را عوض می‌کند.

تشبیه: کارت‌های ورود ساختمان

در یک ساختمان اداری، نگهبان دم در (pg_hba.conf) اول می‌پرسد «کی هستی و از کجا آمده‌ای؟» و کارت شناسایی (رمز) می‌خواهد. بعد از ورود، کارت دسترسی هر کس (GRANT) فقط درهای مشخصی را باز می‌کند: آبدارچی به آشپزخانه، حسابدار به اتاق بایگانی مالی (فقط خواندن)، و فقط مدیر ساختمان کلید همه‌جا را دارد. اگر کارت آبدارچی گم شود، دزد فقط به آشپزخانه می‌رسد. نقش گروهی هم مثل «کارت کارمندان مالی» است: به جای تنظیم تک‌تک کارت‌ها، دسترسی را به گروه می‌دهی و آدم‌ها را عضو گروه می‌کنی.

هر اتصال از چند لایه‌ی بررسی رد می‌شود؛ هر لایه جداگانه می‌تواند جلوی دسترسی را بگیرد.

مثال‌های عملی

داده را تازه کن:

psql shop -f ~/shop.sql

مثال ۱: ساختن نقش، کار مدیر دیتابیس است

در PostgreSQL به کاربر و گروه هر دو نقش (role) می‌گویند؛ نقشی که اجازه‌ی LOGIN دارد همان «کاربر» است. کاربر خودت اجازه‌ی ساختن نقش ندارد:

psql shop -c "CREATE ROLE shop_app LOGIN PASSWORD 'x'"

ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.

این کار با کاربر مدیر postgres است. یادت هست در درس اول با sudo -u postgres دیتابیس را آماده کردیم؛ حالا خود psql را با او باز کن:

sudo -u postgres psql

CREATE ROLE shop_app LOGIN PASSWORD 'S3cure-App-Pass';
CREATE ROLE report LOGIN PASSWORD 'Rep0rt-Pass';
\du

CREATE ROLE
CREATE ROLE
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 ali       | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 report    |
 shop_app  |

\du نقش‌ها را نشان می‌دهد. postgres Superuser است: هیچ قانون دسترسی‌ای برایش اعمال نمی‌شود؛ مثل root در لینوکس. هرگز برنامه را با او وصل نکن. (رمزهای این درس نمونه‌اند؛ برای سرور واقعی رمز طولانی تصادفی بساز، مثلاً با openssl rand -base64 24.) با \q خارج شو.

مثال ۲: ورود با رمز، و نگهبان دم در

psql -h localhost -U shop_app shop

Password for user shop_app:
psql (16.15 (Ubuntu 16.15-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

shop=>

-h localhost یعنی از راه شبکه (TCP، حتی روی همین کامپیوتر) وصل شو و -U shop_app یعنی با این نقش. رمز را پرسید. پرامپت => (به‌جای =#) یعنی کاربر معمولی هستی. حالا بدون -h:

psql -U shop_app shop

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "shop_app"

بدون -h، psql از سوکت محلی (فایلی که درس پورت و سوکت در لینوکس دیدی) وصل می‌شود و برای آن روش peer تنظیم شده: «اسم نقش باید با اسم کاربر لینوکسی که برنامه را اجرا کرده یکی باشد». تو ali هستی، نه shop_app. این قانون‌ها در فایل pg_hba.conf (hba = host-based authentication) هستند:

sudo grep -v '^#' /etc/postgresql/16/main/pg_hba.conf | grep -v '^$'

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

هر خط: نوع اتصال (local سوکت، host شبکه)، دیتابیس، نقش، آدرس، روش. PostgreSQL خط‌ها را از بالا می‌خواند و اولین خط جور را اجرا می‌کند. یعنی: از سوکت، هر کس فقط با هم‌نام لینوکسی خودش (peer)؛ از شبکه‌ی محلی (127.0.0.1 و ::1) هر کس با رمز (scram-sha-256). اتصال از هر آدرس دیگری اصلاً خطی ندارد و رد می‌شود. (این تنظیم پیش‌فرض اوبونتو است و برای شروع عالی است.)

مثال ۳: کاربر جدید هیچ دسترسی‌ای ندارد

در psql با shop_app:

SELECT name FROM products LIMIT 1;

ERROR:  permission denied for table products

خوب است: نقش تازه به جدول‌های کسی دسترسی ندارد. صاحب جدول‌ها (ali) باید اجازه بدهد. در ترمینال دیگری با کاربر خودت:

psql shop

GRANT SELECT, INSERT, UPDATE ON products, customers, orders, order_items TO shop_app;
\dp products

GRANT
                              Access privileges
 Schema |   Name   | Type  | Access privileges | Column privileges | Policies
--------+----------+-------+-------------------+-------------------+----------
 public | products | table | ali=arwdDxt/ali  +|                   |
        |          |       | shop_app=arw/ali  |                   |
(1 row)

\dp دسترسی‌ها را نشان می‌دهد: shop_app=arw/ali یعنی «ali به shop_app اجازه‌ی a (append = INSERT)، r (read = SELECT) و w (write = UPDATE) داده». صاحب جدول همه‌ی حرف‌ها را دارد (d = DELETE، D = TRUNCATE، …). شبیه rwx در ls -l لینوکس، فقط با حرف‌های بیشتر.

مثال ۴: ببین چه چیزهایی هنوز ممنوع است

برگرد به psql با shop_app:

SELECT name, price FROM products WHERE id = 1;
INSERT INTO orders (customer_id) VALUES (5) RETURNING id;
DELETE FROM orders WHERE id = 1;
DROP TABLE products;

    name     | price
-------------+--------
 کتاب پایتون | 320000
(1 row)

ERROR:  permission denied for sequence orders_id_seq
ERROR:  permission denied for table orders
ERROR:  must be owner of table products

خواندن کار کرد. DELETE و DROP ممنوع‌اند: دقیقاً همان چیزی که می‌خواستیم (برنامه‌ی فروشگاه سفارش را حذف نمی‌کند؛ لغو می‌کند). ولی INSERT هم رد شد، به یک دلیل غافلگیرکننده: SERIAL برای ساختن id بعدی از یک sequence (شمارنده‌ی جدا به اسم orders_id_seq) استفاده می‌کند و آن هم دسترسی جدا لازم دارد. با ali:

GRANT USAGE ON SEQUENCE orders_id_seq, customers_id_seq TO shop_app;

و دوباره با shop_app:

INSERT INTO orders (customer_id) VALUES (5) RETURNING id;

 id
----
  7
(1 row)

INSERT 0 1

حالا shop_app دقیقاً همان کارهایی را می‌تواند که برنامه‌ی فروشگاه لازم دارد و نه بیشتر. به این اصل کمترین دسترسی (least privilege) می‌گویند.

مثال ۵: نقش گروهی برای گزارش‌گیری

چند نفر (تیم مالی، ابزار داشبورد) باید فقط بخوانند. به‌جای دادن دسترسی به تک‌تک‌شان، یک نقش گروهی بساز که وارد نمی‌شود (NOLOGIN) و فقط دسترسی‌ها را نگه می‌دارد. با sudo -u postgres psql:

CREATE ROLE readonly NOLOGIN;

با ali (صاحب جدول‌ها) دسترسی را به گروه بده:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

GRANT
ALTER DEFAULT PRIVILEGES

خط اول برای جدول‌های موجود است. خط دوم برای جدول‌هایی که ali در آینده می‌سازد: بدون آن، هر جدول جدید برای گروه بسته می‌ماند و یک روز داشبورد خطا می‌دهد. حالا report را عضو گروه کن (با postgres):

GRANT readonly TO report;

GRANT ROLE

مثال ۶: دسترسی در سطح ستون

تیم گزارش لازم ندارد اسم مشتری‌ها را ببیند (حریم خصوصی)؛ فقط شهر و تاریخ عضویت. با ali:

REVOKE SELECT ON customers FROM readonly;
GRANT SELECT (id, city, joined_on) ON customers TO readonly;

حالا با report وصل شو (psql -h localhost -U report shop):

SELECT category, count(*) FROM products GROUP BY category ORDER BY category;
SELECT * FROM customers;
SELECT city, count(*) FROM customers GROUP BY city ORDER BY count(*) DESC, city;
UPDATE products SET price = 1;

  category   | count
-------------+-------
 ذخیره‌سازی   |     3
 شبکه        |     3
 لوازم جانبی |     3
 کتاب        |     3
(4 rows)

ERROR:  permission denied for table customers
  city  | count
--------+-------
 تهران  |     2
 اصفهان |     1
 تبریز  |     1
 شیراز  |     1
(4 rows)

ERROR:  permission denied for table products

report از گروه readonly ارث برد: محصول‌ها را می‌خواند، SELECT * روی مشتری‌ها رد شد (ستون name مجاز نیست)، ولی گزارش شهرها کار کرد. UPDATE هم ممنوع است: گزارش‌ساز با هیچ اشتباهی داده را عوض نمی‌کند.

⚡ بررسی سریع

به نقش app اجازه‌ی INSERT روی orders داده‌ای ولی INSERT خطای permission denied for sequence می‌دهد. چه کم است؟

جواب درست: GRANT USAGE ON SEQUENCE orders_id_seq

SERIAL از sequence جدا استفاده می‌کند و آن دسترسی جدا لازم دارد.

مثال ۷: رمز امن در اسکریپت با ‎.pgpass

اسکریپت cron نمی‌تواند رمز تایپ کند و نوشتن رمز داخل اسکریپت یعنی هر کس اسکریپت را بخواند رمز را دارد. psql رمز را از فایل ~/.pgpass می‌خواند؛ هر خط: میزبان:پورت:دیتابیس:کاربر:رمز.

printf 'localhost:5432:shop:shop_app:S3cure-App-Pass\n' > ~/.pgpass
psql -h localhost -U shop_app shop -c 'SELECT current_user'

WARNING: password file "/home/ali/.pgpass" has group or world access; permissions should be u=rw (0600) or less
Password for user shop_app:

psql فایلی را که بقیه هم می‌توانند بخوانند نادیده گرفت (و رمز پرسید؛ با Ctrl+C لغو کن). همان مجوزهای درس permissions:

chmod 600 ~/.pgpass
psql -h localhost -U shop_app shop -c 'SELECT current_user'

 current_user
--------------
 shop_app
(1 row)

همین رفتار را در ssh هم دیدی (کلید خصوصی با مجوز باز قبول نمی‌شود). برنامه‌ها معمولاً رمز را از متغیر محیطی یا فایل تنظیمات با مجوز 600 می‌خوانند، هرگز از کد داخل گیت.

مثال ۸: رمزها در دیتابیس چطور ذخیره می‌شوند؟

با sudo -u postgres psql:

SELECT rolname, left(rolpassword, 40) || '...' AS rolpassword FROM pg_authid WHERE rolname = 'shop_app';

 rolname  |                 rolpassword
----------+---------------------------------------------
 shop_app | SCRAM-SHA-256$4096:pEO0MfrvcX9zHzpWtBUnU...
(1 row)

خود رمز ذخیره نشده؛ یک هش نمک‌دار با ۴۰۹۶ دور تکرار (همان مفهوم‌های درس رمز عبور و هش در دوره‌ی مبانی). روش scram-sha-256 در pg_hba.conf هم یعنی رمز هیچ‌وقت به‌صورت خام از شبکه رد نمی‌شود. (عددهای هش برای تو فرق می‌کنند؛ نمک تصادفی است.) برای عوض کردن رمز، در psql دستور \password shop_app را بزن: رمز را دو بار می‌پرسد و هش‌شده می‌فرستد، پس در تاریخچه‌ی psql هم نمی‌ماند.

مثال ۹: تزریق SQL با چشم خودت

یک اسکریپت کوچک bash که اسم محصول را می‌گیرد و جست‌وجو می‌کند. فایل ~/find-product.sh:

#!/usr/bin/env bash
# نسخه‌ی خطرناک: ورودی کاربر مستقیم داخل متن SQL
psql shop -c "SELECT name, price, stock FROM products WHERE name = '$1';"

chmod +x ~/find-product.sh
~/find-product.sh 'هدفون'

 name  |  price  | stock
-------+---------+-------
 هدفون | 1250000 |     0
(1 row)

درست کار می‌کند. حالا ورودی‌ای که خودش ' دارد:

~/find-product.sh "x' OR '1'='1"

      name      |  price  | stock
----------------+---------+-------
 کتاب پایتون    |  320000 |    12
 کتاب لینوکس    |  280000 |     0
 کتاب داکر      |  350000 |     5
 ماوس بی‌سیم     |  450000 |    30
 کیبورد مکانیکی | 1890000 |     8
 هدفون          | 1250000 |     0
 هارد اکسترنال  | 3200000 |     4
 فلش 64 گیگ     |  290000 |    50
 SSD 512 گیگ    | 2750000 |     7
 کابل شبکه      |   95000 |   120
 روتر وای‌فای    | 2400000 |     3
 سوئیچ 8 پورت   | 1650000 |     0
(12 rows)

همه‌ی جدول بیرون آمد. bash ورودی را داخل متن چسباند و دیتابیس این را دید: WHERE name = 'x' OR '1'='1'؛ شرطی که برای همه‌ی سطرها درست است. ورودی کاربر بخشی از کد شد. بدتر:

~/find-product.sh "x'; UPDATE products SET price = 1; --"
psql shop -c 'SELECT name, price FROM products ORDER BY id LIMIT 2'

 name | price | stock
------+-------+-------
(0 rows)

UPDATE 12
    name     | price
-------------+-------
 کتاب پایتون |     1
 کتاب لینوکس |     1
(2 rows)

; دستور اول را تمام کرد، دستور دوم مهاجم اجرا شد و -- (توضیح در SQL) بقیه‌ی خط را بی‌اثر کرد. قیمت همه‌ی محصول‌ها یک تومان. (داده را با psql shop -f ~/shop.sql برگردان.)

راه‌حل: ورودی را هرگز داخل متن SQL نچسبان؛ به‌عنوان «پارامتر» جدا بفرست. در psql با متغیر و :'نام' (که مقدار را درست نقل‌قول و فرار می‌کند). فایل ~/find-product-safe.sh:

#!/usr/bin/env bash
# نسخه‌ی امن: ورودی به‌عنوان «متغیر» به psql داده می‌شود و :'name' آن را درست نقل‌قول می‌کند
psql shop -v name="$1" <<'SQL'
SELECT name, price, stock FROM products WHERE name = :'name';
SQL

chmod +x ~/find-product-safe.sh
~/find-product-safe.sh 'هدفون'
~/find-product-safe.sh "x' OR '1'='1"
~/find-product-safe.sh "x'; UPDATE products SET price = 1; --"

 name  |  price  | stock
-------+---------+-------
 هدفون | 1250000 |     0
(1 row)

 name | price | stock
------+-------+-------
(0 rows)

 name | price | stock
------+-------+-------
(0 rows)

حالا ورودی مهاجم فقط یک اسم عجیب است که هیچ محصولی با آن نیست. (<<'SQL' همان heredoc درس bash است؛ نقل‌قول دور SQL یعنی bash داخلش را دست نزند.) در برنامه‌ها هم همین قانون است. در پایتون با کتابخانه‌ی psycopg (روی اوبونتو: sudo apt install python3-psycopg):

import psycopg

with psycopg.connect("dbname=shop") as conn:
    name = input("اسم محصول: ")
    # غلط:  conn.execute(f"SELECT ... WHERE name = '{name}'")
    rows = conn.execute(
        "SELECT name, price FROM products WHERE name = %s", (name,)
    ).fetchall()

%s اینجا قالب‌بندی متن پایتون نیست؛ جای پارامتر است و کتابخانه مقدار را جدا از متن کوئری به دیتابیس می‌فرستد. هیچ ورودی‌ای نمی‌تواند شکل کوئری را عوض کند.

در کوئری پارامتری، متن SQL و مقدار جدا به سرور می‌رسند؛ مقدار هرگز به‌عنوان کد خوانده نمی‌شود.

پشت پرده: دیتابیس را به اینترنت باز نکن

PostgreSQL روی اوبونتو به‌طور پیش‌فرض فقط روی localhost گوش می‌دهد:

sudo ss -tlnp | grep 5432

LISTEN 0      200        127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=812,fd=6))
LISTEN 0      200            [::1]:5432          [::]:*    users:(("postgres",pid=812,fd=5))

127.0.0.1:5432 یعنی از بیرون این کامپیوتر اصلاً قابل اتصال نیست (درس پورت و سوکت). اگر برنامه روی همان سرور است، همین بهترین حالت است. اگر برنامه روی سرور دیگری است، سه قفل لازم داری و هر سه را:

در postgresql.conf: listen_addresses = 'localhost,10.0.0.5' (فقط آدرس شبکه‌ی داخلی، هرگز '*' روی سرور با IP عمومی).
در pg_hba.conf: یک خط دقیق، مثل host shop shop_app 10.0.0.8/32 scram-sha-256 (فقط این دیتابیس، این کاربر، از این آدرس).
در فایروال (درس ufw): sudo ufw allow from 10.0.0.8 to any port 5432 proto tcp.

بعد از تغییر pg_hba.conf: sudo systemctl reload postgresql؛ بعد از تغییر listen_addresses: restart. ربات‌ها اینترنت را برای پورت 5432 باز با رمزهای ضعیف دائماً می‌گردند؛ همان داستان SSH و fail2ban در دوره‌ی لینوکس.

جدول‌های مرجع

دستور	کار
`CREATE ROLE u LOGIN PASSWORD '...';`	کاربر جدید (با postgres)
`CREATE ROLE g NOLOGIN;`	نقش گروهی
`GRANT g TO u;`	عضویت در گروه
`GRANT SELECT, INSERT ON t TO u;`	دسترسی جدول
`GRANT SELECT (a, b) ON t TO u;`	دسترسی ستونی
`GRANT USAGE ON SEQUENCE s TO u;`	برای INSERT با SERIAL
`GRANT SELECT ON ALL TABLES IN SCHEMA public TO g;`	همه‌ی جدول‌های موجود
`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO g;`	جدول‌های آینده
`REVOKE ... FROM u;`	پس گرفتن
`\du` / `\dp t`	فهرست نقش‌ها / دسترسی‌های جدول
`\password u`	تغییر امن رمز

حرف در `\dp`	دسترسی
`r`	SELECT
`a`	INSERT
`w`	UPDATE
`d`	DELETE
`D`	TRUNCATE
`x`	REFERENCES
`t`	TRIGGER

اشتباهات رایج

۱) برنامه با `postgres` یا صاحب جدول‌ها

هر باگ برنامه = اختیار کامل. راه‌حل: نقش جدا برای هر برنامه با کمترین دسترسی؛ صاحب جدول‌ها فقط برای ساختن و تغییر ساختار (migration).

۲) فراموش کردن sequence

permission denied for sequence. راه‌حل: GRANT USAGE ON SEQUENCE (یا GRANT USAGE ON ALL SEQUENCES IN SCHEMA public).

۳) گم شدن دسترسی‌ها بعد از ساختن دوباره‌ی جدول

shop.sql جدول‌ها را DROP و دوباره CREATE می‌کند؛ GRANTهای shop_app با جدول قدیمی از بین می‌روند (فقط ALTER DEFAULT PRIVILEGES روی جدول‌های تازه اعمال می‌شود). راه‌حل: دستورهای GRANT را هم در یک فایل SQL نگه دار و بعد از ساختن جدول‌ها اجرا کن (تمرین سخت).

۴) رمز در کد یا تاریخچه

رمز داخل اسکریپتی که در گیت است، یا CREATE ROLE ... PASSWORD که در ~/.psql_history می‌ماند. راه‌حل: ‎.pgpass با 600 یا متغیر محیطی؛ \password برای تغییر رمز.

۵) چسباندن ورودی به متن SQL

تزریق SQL (مثال ۹). راه‌حل: همیشه پارامتر؛ حتی برای ورودی‌هایی که «فقط از پنل مدیریت می‌آید».

تمرین

تمرینآسان

یک نقش auditor بساز که بتواند وارد شود و عضو گروه readonly باشد. وصل شو و ثابت کن می‌تواند سفارش‌ها را ببیند ولی نمی‌تواند هیچ سفارشی را لغو کند (UPDATE orders SET status = 'cancelled' WHERE id = 4).

دیدن جواب

sudo -u postgres psql -c "CREATE ROLE auditor LOGIN PASSWORD 'Aud1t-Pass' IN ROLE readonly"
psql -h localhost -U auditor shop

SELECT id, status FROM orders ORDER BY id;
UPDATE orders SET status = 'cancelled' WHERE id = 4;

 id |  status
----+-----------
  1 | paid
  2 | paid
  3 | paid
  4 | pending
  5 | cancelled
  6 | paid
(6 rows)

ERROR:  permission denied for table orders

IN ROLE readonly همان GRANT readonly TO auditor در یک خط است. (اگر بعد از مثال ۹ shop.sql را اجرا کرده‌ای، جدول‌ها تازه‌اند و دسترسی readonly از ALTER DEFAULT PRIVILEGES آمده؛ یعنی حالا customers را هم کامل می‌بیند. اشتباه رایج ۳.)

تمرینمتوسط

اسکریپت امن مثال ۹ را طوری گسترش بده که دو ورودی بگیرد: دسته و حداکثر قیمت (./cheap.sh 'کتاب' 330000) و محصول‌های آن دسته با قیمت کمتر از آن را نشان بدهد. ثابت کن با ورودی مخرب در هر کدام از دو پارامتر، چیزی خراب نمی‌شود.

دیدن جواب

#!/usr/bin/env bash
psql shop -v cat="$1" -v max="$2" <<'SQL'
SELECT name, price FROM products
WHERE category = :'cat' AND price < :'max'::integer
ORDER BY price;
SQL

chmod +x cheap.sh
./cheap.sh 'کتاب' 330000
./cheap.sh "x' OR '1'='1" 330000
./cheap.sh 'کتاب' "1; DROP TABLE products"

    name     | price
-------------+--------
 کتاب لینوکس | 280000
 کتاب پایتون | 320000
(2 rows)

 name | price
------+-------
(0 rows)

ERROR:  invalid input syntax for type integer: "1; DROP TABLE products"
LINE 2: WHERE category = 'کتاب' AND price < '1; DROP TABLE products'...
                                            ^

در پیام خطا می‌بینی psql مقدارها را نقل‌قول‌شده جایگزین کرده ('1; DROP TABLE products' یک متن است، نه دستور). عدد را هم به‌صورت متن نقل‌قول‌شده (:'max') گرفتیم و با ::integer تبدیلش کردیم؛ ورودی غیرعددی فقط یک خطای نوع می‌دهد و جدول سالم می‌ماند. (اگر :max بی‌نقل‌قول می‌نوشتی، ورودی دوباره کد می‌شد.)

تمرینسخت

یک فایل ~/grants.sql بنویس که همه‌ی دسترسی‌های shop_app (جدول‌ها و sequenceها) و دسترسی ستونی readonly روی customers را دوباره بدهد، و یک بار shop.sql و بعد این فایل را اجرا کن. ثابت کن shop_app دوباره می‌تواند سفارش ثبت کند. بعد بنویس اگر خواستی shop_app را کاملاً حذف کنی، چه دستورهایی لازم است.

دیدن جواب

GRANT SELECT, INSERT, UPDATE ON products, customers, orders, order_items TO shop_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO shop_app;
REVOKE SELECT ON customers FROM readonly;
GRANT SELECT (id, city, joined_on) ON customers TO readonly;

psql shop -f ~/shop.sql
psql shop -f ~/grants.sql
psql -h localhost -U shop_app shop -c "INSERT INTO orders (customer_id) VALUES (5) RETURNING id"

GRANT
GRANT
REVOKE
GRANT
 id
----
  7
(1 row)

INSERT 0 1

برای حذف کامل یک نقش، اول باید دسترسی‌ها و چیزهایی که مال اوست پاک شوند، وگرنه DROP ROLE خطای role "shop_app" cannot be dropped because some objects depend on it می‌دهد. با ali (برای جدول‌های خودش) و بعد با postgres:

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM shop_app;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM shop_app;
DROP ROLE shop_app;

(اگر نقش جدولی ساخته باشد، اول با REASSIGN OWNED BY shop_app TO ali; مالکیتش را منتقل کن. فعلاً shop_app را نگه دار؛ درس‌های بعد از آن استفاده می‌کنند.)

آزمونک

در PostgreSQL فرق «کاربر» و «نقش» چیست؟
اصل کمترین دسترسی یعنی…
psql -U shop_app shop (بدون -h) خطای Peer authentication failed داد. چرا؟
ALTER DEFAULT PRIVILEGES برای چیست؟
ورودی x' OR '1'='1 چرا همه‌ی سطرها را برگرداند؟
روی سرور با IP عمومی، listen_addresses = '*' و pg_hba با 0.0.0.0/0 چه خطری دارد؟

جمع‌بندی

نقش (role) = کاربر یا گروه؛ ساختنش با postgres؛ هرگز برنامه را با Superuser یا صاحب جدول‌ها وصل نکن.
GRANT دقیق: جدول، ستون، sequence؛ گروه NOLOGIN برای مدیریت ساده؛ ALTER DEFAULT PRIVILEGES برای جدول‌های آینده.
pg_hba.conf تعیین می‌کند چه کسی از کجا با چه روشی وارد شود؛ پیش‌فرض اوبونتو: peer روی سوکت، رمز روی localhost.
رمز در ~/.pgpass با 600، نه در کد؛ PostgreSQL رمزها را هش‌شده (SCRAM) نگه می‌دارد.
تزریق SQL: ورودی را هرگز به متن SQL نچسبان؛ همیشه پارامتر (:'var' در psql، %s در psycopg).
پورت 5432 را به اینترنت باز نکن: localhost، یا شبکه‌ی داخلی + خط دقیق hba + فایروال.

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
`sudo -u postgres psql`	psql با مدیر
`CREATE ROLE u LOGIN PASSWORD '...';`	کاربر جدید
`GRANT SELECT, INSERT ON t TO u;`	دسترسی
`GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO u;`	sequenceها
`GRANT g TO u;`	عضویت گروه
`\du / \dp t`	نقش‌ها / دسترسی‌ها
`psql -h localhost -U u db`	ورود با رمز
`psql -v x="$1" ... WHERE c = :'x'`	پارامتر امن در psql

مسئله: همه با یک کاربر، با همه‌ی اختیارات

تشبیه: کارت‌های ورود ساختمان

مثال‌های عملی

مثال ۱: ساختن نقش، کار مدیر دیتابیس است

مثال ۲: ورود با رمز، و نگهبان دم در

مثال ۳: کاربر جدید هیچ دسترسی‌ای ندارد

مثال ۴: ببین چه چیزهایی هنوز ممنوع است

مثال ۵: نقش گروهی برای گزارش‌گیری

مثال ۶: دسترسی در سطح ستون

مثال ۷: رمز امن در اسکریپت با ‎.pgpass

مثال ۸: رمزها در دیتابیس چطور ذخیره می‌شوند؟

مثال ۹: تزریق SQL با چشم خودت

پشت پرده: دیتابیس را به اینترنت باز نکن

جدول‌های مرجع

اشتباهات رایج

۱) برنامه با postgres یا صاحب جدول‌ها

۲) فراموش کردن sequence

۳) گم شدن دسترسی‌ها بعد از ساختن دوباره‌ی جدول

۴) رمز در کد یا تاریخچه

۵) چسباندن ورودی به متن SQL

تمرین

آزمونک

جمع‌بندی

۱) برنامه با `postgres` یا صاحب جدول‌ها