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, make sure that highly privileged users have the search_path parameter set to pg_catalog. This makes sure that the search path is secured and 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, 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, administrators must restrict USAGE privileges on the foreign server. This physically prevents non-administrator users from running CREATE FOREIGN TABLE statements.

  1. Restrict USAGE on the foreign server by ensuring that non-administrator roles don't have USAGE privileges on the foreign server. Revoke it from PUBLIC if necessary.
  2. Centralize table creation: Only database administrators, for example, users with the alloydbsuperuser role, can create foreign tables.
  3. Grant selective access: administrators manage read access by using GRANT SELECT on specific foreign tables.

Example: Configuring 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.

-- Step 1: Create the server and restrict access (Executed as Administrator) ```sql 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; ```

-- Step 2: Create a User Mapping for the regular user (Executed as Administrator) sql CREATE USER MAPPING FOR bob SERVER bq_server;

-- Step 3: Create the Foreign Table (Executed as Administrator) -- Because 'bob' does not have USAGE on bq_server, only the administrator can run this. sql CREATE FOREIGN TABLE example_table ( id INT, state VARCHAR ) SERVER bq_server OPTIONS ( project 'BIGQUERY_PROJECT_ID', dataset 'BIGQUERY_DATASET_NAME', table 'example_table' );

-- Step 4: Grant Selective Read Access (Executed as Administrator) -- Explicitly grant read access to the regular user for this specific table. sql GRANT SELECT ON public.example_table TO bob;