drop postgresql schema public without losing extensions As part of my development workflow, I often use
drop schema public cascade;
create schema public;

in order to get a clean state of the test database before re-loading my table and stored procedure definitions.
With the right setup, these statements work without superuser permissions.
My table definitions depend on extensions such as
create extension &quot;pgcrypto&quot;;
create extension &quot;uuid-ossp&quot;;

and whenever I drop the schema, they are lost...
which wouldn't be a problem, if they didn't require super-user permissions to re-create.
Possible solutions I have considered:
Make my user super-user
alter user myuser with superuser;

Sadly, this is the number-one suggestion when you google for any sort of permission-related issue. I would very much like to avoid super-user permissions, even on my development and testing system.
Manually re-create the extensions every time I drop the schema
To my best knowledge (and experimental results), the problem here is that I have to re-connect to the database as regular and super-user several times...

connect as regular user to run drop schema / create schema
connect as superuser to run create extension
connect as regular user to run the various create table / create function statements

This is a lot of hassle and I'd rather avoid it.
It'd be acceptable to switch once, but twice is definitely too much. When I run create schema as superuser, the regular user cannot create tables anymore.
Revoke permission to drop the extensions
update pg_extension set extowner=0;

... that's what I tried, but it didn't seem to change anything.
?
Is there an other, creative solution for this issue? Maybe

dropping all individual parts of the schema but not the schema itself
granting permissions to re-create extensions to my user
creating the extensions in a different schema, if such a thing is possible?
something else entirely?

My searches there didn't yield anything useful, but that may be due to my limited experience in the topic.
I question your desire to routinely drop the public schema, but it should be no problem if you do it like this:

create a new schema extension and put it on the search_path

use CREATE EXTENSION ... SCHEMA extension to create the extension objects in that schema


I personally use the public schema for extensions and put my data in a different schema.