Skip to content

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

5.4 KB
Raw Blame
package pg

import (
	"context"

	"github.com/bytebase/omni/pg/ast"
	"github.com/pkg/errors"

	"github.com/bytebase/bytebase/backend/plugin/parser/base"
)

// ExtractAccessTablesOption configures access table extraction behavior.
type ExtractAccessTablesOption struct {
	// DefaultDatabase is the default database name to use when table reference doesn't specify one.
	DefaultDatabase string
	// DefaultSchema is the default schema name to use when table reference doesn't specify one.
	DefaultSchema string
	// SearchPath is the schema search path to use when table reference doesn't specify one.
	SearchPath []string
	// SkipMetadataValidation skips metadata lookup and validation.
	// When true, returns all table references found in SQL without checking if they exist.
	SkipMetadataValidation bool
	// GetDatabaseMetadata is the function to get database metadata (optional, only used when SkipMetadataValidation is false).
	GetDatabaseMetadata base.GetDatabaseMetadataFunc
	// Ctx is the context for metadata lookup (optional, only used when SkipMetadataValidation is false).
	Ctx context.Context
	// InstanceID is the instance ID for metadata lookup (optional, only used when SkipMetadataValidation is false).
	InstanceID string
}

// ExtractAccessTables extracts all table/view references from a SQL statement.
// This is a lightweight version that doesn't perform full query span analysis.
func ExtractAccessTables(statement string, option ExtractAccessTablesOption) ([]base.ColumnResource, error) {
	omniStmts, err := ParsePg(statement)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to parse PostgreSQL statement")
	}

	if len(omniStmts) != 1 {
		return nil, errors.Errorf("expected exactly 1 statement, got %d", len(omniStmts))
	}

	searchPath := option.SearchPath
	if len(searchPath) == 0 && option.DefaultSchema != "" {
		searchPath = []string{option.DefaultSchema}
	}
	if len(searchPath) == 0 {
		searchPath = []string{"public"}
	}

	extractor := &accessTableExtractor{
		defaultDatabase:        option.DefaultDatabase,
		searchPath:             searchPath,
		getDatabaseMetadata:    option.GetDatabaseMetadata,
		ctx:                    option.Ctx,
		instanceID:             option.InstanceID,
		skipMetadataValidation: option.SkipMetadataValidation,
	}

	// Walk the omni AST to find all RangeVar nodes (table references)
	ast.Inspect(omniStmts[0].AST, func(n ast.Node) bool {
		if extractor.err != nil {
			return false
		}
		rv, ok := n.(*ast.RangeVar)
		if !ok {
			return true
		}
		extractor.processRangeVar(rv)
		return true
	})

	if extractor.err != nil {
		return nil, errors.Wrapf(extractor.err, "failed to extract access tables")
	}

	return extractor.accessTables, nil
}

type accessTableExtractor struct {
	err                    error
	defaultDatabase        string
	searchPath             []string
	accessTables           []base.ColumnResource
	getDatabaseMetadata    base.GetDatabaseMetadataFunc
	ctx                    context.Context
	instanceID             string
	skipMetadataValidation bool
}

func (a *accessTableExtractor) processRangeVar(rv *ast.RangeVar) {
	resource := base.ColumnResource{
		Database: a.defaultDatabase,
	}

	if rv.Catalogname != "" {
		resource.Database = rv.Catalogname
		resource.Schema = rv.Schemaname
		resource.Table = rv.Relname
	} else if rv.Schemaname != "" {
		resource.Schema = rv.Schemaname
		resource.Table = rv.Relname
	} else {
		resource.Table = rv.Relname
	}

	if a.skipMetadataValidation {
		if resource.Schema == "" && !isSystemResource(resource) {
			if len(a.searchPath) > 0 {
				resource.Schema = a.searchPath[0]
			}
		}
		a.accessTables = append(a.accessTables, resource)
		return
	}

	if !isSystemResource(resource) {
		searchPath := a.searchPath
		if resource.Schema != "" {
			searchPath = []string{resource.Schema}
		}

		_, databaseMetadata, err := a.getDatabaseMetadata(a.ctx, a.instanceID, a.defaultDatabase)
		if err != nil {
			a.err = errors.Wrapf(err, "failed to get database metadata for database: %s", a.defaultDatabase)
			return
		}
		// Access pseudo table or table/view we do not sync, return directly.
		if databaseMetadata == nil {
			return
		}
		schemaName, name := databaseMetadata.SearchRelation(searchPath, resource.Table)
		if schemaName == "" && name == "" {
			return
		}
		resource.Schema = schemaName
	}

	a.accessTables = append(a.accessTables, resource)
}

// isMixedQuery checks whether the query accesses the user table and system table at the same time.
func isMixedQuery(m base.SourceColumnSet) (bool, bool) {
	hasSystem, hasUser := false, false
	for table := range m {
		if isSystemResource(table) {
			hasSystem = true
		} else {
			hasUser = true
		}
	}

	if hasSystem && hasUser {
		return false, true
	}

	return !hasUser && hasSystem, false
}

func isSystemResource(resource base.ColumnResource) bool {
	// User can access the system table/view by name directly without database/schema name.
	// For example: `SELECT * FROM pg_database`, which will access the system table `pg_database`.
	// Additionally, user can create a table/view with the same name with system table/view and access them
	// by specify the schema name, for example:
	// `CREATE TABLE pg_database(id INT); SELECT * FROM public.pg_database;` which will access the user table `pg_database`.
	if IsSystemSchema(resource.Schema) {
		return true
	}
	if resource.Schema == "" && IsSystemView(resource.Table) {
		return true
	}
	if resource.Schema == "" && IsSystemTable(resource.Table) {
		return true
	}
	return false
}