0

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.