# Security, IAM, and Compliance: Managing Access and Encryption for Databases

> Chapter 17 of the Courseiva GOOGLE-PCDE curriculum — https://courseiva.com/learn/google-pcde/security-iam-and-compliance

**Official objective:** 1.6 — Configure identity and access management, network security, encryption at rest and in transit, and audit logging for databases.

## Introduction

Exam objective 1.6 for the Google Professional Cloud Database Engineer exam focuses on configuring identity and access management, network security, encryption at rest and in transit, and audit logging for databases. Security, IAM, and Compliance is the set of tools and rules that keep your database safe from unauthorised access, theft, and accidental leaks, which is crucial because databases hold the most sensitive data a company owns, such as customer credit cards, medical records, or trade secrets.

## The Apartment Building Security Desk Analogy

An apartment building security guard is the person responsible for who gets in, what they can do, and making sure the building is safe. They sit at a front desk, checking IDs of everyone who tries to enter. They have a list of residents and a list of approved visitors. A resident can use the gym, the pool, and the laundry room. A delivery driver can only drop off packages at the lobby. A repair person can only go to a specific flat for a specific time. The guard keeps a log of every person who enters and leaves.

Now imagine that same building but with a very valuable safe in the basement holding tenant documents. The guard also controls who can open that safe. They give a unique key to the building manager, a different key to the owner, and no key to the cleaner. To open the safe, a person needs the key and a code that the guard confirms. When the safe is closed, all documents inside are locked and sealed. The guard also checks that the cable company's wire running into the building is encrypted so no outsider can tap into it and steal conversations.

This entire setup maps precisely to database security. The guard is Identity and Access Management (IAM). The list of residents and visitors is the list of users and service accounts. The keys are encryption keys for data at rest. The encrypted cable is encryption in transit (TLS/SSL). The logbook is audit logging. The different permissions for the gym versus the safe are roles and policies. The building is the cloud provider's database service. The safe is the database itself. The security guard doesn't just unlock doors; they ensure only the right people get the right access, they monitor everything, and they protect the data inside and on its way out.

## Core explanation

Let us break down the four pillars of database security in Google Cloud: Identity and Access Management (IAM), network security, encryption, and audit logging.

First, Identity and Access Management (IAM) is the system that decides who can do what on which resource. A resource could be a Cloud SQL instance, a Spanner database, or a BigQuery dataset. In IAM, a ‘who’ is called a principal. A principal can be a human user (like an engineer logging in with their Google account), a service account (a special account used by an application, not a person), or a Google group (a collection of users). Principals are granted roles. A role is a collection of permissions. For example, the roles/cloudsql.client role includes permissions to connect to a Cloud SQL instance but not to delete it.

There are three types of roles: basic roles (Owner, Editor, Viewer) which are broad and dangerous for databases; predefined roles which are fine-grained and managed by Google (like roles/cloudsql.admin); and custom roles which you create yourself by picking specific permissions from a list. For the PCDE exam, you need to understand that the principle of least privilege is critical: you give a principal only the permissions it absolutely needs, nothing more. Never give a database user the Owner role unless it is a human admin who needs to delete the database. Service accounts that connect to a database should get the minimum permissions to run queries, not to create tables.

Second, network security controls how data travels to and from your database. The most important concept here is a VPC (Virtual Private Cloud), which is like a private, isolated network inside Google Cloud. You can place your database inside a VPC so it is not reachable from the public internet. This is called private connectivity. You can also use a Cloud VPN or Cloud Interconnect to connect your on-premises data centre to your VPC securely. Another key tool is a firewall rule, which allows or denies traffic based on IP addresses, ports, and protocols. For example, you can create a firewall rule that says “only allow connections to port 3306 (MySQL) from the IP address of the application server, and deny all other traffic.” You can also use Private Google Access, which allows on-premises machines or VMs without external IPs to reach Google Cloud services like Cloud SQL.

Third, encryption ensures your data is unreadable if someone steals it. There are two states: at rest (when data is stored on disk) and in transit (when data is moving between your application and the database, or between Google Cloud services). Google Cloud automatically encrypts all data at rest using AES-256 encryption before it is written to disk. This is called default encryption at rest, and it happens without any configuration. For extra control, you can use Customer-Managed Encryption Keys (CMEK) through Cloud KMS (Key Management Service). This means you create and manage your own encryption keys, and Google uses them to encrypt your data. You can even use Customer-Supplied Encryption Keys (CSEK) where you provide the key and Google does not store it. For encryption in transit, Google enforces TLS (Transport Layer Security) for all connections to its APIs and services. For databases like Cloud SQL, you must enable SSL/TLS to encrypt traffic between your application and the database. If you do not, data sent over the network can be read by anyone who intercepts it.

Fourth, audit logging records everything that happens to your database. Cloud Audit Logs captures who did what, when, and from where. There are three types: Admin Activity logs (when someone changes configuration, like deleting a database instance), Data Access logs (when someone reads or writes actual data), and System Event logs (Google Cloud actions like automatic maintenance). For databases, enabling Data Access logs is crucial for compliance because it tells you exactly which query was run by which user. Audit logs are stored in Cloud Logging (formerly Stackdriver). You can export them to BigQuery for analysis or to Cloud Storage for long-term archiving. For the exam, remember that audit logs are immutable and cannot be deleted or altered by customers; they are stored by Google.

## Real-world context

Imagine you are a database engineer at a healthcare company called MedSecure, which stores patient health records in a Cloud SQL for PostgreSQL database. You have three teams: doctors who need to read patient data, nurses who need to read and update records, and a billing team who only needs to see billing codes, not medical history.

You start by creating IAM policies. You create a custom role called ‘doctor_reader’ with only the cloudsql.instances.connect and cloudsql.databases.get permissions. You assign this role to a Google group called ‘doctors@medsecure.com’. For the nurses, you create a custom role with additional permissions to update data. For billing, you create a role that only allows access to a specific view that exposes billing codes but not patient names. You also create a service account for the clinic’s web application that needs to query patient records. You give that service account the same minimal ‘doctor_reader’ role.

Next, you configure network security. You create a VPC and launch the Cloud SQL instance with a private IP inside that VPC, meaning it has no public IP address and is not reachable from the internet. You set up a Cloud VPN between the clinic’s on-premises network and the VPC so doctors can connect from their local clinic. You create a firewall rule that only allows incoming connections on port 5432 (PostgreSQL) from the IP range of the VPN tunnel, blocking everything else. For the web application running on Google Kubernetes Engine, you use VPC-native clusters so the app can reach the database internally without going through the internet.

Then you handle encryption. By default, data at rest is encrypted with Google-managed keys. However, the compliance team requires that you use your own keys. So you go to Cloud KMS and create a key ring and a symmetric encryption key. You configure the Cloud SQL instance to use that key as its CMEK. You also enable SSL/TLS on the instance. You download the server certificate and install it on each doctor’s laptop. The web application connects using a connection string that requires SSL mode.

Finally, you enable audit logging. You enable Data Access logs for the Cloud SQL instance. Now, every time a nurse updates a patient record, that action is recorded with the nurse’s email, the timestamp, and the SQL query. You set up a log sink to export these logs to BigQuery, where the compliance officer runs weekly reports to verify no unauthorised access occurred. You also create a log-based metric that alerts if someone tries to connect from an IP outside the approved range; if so, your security team gets a notification within five minutes.

## Exam focus

The PCDE exam loves to test the difference between responsibilities. Expect scenario-based multiple-choice questions where you must decide which Google-managed service handles a specific security task.

Key concepts they repeatedly test:

- The difference between Cloud KMS, Cloud HSM (Hardware Security Module), and CMEK vs CSEK. They will ask which one gives you full control over the key lifecycle versus which one gives you the most control (CSEK) or which one uses a hardware device (Cloud HSM).
- Predefined roles vs basic roles. They will present a scenario where a new hire is given the ‘Owner’ role for a project, and ask what the security risk is. The correct answer is that basic roles grant broad permissions across all resources, violating least privilege.
- Private vs public IP for Cloud SQL. They will ask which configuration prevents the database from being accessed from the internet, with the correct answer being ‘private IP’.
- Which encryption is always on by default? The answer is default encryption at rest using Google-managed keys.
- For audit logs, they will test which type of audit log records queries (Data Access logs) and which is enabled by default (Admin Activity logs).

Common trap patterns:

- They give a scenario where a user has a service account key stored in plain text inside a container image. The trap asks what the best practice is. The correct answer is not to use service account keys at all; instead, attach the service account to the compute resource (like a VM or GKE node) so the key is automatically rotated and never stored.
- They describe a situation where a developer requests access to a production database to debug a slow query. A common wrong answer is to grant them the ‘cloudsql.admin’ role. The correct answer is to use Cloud SQL Proxy with IAM authentication, or grant a temporary custom role with only the Connect permission.
- They show a job description that requires ‘encryption at rest’ but then say the data must be encrypted before it reaches Google Cloud. That is client-side encryption, which is different from server-side encryption (CMEK).

Specific definitions to memorise:

- IAM policy: a collection of bindings that bind one or more principals to a single role.
- CMEK: Customer-Managed Encryption Keys, managed by the customer via Cloud KMS.
- CSEK: Customer-Supplied Encryption Keys, the customer supplies the key and Google does not store it.
- VPC Service Controls: a feature that allows you to define a security perimeter around Google Cloud resources to prevent data exfiltration.
- Cloud Audit Logs: immutable logs stored by Google for 30 days (for Data Access logs) or longer if exported.

## Step by step

1. **Set up IAM roles and principals** — Create Google groups for teams (e.g., db-admins, db-readers). Assign predefined or custom roles to these groups, not to individual users. This gives you a single place to manage access for many people.
2. **Configure VPC and private IP for the database** — Create a Virtual Private Cloud (VPC) and launch your Cloud SQL instance with a private IP inside that VPC. This ensures the database is not reachable from the public internet, reducing the attack surface dramatically.
3. **Set up firewall rules and VPN** — Create firewall rules to allow only specific source IP ranges or VPC networks to connect to the database port. If connecting from on-premises, set up a Cloud VPN or Cloud Interconnect for an encrypted tunnel.
4. **Enable encryption: CMEK and SSL/TLS** — Create a key ring and encryption key in Cloud KMS. Configure the database instance to use that key as its Customer-Managed Encryption Key (CMEK). Then enable SSL/TLS on the instance and download the server certificate to distribute to clients.
5. **Enable and export audit logs** — Navigate to Cloud Audit Logs and enable Data Access logs for your Cloud SQL instance. Create a log sink to export these logs to BigQuery or Cloud Storage for compliance analysis and long-term retention.
6. **Test connectivity and verify security** — Connect from a client using the Cloud SQL Proxy or a direct connection with SSL. Verify that connections from unauthorised IPs are blocked. Check Cloud Logging to confirm that audit log entries are being generated for queries.

## Comparisons

### IAM Role (Predefined) vs IAM Role (Basic)

**IAM Role (Predefined):**
- Contains a curated set of permissions specific to a service (e.g., cloudsql.admin).
- Recommended for production use because it follows least privilege.
- Google manages the permissions but you cannot add or remove individual permissions.

**IAM Role (Basic):**
- Broad permissions like Owner, Editor, Viewer that apply across all services in a project.
- Not recommended for production because it gives excessive access.
- Actually a legacy concept; Google recommends moving away from them.

### Cloud KMS (CMEK) vs Cloud KMS (CSEK)

**Cloud KMS (CMEK):**
- Customer creates and manages keys via Cloud KMS, but Google stores and uses them.
- Keys can be rotated, disabled, or deleted by the customer.
- Auditable via Cloud Audit Logs for key usage.

**Cloud KMS (CSEK):**
- Customer supplies the key themselves and Google does not store it.
- If the customer loses the key, the data cannot be decrypted ever.
- More control but higher risk; less commonly used than CMEK.

### Private IP (Cloud SQL) vs Public IP (Cloud SQL)

**Private IP (Cloud SQL):**
- Database gets an internal IP inside your VPC.
- Not reachable from the internet; reduces exposure.
- Requires a VPN or Cloud Interconnect for on-premises access.

**Public IP (Cloud SQL):**
- Database gets an external IP address reachable over the internet.
- Must be protected with SSL and authorised networks (IP whitelisting).
- Not recommended for production if a VPC is available.

### Admin Activity Audit Logs vs Data Access Audit Logs

**Admin Activity Audit Logs:**
- Records actions that change configuration (e.g., delete instance).
- Enabled by default, no additional cost.
- Cannot be turned off.

**Data Access Audit Logs:**
- Records actions that read or write data (e.g., SELECT, INSERT queries).
- Not enabled by default; must be explicitly turned on.
- Incurs extra logging costs.

### Service Account Key (JSON file) vs Workload Identity / Attached SA

**Service Account Key (JSON file):**
- A static file containing credentials; must be stored securely.
- Risk of theft and requires manual rotation.
- Preferred for applications running outside Google Cloud.

**Workload Identity / Attached SA:**
- No key file; the compute resource (VM, GKE) authenticates automatically.
- Credentials are automatically rotated by Google.
- Preferred for applications running inside Google Cloud.

## Diagram

_This diagram shows the flow from a principal (user or app) through IAM to the database, with network security, encryption, and audit logging acting as protective layers around the database instance._

```mermaid
flowchart TD
    A[Human User or App] --> B[IAM: Who can access?]
    B --> C[Principal: user, group, service account]
    C --> D[Role: collection of permissions]
    D --> E[Database Instance: Cloud SQL, Spanner, etc]
    E --> F[Network Security: VPC, Firewall, Private IP]
    E --> G[Encryption at Rest: CMEK/CSEK via Cloud KMS]
    E --> H[Encryption in Transit: TLS/SSL]
    E --> I[Audit Logs: Cloud Audit Logs -> Cloud Logging]
    I --> J[Export to BigQuery/Storage for Compliance]
```

## Common misconceptions

- **Misconception:** If I enable encryption at rest with Google-managed keys, Google can read my data. **Reality:** Google encrypts your data with keys it manages, but Google employees cannot access those keys without going through strict access control and logging. Google also cannot decrypt your data without your involvement. (People confuse 'managed by Google' with 'accessible to Google'. The encryption happens at the storage layer, and the keys are stored in a separate system with their own access controls.)
- **Misconception:** IAM roles assigned to a user also apply to databases inside that project automatically. **Reality:** IAM roles control permissions to the database instance itself (create, delete, modify), not to the data inside the database. For database-level access (query tables), you use database-native authentication like usernames and passwords or IAM database authentication. (People think IAM is a single layer that covers everything. In reality, there is a separation between cloud resource management (IAM) and data plane access (database users).)
- **Misconception:** Audit logs are turned on for all data access by default. **Reality:** Only Admin Activity logs are turned on by default. Data Access logs (which record queries and data reads) must be explicitly enabled, and they cost extra money. (Beginners assume that Google logs everything automatically, not realising that data-level logging has storage and cost implications.)
- **Misconception:** If I set a firewall rule to allow only my app’s IP, the database is completely secure. **Reality:** Firewall rules protect against unwanted network traffic, but they do not protect against threats like SQL injection, compromised credentials, or authorised users performing malicious actions. Encryption and audit logging are also required. (People think network security is the only layer needed, forgetting that the biggest threats often come from inside the network or from legitimate users.)
- **Misconception:** Using a service account key is the best way to authenticate an application to a database. **Reality:** Service account keys are a security risk because they are long-lived credentials that can be stolen. The best practice is to attach the service account to the compute resource (VM, GKE pod) so that the cloud infrastructure manages authentication without a key file. (It is a common intuitive step to download a key file, but this introduces a static secret that must be stored and rotated, which is more complex and risky.)

## Key takeaways

- IAM roles control access to the database instance resource itself, not to the data inside the database tables. For table-level access, you must configure database users separately.
- Default encryption at rest is always on and uses Google-managed AES-256 keys; you can upgrade to Customer-Managed Encryption Keys (CMEK) for more control.
- Data Access audit logs are not enabled by default and must be explicitly turned on to record individual queries and data reads; they cost additional money.
- Private IP for Cloud SQL prevents direct internet access, but you must still use a VPC, Cloud VPN, or Cloud Interconnect for connectivity.
- The principle of least privilege means granting a principal only the permissions absolutely necessary, never using basic roles like Owner for database resources.
- TLS/SSL must be manually enabled and enforced for database connections like Cloud SQL; otherwise, data in transit is not encrypted.

## FAQ

**If Google encrypts all data at rest by default, why would I ever use CMEK?**

CMEK gives you control over the key lifecycle, including rotation, disabling, and deletion. It is required by many compliance standards (like HIPAA) where the customer must have the ability to revoke access.

**What is the difference between a service account and a user account in IAM?**

A user account represents a human, often with a password and MFA. A service account is a special Google account for applications, not people, and it authenticates using a key file or metadata server.

**Can I use the same IAM role to control both access to a Cloud SQL instance and to the data inside it?**

No, IAM controls access to the instance itself (create, delete, modify). Access to data inside tables is handled by database-native authentication (username/password) or IAM database authentication, which is a separate feature.

**How long are audit logs kept in Cloud Logging?**

Admin Activity logs are kept for 400 days by default. Data Access logs are kept for 30 days by default. You can export logs to Cloud Storage or BigQuery for longer retention.

**What is the Cloud SQL Proxy and why would I use it?**

The Cloud SQL Proxy is a tool that provides secure, encrypted connections to Cloud SQL without needing to whitelist IP addresses or configure SSL. It uses IAM to authenticate the client and is the recommended way to connect from applications.

**Does enabling SSL/TLS on Cloud SQL slow down performance?**

There is a very small performance overhead (typically less than 5%) because of encryption and decryption of each packet. For most applications, this is negligible compared to the security benefit.

## Check your understanding

1. **What is the name of the Google Cloud service that allows you to create and manage your own encryption keys for use with Cloud SQL?**

   Answer: Cloud Key Management Service (Cloud KMS) is the service used to create and manage Customer-Managed Encryption Keys (CMEK) for Cloud SQL.

2. **By default, which type of Cloud Audit Log is enabled for a Cloud SQL instance without any additional configuration?**

   Answer: Admin Activity logs are enabled by default, recording configuration changes but not data access queries.

3. **What is the primary security advantage of using a private IP address for a Cloud SQL instance compared to a public IP address?**

   Answer: A private IP address makes the instance unreachable from the public internet, dramatically reducing the attack surface and eliminating the need for IP whitelisting.

4. **When a service account is attached to a Compute Engine VM, how does the application on that VM authenticate to Cloud SQL without using a key file?**

   Answer: The application uses the VM's metadata server to obtain short-lived OAuth 2.0 tokens that are automatically managed by Google, eliminating the need for stored key files.

5. **What is the name of the tool recommended by Google to securely connect an application to a Cloud SQL instance without requiring SSL/TLS configuration or IP whitelisting?**

   Answer: The Cloud SQL Proxy is the recommended tool, which uses IAM-based authentication and provides an encrypted tunnel to the Cloud SQL instance.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/google-pcde/security-iam-and-compliance