GCP CloudSQL IAM 인증

GCP CloudSQL IAM 인증

 

IAM 인증으로 사용자 관리
https://cloud.google.com/sql/docs/mysql/add-manage-iam-users?hl=ko

IAM 데이터베이스 인증을 사용하여 로그인
https://cloud.google.com/sql/docs/mysql/iam-logins?hl=ko


# cloudsql 인스턴스 및 iam 계정 권한 확인
-> cloudsql 인스턴스에 cloudsql_iam_authentication 플래그 on 으로 설정(iam 인증 타입의 사용자 생성 시 자동으로 세팅됨)
-> iam 계정에 roles/cloudsql.instanceUser 역할 필요


# cloudsql 사용자(iam 사용자 인증 타입) 생성

gcloud sql users create premisan@example.com \
--instance=premisan-test \
--type=cloud_iam_user \
--project=premisan-test

# mysql 권한 설정
-> mysql 내부의 권한은 별도로 부여 필요(grant)

use premisan-test

grant select on * to "premisan";

# 샘플 데이터 생성

use premisan-test

CREATE TABLE Test ( 
  pk INTEGER AUTO_INCREMENT PRIMARY KEY, 
  col01 VARCHAR(12) NOT NULL, 
  regDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  modifyDate DATETIME ON UPDATE CURRENT_TIMESTAMP
); 

INSERT INTO Test SET col01 = '123'; 

select * from Test;

# 접속(cloudsql proxy 실행 중)

MYSQL_PWD=`gcloud sql generate-login-token` mysql --enable-cleartext-plugin -u premisan -h 127.0.0.1

# 출력

mysql> status
--------------
mysql  Ver 8.0.30 for macos12.4 on arm64 (Homebrew)

Connection id:		477
Current database:
Current user:		premisan@cloudsqlproxy~123.123.123.123
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		8.0.26-google (Google)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Binary data as:		Hexadecimal
Uptime:			44 min 33 sec

Threads: 7  Questions: 5822  Slow queries: 0  Opens: 533  Flush tables: 3  Open tables: 413  Queries per second avg: 2.178
--------------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| premisan-test          |
+--------------------+
2 rows in set (0.01 sec)

mysql> use premisan-test
Database changed

mysql> show tables;
+---------------------+
| Tables_in_premisan-test |
+---------------------+
| Test                |
+---------------------+
1 row in set (0.01 sec)

mysql> select * from Test;
+----+-------+---------------------+------------+
| pk | col01 | regDate             | modifyDate |
+----+-------+---------------------+------------+
|  1 | 123   | 2022-09-23 02:46:28 | NULL       |
+----+-------+---------------------+------------+
1 row in set (0.01 sec)

mysql> INSERT INTO Test SET col01 = '123';
ERROR 1142 (42000): INSERT command denied to user 'premisan'@'cloudsqlproxy~123.123.123.123' for table 'Test'

mysql> delete from Test;
ERROR 1142 (42000): DELETE command denied to user 'premisan'@'cloudsqlproxy~123.123.123.123' for table 'Test'