PostgreSQL extension owners? - Stack Overflow

admin管理员组

文章数量:1401499

In PostgreSQL a database can have extensions, such as pg_trgm. Extensions have owners. When (in a Rails app) you try to load the schema, the extension has to be owned by the owner of the database.

I have one database engine with different database-owning roles, let's say role_A and role_G. They each own their own database, and each database inherits from template1.

For historical reasons, template1 already has the pg_trgm extension, and role_A is the extension owner when seen from within template1.

When role_G connects and does a CREATE DATABASE, it gets a database in which role_A is the owner of the pg_trgm extension.

Both databases need the extension, and both database will be dropped and recreated frequently, as Rails likes to do with test databases.

What approach will resolve this conflict? For example, can template1 be modified so that this extension is owned by a role that will resolve to the new owner of any newly-created database? something like PUBLIC ? What commands or queries or statements will accomplish this?

本文标签： PostgreSQL extension ownersStack Overflow