> For the complete documentation index, see [llms.txt](https://docs.teleskope.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.teleskope.ai/connectors/gcp/cloud-sql.md).

# Cloud SQL

## Grant Teleskope Access to Cloud SQL

Attach the following roles to the Teleskope service account you created:

* roles/cloudsql.client
* roles/cloudsql.viewer
* roles/cloudsql.admin (required to generate ssl certificates)

### Terraform

| Variable    | Description                    | Example         |
| ----------- | ------------------------------ | --------------- |
| project\_id | (Required) Your GCP Project ID | "my-project-id" |

```
resource "google_project_iam_member" "teleskope" {
  for_each = toset([
    "roles/cloudsql.client",
    "roles/cloudsql.viewer",
    "roles/cloudsql.admin"
  ])
  project = "project_id"
  role    = each.key
  member = "serviceAccount:${google_service_account.teleskope.email}"
}
```

## Teleskope Database User

For each CloudSQL instance you would like to scan using Teleskope, you will need to create or provide credentials for a database user, and grant that user read permissions.

Teleskope authenticates to the database with a username and password. Cloud SQL IAM database authentication is not currently supported, and Google does not offer it for SQL Server at all.

Each block below is run once per instance and covers every database on that instance.

### Grant Read Access to Teleskope user

#### MySQL or MariaDB

```sql
GRANT SHOW DATABASES, SELECT ON *.* TO teleskope_ro
```

#### Postgres versions 14+

```sql
GRANT pg_read_all_data TO teleskope_ro
```

#### Postgres versions < 14

```sql
SELECT format('GRANT CONNECT ON DATABASE %I TO teleskope_ro;', datname)
  FROM pg_database
  WHERE datallowconn AND datname NOT IN ('template0', 'template1', 'cloudsqladmin') \gexec
SELECT format('GRANT USAGE ON SCHEMA %I TO teleskope_ro;', nspname)
  FROM pg_namespace
  WHERE nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema' \gexec
SELECT format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO teleskope_ro;', nspname)
  FROM pg_namespace
  WHERE nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema' \gexec
```

#### SQL Server

On Cloud SQL the login is created through GCP rather than T-SQL:

```
gcloud sql users create teleskope_ro --instance=<instance-name> --password=<password>
```

Then connect to the instance and run the following once. It creates the user and grants read access in every database on the instance.

```sql
-- Cloud SQL keeps an internal database the instance admin cannot open, and the
-- sqlserver account is not sysadmin, so iterate with TRY/CATCH and skip master.
DECLARE @n SYSNAME, @s NVARCHAR(MAX);
DECLARE c CURSOR FOR
  SELECT name FROM sys.databases
  WHERE state = 0 AND HAS_DBACCESS(name) = 1
    AND name NOT IN ('master','tempdb','model','msdb');
OPEN c; FETCH NEXT FROM c INTO @n;
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    SET @s = 'USE [' + @n + '];
      IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''teleskope_ro'')
        CREATE USER teleskope_ro FOR LOGIN teleskope_ro;
      ALTER ROLE [db_datareader] ADD MEMBER [teleskope_ro];';
    EXEC sp_executesql @s;
    PRINT 'ok ' + @n;
  END TRY
  BEGIN CATCH PRINT 'skip ' + @n + ': ' + ERROR_MESSAGE(); END CATCH;
  FETCH NEXT FROM c INTO @n;
END;
CLOSE c; DEALLOCATE c;
```

It prints `ok <database>` or `skip <database>` with a reason, so a database that cannot be granted does not stop the rest. Do not run `GRANT VIEW ANY DATABASE` on Cloud SQL: the `sqlserver` account is not sysadmin so it is rejected, and it is unnecessary because `public` already holds the permission.

<details>

<summary>SQL Server: add permissions only if a crawl or scan fails</summary>

```sql
-- Server level, for certain metadata queries
GRANT VIEW SERVER STATE TO teleskope_ro;
```

```sql
-- Run per user database when needed
GRANT VIEW DEFINITION TO teleskope_ro;
GRANT VIEW DATABASE STATE TO teleskope_ro;
```

</details>