Cloud SQL

Grant Teleskope access to Cloud SQL

For organization discovery, grant the following roles to the Teleskope connector service account at the organization level. For manual project enrollment, grant them on each enrolled project:

• roles/cloudsql.client

• roles/cloudsql.viewer

• roles/cloudsql.admin (required to generate ssl certificates)

See GCP organization discovery for the organization-level Terraform example and the complete connector role list.

Also grant the shared project-dispatch custom role from GCP organization discovery. It contains resourcemanager.projects.get and compute.regions.list, both of which run before Cloud SQL dispatch.

roles/cloudsql.viewer supplies cloudsql.instances.get and cloudsql.instances.list; roles/cloudsql.client supplies cloudsql.instances.connect for the Cloud SQL Auth Proxy. roles/cloudsql.admin is required only because it includes cloudsql.sslCerts.create when Teleskope generates a client SSL certificate.

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

GRANT SHOW DATABASES, SELECT ON *.* TO teleskope_ro

Postgres versions 14+

GRANT pg_read_all_data TO teleskope_ro

Postgres versions < 14

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.

-- 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.

SQL Server: add permissions only if a crawl or scan fails

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

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