Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

29.3 KB
Raw Blame
package pg

import (
	"context"
	"database/sql"
	"fmt"
	"slices"
	"strings"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/stretchr/testify/require"

	"github.com/bytebase/bytebase/backend/common/testcontainer"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/db"
	pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
)

func TestIsAtLeastPG10(t *testing.T) {
	tests := []struct {
		version string
		want    bool
	}{
		{
			"hello",
			true,
		},
		{
			"9.6.24",
			false,
		},
		{
			"9.624",
			false,
		},
		{
			"10.0.0",
			true,
		},
		{
			"16.0.0",
			true,
		},
		{
			"16.14",
			true,
		},
	}

	for _, test := range tests {
		got := isAtLeastPG10(test.version)
		require.Equal(t, test.want, got)
	}
}

// legacyListColumnQuery is the previous INFORMATION_SCHEMA.COLUMNS-based column query.
// It is kept here as the oracle for TestListColumnQueryMatchesInformationSchema: the
// pg_catalog-based query must produce identical rows when the user has full privileges.
var legacyListColumnQuery = `
SELECT
	cols.table_schema,
	cols.table_name,
	cols.column_name,
	cols.data_type,
	cols.character_maximum_length,
	cols.numeric_precision,
	cols.numeric_scale,
	cols.datetime_precision,
	cols.ordinal_position,
	cols.column_default,
	cols.is_nullable,
	cols.collation_name,
	cols.udt_schema,
	cols.udt_name,
	cols.identity_generation,
	pg_catalog.col_description(c.oid, cols.ordinal_position::int) as column_comment
FROM INFORMATION_SCHEMA.COLUMNS AS cols
LEFT JOIN pg_namespace n ON n.nspname = cols.table_schema
LEFT JOIN pg_class c ON c.relname = cols.table_name AND c.relnamespace = n.oid AND c.relkind IN ('r', 'v', 'm', 'p', 'f')` + fmt.Sprintf(`
WHERE cols.table_schema NOT IN (%s)
  AND cols.table_schema NOT LIKE 'pg_temp%%'
  AND cols.table_schema NOT LIKE 'pg_toast%%'
ORDER BY cols.table_schema, cols.table_name, cols.ordinal_position;`, pgparser.SystemSchemaWhereClause)

type columnQueryRow struct {
	TableSchema        string
	TableName          string
	ColumnName         string
	DataType           string
	CharacterMaxLength sql.NullString
	NumericPrecision   sql.NullString
	NumericScale       sql.NullString
	DatetimePrecision  sql.NullString
	OrdinalPosition    int64
	ColumnDefault      sql.NullString
	IsNullable         string
	CollationName      sql.NullString
	UdtSchema          string
	UdtName            string
	IdentityGeneration sql.NullString
	ColumnComment      sql.NullString
}

func queryColumnRows(t *testing.T, txn *sql.Tx, query string) []columnQueryRow {
	t.Helper()
	rows, err := txn.Query(query)
	require.NoError(t, err)
	defer rows.Close()

	var result []columnQueryRow
	for rows.Next() {
		var r columnQueryRow
		require.NoError(t, rows.Scan(
			&r.TableSchema,
			&r.TableName,
			&r.ColumnName,
			&r.DataType,
			&r.CharacterMaxLength,
			&r.NumericPrecision,
			&r.NumericScale,
			&r.DatetimePrecision,
			&r.OrdinalPosition,
			&r.ColumnDefault,
			&r.IsNullable,
			&r.CollationName,
			&r.UdtSchema,
			&r.UdtName,
			&r.IdentityGeneration,
			&r.ColumnComment,
		))
		result = append(result, r)
	}
	require.NoError(t, rows.Err())

	// The two queries may order rows differently for exotic identifier names
	// (information_schema sorts varchar with the database collation, pg_catalog sorts
	// the name type with C collation), so normalize the order before comparing.
	slices.SortFunc(result, func(a, b columnQueryRow) int {
		if c := strings.Compare(a.TableSchema, b.TableSchema); c != 0 {
			return c
		}
		if c := strings.Compare(a.TableName, b.TableName); c != 0 {
			return c
		}
		return int(a.OrdinalPosition - b.OrdinalPosition)
	})
	return result
}

// TestListColumnQueryMatchesInformationSchema verifies that the pg_catalog-based column
// query produces exactly the same rows as the previous INFORMATION_SCHEMA.COLUMNS-based
// query for a privileged user, across a wide range of column types. This guards the type
// rendering contract: the Go-side formatting in getTableColumns depends on the exact
// data_type / precision / udt values that information_schema used to produce.
func TestListColumnQueryMatchesInformationSchema(t *testing.T) {
	t.Parallel()

	_, pgDB := testcontainer.NewPgDatabase(t)

	setupSQL := `
CREATE SCHEMA app;
CREATE TYPE app.mood AS ENUM ('happy', 'sad');
CREATE TYPE public.shirt_size AS ENUM ('s', 'm', 'l');
CREATE DOMAIN app.positive_int AS integer NOT NULL CHECK (VALUE > 0);
CREATE DOMAIN public.us_zip AS varchar(10);
CREATE DOMAIN app.int_array AS integer[];
CREATE SEQUENCE app.seq1;

CREATE TABLE app.kitchen_sink (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id2 integer GENERATED BY DEFAULT AS IDENTITY,
    c_smallint smallint,
    c_int integer NOT NULL DEFAULT 42,
    c_bigint bigint DEFAULT nextval('app.seq1'),
    c_numeric numeric,
    c_numeric_p numeric(8),
    c_numeric_p0 numeric(8, 0),
    c_numeric_ps numeric(10, 2),
    c_real real,
    c_double double precision,
    c_money money,
    c_char char,
    c_char_n char(5),
    c_varchar varchar,
    c_varchar_n varchar(20) DEFAULT 'hello',
    c_text text COLLATE "C",
    c_bytea bytea,
    c_bool boolean DEFAULT true,
    c_date date,
    c_time time,
    c_time_p time(3),
    c_timetz time with time zone,
    c_timetz_p time(5) with time zone,
    c_ts timestamp,
    c_ts_p timestamp(2),
    c_tstz timestamp with time zone DEFAULT now(),
    c_tstz_p timestamp(4) with time zone,
    c_interval interval,
    c_interval_p interval(3),
    c_uuid uuid,
    c_json json,
    c_jsonb jsonb DEFAULT '{}'::jsonb,
    c_xml xml,
    c_inet inet,
    c_cidr cidr,
    c_macaddr macaddr,
    c_bit bit,
    c_bit_n bit(3),
    c_varbit varbit,
    c_varbit_n varbit(8),
    c_int_array integer[],
    c_text_matrix text[][],
    c_varchar_array varchar(5)[],
    c_enum app.mood DEFAULT 'happy',
    c_enum_public public.shirt_size,
    c_enum_array app.mood[],
    c_domain app.positive_int,
    c_domain_varchar public.us_zip DEFAULT 'x',
    c_domain_array app.int_array,
    c_point point,
    c_tsvector tsvector,
    c_int4range int4range,
    c_oid oid,
    c_name name,
    c_internal_char "char",
    c_generated integer GENERATED ALWAYS AS (c_int * 2) STORED,
    c_drop_me text
);
ALTER TABLE app.kitchen_sink DROP COLUMN c_drop_me;
COMMENT ON COLUMN app.kitchen_sink.c_int IS 'int comment';

CREATE TABLE app.parted (id integer, region text NOT NULL) PARTITION BY LIST (region);
CREATE TABLE app.parted_us PARTITION OF app.parted FOR VALUES IN ('us');

CREATE VIEW app.v_sink AS SELECT id, c_int, c_text FROM app.kitchen_sink;

CREATE TABLE public.plain (a serial, b text);
`
	_, err := pgDB.Exec(setupSQL)
	require.NoError(t, err)

	txn, err := pgDB.Begin()
	require.NoError(t, err)
	defer txn.Rollback()

	// SyncDBSchema clears the search path before the column sync; match it so default
	// expressions render identically.
	require.NoError(t, setTxSearchPath(txn, ""))

	legacyRows := queryColumnRows(t, txn, legacyListColumnQuery)
	newRows := queryColumnRows(t, txn, getListColumnQuery(true))

	// Sanity: the kitchen sink table alone has 50+ columns.
	require.Greater(t, len(legacyRows), 50)
	require.Equal(t, legacyRows, newRows)
}

// TestSyncColumnsWithoutTablePrivilege verifies that column sync no longer depends on the
// sync user's table privileges. information_schema.columns privilege-filters its rows, so
// a sync user without privileges on a table used to get table metadata with indexes and
// constraints (read from pg_catalog) but zero columns. The pg_catalog-based column query
// must return the columns regardless.
func TestSyncColumnsWithoutTablePrivilege(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	pgContainer := testcontainer.SharedPgContainer(t)
	dbName, pgDB := testcontainer.NewPgDatabase(t)

	setupSQL := `
CREATE SCHEMA restricted;
CREATE TABLE restricted.secret_table (
    id integer PRIMARY KEY,
    name varchar(50) NOT NULL DEFAULT 'anon',
    amount numeric(10, 2),
    CONSTRAINT positive_amount CHECK (amount >= 0)
);
CREATE INDEX idx_secret_name ON restricted.secret_table (name);
`
	_, err := pgDB.Exec(setupSQL)
	require.NoError(t, err)

	// pg_read_all_stats lets the sync read pg_table_size/pg_indexes_size; it grants no
	// table or column privileges, so information_schema.columns still hides the table.
	limitedUser := "limited_" + dbName
	_, err = pgDB.Exec(fmt.Sprintf("CREATE USER %s WITH LOGIN PASSWORD 'limited-password'; GRANT pg_read_all_stats TO %s;", limitedUser, limitedUser))
	require.NoError(t, err)

	limitedDB, err := sql.Open("pgx", fmt.Sprintf("host=%s port=%s user=%s password=limited-password database=%s", pgContainer.GetHost(), pgContainer.GetPort(), limitedUser, dbName))
	require.NoError(t, err)
	defer limitedDB.Close()
	require.NoError(t, limitedDB.Ping())

	// Confirm the privilege filtering this fix works around: information_schema.columns
	// returns nothing for the table when queried by the unprivileged user.
	var count int
	require.NoError(t, limitedDB.QueryRow(`
		SELECT COUNT(*) FROM information_schema.columns
		WHERE table_schema = 'restricted' AND table_name = 'secret_table'`).Scan(&count))
	require.Zero(t, count)

	driver := &Driver{}
	config := db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Type:     storepb.DataSourceType_ADMIN,
			Username: limitedUser,
			Host:     pgContainer.GetHost(),
			Port:     pgContainer.GetPort(),
			Database: dbName,
		},
		Password: "limited-password",
		ConnectionContext: db.ConnectionContext{
			EngineVersion: "16.0",
			DatabaseName:  dbName,
		},
	}

	openedDriver, err := driver.Open(ctx, storepb.Engine_POSTGRES, config)
	require.NoError(t, err)
	defer openedDriver.Close(ctx)

	pgDriver, ok := openedDriver.(*Driver)
	require.True(t, ok)

	metadata, err := pgDriver.SyncDBSchema(ctx)
	require.NoError(t, err)

	var secretTable *metadatapb.TableMetadata
	for _, schema := range metadata.Schemas {
		if schema.Name != "restricted" {
			continue
		}
		for _, table := range schema.Tables {
			if table.Name == "secret_table" {
				secretTable = table
				break
			}
		}
	}
	require.NotNil(t, secretTable, "restricted.secret_table not found in synced metadata")

	// Indexes and check constraints were always visible via pg_catalog; columns must now
	// be visible too instead of leaving inconsistent table metadata.
	require.NotEmpty(t, secretTable.Indexes)
	require.NotEmpty(t, secretTable.CheckConstraints)

	require.Len(t, secretTable.Columns, 3)
	id, name, amount := secretTable.Columns[0], secretTable.Columns[1], secretTable.Columns[2]

	require.Equal(t, "id", id.Name)
	require.Equal(t, "integer", id.Type)
	require.False(t, id.Nullable)

	require.Equal(t, "name", name.Name)
	require.Equal(t, "character varying(50)", name.Type)
	require.False(t, name.Nullable)
	require.Equal(t, "'anon'::character varying", name.Default)

	require.Equal(t, "amount", amount.Name)
	require.Equal(t, "numeric(10,2)", amount.Type)
	require.True(t, amount.Nullable)
}

func TestSync_ColumnDefaultSchemaQualification(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	pgContainer := testcontainer.SharedPgContainer(t)
	dbName, pgDB := testcontainer.NewPgDatabase(t)

	// Set up test schema with various default value scenarios
	setupSQL := `
-- Create enum type for testing schema qualification
CREATE TYPE test_size AS ENUM ('small', 'medium', 'large');

-- Create sequence for testing schema qualification  
CREATE SEQUENCE test_sequence START 1;

-- Create test table with various default scenarios
CREATE TABLE test_defaults (
    -- No default
    id INTEGER,
    
    -- NULL default
    col_null_default TEXT DEFAULT NULL,
    
    -- String literal with schema-qualified type
    col_string_literal VARCHAR(50) DEFAULT 'hello',
    
    -- Numeric literal
    col_numeric INTEGER DEFAULT 42,
    
    -- Boolean literal
    col_boolean BOOLEAN DEFAULT true,
    
    -- Function expression
    col_function TIMESTAMP DEFAULT now(),
    
    -- Expression
    col_expression INTEGER DEFAULT (10 + 20),
    
    -- SERIAL (creates sequence automatically)
    col_serial SERIAL,
    
    -- Enum default (should be schema-qualified)
    col_enum test_size DEFAULT 'medium',
    
    -- Custom sequence (should be schema-qualified)
    col_sequence INTEGER DEFAULT nextval('test_sequence'),
    
    -- Array default
    col_array INTEGER[] DEFAULT '{1,2,3}',
    
    -- JSON default
    col_json JSONB DEFAULT '{"key": "value"}',
    
    -- Binary default
    col_binary BYTEA DEFAULT '\xDEADBEEF'
);

-- Add comment to help identify the table
COMMENT ON TABLE test_defaults IS 'Test table for column default schema qualification';
`

	// Execute the DDL
	_, err := pgDB.Exec(setupSQL)
	require.NoError(t, err)

	// Create driver and get metadata using SyncDBSchema
	driver := &Driver{}
	config := db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Type:     storepb.DataSourceType_ADMIN,
			Username: "postgres",
			Host:     pgContainer.GetHost(),
			Port:     pgContainer.GetPort(),
			Database: dbName,
		},
		Password: "root-password",
		ConnectionContext: db.ConnectionContext{
			EngineVersion: "16.0",
			DatabaseName:  dbName,
		},
	}

	openedDriver, err := driver.Open(ctx, storepb.Engine_POSTGRES, config)
	require.NoError(t, err)
	t.Cleanup(func() { require.NoError(t, openedDriver.Close(ctx)) })

	pgDriver, ok := openedDriver.(*Driver)
	require.True(t, ok)

	// Run sync operation to get metadata
	metadata, err := pgDriver.SyncDBSchema(ctx)
	require.NoError(t, err)
	require.NotNil(t, metadata)

	// Find our test table
	var testTable *metadatapb.TableMetadata
	for _, schema := range metadata.Schemas {
		if schema.Name == "public" {
			for _, table := range schema.Tables {
				if table.Name == "test_defaults" {
					testTable = table
					break
				}
			}
		}
	}
	require.NotNil(t, testTable, "test_defaults table should be found")

	// Create a map for easier column lookup
	columnMap := make(map[string]*metadatapb.ColumnMetadata)
	for _, col := range testTable.Columns {
		columnMap[col.Name] = col
	}

	// Test cases to verify schema qualification
	testCases := []struct {
		columnName      string
		expectedDefault string
		description     string
	}{
		{
			columnName:      "id",
			expectedDefault: "",
			description:     "No default should be empty string",
		},
		{
			columnName:      "col_null_default",
			expectedDefault: "",
			description:     "DEFAULT NULL should be empty string (PostgreSQL limitation)",
		},
		{
			columnName:      "col_string_literal",
			expectedDefault: "'hello'::character varying",
			description:     "String literal should have type cast",
		},
		{
			columnName:      "col_numeric",
			expectedDefault: "42",
			description:     "Numeric literal should be unquoted",
		},
		{
			columnName:      "col_boolean",
			expectedDefault: "true",
			description:     "Boolean literal should be unquoted",
		},
		{
			columnName:      "col_function",
			expectedDefault: "now()",
			description:     "Function should be preserved",
		},
		{
			columnName:      "col_expression",
			expectedDefault: "(10 + 20)",
			description:     "Expression should preserve parentheses",
		},
		{
			columnName:      "col_enum",
			expectedDefault: "'medium'::public.test_size",
			description:     "Enum default should be SCHEMA-QUALIFIED with public.test_size",
		},
		{
			columnName:      "col_sequence",
			expectedDefault: "nextval('public.test_sequence'::regclass)",
			description:     "Custom sequence should be SCHEMA-QUALIFIED with public.test_sequence",
		},
		{
			columnName:      "col_array",
			expectedDefault: "'{1,2,3}'::integer[]",
			description:     "Array should have type cast",
		},
		{
			columnName:      "col_json",
			expectedDefault: "'{\"key\": \"value\"}'::jsonb",
			description:     "JSONB should have type cast",
		},
		{
			columnName:      "col_binary",
			expectedDefault: "'\\xdeadbeef'::bytea",
			description:     "Binary should have type cast and lowercase hex",
		},
	}

	// Verify each test case
	for _, tc := range testCases {
		t.Run(tc.columnName, func(t *testing.T) {
			t.Parallel()
			column, exists := columnMap[tc.columnName]
			require.True(t, exists, "Column %s should exist", tc.columnName)

			require.Equal(t, tc.expectedDefault, column.Default,
				"Column %s: %s. Expected: %q, Got: %q",
				tc.columnName, tc.description, tc.expectedDefault, column.Default)
		})
	}

	// Special test: Verify SERIAL column has schema-qualified sequence
	serialColumn, exists := columnMap["col_serial"]
	require.True(t, exists, "col_serial should exist")
	require.Contains(t, serialColumn.Default, "public.test_defaults_col_serial_seq",
		"SERIAL column should have schema-qualified sequence name. Got: %s", serialColumn.Default)
	require.Contains(t, serialColumn.Default, "nextval(",
		"SERIAL column should use nextval function. Got: %s", serialColumn.Default)

	// Verify that Default field is being used (consolidation from DefaultNull and DefaultExpression)
	for columnName, column := range columnMap {
		// The Default field should be used instead of the deprecated DefaultExpression and DefaultNull fields
		if column.Default != "" {
			t.Logf("Column %s uses Default field: %s", columnName, column.Default)
		}
	}
}

func TestSync_ColumnDefaultCrossSchemaQualification(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	pgContainer := testcontainer.SharedPgContainer(t)
	dbName, pgDB := testcontainer.NewPgDatabase(t)

	// Create schema with cross-schema references
	setupSQL := `
-- Create custom schema to test cross-schema qualification
CREATE SCHEMA custom_schema;

-- Create enum in custom schema
CREATE TYPE custom_schema.status_type AS ENUM ('active', 'inactive', 'pending');

-- Create sequence in custom schema
CREATE SEQUENCE custom_schema.my_sequence START 100;

-- Create function in custom schema for testing
CREATE OR REPLACE FUNCTION custom_schema.get_prefix() RETURNS TEXT AS $$
BEGIN
    RETURN 'prefix_value';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Test table in public schema with references to custom schema objects
CREATE TABLE critical_test (
    -- Cross-schema enum reference
    status custom_schema.status_type DEFAULT 'active',
    
    -- Cross-schema sequence reference  
    counter INTEGER DEFAULT nextval('custom_schema.my_sequence'),
    
    -- Complex expression with cross-schema function
    computed TEXT DEFAULT custom_schema.get_prefix()
);
`

	// Execute the DDL
	_, err := pgDB.Exec(setupSQL)
	require.NoError(t, err)

	// Create driver and get metadata
	driver := &Driver{}
	config := db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Type:     storepb.DataSourceType_ADMIN,
			Username: "postgres",
			Host:     pgContainer.GetHost(),
			Port:     pgContainer.GetPort(),
			Database: dbName,
		},
		Password: "root-password",
		ConnectionContext: db.ConnectionContext{
			EngineVersion: "16.0",
			DatabaseName:  dbName,
		},
	}

	openedDriver, err := driver.Open(ctx, storepb.Engine_POSTGRES, config)
	require.NoError(t, err)
	defer openedDriver.Close(ctx)

	pgDriver, ok := openedDriver.(*Driver)
	require.True(t, ok)

	// Run sync operation
	metadata, err := pgDriver.SyncDBSchema(ctx)
	require.NoError(t, err)

	// Find the test table
	var testTable *metadatapb.TableMetadata
	for _, schema := range metadata.Schemas {
		if schema.Name == "public" {
			for _, table := range schema.Tables {
				if table.Name == "critical_test" {
					testTable = table
					break
				}
			}
		}
	}
	require.NotNil(t, testTable)

	// Verify cross-schema qualification
	columnMap := make(map[string]*metadatapb.ColumnMetadata)
	for _, col := range testTable.Columns {
		columnMap[col.Name] = col
	}

	// Critical test: Cross-schema enum should be fully qualified
	statusCol := columnMap["status"]
	require.NotNil(t, statusCol)
	require.Equal(t, "'active'::custom_schema.status_type", statusCol.Default,
		"Cross-schema enum should be fully qualified")

	// Critical test: Cross-schema sequence should be fully qualified
	counterCol := columnMap["counter"]
	require.NotNil(t, counterCol)
	require.Equal(t, "nextval('custom_schema.my_sequence'::regclass)", counterCol.Default,
		"Cross-schema sequence should be fully qualified")

	// Critical test: Complex expression with cross-schema function
	computedCol := columnMap["computed"]
	require.NotNil(t, computedCol)
	require.Equal(t, "custom_schema.get_prefix()", computedCol.Default,
		"Cross-schema function should be fully qualified")

	// Cleanup: Drop test objects to avoid interference with other tests
	cleanupSQL := `
		DROP TABLE IF EXISTS critical_test CASCADE;
		DROP TABLE IF EXISTS test_defaults CASCADE;
		DROP SCHEMA IF EXISTS custom_schema CASCADE;
		DROP TYPE IF EXISTS test_size CASCADE;
		DROP SEQUENCE IF EXISTS test_sequence CASCADE;
	`
	_, err = pgDB.Exec(cleanupSQL)
	require.NoError(t, err)
}

// TestSyncCompositeTypes pins the exact metadata sync produces for composite
// types. The definition round-trip in backend/plugin/schema/pg compares two
// synced databases against each other, so a sync that is wrong the same way on
// both sides passes it vacuously; these assertions do not.
func TestSyncCompositeTypes(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	pgContainer := testcontainer.SharedPgContainer(t)
	dbName, pgDB := testcontainer.NewPgDatabase(t)

	_, err := pgDB.ExecContext(ctx, `
CREATE TYPE zz_base AS (street text COLLATE "C", city varchar(50));
COMMENT ON TYPE zz_base IS 'base address type';
COMMENT ON COLUMN zz_base.street IS 'street line';
CREATE TYPE aa_nested AS (home zz_base, homes zz_base[], tags text[]);
CREATE TYPE empty_type AS ();
CREATE SCHEMA geo;
CREATE TYPE geo.point2 AS (lat numeric(9,6), lng numeric(9,6));
CREATE TYPE wrapper AS (p geo.point2);
CREATE TABLE plain_table (id int);
CREATE SCHEMA locale;
CREATE COLLATION locale.mycoll (locale = 'C');
CREATE TYPE coll_qualified AS (v text COLLATE locale.mycoll);
`)
	require.NoError(t, err)

	driver := &Driver{}
	config := db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Type:     storepb.DataSourceType_ADMIN,
			Username: "postgres",
			Host:     pgContainer.GetHost(),
			Port:     pgContainer.GetPort(),
			Database: dbName,
		},
		Password: "root-password",
		ConnectionContext: db.ConnectionContext{
			EngineVersion: "16.0",
			DatabaseName:  dbName,
		},
	}

	openedDriver, err := driver.Open(ctx, storepb.Engine_POSTGRES, config)
	require.NoError(t, err)
	t.Cleanup(func() { require.NoError(t, openedDriver.Close(ctx)) })

	pgDriver, ok := openedDriver.(*Driver)
	require.True(t, ok)

	metadata, err := pgDriver.SyncDBSchema(ctx)
	require.NoError(t, err)

	compositesBySchema := make(map[string]map[string]*metadatapb.CompositeTypeMetadata)
	for _, schemaMeta := range metadata.Schemas {
		m := make(map[string]*metadatapb.CompositeTypeMetadata)
		for _, composite := range schemaMeta.CompositeTypes {
			m[composite.Name] = composite
		}
		compositesBySchema[schemaMeta.Name] = m
	}

	public := compositesBySchema["public"]
	require.Len(t, public, 5, "public schema should have exactly the created composite types")
	require.NotContains(t, public, "plain_table", "table row types must not be synced as composite types")

	collQualified := public["coll_qualified"]
	require.NotNil(t, collQualified)
	require.Len(t, collQualified.Attributes, 1)
	require.Equal(t, "locale.mycoll", collQualified.Attributes[0].Collation,
		"non-pg_catalog collations must be schema-qualified")

	base := public["zz_base"]
	require.NotNil(t, base)
	require.Equal(t, "base address type", base.Comment)
	require.Len(t, base.Attributes, 2)
	require.Equal(t, "street", base.Attributes[0].Name)
	require.Equal(t, "text", base.Attributes[0].Type)
	require.Equal(t, `"C"`, base.Attributes[0].Collation)
	require.Equal(t, "street line", base.Attributes[0].Comment)
	require.Equal(t, "city", base.Attributes[1].Name)
	require.Equal(t, "character varying(50)", base.Attributes[1].Type)
	require.Empty(t, base.Attributes[1].Collation)

	nested := public["aa_nested"]
	require.NotNil(t, nested)
	require.Len(t, nested.Attributes, 3)
	require.Equal(t, "public.zz_base", nested.Attributes[0].Type, "user-defined attribute types must be schema-qualified")
	require.Equal(t, "public.zz_base[]", nested.Attributes[1].Type)
	require.Equal(t, "text[]", nested.Attributes[2].Type)

	empty := public["empty_type"]
	require.NotNil(t, empty, "zero-attribute composite types must be synced")
	require.Empty(t, empty.Attributes)

	wrapper := public["wrapper"]
	require.NotNil(t, wrapper)
	require.Len(t, wrapper.Attributes, 1)
	require.Equal(t, "geo.point2", wrapper.Attributes[0].Type, "cross-schema attribute types must be schema-qualified")

	geo := compositesBySchema["geo"]
	require.Len(t, geo, 1)
	require.NotNil(t, geo["point2"])
}

// legacyListForeignTableQuery is the previous information_schema.foreign_tables-based
// query, kept as the oracle: for a privileged user the pg_catalog-based query must
// produce identical rows.
const legacyListForeignTableQuery = `SELECT
	foreign_table.foreign_table_schema,
	foreign_table.foreign_table_name,
	foreign_table.foreign_server_catalog,
	foreign_table.foreign_server_name
FROM information_schema.foreign_tables AS foreign_table
ORDER BY foreign_table.foreign_table_schema, foreign_table.foreign_table_name;`

const foreignTableSetupSQL = `
CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'postgres');
CREATE SCHEMA fdw_schema;
CREATE FOREIGN TABLE fdw_schema.remote_orders (
    id integer NOT NULL,
    note varchar(30)
) SERVER loopback_server OPTIONS (schema_name 'public', table_name 'orders');
CREATE FOREIGN TABLE public.remote_items (
    item_id bigint
) SERVER loopback_server OPTIONS (schema_name 'public', table_name 'items');
`

func queryForeignTableRows(t *testing.T, conn *sql.DB, query string) [][4]string {
	t.Helper()
	rows, err := conn.Query(query)
	require.NoError(t, err)
	defer rows.Close()

	var result [][4]string
	for rows.Next() {
		var r [4]string
		require.NoError(t, rows.Scan(&r[0], &r[1], &r[2], &r[3]))
		result = append(result, r)
	}
	require.NoError(t, rows.Err())
	return result
}

// TestListForeignTableQueryMatchesInformationSchema verifies that the pg_catalog-based
// foreign table query produces exactly the same rows as the previous
// information_schema.foreign_tables-based query for a privileged user.
func TestListForeignTableQueryMatchesInformationSchema(t *testing.T) {
	t.Parallel()

	_, pgDB := testcontainer.NewPgDatabase(t)

	_, err := pgDB.Exec(foreignTableSetupSQL)
	require.NoError(t, err)

	legacyRows := queryForeignTableRows(t, pgDB, legacyListForeignTableQuery)
	newRows := queryForeignTableRows(t, pgDB, getListForeignTableQuery())

	require.Len(t, legacyRows, 2)
	require.Equal(t, legacyRows, newRows)
}

// TestSyncForeignTablesWithoutTablePrivilege verifies that foreign table sync no longer
// depends on the sync user's privileges. information_schema.foreign_tables
// privilege-filters its rows, so a sync user without privileges on a foreign table used
// to silently lose the whole table from the synced metadata.
func TestSyncForeignTablesWithoutTablePrivilege(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	pgContainer := testcontainer.SharedPgContainer(t)
	dbName, pgDB := testcontainer.NewPgDatabase(t)

	_, err := pgDB.Exec(foreignTableSetupSQL)
	require.NoError(t, err)

	// pg_read_all_stats lets the sync read pg_table_size/pg_indexes_size; it grants no
	// table or column privileges, so information_schema.foreign_tables still filters.
	limitedUser := "limited_" + dbName
	_, err = pgDB.Exec(fmt.Sprintf("CREATE USER %s WITH LOGIN PASSWORD 'limited-password'; GRANT pg_read_all_stats TO %s;", limitedUser, limitedUser))
	require.NoError(t, err)

	limitedDB, err := sql.Open("pgx", fmt.Sprintf("host=%s port=%s user=%s password=limited-password database=%s", pgContainer.GetHost(), pgContainer.GetPort(), limitedUser, dbName))
	require.NoError(t, err)
	defer limitedDB.Close()
	require.NoError(t, limitedDB.Ping())

	// Confirm the privilege filtering this fix works around: the unprivileged user sees
	// no foreign tables through information_schema.
	var count int
	require.NoError(t, limitedDB.QueryRow(`SELECT COUNT(*) FROM information_schema.foreign_tables`).Scan(&count))
	require.Zero(t, count)

	driver := &Driver{}
	config := db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Type:     storepb.DataSourceType_ADMIN,
			Username: limitedUser,
			Host:     pgContainer.GetHost(),
			Port:     pgContainer.GetPort(),
			Database: dbName,
		},
		Password: "limited-password",
		ConnectionContext: db.ConnectionContext{
			EngineVersion: "16.0",
			DatabaseName:  dbName,
		},
	}

	openedDriver, err := driver.Open(ctx, storepb.Engine_POSTGRES, config)
	require.NoError(t, err)
	defer openedDriver.Close(ctx)

	pgDriver, ok := openedDriver.(*Driver)
	require.True(t, ok)

	metadata, err := pgDriver.SyncDBSchema(ctx)
	require.NoError(t, err)

	var fdwSchema *metadatapb.SchemaMetadata
	for _, schema := range metadata.Schemas {
		if schema.Name == "fdw_schema" {
			fdwSchema = schema
			break
		}
	}
	require.NotNil(t, fdwSchema, "fdw_schema not found in synced metadata")
	require.Len(t, fdwSchema.ExternalTables, 1)

	remoteOrders := fdwSchema.ExternalTables[0]
	require.Equal(t, "remote_orders", remoteOrders.Name)
	require.Equal(t, "loopback_server", remoteOrders.ExternalServerName)
	require.Equal(t, dbName, remoteOrders.ExternalDatabaseName)

	// Columns of foreign tables come from the privilege-independent column sync.
	require.Len(t, remoteOrders.Columns, 2)
	require.Equal(t, "id", remoteOrders.Columns[0].Name)
	require.Equal(t, "integer", remoteOrders.Columns[0].Type)
	require.False(t, remoteOrders.Columns[0].Nullable)
	require.Equal(t, "note", remoteOrders.Columns[1].Name)
	require.Equal(t, "character varying(30)", remoteOrders.Columns[1].Type)
}