What db objects can only be created with superuser?

On Tue, 26 Nov 2024 at 12:59, Yash Jain <jainism987e@gmail.com> wrote:

Hi all,
I noticed that in the CREATE EXTENSION code, the permission is elevated to the superuser who creates and owns all of the extension objects.
I was wondering why this elevation is done.
I understand that the C-based functions can only be created by a superuser. Are there any other db objects that require superuser? Or is C-based functions the only one?

I am hacking around the extension code (locally for my own purpose), and seeking information on this. Thank you.

Hi! This is a more pgsql-general list question.

Objects that created during extension install script will be owned by
superuser (except when extension control file uses superuser=false)

Try this:

create extension dblink ;

then
select * from pg_depend where deptype = 'e' and refobjid = (select oid
from pg_extension where extname = 'dblink');

You will see a bunch of functions, types and foreign server objects,
all owned by superuser.

For example, for foreign server you can verify it like this:

db2=> select fdwowner from pg_foreign_data_wrapper where fdwname = 'dblink_fdw';
fdwowner
----------
10
(1 row)

--
Best regards,
Kirill Reshke

Yash Jain <jainism987e@gmail.com> writes:

I understand that the C-based functions can only be created by a superuser.
Are there any other db objects that require superuser? Or is C-based
functions the only one?

Scalar types and operator classes are two, there are probably others
I'm not thinking of. Search the CREATE reference pages for mentions
of "superuser".

regards, tom lane