Is it possible to run sqitch without CREATE SCHEMA privilege in Snowflake?

[peter-wimsey]

Dear Sqitch,

We'd prefer to not give sqitch CREATE SCHEMA privileges. Instead we'd like to create the registry schema through another process and then have sqitch only create the tables in there.

Looking at the SQL for Snowflake it requires CREATE SCHEMA privileges even if the schema exists:

https://github.com/sqitchers/sqitch/blob/v1.4.1/lib/App/Sqitch/Engine/snowflake.sql

Would it be possible to do something like this instead?

execute immediate $$
begin
if (not exists(select * from information_schema.schemata where schema_name = upper('&registry'))) then
    create schema identifier('&registry');
end if;
end;
$$;

Thanks

[peter-wimsey]

After patching up snowflake.sql as described before and also taking out COMMENT ON SCHEMA ... as this requires database ownership, sqitch seems to works with a pre-existing schema.

[theory]

I'm confused as to why this is running at all. Sqitch only tries to create the scheme if it doesn't already exist. Here's the relevant code:

sqitch/lib/App/Sqitch/Role/DBIEngine.pm

Lines 103 to 115 in 5522821

	return try {
	$self->dbh->selectcol_arrayref(qq{
	SELECT change_id
	FROM changes
	WHERE project = ?
	ORDER BY committed_at $ord
	LIMIT 1
	OFFSET COALESCE(?, 0)
	}, undef, $project || $self->plan->project, $offset)->[0];
	} catch {
	return if $self->_no_table_error && !$self->initialized;
	die $_;
	};

It depends on the _no_table_error function:

return $DBI::state && $DBI::state eq '42S02'; # ERRCODE_UNDEFINED_TABLE

And _initialized:

https://github.com/sqitchers/sqitch/blob/5522821bdee36523bc9ab36d7c550834ca2c2748/lib/App/Sqitch/Engine/snowflake.pm#L288C5-L297

I wonder why Sqitch thinks the schema doesn't exist in your case. Here's where it creates the schema only if it doesn't exist already:

sqitch/lib/App/Sqitch/Engine.pm

Lines 241 to 251 in 5522821

	# Initialize or upgrade the database, if necessary.
	if ($self->initialized) {
	$self->upgrade_registry;
	} else {
	$sqitch->info(__x(
	'Adding registry tables to {destination}',
	destination => $self->registry_destination,
	));
	$self->initialize;
	}
	$self->register_project;