🕌
Cloud SQL for PostgreSQLにおけるロールの話
はじめに
これは、PostgreSQL Advent Calendar 2021の8日目の記事です。
いつかのPostgreSQLアンカンファレンスでPostgreSQLのCREATEROLE権限強すぎ問題について話しました。
CREATEROLE権限を持つロールは
- OSコマンドを実行する権限の付与が行えて、それによってスーパーユーザを奪取できてしまう
- スーパーユーザ以外のロールの削除/権限変更/メンバ資格変更などができてしまう（スーパーユーザを奪取してしまえば、スーパーユーザに対してもこの操作が行える）
本記事では、これらの技を使ってCloud SQL for PostgreSQLで悪さができないかなーと思って調べてみました。（RDS for PostgreSQLやAzure Database for PostgreSQLでも試す予定が、時間が無かったのでまたの機会に・・・）
実行環境
- バージョン
- Cloud SQL for PostgreSQL 13
- リージョン
- asia-northeast1 (東京)
- ゾーンの可用性
- 複数のゾーン（高可用性）
今回やること
- どんなロールがデフォルトで作られているか
- そのロールにログインできるか
- そのロールをALTER/DROPできるか
- そのロールのメンバ資格をGRANT/REVOKEできるか
- 自分自身にPostgreSQLのデフォルトロールを付与できるか（pg_execute_server_programなど）
実行結果
どんなロールがデフォルトで作られているか
postgres=> SELECT current_user ;
current_user
--------------
postgres
(1 row)
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {cloudsqlsuperuser}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {cloudsqlsuperuser}
cloudsqlreplica | Replication | {pg_monitor}
cloudsqlsuperuser | Create role, Create DB | {pg_monitor,pg_signal_backend}
postgres | Create role, Create DB | {cloudsqlsuperuser}
色々なロールがデフォルトで作られていますね。利用者はpostgresロールでログインできて、CREATEROLE権限とCREATEDB権限とcloudsqlsuperuserのメンバ資格を持っているようです。
そのロールにログインできるか
postgres=> \c - cloudsqladmin
Password for user cloudsqladmin:
postgres=> \c - cloudsqlagent
Password for user cloudsqlagent:
connection to server at "34.146.175.95", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "35.221.151.23", user "cloudsqlagent", database "postgres", SSL on
connection to server at "34.146.175.95", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "35.221.151.23", user "cloudsqlagent", database "postgres", SSL off
Previous connection kept
postgres=> \c - cloudsqlimportexport
Password for user cloudsqlimportexport:
connection to server at "34.146.175.95", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "35.221.151.23", user "cloudsqlimportexport", database "postgres", SSL on
connection to server at "34.146.175.95", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "35.221.151.23", user "cloudsqlimportexport", database "postgres", SSL off
Previous connection kept
postgres=> \c - cloudsqlreplica
Password for user cloudsqlreplica:
connection to server at "34.146.175.95", port 5432 failed: FATAL: connection requires a valid client certificate
connection to server at "34.146.175.95", port 5432 failed: FATAL: password authentication failed for user "cloudsqlreplica"
Previous connection kept
postgres=> \c - cloudsqlsuperuser
Password for user cloudsqlsuperuser:
connection to server at "34.146.175.95", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "35.221.151.23", user "cloudsqlsuperuser", database "postgres", SSL on
connection to server at "34.146.175.95", port 5432 failed: FATAL: pg_hba.conf rejects connection for host "35.221.151.23", user "cloudsqlsuperuser", database "postgres", SSL off
Previous connection kept
まあ予想通りですがログインできません。
そのロールをALTER/DROPできるか
素のPostgreSQLだったら、ログインしているpostgresロールはCREATEROLE権限を持っているので、スーパーユーザ以外はALTER/DROPできるはずです。試してみましょう。
postgres=> ALTER ROLE cloudsqladmin NOCREATEROLE ;
ERROR: must be superuser to alter superusers
postgres=> ALTER ROLE cloudsqlagent NOCREATEROLE ;
ERROR: "cloudsqlagent" can't be altered
postgres=> \errverbose
ERROR: 42501: "cloudsqlagent" can't be altered
LOCATION: AlterRole, user.c:792
postgres=> ALTER ROLE cloudsqliamserviceaccount LOGIN ;
ERROR: "cloudsqliamserviceaccount" can't be altered
postgres=> ALTER ROLE cloudsqliamuser LOGIN ;
ERROR: "cloudsqliamuser" can't be altered
postgres=> ALTER ROLE cloudsqlimportexport NOCREATEROLE ;
ERROR: "cloudsqlimportexport" can't be altered
postgres=> ALTER ROLE cloudsqlreplica NOREPLICATION ;
ERROR: "cloudsqlreplica" can't be altered
postgres=> ALTER ROLE cloudsqlsuperuser NOCREATEDB ;
ERROR: "cloudsqlsuperuser" can't be altered
やはり何かしらソースコードに変更を加えて、ALTERできないようになっています。
postgres=> DROP ROLE cloudsqladmin ;
ERROR: must be superuser to drop superusers
postgres=> DROP ROLE cloudsqlagent ;
ERROR: role "cloudsqlagent" cannot be dropped because some objects depend on it
DETAIL: 1 object in database cloudsqladmin
postgres=> DROP ROLE cloudsqliamserviceaccount ;
ERROR: role "cloudsqliamserviceaccount" cannot be dropped because some objects depend on it
DETAIL: 1 object in database cloudsqladmin
postgres=> DROP ROLE cloudsqliamuser ;
ERROR: role "cloudsqliamuser" cannot be dropped because some objects depend on it
DETAIL: 1 object in database cloudsqladmin
postgres=> DROP ROLE cloudsqlimportexport ;
ERROR: role "cloudsqlimportexport" cannot be dropped because some objects depend on it
DETAIL: 1 object in database cloudsqladmin
postgres=> DROP ROLE cloudsqlreplica ;
ERROR: role "cloudsqlreplica" cannot be dropped because some objects depend on it
DETAIL: 1 object in database cloudsqladmin
postgres=> DROP ROLE cloudsqlsuperuser ;
ERROR: role "cloudsqlsuperuser" cannot be dropped because some objects depend on it
DETAIL: owner of database template1
owner of schema public
owner of database postgres
1 object in database cloudsqladmin
1 object in database template1
もちろんcloudsqladminはスーパーユーザなので削除できません。そして、他のロールは依存するオブジェクトがあるという理由で削除できませんでした。cloudsqladminというデータベースで各ロールにオブジェクトを所有させてロールを削除できないようにしているようです。
そのロールのメンバ資格をGRANT/REVOKEできるか
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {cloudsqlsuperuser}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {cloudsqlsuperuser}
cloudsqlreplica | Replication | {pg_monitor}
cloudsqlsuperuser | Create role, Create DB | {pg_monitor,pg_signal_backend}
postgres | Create role, Create DB | {cloudsqlsuperuser}
postgres=> GRANT postgres TO cloudsqladmin ;
GRANT ROLE
postgres=> GRANT postgres TO cloudsqlagent ;
GRANT ROLE
postgres=> GRANT postgres TO cloudsqliamserviceaccount ;
GRANT ROLE
postgres=> GRANT postgres TO cloudsqliamuser ;
GRANT ROLE
postgres=> GRANT postgres TO cloudsqlimportexport ;
GRANT ROLE
postgres=> GRANT postgres TO cloudsqlreplica ;
GRANT ROLE
postgres=> GRANT postgres TO cloudsqlsuperuser ;
ERROR: role "postgres" is a member of role "cloudsqlsuperuser"
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {postgres}
cloudsqlagent | Create role, Create DB | {cloudsqlsuperuser,postgres}
cloudsqliamserviceaccount | Cannot login | {postgres}
cloudsqliamuser | Cannot login | {postgres}
cloudsqlimportexport | Create role, Create DB | {cloudsqlsuperuser,postgres}
cloudsqlreplica | Replication | {pg_monitor,postgres}
cloudsqlsuperuser | Create role, Create DB | {pg_monitor,pg_signal_backend}
postgres | Create role, Create DB | {cloudsqlsuperuser}
postgresメンバ資格を各ロールにGRANTしてみましたが、普通にGRANTすることができました。とは言ってもこれらのロールを直接使うことはできないので意味はないですが・・・。（cloudsqlsuperuserへのGRANTが失敗しているのは、postgresロールがcloudsqlsuperuserのメンバ資格を所有しており、ロール間で循環が生じてしまうため）
次にREVOKEできるか確認してみます。
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {cloudsqlsuperuser}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {cloudsqlsuperuser}
cloudsqlreplica | Replication | {pg_monitor}
cloudsqlsuperuser | Create role, Create DB | {pg_monitor,pg_signal_backend}
postgres | Create role, Create DB | {cloudsqlsuperuser}
postgres=> REVOKE cloudsqlsuperuser FROM cloudsqlagent ;
REVOKE ROLE
postgres=> REVOKE cloudsqlsuperuser FROM cloudsqlimportexport ;
REVOKE ROLE
postgres=> REVOKE pg_monitor FROM cloudsqlreplica ;
REVOKE ROLE
postgres=> REVOKE pg_monitor FROM cloudsqlsuperuser ;
REVOKE ROLE
postgres=> REVOKE pg_signal_backend FROM cloudsqlsuperuser ;
REVOKE ROLE
postgres=> REVOKE cloudsqlsuperuser FROM postgres ;
REVOKE ROLE
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+-----------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {}
cloudsqlreplica | Replication | {}
cloudsqlsuperuser | Create role, Create DB | {}
postgres | Create role, Create DB | {}
なんとすべてREVOKEできてしまいました。こんなことしちゃって大丈夫なのか謎ですが。
postgres=> GRANT cloudsqlsuperuser TO postgres ;
ERROR: must be superuser or cloudsqlsuperuser to grant or revoke role "cloudsqlsuperuser"
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+-----------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {}
cloudsqlreplica | Replication | {}
cloudsqlsuperuser | Create role, Create DB | {}
postgres | Create role, Create DB | {}
そして、元に戻そうとcloudsqlsuperuserをGRANTしようとすると上のエラーが出て元に戻せなくなりました。
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {}
cloudsqlreplica | Replication | {}
cloudsqlsuperuser | Create role, Create DB | {pg_monitor,pg_signal_backend}
postgres | Create role, Create DB | {}
postgres=> GRANT cloudsqlsuperuser TO postgres ;
ERROR: must be superuser or cloudsqlsuperuser to grant or revoke role "cloudsqlsuperuser"
インスタンスを再起動すると、cloudsqlsuperuserのメンバ資格だけ元に戻ってましたが、相変わらずcloudsqlsuperuserのGRANTはできない状態でした。
どうしようもないなと思っていると、GUIからロールの作成ができることを発見して、試してみました。
ここから新たなロールを作成すると、cloudsqlsuperuserを保持するロールが作成され、これで無事元に戻すことができそうです。
postgres=> \du shinya
List of roles
Role name | Attributes | Member of
-----------+------------------------+---------------------
shinya | Create role, Create DB | {cloudsqlsuperuser}
自分自身にPostgreSQLのデフォルトロールを付与できるか（pg_execute_server_programなど）
postgres=> GRANT pg_execute_server_program TO postgres ;
ERROR: grant or revoke of role "pg_execute_server_program" is not allowed
postgres=> \errverbose
ERROR: 0LP01: grant or revoke of role "pg_execute_server_program" is not allowed
LOCATION: check_system_role_grants, superuser.c:63
postgres=> GRANT pg_write_server_files TO postgres ;
ERROR: grant or revoke of role "pg_write_server_files" is not allowed
postgres=> GRANT pg_read_server_files TO postgres ;
ERROR: grant or revoke of role "pg_read_server_files" is not allowed
postgres=> GRANT pg_read_all_settings TO postgres ;
GRANT ROLE
postgres=> GRANT pg_read_all_stats TO postgres ;
GRANT ROLE
postgres=> GRANT pg_stat_scan_tables TO postgres ;
GRANT ROLE
postgres=> GRANT pg_monitor TO postgres ;
GRANT ROLE
postgres=> GRANT pg_signal_backend TO postgres ;
GRANT ROLE
予想通りプログラムを実行したり、サーバのファイルの読み書きはできないようになっています。
まとめ
個人的な予想としては、ロールの削除やロールの権限の変更もできず、メンバ資格の変更もできないと思っていましたが、メンバ資格については特に制限はかけていないことがわかりました。そして、REVOKEしてしまうと元に戻すのが大変だということもわかりました。
少し話は変わりますが、ロールを保護するエクステンション（supautils）もあるのでぜひ活用してみてください。このエクステンションを使えば、保護したいロールに対してDROP ROLE/ALTER ROLE/GRANT/REVOKEできなくなります。また、pg_execute_server_programのような厄介なロールもGRANTできないように設定することも可能です。私も少しだけこのエクステンションにコントリビュートしているのでフィードバックいただけると幸いです。
Discussion