[ADMIN] Create extension without superuser

[Nguyen Hoai Nam]

Hi everyone.

Currently, I would like to create btree_gist with normal user. Do you have any suggesstion to do it?

Thank you.

[Alvaro Aguayo Garcia-Rada]

"CREATE EXTENSION" is not itself restricted to superuser. However, various extensions will create different objects for it's usage: schemas, tables, types, functions, casts, etc. To "CREATE EXTENSION" as a non-superuser, the user must have required privileges to create all the objects required by the extension. The full SQL script for each extension can be found on /usr/share folder(/usr/share/postgresql/9.4/extension ; may vary by version and distro)

So, to know wheter a non-superuser can run "CREATE EXTENSION btree_gist;", we need to check the installation script for btree_gist. And, unfortunatelly for you, it cannot be run without being superuser. The reason is that it creates scalar types. Scalar types can only be created with superuser privilege, as a bad scalar type definition can lead to server crash.

Your best option is to install btree_gist in template1, so it will be automatically created on any new database. If you need it on other databases, you can just install it.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

---- Nguyen Hoai Nam wrote ----