CREATE ROLE auditor WITH NOLOGIN;
ALTER DATABASE orders SET pgaudit.role = 'auditor';
GRANT SELECT ON order_items TO auditor;
그 후 사용자 주문 요약, 개별 제품별 요약, 물류센터별 주문에 따른 쿼리를 실행 출력이 500행이므로 프롬프트로 돌아가려면 q와 콜론 프롬프트를 클릭
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;
psql 종료
\q
pgAudit 로그 보기 : 콘솔 창에서 Operations - 로깅 - 로그 탐색기를 클릭 후 쿼리 탭에 쿼리를 실행 후 표시된 히스토그램을 통해 DDL 삽입 및 SELECT 쿼리와 관련된 감사 활동을 볼 수 있음
SELECT 쿼리
Cloud SQL IAM 데이터베이스 인증 구성
Cloud SQL IAM 인증을 구성하기 전에 Cloud IAM 사용자를 사용하여 데이터베이스 액세스를 테스트 : Cloud SQL IAM 사용자가 아직 생성되지 않았기 때문에 에러 메시지 표시
Cloud SQL IAM 사용자 만들기 : SQL - postgres-orders 인스턴스를 클릭 후 Overview의 Configuration에서 데이터베이스 플래그 목록 확인
Users를 클릭한 후 사용자 계정 추가
Cloud IAM 사용자에게 Cloud SQL 데이터베이스 테이블에 대한 액세스 권한 부여 : 클라우드 셸에서 postgres-orders 인스턴스에 연결한 후 orders 데이터베이스로 전환하고 사용자에게 order_items 테이블에 대한 모든 권한을 부여
gcloud sql connect postgres-orders --user=postgres --quiet
supersecret!
\c orders
supersecret!
GRANT ALL PRIVILEGES ON TABLE order_items TO "student-04-c3ea8f6fdb56@qwiklabs.net";
\q
Cloud SQL IAM 인증이 구성된 후 Cloud IAM 사용자를 사용하여 데이터베이스 액세스를 테스트 : 클라우드 셸에서 Cloud IAM 데이터베이스 사용자를 사용하여 order_items 데이터베이스에 연결 후 액세스 권한을 테스트 그 후 다른 테이블에 대해서는 액세스 권한이 없는지 확인
export PGPASSWORD=$(gcloud auth print-access-token)
psql --host=$POSTGRESQL_IP $USERNAME --dbname=orders
SELECT COUNT(*) FROM order_items;
SELECT COUNT(*) FROM users;
