[Google Cloud] Google Study Jam : Create and Manage Cloud SQL for PostgreSQL Instances (3)

본 포스트는 2024년 Google Study Jam을 공부하면서 개인적으로 내용을 정리한 포스트 입니다.

 

 

Task 1. Create a Cloud SQL for PostgreSQL instance with CMEK enabled

gcloud beta services identity create 명령어로 Cloud SQL CMEK에 필요한 서비스 계정을 만들 수 있다.

export PROJECT_ID=$(gcloud config list --format 'value(core.project)')
gcloud beta services identity create \
    --service=sqladmin.googleapis.com \
    --project=$PROJECT_ID

Authorize 버튼을 누른다.

 

Cloud KMS keyring을 만든다.

export KMS_KEYRING_ID=cloud-sql-keyring
export ZONE=$(gcloud compute instances list --filter="NAME=bastion-vm" --format=json | jq -r .[].zone | awk -F "/zones/" '{print $NF}')
export REGION=${ZONE::-2}
gcloud kms keyrings create $KMS_KEYRING_ID \
    --location=$REGION

 

Cloud KMS key을 만든다.

export KMS_KEY_ID=cloud-sql-key
gcloud kms keys create $KMS_KEY_ID \
 --location=$REGION \
 --keyring=$KMS_KEYRING_ID \
 --purpose=encryption

 

 

서비스 계정에 key를 바인딩

export PROJECT_NUMBER=$(gcloud projects describe ${PROJECT_ID} \
    --format 'value(projectNumber)')
gcloud kms keys add-iam-policy-binding $KMS_KEY_ID \
    --location=$REGION \
    --keyring=$KMS_KEYRING_ID \
    --member=serviceAccount:service-${PROJECT_NUMBER}@gcp-sa-cloud-sql.iam.gserviceaccount.com \
    --role=roles/cloudkms.cryptoKeyEncrypterDecrypter

 

 

서비스 계정 이름은 이전 하위 작업에서 "gcloud beta services identity create" 명령으로 반환된 이름과 동일하다.

 

bastion-vm VM 인스턴스의 외부 IP 주소를 찾는다.

export AUTHORIZED_IP=$(gcloud compute instances describe bastion-vm \
    --zone=$ZONE \
    --format 'value(networkInterfaces[0].accessConfigs.natIP)')
echo Authorized IP: $AUTHORIZED_IP

 

외부 주소 찾기

export CLOUD_SHELL_IP=$(curl ifconfig.me)
echo Cloud Shell IP: $CLOUD_SHELL_IP

 

Cloud SQL for PostgreSQL instance를 만든다.

export KEY_NAME=$(gcloud kms keys describe $KMS_KEY_ID \
    --keyring=$KMS_KEYRING_ID --location=$REGION \
    --format 'value(name)')

export CLOUDSQL_INSTANCE=postgres-orders
gcloud sql instances create $CLOUDSQL_INSTANCE \
    --project=$PROJECT_ID \
    --authorized-networks=${AUTHORIZED_IP}/32,$CLOUD_SHELL_IP/32 \
    --disk-encryption-key=$KEY_NAME \
    --database-version=POSTGRES_13 \
    --cpu=1 \
    --memory=3840MB \
    --region=$REGION \
    --root-password=supersecret!

 

 

Task 2. Enable and configure pgAudit on a Cloud SQL for PostgreSQL database

Cloud SQL instance에 pgAudit 데이터베이스 flag를 추가한다.

gcloud sql instances patch $CLOUDSQL_INSTANCE \
    --database-flags cloudsql.enable_pgaudit=on,pgaudit.log=all

진행될 때까지 Enter를 계속 누른다.

 

Navigation menu > Databases > SQL > postgres-orders > Overview > Restart을 누른다.

Connect to this instance에 Open Cloud Shell을 누르면 Cloud Shell에 명령어가 입력된다.

 

비밀번호를 입력하고 psql 실행 후 SQL 명령어를 입력한다.

CREATE DATABASE orders;
\c orders;

CREATE EXTENSION pgaudit;
ALTER DATABASE orders SET pgaudit.log = 'read,write';

 

 

Navigation menu > IAM & Admin > Audit Logs

• Filter box : Cloud SQL
• Cloud SQL 체크박스를 선택하고 오른쪽 정보 패널에 모든 것을 체크한다.

 

저장한다.

 

Cloud Shell에 명령어를 입력한다.

export SOURCE_BUCKET=gs://cloud-training/gsp920
gsutil -m cp ${SOURCE_BUCKET}/create_orders_db.sql .
gsutil -m cp ${SOURCE_BUCKET}/DDL/distribution_centers_data.csv .
gsutil -m cp ${SOURCE_BUCKET}/DDL/inventory_items_data.csv .
gsutil -m cp ${SOURCE_BUCKET}/DDL/order_items_data.csv .
gsutil -m cp ${SOURCE_BUCKET}/DDL/products_data.csv .
gsutil -m cp ${SOURCE_BUCKET}/DDL/users_data.csv .

export CLOUDSQL_INSTANCE=postgres-orders
export POSTGRESQL_IP=$(gcloud sql instances describe $CLOUDSQL_INSTANCE --format="value(ipAddresses[0].ipAddress)")
export PGPASSWORD=supersecret!
psql "sslmode=disable user=postgres hostaddr=${POSTGRESQL_IP}" \
    -c "\i create_orders_db.sql"

 

Cloud Shell에서 psql 세션에서 SQL 문을 입력한다.

CREATE ROLE auditor WITH NOLOGIN;
ALTER DATABASE orders SET pgaudit.role = 'auditor';
GRANT SELECT ON order_items TO auditor;

SELECT
    users.id  AS users_id,
    users.first_name  AS users_first_name,
    users.last_name  AS users_last_name,
    COUNT(DISTINCT order_items.order_id ) AS order_items_order_count,
    COALESCE(SUM(order_items.sale_price ), 0) AS order_items_total_revenue
FROM order_items
LEFT JOIN users ON order_items.user_id = users.id
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 500;

SELECT
    products.id  AS products_id,
    products.name  AS products_name,
    products.sku  AS products_sku,
    products.cost  AS products_cost,
    products.retail_price  AS products_retail_price,
    products.distribution_center_id  AS products_distribution_center_id,
    COUNT(DISTINCT order_items.order_id ) AS order_items_order_count,
    COALESCE(SUM(order_items.sale_price ), 0) AS order_items_total_revenue
FROM order_items
LEFT JOIN inventory_items ON order_items.inventory_item_id = inventory_items.id
LEFT JOIN products ON inventory_items.product_id = products.id
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 7 DESC
LIMIT 500;

SELECT
    order_items.order_id AS order_id,
    distribution_centers.id  AS distribution_centers_id,
    distribution_centers.name  AS distribution_centers_name,
    distribution_centers.latitude  AS distribution_centers_latitude,
    distribution_centers.longitude  AS distribution_centers_longitude
FROM order_items
LEFT JOIN inventory_items ON order_items.inventory_item_id = inventory_items.id
LEFT JOIN products ON inventory_items.product_id = products.id
LEFT JOIN distribution_centers ON products.distribution_center_id = distribution_centers.id
GROUP BY 1, 2, 3, 4, 5
ORDER BY 2
LIMIT 500;

 

 

Navigation menu > Observablity > Logging > Logs Explorer > Query 탭에 Run query 버튼을 누른다.

resource.type="cloudsql_database"
logName="projects/(GCP Project)/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

 

 

Task 3. Configure Cloud SQL IAM database authentication

IAM 관련 설정 문서

IAM 데이터베이스 인증을 사용하여 로그인  |  Cloud SQL for PostgreSQL  |  Google Cloud

 

실습 계정을 사용자 이름으로 사용하여 주문 데이터베이스에 대한 액세스 테스트를 한다.

export USERNAME=$(gcloud config list --format="value(core.account)")
export CLOUDSQL_INSTANCE=postgres-orders
export POSTGRESQL_IP=$(gcloud sql instances describe $CLOUDSQL_INSTANCE --format="value(ipAddresses[0].ipAddress)")
export PGPASSWORD=$(gcloud auth print-access-token)
psql --host=$POSTGRESQL_IP $USERNAME --dbname=orders

연결 시도가 실패하며 Cloud SQL IAM 사용자가 아직 생성되지 않았기 때문에 다음과 유사한 인증 실패 메시지가 표시된다.

Cloud SQL IAM 데이터베이스 인증은 OAuth 2.0 액세스 토큰을 사용한다.

이 토큰은 수명이 짧고 1시간만 유효하므로 인증이 필요할 때마다 토큰을 재생성해야 한다.

액세스 토큰은 항상 PGPASWARD 환경변수를 사용하여 psql 명령에 전달되어야 하며, 이는 psql 비밀번호 매개변수의 버퍼가 너무 작아서 OAuth 2.0 토큰 문자열을 보관할 수 없기 때문이다.

 

 

Navigation menu > Databases > SQL > postgres-orders > Configuration > Overview 

 

Database flags : pgAudit.log, cloudsql.enable_pgaudit 만 포함되어 있음

Users 클릭 > Add user account 클릭 > Cloud IAM에 Principal box에 lab student name을 입력

 

Add 클릭

Overview에 cloudsql.iam_authentication가 Database flags에 추가된 것을 볼 수 있다.

 

Cloud Shell을 Cloud SQL instance의 postgres-orders에 연결한다.

gcloud sql connect postgres-orders --user=postgres --quiet

 

SQL 명령문으로 사용자 권한을 설정한다.

\c orders

GRANT ALL PRIVILEGES ON TABLE order_items TO "[IAM Username]";

\q

 

Cloud IAM 데이터베이스 사용자를 사용하여 데이터베이스에 연결

export PGPASSWORD=$(gcloud auth print-access-token)
psql --host=$POSTGRESQL_IP $USERNAME --dbname=orders

 

SQL 쿼리를 통해 액세스 권한을 테스트

SELECT COUNT(*) FROM order_items;

다른 테이블에 액세스 할 수 있는지 테스트

SELECT COUNT(*) FROM users;