SQLインジェクションの対策として、根本的なものとしては、プレースホルダの方で対応するのが最善。（参考：安全なウェブサイトの作り方 - 1.1 SQLインジェクション）
今回は、保険的な対策の『データベースアカウントに適切な権限を与える』に取り組んだ。
まずは、memoDBにパスワード付きユーザを作る。
memoDB=# CREATE USER goruchan PASSWORD 'hogehoge'; CREATE ROLE memoDB=# SELECT usename FROM pg_user; usename ---------- goruchan (1 rows)
次に作ったユーザに権限を付与する。これをしないと、作成したユーザでデータベースにアクセスすることができない。権限は、GRANTコマンドで与えることができる
-- ユーザに指定したテーブルのSELECT権限を与える memoDB=# GRANT SELECT ON memos TO goruchan; GRANT -- ユーザに指定したテーブルのUPDATE権限を与える memoDB=# GRANT UPDATE ON memos TO goruchan; GRANT -- ユーザに指定したテーブルのINSERT権限を与える memoDB=# GRANT INSERT ON memos TO goruchan; GRANT -- ユーザに指定したテーブルのDELETE権限を与える memoDB=# GRANT DELETE ON memos TO goruchan; GRANT -- ユーザに指定したテーブルの権限をまとめて与える GRANT SELECT, UPDATE, INSERT, DELETE ON memos TO goruchan; -- 利用可能な権限全てを一度に付与 GRANT ALL PRIVILEGES ON memos TO goruchan;
下記コマンドを叩いて、ユーザ権限を確認する。
memoDB=# \z memos
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+----------------------+-------------------+----------
public | memos | table | foo=arwdDxt/foo +| |
| | | goruchan=arwdDxt/foo | |
(1 row)
Access privilegesの記号の意味は次のとおり。
| 記号 | 意味 |
|---|---|
| r | SELECT (読み取り(read)) |
| w | UPDATE (書き込み(write)) |
| a | INSERT (追加(append)) |
| d | DELETE |
| R | RULE |
| x | REFERENCES |
| t | TRIGGER |
| X | EXECUTE |
| U | USAGE |
| C | CREATE |
| T | TEMPORARY |
ただ、INSERT権限を与えたのに、なぜか新規作成できない😱調べてみると、どうやらメモのIDのタイプをSERIALにしていたのが影響していたみたい。SERIALにすると、Sequenceとして定義されるらしい。
そして、Sequenceは、このコマンドを実行したユーザによって所有される。よって、所有者以外が使用するためには、当該ユーザにSequenceの権限を与える必要がある。
というわけで、Sequenceに権限を与える。
-- Sequenceを確認する memoDB=# \ds List of relations Schema | Name | Type | Owner --------+-------------------+----------+------- public | memos_memo_id_seq | sequence | ryo (1 row) -- Sequenceにも権限を与える GRANT USAGE ON SEQUENCE memos_memo_id_seq TO goruchan;
これで新しく作ったユーザでメモアプリの日通りのDB操作ができるようになりました。
