PostgreSQL初心者の筆者が、うっかりやってしまってめちゃくちゃ冷や汗をかいたので、
備忘録として書いていきます。
また、PostgreSQLを始める人のお役に立てればと思います。
この記事の対象者
・PostgreSQL初心者
・PostgreSQLをインストールしようと思っている人
・PostgreSQLでやってはいけないことを知りたい人
・スーパーユーザ権限を削除して「しまった！」と思っている人
動作環境
・Windows 10
・PostgreSQL 12
・SQL Shell(psql)
PostgreSQL12インストール時の初期状態
スキーマ名：postgres
データベース名：postgres
ユーザ（ロール）：postgres
#PostgreSQLでやってはいけないこと
デフォルトユーザで、
且つスーパーユーザの権限を持っている「postgres」から、
スーパーユーザの権限を削除してはいけない。
です。結論から言って、この状態から、気軽に以下のコマンドを打ってはいけません。
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文書
ここまでで、先ほどの結論の言っていることがわかるかと思います。
この状態（スーパーユーザpostgres）から、気軽に以下のコマンド（スーパーユーザでない権限に変更する）を打ってはいけません。
ということです。 （大事なので２度書いています）postgres=# ALTER ROLE postgres NOSUPERUSER;
では、なぜ気軽に打ってはいけないのか、その理由を書いていきます。
#スーパーユーザの権限を付与できるのは、スーパユーザのみ
スーパユーザの権限は、スーパユーザでないと与えることができません。
以下のような手順で確認してみます。
１. スーパーユーザ「postgres」でログインし、スーパユーザでない「test」ユーザを作成します。
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」にスーパユーザの権限を与えるコマンドをたたきます。
postgres=> ALTER ROLE test NOSUPERUSER;
ERROR: スーパユーザを更新するにはスーパユーザである必要があります
すると、上記のようにエラーとなってしまいます。
これでスーパユーザの権限は、スーパユーザでないと付与できないことを確認しました。
これを踏まえて考えると、スーパーユーザ「postgres」から、スーパーユーザ権限を削除してしまうと、何が起きるか想像がつきますね。
そうです、「postgres」にスーパユーザの権限を、復元する手段がなくなってしまうのです。
※pgAdminからも復元はできないので、ご注意ください。pgAdminとは、PostgreSQLのデータベースやユーザの管理をGUIで行えるツールのことです。
#スーパーユーザpostgresの復元方法
できるなら再インストールしましょう。
それが早いです。 これによって、新たにスーパーユーザの権限を持ち、且つデフォルトユーザであるpostgresを使用することができます。以下は復元方法についての余談です。
再インストール以外に復元方法はないか探したところ、スーパーユーザを復元させる方法がいくつかヒットしました。
例えば、以下のような記事です。
しかし筆者はどれを試してもうまくいきませんでした。
記事で見受けれられるpostgesコマンドにオプション-Dを付けて実行する方法についてですが、
PostgreSQL12.4文書においては、それに関する記載は見受けられなかったため、PostgreSQL 12でも使用できるのか不明です。
もし何かの方法でスーパーユーザを復元できたら、また記事にしようと思います。
#対策
こんなことが起こらないように、以下のような予防策が必要かと思います。
-
必ずpostgres以外のユーザを作成する（例：test、test_userなど）。
-
デフォルトユーザーであり、且つスーパーユーザーの権限をもつpostgresでは、動作確認は行わない。
-
ロール属性を変更するALTER ROLEや、権限を付与する「GRANT」、権限を削除する「REVOKE」コマンドを打つ前に、
現在のユーザ（ロール）を確認する（select current_user;）。
#あとがき
筆者がスーパーユーザの権限を取ってしまったのが、ローカル環境だったのでまだ救いがありました。
これが本番環境でしてしまったなんて考えると…恐ろしいですね…。
スーパーユーザ（管理者権限）を削除してはいけないというのは、PostgreSQLに限らず当たり前のことなのですが、
うっかりやってしまったので、備忘録兼自分への戒めとして残しました。
同じように「しまった！」と思ってる人や、PostgreSQL初心者の方のお役に立てれば幸いです。
#参考
データベース、テーブル、ユーザ権限、スキーマについてのコマンドが書かれています。
GRANTやREVOKEコマンドについても詳しく記載されています。