マネージドPostgreSQLの実現に向けたPostgreSQL機能向上（PostgreSQL Conference Japan 2023 発表資料）

© 2023 NTT DATA Group Corporation 3 本講演について • 講演資料は、NTTデータグループのSlideShareアカウント上で公開中 • https://www.slideshare.net/nttdata-tech • 検証環境製品名バージョン Amazon RDS for PostgreSQL PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit Azure Database for PostgreSQL （フレキシブルサーバー） PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0- 3ubuntu1~18.04) 7.5.0, 64-bit Cloud SQL for PostgreSQL PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit オープンソース版PostgreSQL PostgreSQL 16.1

© 2023 NTT DATA Group Corporation 5 PostgreSQLの機能が向上することでこれらの課題が解決しつつあります！スーパーユーザ使えないのか…？ということはCHECKPOINTコマンド実行できないな… 必要な権限を付与できない… あれ、何故かこのパラメータは変更できないぞ… v15~ CHECKPOINTコマンドの権限を付与可能に！ v16~ 権限付与のルールが変更 v15~ パラメータ毎にSET権限を設定可能

© 2023 NTT DATA Group Corporation 6 マネージドPostgreSQLの主な制約スーパーユーザを使用できない OSにログインできない • 一部のSQLコマンドを実行できない • 一部の権限を付与できない • 一部のパラメータを設定できない • スーパーユーザの所有するリレーションに対する任意の操作を実行できない • サーバのファイルを読み書きできない • サーバプログラムを実行できない • 拡張機能をインストールできない

© 2023 NTT DATA Group Corporation 9 CHECKPOINTコマンド • v14まではスーパーユーザしかCHECKPOINTコマンドを実行できなかった • v15で実装されたpg_checkpoint事前定義ロールを付与することで、一般ユーザでもCHECKPOINTコマンドを実行できるようになった v15 =# CREATE ROLE not_have_pg_checkpoint ; =# SET ROLE not_have_pg_checkpoint ; => CHECKPOINT ; ERROR: 42501: permission denied to execute CHECKPOINT command DETAIL: Only roles with privileges of the "pg_checkpoint" role may execute this command. => RESET ROLE ; =# CREATE ROLE has_pg_checkpoint IN ROLE pg_checkpoint ; =# SET ROLE has_pg_checkpoint ; => CHECKPOINT ; CHECKPOINT pg_checkpointをもたないロールで CHECKPOINTコマンドを実行するとエラー pg_checkpointをもつロールで CHECKPOINTコマンドを実行すると成功

© 2023 NTT DATA Group Corporation 10 CHECKPOINTコマンド • Amazon RDS for PostgreSQL • v14時点で独自に対応済み • Azure Database for PostgreSQL • v14時点で独自に対応済み • Cloud SQL for PostgreSQL • 未対応 => CHECKPOINT ; CHECKPOINT v15 => CHECKPOINT ; ERROR: 42501: must be superuser to do CHECKPOINT つまりソースコードを変更している！ Cloud SQL for PostgreSQL Amazon RDS for PostgreSQL, Azure Database for PostgreSQL

© 2023 NTT DATA Group Corporation 11 VACUUM、ANALYZE、CLUSTER、 REFRESH MATERIALIZED VIEW、REINDEX、LOCK TABLEコマンド • リレーションを所有するなどの適切な権限をもつロールしかこれらのSQLコマンドを実行できなかった • 例）スーパーユーザの所有するシステムカタログのVACUUMを一般ユーザは実行できない • pg_maintain事前定義ロールを付与することで、全リレーションに対してこれらのSQLコマンドを実行できるようになった • v16で導入予定だったが、権限昇格のバグによりRevertされた v16で実装されたが最終的にはRevert

© 2023 NTT DATA Group Corporation 12 VACUUM、ANALYZE、CLUSTER、 REFRESH MATERIALIZED VIEW、REINDEX、LOCK TABLEコマンド • Amazon RDS for PostgreSQL • v14時点で独自に対応済み • Azure Database for PostgreSQL • 未対応 • Cloud SQL for PostgreSQL • 未対応 => VACUUM pg_db_role_setting ; VACUUM => VACUUM pg_db_role_setting ; WARNING: skipping "pg_db_role_setting" --- only superuser can vacuum it VACUUM v16で実装されたが最終的にはRevert Azure Database for PostgreSQL, Cloud SQL for PostgreSQL Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 13 CREATE EVENT TRIGGERコマンド • CREATE EVENT TRIGGERコマンドはスーパーユーザしか実行できない • PostgreSQL本体には実行権限を制御する機能はないが、調査対象のクラウドでは独自に対応している未対応 => CREATE EVENT TRIGGER trgr ON ddl_command_start EXECUTE FUNCTION any_func() ; ERROR: 42501: permission denied to create event trigger "abort_ddl" HINT: Must be superuser to create an event trigger. PostgreSQL 16 => CREATE EVENT TRIGGER trgr ON ddl_command_start EXECUTE FUNCTION any_func() ; CREATE EVENT TRIGGER Amazon RDS for PostgreSQL, Azure Database for PostgreSQL, Cloud SQL for PostgreSQL

© 2023 NTT DATA Group Corporation 14 BYPASSRLS属性の付与 • v15まではスーパーユーザしかBYPASSRLS属性の付与ができなかった • v16では自身がCREATEROLE属性とBYPASSRLS属性をもっていれば付与できるようになった => ¥du shinya List of roles Role name | Attributes -----------+------------------------- shinya | Create role, Bypass RLS => CREATE ROLE has_bypassrls BYPASSRLS ; CREATE ROLE PostgreSQL 16 v16

© 2023 NTT DATA Group Corporation 15 BYPASSRLS属性の付与 • Amazon RDS for PostgreSQL • v14時点で独自に対応済み • Azure Database for PostgreSQL • 未対応 • Cloud SQL for PostgreSQL • v14時点で独自に対応済み => CREATE ROLE has_bypassrls BYPASSRLS ; CREATE ROLE => CREATE ROLE has_replication REPLICATION ; ERROR: must be superuser to create bypassrls users => CREATE ROLE has_bypassrls BYPASSRLS ; CREATE ROLE v16 Cloud SQL for PostgreSQL Azure Database for PostgreSQL Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 16 REPLICATION属性の付与 • v15まではスーパーユーザしかREPLICATION属性の付与ができなかった • v16では自身がCREATEROLE属性とREPLICATION属性をもっていれば付与できるようになった => ¥du shinya List of roles Role name | Attributes -----------+-------------------------- shinya | Create role, Replication => CREATE ROLE has_replication REPLICATION ; CREATE ROLE PostgreSQL 16 v16

© 2023 NTT DATA Group Corporation 17 REPLICATION属性の付与 • Amazon RDS for PostgreSQL • 未対応 • Azure Database for PostgreSQL • v14時点で独自に対応済み • Cloud SQL for PostgreSQL • v14時点で独自に対応済み => CREATE ROLE has_replication REPLICATION ; ERROR: 42501: must be superuser to create replication users => CREATE ROLE has_replication REPLICATION ; CREATE ROLE v16 Azure Database for PostgreSQL, Cloud SQL for PostgreSQL Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 18 任意のロールの付与 • v15まではCREATEROLE属性をもつロールは、スーパーユーザ以外の任意のロールの付与・剥奪ができた • v16ではADMIN OPTION付きで自身がもつロールの付与・剥奪のみ実行可能になった => ¥drg shinya List of role grants Role name | Member of | Options | Grantor -----------+---------------+---------------------+--------- shinya | pg_checkpoint | ADMIN, INHERIT, SET | rocky => GRANT pg_checkpoint TO role_a ; GRANT ROLE => GRANT pg_execute_server_program TO role_a ; ERROR: 42501: permission denied to grant role "pg_execute_server_program" DETAIL: Only roles with the ADMIN option on role "pg_execute_server_program" may grant this role. PostgreSQL 16 v16 ADMIN OPTIONつきでpg_checkpoint をもつのでGRANT可能もたないpg_execute_server_programはGRANT不可

© 2023 NTT DATA Group Corporation 19 任意のロールの付与 • Amazon RDS for PostgreSQL • v14時点で独自に対応済み • Azure Database for PostgreSQL • v14時点で独自に対応済み • Cloud SQL for PostgreSQL • v14時点で独自に対応済み => GRANT pg_execute_server_program TO role_a ; ERROR: 42501: cannot alter members of "pg_execute_server_program" DETAIL: "pg_execute_server_program" is a protected role. LOCATION: check_granted_role, rdsutils.c:1711 v16 rdsutils.cというファイルに実装されている Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 21 設定パラメータのSET、ALTER SYSTEM SET権限 • v15から、設定パラメータの SET、ALTER SYSTEM SET権限をGRANTコマンドで付与可能となった v15 => SELECT name, context FROM pg_settings WHERE name IN ('deadlock_timeout', 'commit_delay') ; name | context ------------------+----------- commit_delay | superuser deadlock_timeout | superuser =# GRANT SET ON PARAMETER deadlock_timeout TO shinya ; =# SET ROLE shinya ; => SET deadlock_timeout TO '5s’ ; SET => SET commit_delay TO '5s' ; ERROR: 42501: permission denied to set parameter "commit_delay" deadlock_timeout のSET権限を付与 deadlock_timeoutのSET成功 commit_delayのSET失敗 PostgreSQL 16

© 2023 NTT DATA Group Corporation 22 設定パラメータのSET、ALTER SYSTEM SET権限 • Amazon RDS for PostgreSQL • v14時点で一部のパラメータのみ SETできるように対応済み • Azure Database for PostgreSQL • v14時点で session_replication_roleのみ SETできるように対応済み • Cloud SQL for PostgreSQL • 未対応 => SET deadlock_timeout TO '5s' ; SET => SET session_replication_role TO 'replica' ; SET => SET deadlock_timeout TO '5s' ; ERROR: permission denied to set parameter "deadlock_timeout" v15 Cloud SQL for PostgreSQL Azure Database for PostgreSQL Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 23 （スーパーユーザ以外の）任意のロールのコネクションの予約 • スーパーユーザのコネクションは superuser_reserved_connectionsパラメータで予約できた • v16から、 pg_use_reserved_connectionsロールを付与したロールのコネクションをreserved_connectionsパラメータで予約可能になった v16 superuser_ reserved_connections reserved_connections その他の接続 max_connections 同時接続可能な最大数スーパーユーザのために予約するコネクション数 pg_use_reserved_connections ロールをもつロールのために予約するコネクション数

© 2023 NTT DATA Group Corporation 24 （スーパーユーザ以外の）任意のロールのコネクションの予約 • Amazon RDS for PostgreSQL • v14時点で独自にrds.rds_superuser_reserved_connectionsというパラメータで RDS管理者ロールのコネクションを予約可能 • Azure Database for PostgreSQL • 未対応 • Cloud SQL for PostgreSQL • 未対応 => SHOW rds.rds_superuser_reserved_connections ; rds.rds_superuser_reserved_connections ---------------------------------------- 2 v16 Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 27 pg_ident_file_mappingsビュー • v15から、pg_ident.confファイルの中身を pg_ident_file_mappingsビューで確認できるようになった • postgresql.confはpg_settingsビュー、 pg_hba.confはpg_hba_file_rulesビューで確認可能 • ただし、デフォルトではスーパーユーザのみSELECT可能なのでマネージドサービス利用者は使えない可能性が高い v15 =# SELECT * FROM pg_ident_file_mappings ; -[ RECORD 1 ]---------------------------------------- map_number | 1 file_name | /home/rocky/pgsql/16/data/pg_ident.conf line_number | 73 map_name | mymap sys_name | /^(.*)@mydomain¥.com$ pg_username | ¥1 error | [NULL] PostgreSQL 16

© 2023 NTT DATA Group Corporation 28 pg_hba_file_rulesビュー • デフォルトではスーパーユーザのみSELECT可能 • Amazon RDS for PostgreSQL • SELECT権限が付与された状態でPostgreSQLが作成される • Azure Database for PostgreSQL • 未対応 • Cloud SQL for PostgreSQL • 未対応補足 => SELECT * FROM pg_hba_file_rules LIMIT 3 ; line_number | type | database | user_name | address | netmask | auth_method | options | error -------------+-------+----------+------------+---------+---------+---------------+-----------+------- 2 | local | {all} | {rdsadmin} | [NULL] | [NULL] | peer | {map=rds} | [NULL] 6 | local | {all} | {all} | [NULL] | [NULL] | scram-sha-256 | [NULL] | [NULL] 12 | host | {all} | {rdsadmin} | all | [NULL] | reject | [NULL] | [NULL] Amazon RDS for PostgreSQL

© 2023 NTT DATA Group Corporation 29 pg_walinspect拡張機能 • v15でcontribモジュールに追加された拡張機能 • マネージドサービスではOSにログインできずpg_waldumpを実行できないため、 SQLコマンドでWALの情報を確認できるようにと開発 • ただし、クラウド側でサポートされるまでは使用できない • Amazon RDS for PostgreSQLはサポート済み v15 =# SELECT start_lsn, end_lsn, prev_lsn, block_ref FROM pg_get_wal_record_info('0/E419E28'); -[ RECORD 1 ]----+------------------------------------------------- start_lsn | 0/E419E28 end_lsn | 0/E419E68 prev_lsn | 0/E419D78 block_ref | blkref #0: rel 1663/16385/1249 fork main blk 364 PostgreSQL 16

© 2023 NTT DATA Group Corporation 30 Trusted Language Extensions for PostgreSQL（pg_tle） • AWSがOSSとして公開している、”安全な”拡張機能を開発するためのツール • ファイルシステムアクセスやネットワーク設定を制限し、安全性を備えた信頼性のある言語を提供 • JavaScript、Perl、PL/pgSQL、SQLでの拡張機能の作成をサポート • Amazon RDS for PostgreSQLでは pg_tleを使用して開発した拡張機能を自由にインストール可能補足

© 2023 NTT DATA Group Corporation 31 まとめ • マネージドPostgreSQLを実現するための PostgreSQLの機能向上について説明しました • クラウドにより対応状況は様々ですが、利用者の利便性を向上させたり、危険な操作を防止するために、 PostgreSQLのソースコードを変更して独自に対応していることがわかりました • マネージドPostgreSQLとオープンソース版PostgreSQLの違いを理解しつつ、 PostgreSQLのアップデートを楽しみましょう

© 2023 NTT DATA Group Corporation 32 参考文献 • https://www.postgresql.org/docs/15/release-15.html • https://www.postgresql.org/docs/16/release-16.html • https://www.slideshare.net/slideshow/embed_code/key/rscjGQhRUduPIL • https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_ language_extension.html • https://github.com/aws/pg_tle

その他、記載されている会社名、商品名、又はサービス名は、各社の登録商標又は商標です。

マネージドPostgreSQLの実現に向けたPostgreSQL機能向上（PostgreSQL Conference Japan 2023 発表資料）

More Related Content

What's hot

Similar to マネージドPostgreSQLの実現に向けたPostgreSQL機能向上（PostgreSQL Conference Japan 2023 発表資料）

More from NTT DATA Technology & Innovation

Recently uploaded

マネージドPostgreSQLの実現に向けたPostgreSQL機能向上（PostgreSQL Conference Japan 2023 発表資料）