goodbyegangsterのブログ

備忘録的な

Redshiftにて、作成したDBユーザのSYSLOG ACCESS UNRESTRICTEDが正しく設定されているか調べる方法

Redshiftにて、ユーザのSYSLOG ACCESS UNRESTRICTEDが正しく設定されているかどうか、カタログテーブルや管理ビューで確認する方法はないそうです。

SYSLOG ACCESS UNRESTRICTED/RESTRICTED とは、

  • Amazon Redshift のシステムテーブルとビューに対するユーザーのアクセスレベルを指定する句です。
  • RESTRICTED が指定されている場合、そのユーザーは、ユーザーが表示可能なシステムテーブルとビューで生成した行しか表示できません。デフォルトは RESTRICTED です。
  • UNRESTRICTED が指定されている場合、そのユーザーは、別のユーザーによって生成された行を含む、ユーザーが表示可能なシステムテーブルとビューのすべての行を表示できます。

ALTER USER - Amazon Redshift

そのため、正しく設定されているかどうか確認するには、実際に他ユーザが実行したクエリ結果を参照できるか、確認する方法が取られます。その手順を記載。

まず、SYSLOG ACCESS UNRESTRICTEDを設定してDBユーザ master を作成します。

test=# CREATE USER master WITH PASSWORD '1234Abcd' SYSLOG ACCESS UNRESTRICTED;
CREATE USER

続いて、SYSLOG ACCESS RESTRICTEDにて(デフォルト設定)、DBユーザ reader を作成します。

test=# CREATE USER reader WITH PASSWORD '1234Abcd';
CREATE USER

postgres標準の pg_user テーブルを見ても、設定されているかどうかは分かりません。

test=# select * from pg_user where usename = 'master' or usename = 'reader';
 usename | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
---------+----------+-------------+----------+-----------+----------+----------+-----------
 master  |      107 | f           | f        | f         | ******** |          |
 reader  |      108 | f           | f        | f         | ******** |          |

なので、 stl_query テーブルを利用して、自ユーザ以外の実行クエリ情報を参照できるかどうか確認します。stl_query は、データベースのクエリ実行情報を格納しているシステムテーブルです。公式情報はこちら。

STL_QUERY - Amazon Redshift

ACCESS UNRESTRICTEDであるmasterユーザにてログインし、masterユーザ以外のクエリ情報を参照できるかどうか確認します。

C:\>psql -h test-cluster.aaaaaaaaa.ap-northeast-1.redshift.amazonaws.com -U master -d test -p 5439
ユーザ master のパスワード:
psql (9.3.22, サーバー 8.0.2)
SSL 接続 (暗号化方式: ECDHE-RSA-AES256-GCM-SHA384, ビット長: 256)
"help" でヘルプを表示します.

test=> select count(*) from stl_query where userid <> (select usesysid from pg_user where usename = 'master');
 count
-------
  1187
(1 行)

参照できていますね。

次は、ACCESS RESTRICTEDであるreaderユーザにてログインし、readerユーザ以外のクエリ情報を参照できるかどうか確認します。

C:\>psql -h test-cluster.aaaaaaaaa.ap-northeast-1.redshift.amazonaws.com -U reader -d test -p 5439
ユーザ reader のパスワード:
psql (9.3.22, サーバー 8.0.2)
SSL 接続 (暗号化方式: ECDHE-RSA-AES256-GCM-SHA384, ビット長: 256)
"help" でヘルプを表示します.

test=> select count(*) from stl_query where userid <> (select usesysid from pg_user where usename = 'reader');
 count
-------
     0
(1 行)

結果は0件ですので、参照できていませんね。

Redshiftでは、ヘルスチェックのため様々なクエリが常時動いているので、仮に作成したユーザが何もクエリを実行していない状態でも、この方法で確認ができます。