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 ではマスターユーザーでのみ実行可能です。
今回は以上です。
PostgreSQLRDSSQL
