Enhancing Cloud Database Security in PostgreSQL

Cloud SQL represents a fully managed relational database service catering to MySQL, PostgreSQL, and SQL Server, and it is proudly hosted by Google Cloud Platform. An exciting development in this service is the recent introduction of IAM integration, brought forth by Google Cloud. This enhancement empowers users to access Cloud SQL through their email addresses, which are associated with their cloud identities. Moreover, this integration facilitates the streamlined management of user permissions for databases. The noteworthy aspect of this integration is its elimination of password requirements, reliance on short-lived authentication tokens, and the meticulous tracking of user login activities in system logs.

‍

Utilizing IAM database authentication for the management of user access to Cloud SQL is a robust and highly secure approach, ensuring efficiency and peace of mind.

‍

In this blog post, we will delve into the process of configuring IAM database authentication within Cloud SQL.

Two Levels of Access Control

When implementing IAM database authentication, it becomes necessary to assign permissions at two distinct levels:

1. IAM (Identity and Access Management)

2. Databases

‍

IAM access

At the IAM level, individuals are granted the privilege to establish connections with the Cloud SQL instance using their email addresses. To enable IAM authentication, follow these steps:

‍

Step 1: When creating a new Cloud SQL instance or updating an existing one, include the following flag:

‍

cloudsql.iam_authentication=on

‍

‍

Step 2: Assign the "Cloud SQL Instance user" role to the users. This role grants them the necessary permissions to log in to the Cloud SQL instance.

‍

Step 3: Add the desired users to the Cloud SQL instance.

With these steps completed, users can successfully connect to the Cloud SQL instance and access the databases. However, for the ability to query and modify the databases, it is imperative to grant access at the database level.

Databases access

‍

In PostgreSQL, it is possible to grant permissions directly to individual database users; however, this approach is not recommended as it can become cumbersome to manage permissions for each user individually. A more efficient and organized solution involves utilizing roles within PostgreSQL, which streamlines the permission management process.

To clarify, it's important to understand that in PostgreSQL, users, groups, and roles are essentially the same, with the only distinction being that users possess login access by default. In other words:

• A Role is equivalent to a User and grants login access.

• A User is essentially a Role with login access.

To simplify permission management, we can create multiple roles tailored to specific sets of permissions, and then assign the appropriate role to each user.

For the sake of simplification, let's create two roles: one with Readonly access and another with Readwrite access.

ReadOnly role

‍

This role only allows you to run select queries on the tables of the database:

‍

CREATE ROLE read_only_role; 

-- Grant SELECT privilege to this role on all tables in your schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
-- Ensure that future tables in the schema will also inherit this privilege
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_role;

‍

ReadWrite role

‍

This role allows you to run select, insert, update, and delete queries on the tables of the database:

‍

CREATE ROLE read_write_role;

-- Grant SELECT, INSERT, UPDATE, and DELETE privileges to this role on all tables in your schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write_role;

-- Ensure that future tables in the schema will also inherit these privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write_role;

‍

Grant role to the users

‍

Once roles have been established, you can assign them to users as needed. With predefined roles in place, the process involves granting these roles to users. Additionally, you have the flexibility to create new roles or adjust existing ones to align with specific requirements. It's important to note that any alterations made to the roles will directly impact the access permissions granted to the associated users.

This approach simplifies and streamlines the management of user privileges within the PostgreSQL database.

‍

GRANT read_only_role to user_email@test.com
GRANT read_write_role to user_email@test.com

How to Access Databases Using Cloud SQL IAM Database Authentication

A secure method for establishing a connection with a Cloud SQL instance involves utilizing the CloudSQL Auth proxy.

‍

After installing the most recent version of the Cloud SQL Auth proxy binary, the next step is to authenticate through Google Cloud IAM. This can be accomplished as follows:

‍

# Sign in to your account
$ gcloud auth login

‍

Upon successful authentication, we proceed to establish a proxy connection to the Cloud SQL instance using the following command:

‍

# Establish a connection using CloudSQL Proxy
$ ./cloud_sql_proxy PROJECT:REGION:CLOUDSQL_INSTANCE_NAME --auto-iam-authn

‍

Once the connection is established, we can access the database using our email address:

‍

# Access PostgreSQL
$ psql "host=127.0.0.1 port=$CLOUDSQLPROXYPORT sslmode=disable dbname=$DBNAME user=$email" 

‍

Since we are connecting to the CloudSQL instance publicly, it will expect a SSL certificate — so, thesslmode parameter is set to disable.However, the Cloud SQL Auth proxy does provide an encrypted connection.

‍

Alternatively, for users employing Postgres utilities like PGAdmin or DataGrip the authentication token can be used as the password:

‍

# To get the authentication token, after gcloud auth login

$ gcloud auth print-access-token

Streamlining Access Management with P0 Security

Through P0 Security's Slack Just-In-Time (JIT) access management, user creation in Cloud SQL and access to specific roles are efficiently handled.

‍

With P0 Security integrated into your Slack workspace, the process of granting access is streamlined. Users can request access simply by typing /p0 request within Slack. Configured team members can then approve these requests, specifying an expiration time and confirming it via a Slack message button. P0 Security automates the provisioning of access for the user, with access being automatically revoked upon reaching the specified expiration time.

‍

P0 Security takes care of adding the user to the Cloud SQL instance and provides the necessary Cloud SQL permissions to connect to the instance. Users are granted short-lived access to the roles they requested.

‍

To delve deeper into the capabilities of P0 Security, refer to the comprehensive docs. Additionally, you have the opportunity to engage with a community of like-minded users through our community Slack channel.

‍

What's more, you can get started with P0 Security by creating a free account, and no credit card is required for the initial setup.