【初心者向け】PostgreSQLでやってはいけないこと

PostgreSQL初心者の筆者が、うっかりやってしまってめちゃくちゃ冷や汗をかいたので、
備忘録として書いていきます。
また、PostgreSQLを始める人のお役に立てればと思います。

この記事の対象者
---
・PostgreSQL初心者
・PostgreSQLをインストールしようと思っている人
・PostgreSQLでやってはいけないことを知りたい人
・スーパーユーザ権限を削除して「しまった！」と思っている人

動作環境
---
・Windows 10
・PostgreSQL 12
・SQL Shell(psql)

PostgreSQL12インストール時の初期状態
---
スキーマ名：postgres
データベース名：postgres
ユーザ（ロール）：postgres


#PostgreSQLでやってはいけないこと
<h3>デフォルトユーザで、<br>且つスーパーユーザの権限を持っている「postgres」から、<br>
スーパーユーザの権限を削除してはいけない。</h3>

です。結論から言って、この状態から、気軽に以下のコマンドを打ってはいけません。

```SQL:psql
postgres=# ALTER ROLE postgres NOSUPERUSER;
```

では内容について、用語解説を加えながら詳しく書いていきます。

**デフォルトユーザとは**
インストール時に、初期状態から作成されているユーザのことです。つまりここでは、「postgres」を指します。
また「postgres」はスーパーユーザーでもあり、データベースの管理ユーザでもあります。
※データベースの管理ユーザの説明については、割愛させていただきます。

**スーパーユーザとは**
管理者権限とも呼ばれ、簡単に言えば、「なんでもできる権限をもつユーザ」です。
逆に言えば、スーパーユーザでなければ、操作の一部が制限されるということです。

**「postgres=#」の意味**
「#」はスーパユーザであることを指し、スーパーユーザでない場合は、「>」で表されます（たとえば「postgres=>」）。
そのため、「postgres=#」とは、
`データベース「postgres」のスーパーユーザ「postgres」でログインしていること`を意味します。

**ALTER ROLE postgres NOSUPERUSER;」のコマンドについて**
「ALTER ROLE ロール名 属性」で、ロールの属性を変更します。
このコマンドの意味は、`ロール「postgres」の属性を、スーパーユーザでない属性（NOSUPERUSER）に変更する。`となります。

「ロール」と「ユーザ」の単語が出てきていますが、統一して考えてよいです。
理由としては、PostgreSQL 9.5.4文書にそのように記載があるためです。
>「ユーザ」と「グループ」という概念は「ロール」に統合されました
[PostgreSQL 9.5.4文書](https://www.postgresql.jp/document/9.5/html/app-psql.html)

---
ここまでで、先ほどの結論の言っていることがわかるかと思います。
<h4>この状態（スーパーユーザpostgres）から、気軽に以下のコマンド（スーパーユーザでない権限に変更する）を打ってはいけません。</h4>
ということです。
（大事なので２度書いています）

```SQL:psql
postgres=# ALTER ROLE postgres NOSUPERUSER;
```

では、なぜ気軽に打ってはいけないのか、その理由を書いていきます。

#スーパーユーザの権限を付与できるのは、スーパユーザのみ

スーパユーザの権限は、スーパユーザでないと与えることができません。
以下のような手順で確認してみます。

１.　スーパーユーザ「postgres」でログインし、スーパユーザでない「test」ユーザを作成します。

```SQL:psql
postgres=# CREATE ROLE test LOGIN PASSWORD 'test';
CREATE ROLE
```

２.　\dgコマンドで、ロールの一覧を表示し、ロールの属性を確認します。

```
postgres=# \dg
                                             ロール一覧
 ロール名  |                                   属性                                   | 所属グループ
-----------+--------------------------------------------------------------------------+--------------
 postgres  | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス      | {}
 test      |                                                                         | {}
```

「postgres」がスーパユーザ状態、「test」はスーパユーザ状態でないことが確認できました。

３.　PostgreSQLに、ユーザ「test」としてログインします。

```
postgres=# \c - test
```

４.　ユーザ「test」から、ユーザ「test」にスーパユーザの権限を与えるコマンドをたたきます。

```SQL:psql
postgres=> ALTER ROLE test NOSUPERUSER;
ERROR:  スーパユーザを更新するにはスーパユーザである必要があります
```

すると、上記のようにエラーとなってしまいます。
これでスーパユーザの権限は、スーパユーザでないと付与できないことを確認しました。

これを踏まえて考えると、スーパーユーザ「postgres」から、スーパーユーザ権限を削除してしまうと、何が起きるか想像がつきますね。

そうです、**「postgres」にスーパユーザの権限を、復元する手段がなくなってしまうのです。**
※pgAdminからも復元はできないので、ご注意ください。pgAdminとは、PostgreSQLのデータベースやユーザの管理をGUIで行えるツールのことです。

#スーパーユーザpostgresの復元方法

<h4>できるなら再インストールしましょう。</h4>
それが早いです。
これによって、新たにスーパーユーザの権限を持ち、且つデフォルトユーザであるpostgresを使用することができます。


以下は復元方法についての余談です。
再インストール以外に復元方法はないか探したところ、スーパーユーザを復元させる方法がいくつかヒットしました。
例えば、以下のような記事です。

- [PostgreSQLでpostgresを回復する](https://stackoverflow.com/questions/51973732/recover-postgres-user-in-postgresql)

- [誤まってPostgreSQLユーザーの権限を削除した場合の対応方法](https://teratail.com/questions/21787)

しかし筆者はどれを試してもうまくいきませんでした。
記事で見受けれられるpostgesコマンドにオプション-Dを付けて実行する方法についてですが、
PostgreSQL12.4文書においては、それに関する記載は見受けられなかったため、PostgreSQL 12でも使用できるのか不明です。
もし何かの方法でスーパーユーザを復元できたら、また記事にしようと思います。

#対策

こんなことが起こらないように、以下のような予防策が必要かと思います。

- 必ずpostgres以外のユーザを作成する（例：test、test_userなど）。

- デフォルトユーザーであり、且つスーパーユーザーの権限をもつpostgresでは、動作確認は行わない。

- ロール属性を変更するALTER ROLEや、権限を付与する「GRANT」、権限を削除する「REVOKE」コマンドを打つ前に、<br> 現在のユーザ（ロール）を確認する（select current_user;）。

#あとがき

筆者がスーパーユーザの権限を取ってしまったのが、ローカル環境だったのでまだ救いがありました。
これが本番環境でしてしまったなんて考えると…恐ろしいですね…。

スーパーユーザ（管理者権限）を削除してはいけないというのは、PostgreSQLに限らず当たり前のことなのですが、
うっかりやってしまったので、備忘録兼自分への戒めとして残しました。

同じように「しまった！」と思ってる人や、PostgreSQL初心者の方のお役に立てれば幸いです。

#参考

- [[PostgreSQL] よく使うコマンドまとめ](https://dev.classmethod.jp/articles/postgresql-organize-command/)

　　データベース、テーブル、ユーザ権限、スキーマについてのコマンドが書かれています。

- [PostgresSQLの初期設定](https://qiita.com/motoki1990/items/ad4175289f9ce50d5396)

　　GRANTやREVOKEコマンドについても詳しく記載されています。