drop postgresql schema public without losing extensions

Question

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 "pgcrypto";
create extension "uuid-ossp";

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.

Answer 1

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.