PostgreSQL の Public ロールと Public スキーマ

  • PostgreSQL
  • RDS
  • SQL
  • Public ロールと Public スキーマとは

    一言で言えば、全てのユーザーが属するデフォルトのロール、スキーマと言えます。

    正直なところ、Public ロールと Public スキーマについて正確に理解していなくても、色々できてしまうところがあります。

    実際にはここをちゃんと理解しないまま、DBやスキーマ・テーブルの設計をしてしまっている方も多いと思います。

    ということで細かく見ていきましょう。

    Public ロール

    全てのユーザーはデフォルトで Public ロールに属しています。

    ¥l データベースの一覧

    「xx00」「xy00」が自分で作成したデータベースです。これらは Public ロールによるアクセスを制限しています。

    masterdb=> \l データベース一覧 名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限 -----------+------------+------------------+-------------+-------------------+--------------------------- masterdb | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | masteruser=CTc/masteruser postgres | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | masteruser=CTc/masteruser rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin + | | | | | rdsadmin=CTc/rdsadmin template1 | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/masteruser + | | | | | masteruser=CTc/masteruser xx00 | xx_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xx_adm=CTc/xx_adm + | | | | | xx_apl1=c/xx_adm + | | | | | xx_bat1=Tc/xx_adm + | | | | | xx_ipls=Tc/xx_adm xy00 | xy_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xy_adm=CTc/xy_adm + | | | | | xx_apl1=c/xy_adm + | | | | | xx_bat1=Tc/xy_adm

    Public ロールを許可したデータベースのアクセス権限

    新たにデータベース「xx01」を作ってみます。「アクセス権限」のところに注目して結果を見てください。

    masterdb=> CREATE DATABASE xx01 lc_collate 'ja_JP.UTF-8' lc_ctype 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0 OWNER xx_adm; CREATE DATABASE masterdb=> masterdb=> \l データベース一覧 名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限 -----------+------------+------------------+-------------+-------------------+--------------------------- masterdb | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | masteruser=CTc/masteruser postgres | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | masteruser=CTc/masteruser rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin + | | | | | rdsadmin=CTc/rdsadmin template1 | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/masteruser + | | | | | masteruser=CTc/masteruser xx00 | xx_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xx_adm=CTc/xx_adm + | | | | | xx_apl1=c/xx_adm + | | | | | xx_bat1=Tc/xx_adm + | | | | | xx_ipls=Tc/xx_adm xx01 | xx_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xy00 | xy_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xy_adm=CTc/xy_adm + | | | | | xx_apl1=c/xy_adm + | | | | | xx_bat1=Tc/xy_adm

    アクセス権限に何も表示されませんね。実はこれ Public ロールによるアクセスが許可されている状態なのです。

    Public ロールによるアクセスが許可されているということは、誰でもログインできてしまうということです。

    $ psql -U xx_apl1 -d xx00 psql (12.5、サーバ 11.8) SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ) "help"でヘルプを表示します。 xx00=> xx00=> \c xx01 psql (12.5、サーバ 11.8) SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ) データベース"xx01"にユーザ"xx_apl1"として接続しました。 xx01=>

    ミッションクリティカルなDBで Public ロールは危険

    上記の例からわかる通り、使い方によっては、Public ロールが危険であることはご理解いただけたかと思います。

    もちろん、スキーマやテーブルのアクセス権を制限していれば、DB にアクセスできたとしてもデータを閲覧されることはありません。

    Public ロールによるアクセスを制限

    public role による操作を revoke できます。

    masterdb=> revoke all on database xx01 from public; REVOKE masterdb=> masterdb=> \l データベース一覧 名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限 -----------+------------+------------------+-------------+-------------------+--------------------------- masterdb | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | masteruser=CTc/masteruser postgres | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | masteruser=CTc/masteruser rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin + | | | | | rdsadmin=CTc/rdsadmin template1 | masteruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/masteruser + | | | | | masteruser=CTc/masteruser xx00 | xx_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xx_adm=CTc/xx_adm + | | | | | xx_apl1=c/xx_adm + | | | | | xx_bat1=Tc/xx_adm + | | | | | xx_ipls=Tc/xx_adm xx01 | xx_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xx_adm=CTc/xx_adm xy00 | xy_adm | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | xy_adm=CTc/xy_adm + | | | | | xx_apl1=c/xy_adm + | | | | | xx_bat1=Tc/xy_adm

    すると、明示的に DB オーナーによるアクセス権限のみが表示されるようになりました。

    別のユーザーでログインを試みてみます。

    $ psql -U xx_apl1 -d xx00 psql (12.5、サーバ 11.8) SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ) "help"でヘルプを表示します。 xx00=> xx00=> \c xx01 FATAL: permission denied for database "xx01" DETAIL: User does not have CONNECT privilege. 以前の接続は保持されています xx00=>

    アクセス権がないということで、接続が失敗していますね。

    以上が Public ロールの仕様です。不要なアクセスをさせないために、DB 構築時に Public スキーマによるアクセスは revoke しておくと良いでしょう。

    Public スキーマ

    Public スキーマは誰でもアクセスできるスキーマです。DB 構築時にデフォルトで存在します。

    xx01=> \dn スキーマ一覧 名前 | 所有者 --------+---------- public | rdsadmin (1 行)

    Public スキーマの所有者

    結論から言うと、Public スキーマの所有者は template0 の所有者です。

    「¥l」の結果より、データベース template0 の所有者と同じであることが確認できます。

    データベース作成コマンドを見るとデータベース template0 を使ってデータベース xx01 を作成していることがわかります。

    PostgreSQL の場合、データベース作成コマンドは内部的に、デフォルトで template1 をコピーして新たにデータベースを作ろうとします。

    このことは PostgreSQL公式サイトにも掲載されています。

    Public スキーマへ Public ロールがアクセスできる!?

    スキーマのアクセス権限を見てみましょう。「¥dn」ではなく「¥dn+」とすることで追加の情報が見れます。

    xx01=> \dn+ スキーマ一覧 名前 | 所有者 | アクセス権限 | 説明 --------+----------+----------------------+------------------------ public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema | | =UC/rdsadmin | (1 行)

    このアクセス権の意味は以下のようになります。

    アクセス権限 意味
    rdsadmin=UC/rdsadmin+ rdsadmin によって rdsadmin の U=Usage,C=Create を許可
    =UC/rdsadmin rdsadmin によって public ロールの U=Usage,C=Create を許可

    この何も書いていない部分は Public というワードが隠れていたんですね〜。私もつい最近まで知りませんでした。

    Public スキーマへアクセスできないようにする

    以下の構文によって可能です。※AuroraPostgreSQL ではマスターユーザーでのみ実行可能です。

    xx01=> ALTER SCHEMA public OWNER TO xx_adm; ALTER SCHEMA xx01=> ¥dn+ スキーマ一覧 名前 | 所有者 | アクセス権限 | 説明 --------+--------+------------------+------------------------ public | xx_adm | xx_adm=UC/xx_adm+| standard public schema | | =UC/xx_adm | xx01=> revoke all on schema public from public; REVOKE xx01=> ¥dn+ スキーマ一覧 名前 | 所有者 | アクセス権限 | 説明 --------+--------+------------------+------------------------ public | xx_adm | xx_adm=UC/xx_adm | standard public schema

    スキーマオーナーを変更している理由は、AWSの公式サイトのナレッジを確認してください。

    この結果から、Public スキーマへアクセスできるのはスキーマオーナーだけになりましたね。

    「Public スキーマへアクセスできないようにする」とは、「Public スキーマへ Public ロールがアクセスできないようにする」ということと同じようなことです。

    まとめ

    Public ロールと Public スキーマを制限するときは、以下を実行しましょう。

    > revoke all on database xxxxxx from public; > ¥c xxxxxx > ALTER SCHEMA public OWNER TO xxxxxx; > revoke all on schema public from public;

    「ALTER SCHEMA public OWNER TO xxxxxx;」については、AuroraPostgreSQL ではマスターユーザーでのみ実行可能です。

    今回は以上です。

  • PostgreSQL
  • RDS
  • SQL