MySQL経験者がPostgreSQLにハマりがちなポイント＆実践ノウハウまとめ（RLS・スキーマ・接続文字列含む）


PostgreSQL を業務で本格的に使い始めて10年。MySQL時代から合わせるともう20年近くDB触ってますが、**PostgreSQLはハマるポイントが多い一方で、うまく付き合うと非常に強力なDBMS**です。

この記事では、**MySQLに慣れたエンジニアがPostgreSQLを使いこなすための最短ルート**を、サンプルとともにまとめておきます。特に、\*\*スキーマ、接続文字列、そしてRLS（行レベルセキュリティ）\*\*あたりは、最初に抑えておくと後がラクです。

---

## 🔰 基本の違いとよく使うコマンド比較

| 操作       | MySQL              | PostgreSQL               |
| -------- | ------------------ | ------------------------ |
| CLI起動    | `mysql -u user -p` | `psql -U user -d dbname` |
| DB一覧     | `SHOW DATABASES;`  | `\l`                     |
| テーブル一覧   | `SHOW TABLES;`     | `\dt`                    |
| テーブル構造確認 | `DESC users;`      | `\d users`               |
| DB切り替え   | `USE dbname;`      | `\c dbname`              |

PostgreSQLのpsqlは `\` から始まる独自コマンドが充実してます。`TAB` 補完も強力。

---

## 📁 PostgreSQLの「スキーマ」概念に注意

MySQLはデータベースを分けるのが主流ですが、PostgreSQLは**1つのデータベース内に複数のスキーマを持つ設計**です。

### ✅ 例

```sql
CREATE SCHEMA accounting;

CREATE TABLE accounting.invoices (
  id SERIAL PRIMARY KEY,
  amount NUMERIC,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

デフォルトは `public` スキーマ。**何も指定しないと public にテーブルが作られます**。

---

## 🔐 public スキーマの扱いに注意

### 初期状態では「誰でもCREATEできてしまう」

```sql
GRANT ALL ON SCHEMA public TO PUBLIC;
```

つまり**アプリユーザーが勝手にテーブルや関数を作れる可能性あり**。以下で制限推奨。

```sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```

---

## 🔌 接続文字列（DSN）と環境変数

PostgreSQLはURL形式で接続できます。

```bash
psql postgresql://user:pass@localhost:5432/mydb
```

または環境変数で設定：

```bash
export PGUSER=myuser
export PGPASSWORD=mypassword
export PGDATABASE=mydb
psql
```

CLIツールやアプリケーション設定ファイルに使えるので便利。

---

## 🔧 型の違い・対応表

| MySQL               | PostgreSQL         |
| ------------------- | ------------------ |
| INT AUTO\_INCREMENT | SERIAL or IDENTITY |
| DATETIME            | TIMESTAMP          |
| BOOLEAN（TINYINT）    | BOOLEAN            |
| TEXT                | TEXT               |

---

## 🔄 `REPLACE INTO`は使えない → `ON CONFLICT`

MySQLの `REPLACE INTO` はPostgreSQLにはありません。代替はこれ：

```sql
INSERT INTO users (id, name)
VALUES (1, 'Hanako')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;
```

追記：MERGE文の追加（PostgreSQL 15新機能）
例（PostgreSQL 15以降）:
```sql
MERGE INTO users AS target
USING (VALUES (1, 'Hanako')) AS source(id, name)
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (source.id, source.name);
```
---

## 🧪 トランザクションの使い方

```sql
BEGIN;
UPDATE users SET name = 'foo' WHERE id = 1;
COMMIT; -- or ROLLBACK;
```

PostgreSQLはトランザクション制御がとても堅牢。

---

## 🛡️ Row-Level Security（RLS）を使いこなす

### 🔍 概要

* テーブルの**行単位でアクセス制御**ができる機能
* ユーザーごとに\*\*「見える行」「操作できる行」\*\*を制御可能
* SaaSやマルチテナントで非常に重宝する

---

### ✅ 基本構成

```sql
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY my_policy
ON notes
FOR SELECT
USING (user_id = current_setting('app.current_user_id')::INT);

ALTER TABLE notes FORCE ROW LEVEL SECURITY;
```

アプリケーション側でセッション変数を設定：

```sql
SELECT set_config('app.current_user_id', '101', false);
SELECT * FROM notes;  -- user_id = 101 の行のみ見える
```

---

### INSERT/UPDATE/DELETE制御も追加可能

```sql
CREATE POLICY write_policy
ON notes
FOR INSERT, UPDATE, DELETE
USING (user_id = current_setting('app.current_user_id')::INT)
WITH CHECK (user_id = current_setting('app.current_user_id')::INT);
```

---

## 🧭 search\_path を活用したスキーマ分離

```sql
SET search_path TO accounting, public;
```

スキーマを切り替えて参照できる。アプリユーザーごとに設定して、**名前衝突や誤操作を防ぐ**。

```sql
ALTER ROLE app_user SET search_path = accounting;
```

---

## ✅ 実運用でのおすすめベストプラクティス

| 項目                | 推奨                                       |
| ----------------- | ---------------------------------------- |
| スキーマ設計            | `public`を使わず、機能ごとに独自スキーマを作成              |
| public制御          | `REVOKE CREATE`で制限必須                     |
| search\_path      | ユーザー/ロール単位で明示的に設定                        |
| RLS               | セッション変数（例: tenant\_id, user\_id）＋ポリシーで制御 |
| 接続文字列             | DSN形式 or 環境変数で柔軟に管理                      |
| AUTO\_INCREMENT代替 | `SERIAL` or `GENERATED AS IDENTITY`      |

---

## 📝 最後に

PostgreSQLはMySQLよりも柔軟で強力な一方、設計ミスや運用ミスが**セキュリティリスクに直結しやすい**です。特に**スキーマとRLSを理解して使えるようになると、かなり運用の質が上がります**。

RLSをちゃんと組み込んだSaaSアプリは、**アプリがバグってもテナント越境しない構造にできる**。これはMySQLにはない大きな強みです。