pg_synonym

pg_synonym : Table and view aliases synchronized with target DDL.

Overview

Packages

Source

pig build pkg pg_synonym;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_synonym;		# install via package name, for the active PG version

pig install pg_synonym -v 18;   # install for PG 18
pig install pg_synonym -v 17;   # install for PG 17
pig install pg_synonym -v 16;   # install for PG 16
pig install pg_synonym -v 15;   # install for PG 15

Create this extension with:

CREATE EXTENSION pg_synonym;

Usage

Sources:

• Official source
• Official source
• Official source

pg_synonym maintains table and view aliases as invoker-security views. Release 1.0.0 installs extension version 1.0; PostgreSQL 15 or later and superuser installation are required. It needs no preload or server restart.

Core Workflow

CREATE EXTENSION pg_synonym;
CREATE SCHEMA app;
CREATE SCHEMA erp;
CREATE TABLE erp.customers (id bigint PRIMARY KEY, name text);
SELECT synonym.create('app.customer', 'erp.customers');
INSERT INTO app.customer VALUES (1, 'Ada');
SELECT * FROM app.customer;
SELECT * FROM synonym.list;

All helpers live in the synonym schema.

Operations and Permissions

synonym.create can register an alias before its target exists; event triggers resolve it later. Target renames follow the object identity, and supported column changes rebuild the alias. synonym.refresh() reconciles aliases manually. synonym.drop removes one, with optional if_exists => true.

Access through an alias still checks target permissions and row security. Creating an alias requires creation rights in its schema and usage rights on the target schema. Removing one requires ownership, role membership or superuser rights.

Boundaries

Targets include ordinary, partitioned and foreign tables, views and materialized views. Aliases to aliases, cross-database objects, functions and sequences are unsupported. Use a query for copying data out; the alias is not a substitute for target DDL or truncation. Merging through a view requires PostgreSQL 17 or later.

Manual changes to an alias view can break synchronization. Dependent views may block destructive target changes, and rebuilding an alias can reset grants applied directly to it. Dropping the extension leaves ordinary alias views behind; plan their cleanup separately.