CODEWITHSUNDEEP
postgresql
exodehm
exodehm

Reputation: 581

Create extension from postgres superuser to another database with it own owner?

Maybe I'm saying a silliness, but is it possible to create a extension from a superuser - like postgres - in other database with it own schema and owner?

I have a database called sdmed, with two schemas, public and sdmed. I also have a role called sdmed . This role can only create tables.

I would like to create a extension in those database. The extension has functions, tables and types.

If not, what would be the better way to do that?

Edit:

My original question was incomplete. I mean that I want to create extension from user postgres (or a superuser) in other database, with it owner, role and schema. Also, those extension would be used in those database.

I try this one:

First, I create a function only for those schema (sdmed is a database, with a schema called sdmed and it owner is sdmed):

CREATE OR REPLACE FUNCTION sdmed.di_hola() 
RETURNS void AS 
$BODY$
BEGIN
RAISE NOTICE 'Hola';
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sdmed.di_hola()
  OWNER TO sdmed;

Also (after prepare my own extension) 

postgres=# create extension hola;
ERROR:  schema "sdmed" does not exist

(The database role and schema sdmed exists)

And if I try from sdmed database:

sdmed=> create extension hola;
ERROR:  permission denied to create extension "hola"
HINT:  Must be superuser to create this extension.

I would make sdmed a SuperUser, but I would like to avoid that for keep best practices.

Upvotes: 3

Views: 3154

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

I'm not sure I got your question right, but in case you're wondering if it would somehow make the extension not to work properly the answer is: it shouldn't be an issue :)

Since the user sdmed is the owner of the database sdmed, it has access to all objects in this database - no matter who created them! So, objects installed by the function created with the user postgres should work with the user sdmed without any problem.

Upvotes: 1

Related Questions