github.com/turbot/steampipe@v1.7.0-rc.0.0.20240517123944-7cef272d4458/pkg/db/db_common/sql_connections.go (about)

     1  package db_common
     2  
     3  import (
     4  	"fmt"
     5  	"github.com/turbot/steampipe-plugin-sdk/v5/grpc/proto"
     6  	"strings"
     7  )
     8  
     9  func GetCommentsQueryForPlugin(connectionName string, p map[string]*proto.TableSchema) string {
    10  	var statements strings.Builder
    11  	for t, schema := range p {
    12  		table := PgEscapeName(t)
    13  		schemaName := PgEscapeName(connectionName)
    14  		if schema.Description != "" {
    15  			tableDescription := PgEscapeString(schema.Description)
    16  			statements.WriteString(fmt.Sprintf("COMMENT ON FOREIGN TABLE %s.%s is %s;\n", schemaName, table, tableDescription))
    17  		}
    18  		for _, c := range schema.Columns {
    19  			if c.Description != "" {
    20  				column := PgEscapeName(c.Name)
    21  				columnDescription := PgEscapeString(c.Description)
    22  				statements.WriteString(fmt.Sprintf("COMMENT ON COLUMN %s.%s.%s is %s;\n", schemaName, table, column, columnDescription))
    23  			}
    24  		}
    25  	}
    26  	return statements.String()
    27  }
    28  
    29  func GetUpdateConnectionQuery(connectionName, pluginSchemaName string) string {
    30  	// escape the name
    31  	connectionName = PgEscapeName(connectionName)
    32  
    33  	var statements strings.Builder
    34  
    35  	// Each connection has a unique schema. The schema, and all objects inside it,
    36  	// are owned by the root user.
    37  	statements.WriteString(fmt.Sprintf("drop schema if exists %s cascade;\n", connectionName))
    38  	statements.WriteString(fmt.Sprintf("create schema %s;\n", connectionName))
    39  	statements.WriteString(fmt.Sprintf("comment on schema %s is 'steampipe plugin: %s';\n", connectionName, pluginSchemaName))
    40  
    41  	// Steampipe users are allowed to use the new schema
    42  	statements.WriteString(fmt.Sprintf("grant usage on schema %s to steampipe_users;\n", connectionName))
    43  
    44  	// Permissions are limited to select only, and should be granted for all new
    45  	// objects. Steampipe users cannot create tables or modify data in the
    46  	// connection schema - they need to use the public schema for that.  These
    47  	// commands alter the defaults for any objects created in the future.
    48  	// See https://www.postgresql.org/docs/12/ddl-priv.html
    49  	statements.WriteString(fmt.Sprintf("alter default privileges in schema %s grant select on tables to steampipe_users;\n", connectionName))
    50  
    51  	// If there are any objects already then grant their permissions now. (This
    52  	// should not actually do anything at this point.)
    53  	statements.WriteString(fmt.Sprintf("grant select on all tables in schema %s to steampipe_users;\n", connectionName))
    54  
    55  	// Import the foreign schema into this connection.
    56  	statements.WriteString(fmt.Sprintf("import foreign schema \"%s\" from server steampipe into %s;\n", pluginSchemaName, connectionName))
    57  
    58  	return statements.String()
    59  }
    60  
    61  func GetDeleteConnectionQuery(name string) string {
    62  	return fmt.Sprintf("DROP SCHEMA IF EXISTS %s CASCADE;\n", PgEscapeName(name))
    63  }