Date: Wed, 13 Jul 2016 23:48:35 -0500 (PET)
Subject: Re: Create extension without superuser
Message-ID: <h6blkum78rldsrvihgeng2n3.1468471039937@email.android.com>
From: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
To: pgsql-admin@postgresql.org, Nguyen Hoai Nam <namptit307@gmail.com>
MIME-Version: 1.0
In-Reply-To: 
 <CA+vg4mOxQonjZkRbGjhnybhFY3ZoXQJOYChBYOu53RRM-8py5Q@mail.gmail.com>
References: 
 <CA+vg4mOxQonjZkRbGjhnybhFY3ZoXQJOYChBYOu53RRM-8py5Q@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="----=_Part_72997_1264334671.1468471715907"
Thread-Topic: Create extension without superuser
Thread-Index: oIOl+xrj8Z6HbhpqoOJxByt/h2VlkA==
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

------=_Part_72997_1264334671.1468471715907
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

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

So, to know wheter a non-superuser can run "CREATE EXTENSION btree_gist;", =
we need to check the installation script for btree_gist. And, unfortunatell=
y 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 privi=
lege, 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 autom=
atically created on any new database. If you need it on other databases, yo=
u can just install it.

Regards,

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

Oficina: (+51-1)=C2=A03377813=C2=A0| RPM: #034252 / (+51)=C2=A0995540103=C2=
=A0 | RPC: (+51)=C2=A0954183248
Website:=C2=A0www.ocs.pe

Sent from my Sony Xperia=E2=84=A2 smartphone

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

Hi everyone.

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

Thank you.

------=_Part_72997_1264334671.1468471715907
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<html><body><div><p dir="ltr">"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)</p>
<p dir="ltr">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.</p>
<p dir="ltr">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.</p>
<p dir="ltr">Regards,</p>
<p dir="ltr">Alvaro Aguayo<br>
Jefe de Operaciones<br>
Open Comb Systems E.I.R.L.</p>
<p dir="ltr">Oficina: (+51-1)&#160;3377813&#160;| RPM: #034252 / (+51)&#160;995540103&#160; | RPC: (+51)&#160;954183248<br>
Website:&#160;<a href="http://www.ocs.pe">www.ocs.pe</a></p>
<p dir="ltr">Sent from my Sony Xperia&#8482; smartphone</p>
<br><br>---- Nguyen Hoai Nam wrote ----<br><br></div><br><div><div dir="ltr"><div><div>Hi everyone.<br><br></div>Currently, I would like to create btree_gist with normal user. Do you have any suggesstion to do it?<br><br></div>Thank you.<br></div>
</div></body></html>
------=_Part_72997_1264334671.1468471715907--