bug: `drop_secret` function - secrets can only be removed via raw catalog DDL

[oscarbritse]

Summary

pg_ducklake provides ducklake.create_s3_secret() and
ducklake.create_azure_secret() to manage object-storage credentials, but
there is no corresponding ducklake.drop_secret() function.

The only way to remove a registered secret is to reach directly into
pg_foreign_server and issue DROP SERVER … CASCADE - a superuser-only raw
DDL operation that exposes implementation details (the auto-generated name
simple_s3_secret, the ducklake_secret FDW) that users should not need to
know about.

Reproduction

-- Register a secret
SELECT ducklake.create_s3_secret('s3', 'key', 'secret',
    endpoint => 'minio.example.com:9000');

-- ❌ No drop function exists
SELECT ducklake.drop_secret('s3');
-- ERROR: function ducklake.drop_secret(unknown) does not exist

-- Confirm no secret-related drop function is available at all
SELECT proname, pg_get_function_arguments(oid)
FROM pg_proc
WHERE pronamespace = 'ducklake'::regnamespace
  AND proname LIKE '%secret%';
-- _secret_fdw_handler   | (internal)
-- _secret_fdw_validator | text[], oid
-- create_s3_secret      | type text, key_id text, secret text, …
-- create_azure_secret   | connection_string text, …
-- (no drop_secret)

-- ✅ Only workaround - raw catalog DDL (requires superuser)
DROP SERVER simple_s3_secret CASCADE;

Impact

• Rotating credentials requires knowing the internal foreign-server name
  (simple_s3_secret), which is an undocumented implementation detail.
• Without a drop function, bootstrap scripts cannot be made idempotent at the
  SQL level - they must use raw DDL workarounds (see related issue:
  create_s3_secret silently creates duplicates on re-run).
• Non-superuser roles granted USAGE ON FOREIGN DATA WRAPPER ducklake_secret
  can create secrets but have no supported way to remove them.

Suggested fix

Add a ducklake.drop_secret(type text) function (and optionally
drop_secret(type text, if_exists boolean)) that:

1. Looks up the foreign server(s) registered on the ducklake_secret FDW
   whose options match the given type.
2. Drops them with CASCADE.
3. Is callable by any role that holds USAGE ON FOREIGN DATA WRAPPER ducklake_secret (matching the privilege required to create secrets).

This would also enable a clean CREATE OR REPLACE pattern:

SELECT ducklake.drop_secret('s3', if_exists => true);
SELECT ducklake.create_s3_secret('s3', 'new_key', 'new_secret', …);

Workaround

-- Run as superuser; replace the server name with the actual generated name
SELECT srvname FROM pg_foreign_server
WHERE srvfdw = (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname = 'ducklake_secret');

DROP SERVER simple_s3_secret CASCADE;

Version: pg_ducklake 1.0.0, PostgreSQL 18.4