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
- connect as superuser to run
- connect as regular user to run the various
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.