Redshift で superuser 権限を付与せずに sys_connection_log にアクセスを許可するためには、sys:monitor ロールを付与すればよい
- ユーザー作成
dev=# create user test_monitor password 'xxx'; CREATE USER dev=# \q
- 新ユーザーで sys_connection_log にアクセス
$ psql -h dwh-cluster-ads-test.********.ap-northeast-1.redshift.amazonaws.com -p 5439 -d dev -U test_monitor Password for user test_monitor: psql (15.14, server 8.0.2) WARNING: psql major version 15, server major version 8.0. Some psql features might not work. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off) Type "help" for help. dev=> select * from sys_connection_log; ERROR: permission denied for relation sys_connection_log
- sys:monitor を GRANT
$ psql -h dwh-cluster-ads-test.********.ap-northeast-1.redshift.amazonaws.com -p 5439 -d dev -U awsuser Password for user awsuser: psql (15.14, server 8.0.2) WARNING: psql major version 15, server major version 8.0. Some psql features might not work. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off) Type "help" for help. dev=# grant role "sys:monitor" to test_monitor; GRANT dev=# \q
- 新ユーザーで sys_connection_log にアクセス
$ psql -h dwh-cluster-ads-test.********.ap-northeast-1.redshift.amazonaws.com -p 5439 -d dev -U test_monitor Password for user test_monitor: psql (15.14, server 8.0.2) WARNING: psql major version 15, server major version 8.0. Some psql features might not work. SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off) Type "help" for help. dev=> select user_name,count(*) from sys_connection_log group by user_name; user_name | count ----------------------------------------------------------------------------------------------------------------------------------+-------- rdsdb | 147959 awsuser | 37 test_monitor | 8 IAMR:Admin | 54 (4 rows)
