Jump to Content

Improving security and governance in PostgreSQL with Cloud SQL

October 23, 2020
Ori Kashi

Product Manager

Akhil Jariwala

Product Manager, Cloud SQL

Ensuring databases are securely managed is a crucial part of every organization’s critical operations. When those organizations rely on a managed service like Cloud SQL, a key benefit is consistency of management, including security policies that extend beyond a single service. Cloud SQL has continued to enhance its security capabilities. We've introduced VPC Service Controls so you can securely connect to your database instance, and have added Customer Managed Encryption Keys as an option for meeting regulatory compliance. Now, we're proud to announce Cloud Identity and Access Management (Cloud IAM) integration and the enablement of PostgreSQL Audit Extension (pgAudit), both available in preview for Cloud SQL for PostgreSQL.

Enablement of pgAudit offers Cloud SQL users the flexibility to log statements at their needed level of granularity for future investigation or auditing purposes. With pgAudit, Cloud SQL users configure filters that log only the sensitive actions that are specific to their data, minimizing performance impacts to the database. Cloud SQL pgAudit logs contain the timestamp, username, database, command type, and the raw query to equip security teams with detailed information about database accesses. This extension can be configured to include which particular command sets should be audited and also allows for the creation of auditor roles, which can then be assigned to designated users. Once those logs are collected, users can analyze and monitor them from Cloud Logging, BigQuery, or their preferred third-party log management tool.

The integration with Cloud IAM enables administrators to authorize users to log in to the PostgreSQL database using short-term access tokens instead of traditional database passwords. This simplifies the authentication workflow for users by removing the need for a separate set of credentials to access the database, as well as reducing identity management complexity. This centralized approach with Cloud IAM brings greater consistency to the authentication and authorization experience with other Google Cloud database services and is simple and straightforward to set up, as demonstrated below.

Authorizing a Cloud IAM user for database login

Cloud IAM integration can be enabled by an administrator for a database instance by updating a single flag, as seen in the following command:

$ gcloud sql instances patch [INSTANCE­_NAME] --database-flag \ cloudsql.iam_authentication=on

Database users can now be created by using the same email address as the one in use for Cloud IAM and then granted privileges with a normal grant command or by assigning roles to that user.

$ gcloud beta sql users create [EMAIL] --instance=[INSTANCE­_NAME] \ --type=cloud_iam_user ...

To learn more about these new features, check our documentation here and here, and try it out with your own project. Cloud SQL continues to enhance its security and governance capabilities alongside advancements by the rest of Google Cloud and meet the needs of our customers. Stay tuned for additional investment and updates in this space across all of our database engines.

Posted in