Remove EnsureSuper calls and revoke permissions instead

The following functions still call `EnsureSuperuser()` to prevent other users from executing them.

- citus_create_restore_point
- worker_drop_distributed_table
- start_metadata_sync_to_node
- stop_metadata_sync_to_node
- lock_shard_metadata
- lock_shard_resources
- worker_create_truncate_trigger
- worker_drop_distributed_table

It would be preferable to revoke their permissions from all users and then remove the call to `EnsureSuperuser`. That way, the administrator can decide which users can call these functions such that superuser is not required.

In addition `COPY (format transmit)` currently requires superuser, but we've made improvements to ensure that only files that were generated by the original user can be read. We could consider removing the superuser requirement in transmit as well.