Best practices for securing your AlloyDB for PostgreSQL resources

To help you achieve a strong security posture for your AlloyDB for PostgreSQL resources, follow the best practices provided in this page.

Prevent search path hijacking

To prevent search path hijacking, set the search_path parameter to pg_catalog for highly privileged users. This configuration secures the search path so that untrusted schemas like public are bypassed.

To set this parameter permanently for a user, run the following command:

ALTER ROLE USER_NAME SET search_path = pg_catalog,pg_temp;

To set this parameter only for the current session, run the following command:

SET search_path TO pg_catalog,pg_temp;

To set this parameter for all users when connected to a database, run the following command:

ALTER DATABASE DB_NAME SET search_path TO schema1, schema2, public;

For more information about securing the public schema, see the PostgreSQL documentation on secure schema usage and the CVE-2018-1058 guide.

Secure BigQuery data access using the foreign data wrapper

When you use the BigQuery foreign data wrapper (FDW), all queries from AlloyDB to BigQuery are authenticated using the AlloyDB cluster's service account. Because this service account might have access to multiple sensitive BigQuery datasets, it's critical to prevent regular database users from mapping unauthorized tables.

To enforce least-privilege access, restrict USAGE privileges on the foreign server so that non-administrator users can't run CREATE FOREIGN TABLE statements:

1. Restrict USAGE on the foreign server: prevent non-administrator roles from having USAGE privileges, and revoke USAGE from PUBLIC if necessary.

2. Centralize table creation: allow only database administrators, such as users with the alloydbsuperuser role, to create foreign tables.

3. Grant selective access: manage read access by using GRANT SELECT on specific foreign tables.

Example: Configure least-privilege access

The following workflow demonstrates how an administrator can securely grant a regular user (bob) access to a specific BigQuery table without allowing them to map arbitrary datasets:

1. As an administrator, create the foreign server and revoke USAGE privileges from PUBLIC to prevent unauthorized table creation:

   CREATE EXTENSION IF NOT EXISTS bigquery_fdw;
   CREATE SERVER bq_server FOREIGN DATA WRAPPER bigquery_fdw;
   
   -- Explicitly revoke USAGE from all users to prevent unauthorized table creation
   REVOKE ALL ON FOREIGN SERVER bq_server FROM PUBLIC;
   

2. As an administrator, create a user mapping for the regular user:

   CREATE USER MAPPING FOR bob SERVER bq_server;
   

3. As an administrator, create the foreign table:

   -- Because 'bob' does not have USAGE on bq_server, only the administrator can run this.
   CREATE FOREIGN TABLE example_table (
       id INT,
       state VARCHAR
   ) SERVER bq_server OPTIONS (
       project 'BIGQUERY_PROJECT_ID',
       dataset 'BIGQUERY_DATASET_NAME',
       table 'example_table'
   );
   

4. As an administrator, grant selective read access on the specific foreign table to the regular user:

   GRANT SELECT ON public.example_table TO bob;