Re: What db objects can only be created with superuser?

On Tue, 26 Nov 2024 at 12:59, Yash Jain <jainism987e(at)gmail(dot)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