Fdw Setup Prereq

In Materialize, create a dedicated service account fdw_svc_account as an Organization Member. For details on setting up a service account, see Create a service account

{{< tip >}} Per the linked instructions, be sure you connect at least once with the new service account to finish creating the new account. You will also need the connection details (host, port, password) when setting up the foreign server and user mappings in PostgreSQL.

{{</ tip >}}

After you have connected at least once with the new service account to finish the new account creation, modify the fdw_svc_account role:

1. Set the default cluster to the name of your serving cluster:

   mzsqlCopy

   ALTER ROLE fdw_svc_account SET CLUSTER = <serving_cluster>;
   

2. Grant USAGE privileges on the serving cluster, and the database and schema of your views and materialized views.

   mzsqlCopy

   GRANT USAGE ON CLUSTER <serving_cluster> TO fdw_svc_account;
   GRANT USAGE ON DATABASE <db_name> TO fdw_svc_account;
   GRANT USAGE ON SCHEMA <db_name.schema_name> TO fdw_svc_account;
   

3. Grant SELECT privileges to the various view(s)/materialized view(s):

   mzsqlCopy

   GRANT SELECT ON <db_name.schema_name.view_name>, <...> TO fdw_svc_account;