Goal This query identifies foreign tables established via the postgres_fdw (PostgreSQL Foreign Data Wrapper) that are configured to permit data modification (updatability). While postgres_fdw supports INSERT, UPDATE, and DELETE operations on remote tables, enabling this capability introduces complexity regarding distributed transactions, performance, and security. The query serves as an audit tool to verify that the updatability of these foreign tables is a deliberate architectural requirement and not an unintended default configuration.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Fixing Suggestion If updatability is not needed then set updatable=false
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, 
foreign_table_name, 
foreign_server_name,
foreign_data_wrapper_name,
foreign_server_type AS fs_type,
foreign_server_version AS fs_version,
authorization_identifier AS foreign_server_owner
FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name)
WHERE foreign_data_wrapper_name='postgres_fdw')
SELECT foreign_table_schema, 
foreign_table_name, 
foreign_server_name,
foreign_data_wrapper_name,
fs_type,
fs_version,
foreign_server_owner
FROM postgres_foreign_tables AS pfs
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable'
AND fto.option_value='false')
ORDER BY foreign_server_name, foreign_table_schema, foreign_table_name;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, 
foreign_table_name
FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name)
WHERE foreign_data_wrapper_name='postgres_fdw')
SELECT format('ALTER FOREIGN TABLE %1$I.%2$I OPTIONS (ADD updatable ''false'');', foreign_table_schema, foreign_table_name) AS statements
FROM postgres_foreign_tables AS pfs
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable'
AND fto.option_value='false')
AND NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable')
ORDER BY foreign_table_schema, foreign_table_name;

Add the property-value pair in case the external table does not have the "updatable" property.

Fix Action #2
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, 
foreign_table_name
FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name)
WHERE foreign_data_wrapper_name='postgres_fdw')
SELECT format('ALTER FOREIGN TABLE %1$I.%2$I OPTIONS (SET updatable ''false'');', foreign_table_schema, foreign_table_name) AS statements
FROM postgres_foreign_tables AS pfs
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable'
AND fto.option_value='false')
AND EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable')
ORDER BY foreign_table_schema, foreign_table_name;

Change the property-value pair in case the external table does have the "updatable" property but its value is currently true.

SQL Query to Generate FixDescription
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, 
foreign_table_name
FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name)
WHERE foreign_data_wrapper_name='postgres_fdw')
SELECT format('ALTER FOREIGN TABLE %1$I.%2$I OPTIONS (ADD updatable ''false'');', foreign_table_schema, foreign_table_name) AS statements
FROM postgres_foreign_tables AS pfs
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable'
AND fto.option_value='false')
AND NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable')
ORDER BY foreign_table_schema, foreign_table_name;
Add the property-value pair in case the external table does not have the "updatable" property.
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, 
foreign_table_name
FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name)
WHERE foreign_data_wrapper_name='postgres_fdw')
SELECT format('ALTER FOREIGN TABLE %1$I.%2$I OPTIONS (SET updatable ''false'');', foreign_table_schema, foreign_table_name) AS statements
FROM postgres_foreign_tables AS pfs
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable'
AND fto.option_value='false')
AND EXISTS (SELECT *
FROM INFORMATION_SCHEMA.foreign_table_options AS fto
WHERE pfs.foreign_table_schema=fto.foreign_table_schema
AND pfs.foreign_table_name=fto.foreign_table_name
AND fto.option_name='updatable')
ORDER BY foreign_table_schema, foreign_table_name;
Change the property-value pair in case the external table does have the "updatable" property but its value is currently true.

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Distributed database

Queries of this category provide information about the foreign table mechanism.

NameDescription
Distributed databaseQueries of this category provide information about the foreign table mechanism.