GCP CloudSql Monitoring

Your data needs highest level of protection. Cloudanix can you help with that!

GCP CloudSql audit checks

Root User Should Not Be Accessible From Any Host

Ensures SQL instances root user cannot be accessed from any host. Root access for SQL instance should only be allowed from whitelisted IPs to ensure secure access only from trusted entities.

Databases Should Have SSL

Ensures SQL databases have SSL enabled. Enabling SSL ensures that the sensitive data being transferred from the database is encrypted.

Point In Time Restore Should Be Enabled

Ensures SQL instances can be restored to a recent point. GCP will maintain a point to which the database can be restored. This point should not drift too far into the past, or else the risk of irrecoverable data loss may occur.

DB Instances Should Not Be Publicly Accessible

Ensures that SQL instances are not publicly accessible. Public IP associated with these SQL DB Instances should be removed.

Instances Should Be Multi AZ

Ensures that SQL instances have a failover replica to be cross-AZ for high availability. Creating SQL instances in with a single AZ creates a single point of failure for all systems relying on that database. All SQL instances should be created in multiple AZs to ensure proper failover.

Automated Backups Should Be Enabled

Ensures automated backups are enabled for SQL instances. Google provides a simple method of backing up SQL instances at a regular interval. This should be enabled to provide an option for restoring data in the event of a database compromise or hardware failure.

Database Instances Should Not Have Public IPs

Ensure that Cloud SQL database instances do not have public IPs.

Cross DB Ownership Chaining Should Be Disabled

Ensure that the cross db ownership chaining database flag for Cloud SQL SQL Server instance is set to off.

Database Authentication Flag Should Be Disabled

Ensure that the contained database authentication database flag for Cloud SQL on the SQL Server instance is set to off.

MySQL Local Infile Flag Should Be Disabled

Ensure that the local_infile database flag for a Cloud SQL Mysql instance is set to off.

PostgreSQL Log Checkpoints Flag Should Be Disabled

Ensure that the log_checkpoints database flag for Cloud SQL PostgreSQL instance is set to on.

PostgreSQL Log Connections Flag Should Be On

PostgreSQL does not log attempted connections by default. Enabling the log_connections setting will create log entries for each attempted connection as well as successful completion of client authentication which can be useful in troubleshooting issues and to determine any unusual connection attempts to the server. This recommendation is applicable to PostgreSQL database instances.

PostgreSQL Log Disconnections Flag Should Be Disabled

Ensure that the log_disconnections database flag for Cloud SQL PostgreSQL instance is set to on.

PostgreSQL Log Lock Waits Flag Should Be Disabled

Ensure that the log_lock_waits database flag for Cloud SQL PostgreSQL instance is set to on.

PostgreSQL Log Min Messages Flag Should Be Disabled

Ensure that the log_min_messages database flag for Cloud SQL PostgreSQL instance is set appropriately.

PostgreSQL Log Temp Files Flag Should Be 0

The log_parser_stats flag enables a crude profiling method for logging parser performance statistics which even though can be useful for troubleshooting, it may increase the amount of logs significantly and have performance overhead.

PostgreSQL Log Min Duration Statement Flag Should Be -1

Database SSL Certificate Should Be Rotated After Every 90 Days

Ensured that SSL certificates are rotated after every 90 days

Read Replica Instances Should Not Be public

Ensure that Read Replica Instances are not publicly accessible to prevent private data from being exposed.

Determine SQL Instances Provisioned are of Desired Type

Determine if the SQL instances provisioned in the account are only of the type desired by the organization and nothing else.

SQL Instance Should Have Backend Type Second Generation

Ensure that SQL Instances Backend Type is Second Generation.

SQL Instances Should Be Encrypted Using Customer Managed Keys (CMKs)

Ensure that SQL Instances are encrypted using Customer Managed Keys (CMKs).

SQL Instances should have Storage Auto Resize Enabled

Ensure that SQL Instances are have storageAutoResize set to True

Provisioned Instances Should Not Exceed Set Threshold

Ensure that total number of SQL Instances does not exceed the threshold set by the organization.

SQL Instances Should Not Be Configured with Barred Usernames

Ensure that SQL Instances are not configured with Barred Usernames

Minimum Number of SQL Backups To Be Retained

Ensure that SQL Instances have a reasonable number of backups retained in order to recover from failures.

SQL Instances Should Be Configured with the Latest SQL Versions

Ensure that SQL Instances are configured to use the latest SQL versions.

SQL Read Replica Instances Should Be Encrypted Using Customer Managed Keys (CMKs)

Ensure that SQL Read Replica Instances are encrypted using Customer Managed Keys (CMKs).

SQL Backup Configuration Should Be Enabled

Ensure that SQL Instances have Backup Configuration enabled.

SQL Instances Should Have Maintenance Scheduled Within the Next 30 Day

Ensure that SQL Instances have Maintenance scheduled within the next 30 days

SQL Query Insights should be Enabled

Ensure that SQL Instances have Query Insights enabled.

SQL Instances Should Have Binary Logging Enabled

Ensure that SQL Instances have Binary Logging Enabled that maintains all the Update statements of the Database to help in recovery.

PostgreSQL Log Min Error Statement Flag Should Be Error Or Stricter

Auditing helps in troubleshooting operational problems and also permits forensic analysis. If log_min_error_statement is not set to the correct value, messages may not be classified as error messages appropriately. An organization will need to decide their own threshold for logging log_min_messages flag. This recommendation is applicable to PostgreSQL database instances.

PostgreSQL Log Parser Stats Flag Should Be Off

Ensure that the log_parser_stats database flag for a Cloud SQL PostgreSQL instance is set to off.

PostgreSQL Log Planner Stats Flag Should Be Off

Ensure that the log_planner_stats database flag for a Cloud SQL PostgreSQL instance is set to off.

PostgreSQL Log Executor Stats Flag Should Be Off

Ensure that the log_executor_stats database flag for a Cloud SQL PostgreSQL instance is set to off.

PostgreSQL Log Statement Stats Flag Should Be Set Appropriately

The log_statement_stats flag controls the inclusion of end to end performance statistics of a SQL query in the PostgreSQL logs for each query. This cannot be enabled with other module statistics (log_parser_stats, log_planner_stats, log_executor_stats). Default value for log_statement_stats flag is off. The log_statement_stats flag enables a crude profiling method for logging end to end performance statistics of a SQL query. This can be useful for troubleshooting but may increase the amount of logs significantly and have performance overhead.

PostgreSQL External Scripts Enabled Flag Should Be Off

external scripts enabled, enables the execution of scripts with certain remote language extensions. This property is OFF by default. When Advanced Analytics Services is installed, setup can optionally set this property to true. As the External Scripts Enabled feature allows scripts external to SQL such as files located in an R library to be executed, which could adversely affect the security of the system, hence this should be disabled.This recommendation is applicable to SQL Server database instances.

SQL Server User Connections Flag Should Be A Non-Limiting Value

The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is by default a self-configuring value, with SQL Server adjusting the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that the maximum (32,767) user connections are allowed. However if there is a number defined here that limits connections, SQL Server will not allow anymore above this limit. If the connections are at the limit, any new requests will be dropped, potentially causing lost data or outages for those using the database.

SQL Server User Options Flag Should Not Be Configured

The user options option specifies global defaults for all users. A list of default query processing options is established for the duration of a user's work session. The user options option allows you to change the default values of the SET options (if the server's default settings are not appropriate). A user can override these defaults by using the SET statement. You can configure user options dynamically for new logins. After you change the setting of user options, new login sessions use the new setting; current login sessions are not affected. This recommendation is applicable to SQL Server database instances.

SQL Server Remove Access Flag Should Be Off

The remote access option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. This default value for this option is 1. This grants permission to run local stored procedures from remote servers or remote stored procedures from the local server. To prevent local stored procedures from being run from a remote server or remote stored procedures from being run on the local server, this must be disabled. The Remote Access option controls the execution of local stored procedures on remote servers or remote stored procedures on local server. 'Remote access' functionality can be abused to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target, hence this should be disabled. This recommendation is applicable to SQL Server database instances.

SQL Server Trace Flag Should Be Off

Microsoft SQL Trace Flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems, but they may also be recommended by Microsoft Support to address behavior that is negatively impacting a specific workload. All documented trace flags and those recommended by Microsoft Support are fully supported in a production environment when used as directed. 3625(trace log) Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages using '******'. Setting this in a Google Cloud flag for the instance allows for security through obscurity and prevents the disclosure of sensitive information, hence this is recommended to set this flag globally to off to prevent the flag having been left on, or turned on by bad actors. This recommendation is applicable to SQL Server database instances.

PostgreSQL Enable PGAudit Database Flag Should Be On

As numerous other recommendations in this section consist of turning on flags for logging purposes, your organization will need a way to manage these logs. You may have a solution already in place. If you do not, consider installing and enabling the open source pgaudit extension within PostgreSQL and enabling its corresponding flag of cloudsql.enable_pgaudit. This flag and installing the extension enables database auditing in PostgreSQL through the open-source pgAudit extension. This extension provides detailed session and object logging to comply with government, financial, & ISO standards and provides auditing capabilities to mitigate threats by monitoring security events on the instance. Enabling the flag and settings later in this recommendation will send these logs to Google Logs Explorer so that you can access them in a central location. to This recommendation is applicable only to PostgreSQL database instances.

MySQL Skip Show Database Flag Should Be On

skip_show_database' database flag prevents people from using the SHOW DATABASES statement if they do not have the SHOW DATABASES privilege. This can improve security if you have concerns about users being able to see databases belonging to other users. Its effect depends on the SHOW DATABASES privilege: If the variable value is ON, the SHOW DATABASES statement is permitted only to users who have the SHOW DATABASES privilege, and the statement displays all database names. If the value is OFF, SHOW DATABASES is permitted to all users, but displays the names of only those databases for which the user has the SHOW DATABASES or other privilege. This recommendation is applicable to Mysql database instances.

PostgreSQL Log Hostname Flag Should Be On

Logging hostnames allows for the association of hostname to IP address at the time of connection. This information can aid with incident response efforts particularly in an environment that utilized dynamic IP addresses. Logging hostnames may incur overhead on server performance as for each statement logged, DNS resolution will be required to convert IP address to hostname. Depending on the setup, this may be non-negligible. This recommendation is applicable to PostgreSQL database instances.

PostgreSQL Log Statement Flag Should Be Set Appropriately

The value of log_statement flag determined the SQL statements that are logged. Valid values are: • none • ddl • mod • all The value ddl logs all data definition statements. The value mod logs all ddl statements, plus data-modifying statements. The statements are logged after a basic parsing is done and statement type is determined, thus this does not logs statements with errors. When using extended query protocol, logging occurs after an Execute message is received and values of the Bind parameters are included. A value of 'ddl' is recommended unless otherwise directed by your organization's logging policy.