Skip to content

Commit 8d3b421

Browse files
tglsfdctglsfdc
committed
Allow non-superusers to create (some) extensions.
Remove the unconditional superuser permissions check in CREATE EXTENSION, and instead define a "superuser" extension property, which when false (not the default) skips the superuser permissions check. In this case the calling user only needs enough permissions to execute the commands in the extension's installation script. The superuser property is also enforced in the same way for ALTER EXTENSION UPDATE cases. In other ALTER EXTENSION cases and DROP EXTENSION, test ownership of the extension rather than superuserness. ALTER EXTENSION ADD/DROP needs to insist on ownership of the target object as well; to do that without duplicating code, refactor comment.c's big switch for permissions checks into a separate function in objectaddress.c. I also removed the superuserness checks in pg_available_extensions and related functions; there's no strong reason why everybody shouldn't be able to see that info. Also invent an IF NOT EXISTS variant of CREATE EXTENSION, and use that in pg_dump, so that dumps won't fail for installed-by-default extensions. We don't have any of those yet, but we will soon. This is all per discussion of wrapping the standard procedural languages into extensions. I'll make those changes in a separate commit; this is just putting the core infrastructure in place.
1 parent 4442e19 commit 8d3b421

File tree

20 files changed

+410
-293
lines changed

20 files changed

+410
-293
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6460,8 +6460,8 @@
64606460

64616461
<para>
64626462
The <structname>pg_available_extensions</structname> view lists the
6463-
extensions that are available for installation. This view can only
6464-
be read by superusers. See also the
6463+
extensions that are available for installation.
6464+
See also the
64656465
<link linkend="catalog-pg-extension"><structname>pg_extension</structname></link>
64666466
catalog, which shows the extensions currently installed.
64676467
</para>
@@ -6522,8 +6522,8 @@
65226522

65236523
<para>
65246524
The <structname>pg_available_extension_versions</structname> view lists the
6525-
specific extension versions that are available for installation. This view
6526-
can only be read by superusers. See also the <link
6525+
specific extension versions that are available for installation.
6526+
See also the <link
65276527
linkend="catalog-pg-extension"><structname>pg_extension</structname></link>
65286528
catalog, which shows the extensions currently installed.
65296529
</para>
@@ -6560,6 +6560,12 @@
65606560
installed</entry>
65616561
</row>
65626562

6563+
<row>
6564+
<entry><structfield>superuser</structfield></entry>
6565+
<entry><type>bool</type></entry>
6566+
<entry>True if only superusers are allowed to install this extension</entry>
6567+
</row>
6568+
65636569
<row>
65646570
<entry><structfield>relocatable</structfield></entry>
65656571
<entry><type>bool</type></entry>

‎doc/src/sgml/extend.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -463,6 +463,19 @@
463463
</listitem>
464464
</varlistentry>
465465

466+
<varlistentry>
467+
<term><varname>superuser</varname> (<type>boolean</type>)</term>
468+
<listitem>
469+
<para>
470+
If this parameter is <literal>true</> (which is the default),
471+
only superusers can create the extension or update it to a new
472+
version. If it is set to <literal>false</>, just the privileges
473+
required to execute the commands in the installation or update script
474+
are required.
475+
</para>
476+
</listitem>
477+
</varlistentry>
478+
466479
<varlistentry>
467480
<term><varname>relocatable</varname> (<type>boolean</type>)</term>
468481
<listitem>

‎doc/src/sgml/ref/alter_extension.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -114,7 +114,9 @@ ALTER EXTENSION <replaceable class="PARAMETER">extension_name</replaceable> DROP
114114
</para>
115115

116116
<para>
117-
Only superusers can execute <command>ALTER EXTENSION</command>.
117+
You must own the extension to use <command>ALTER EXTENSION</command>.
118+
The <literal>ADD</>/<literal>DROP</> forms require ownership of the
119+
added/dropped object as well.
118120
</para>
119121
</refsect1>
120122

‎doc/src/sgml/ref/create_extension.sgml

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE EXTENSION <replaceable class="parameter">extension_name</replaceable>
24+
CREATE EXTENSION [ IF NOT EXISTS ] <replaceable class="parameter">extension_name</replaceable>
2525
[ WITH ] [ SCHEMA <replaceable class="parameter">schema</replaceable> ]
2626
[ VERSION <replaceable class="parameter">version</replaceable> ]
2727
[ FROM <replaceable class="parameter">old_version</replaceable> ]
@@ -51,7 +51,12 @@ CREATE EXTENSION <replaceable class="parameter">extension_name</replaceable>
5151
</para>
5252

5353
<para>
54-
Only superusers can execute <command>CREATE EXTENSION</command>.
54+
Loading an extension requires the same privileges that would be
55+
required to create its component objects. For most extensions this
56+
means superuser or database owner privileges are needed.
57+
The user who runs <command>CREATE EXTENSION</command> becomes the
58+
owner of the extension for purposes of later privilege checks, as well
59+
as the owner of any objects created by the extension's script.
5560
</para>
5661

5762
</refsect1>
@@ -60,6 +65,18 @@ CREATE EXTENSION <replaceable class="parameter">extension_name</replaceable>
6065
<title>Parameters</title>
6166

6267
<variablelist>
68+
<varlistentry>
69+
<term><literal>IF NOT EXISTS</></term>
70+
<listitem>
71+
<para>
72+
Do not throw an error if an extension with the same name already
73+
exists. A notice is issued in this case. Note that there is no
74+
guarantee that the existing extension is anything like the one that
75+
would have been created.
76+
</para>
77+
</listitem>
78+
</varlistentry>
79+
6380
<varlistentry>
6481
<term><replaceable class="parameter">extension_name</replaceable></term>
6582
<listitem>

‎doc/src/sgml/ref/drop_extension.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ DROP EXTENSION [ IF EXISTS ] <replaceable class="PARAMETER">extension_name</repl
3434
</para>
3535

3636
<para>
37-
An extension can only be dropped by a superuser.
37+
You must own the extension to use <command>DROP EXTENSION</command>.
3838
</para>
3939
</refsect1>
4040

‎src/backend/catalog/aclchk.c

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include "catalog/pg_conversion.h"
3030
#include "catalog/pg_database.h"
3131
#include "catalog/pg_default_acl.h"
32+
#include "catalog/pg_extension.h"
3233
#include "catalog/pg_foreign_data_wrapper.h"
3334
#include "catalog/pg_foreign_server.h"
3435
#include "catalog/pg_language.h"
@@ -3148,6 +3149,8 @@ static const char *const no_priv_msg[MAX_ACL_KIND] =
31483149
gettext_noop("permission denied for foreign server %s"),
31493150
/* ACL_KIND_FOREIGN_TABLE */
31503151
gettext_noop("permission denied for foreign table %s"),
3152+
/* ACL_KIND_EXTENSION */
3153+
gettext_noop("permission denied for extension %s"),
31513154
};
31523155

31533156
static const char *const not_owner_msg[MAX_ACL_KIND] =
@@ -3192,6 +3195,8 @@ static const char *const not_owner_msg[MAX_ACL_KIND] =
31923195
gettext_noop("must be owner of foreign server %s"),
31933196
/* ACL_KIND_FOREIGN_TABLE */
31943197
gettext_noop("must be owner of foreign table %s"),
3198+
/* ACL_KIND_EXTENSION */
3199+
gettext_noop("must be owner of extension %s"),
31953200
};
31963201

31973202

@@ -4688,6 +4693,48 @@ pg_conversion_ownercheck(Oid conv_oid, Oid roleid)
46884693
return has_privs_of_role(roleid, ownerId);
46894694
}
46904695

4696+
/*
4697+
* Ownership check for an extension (specified by OID).
4698+
*/
4699+
bool
4700+
pg_extension_ownercheck(Oid ext_oid, Oid roleid)
4701+
{
4702+
Relation pg_extension;
4703+
ScanKeyData entry[1];
4704+
SysScanDesc scan;
4705+
HeapTuple tuple;
4706+
Oid ownerId;
4707+
4708+
/* Superusers bypass all permission checking. */
4709+
if (superuser_arg(roleid))
4710+
return true;
4711+
4712+
/* There's no syscache for pg_extension, so do it the hard way */
4713+
pg_extension = heap_open(ExtensionRelationId, AccessShareLock);
4714+
4715+
ScanKeyInit(&entry[0],
4716+
ObjectIdAttributeNumber,
4717+
BTEqualStrategyNumber, F_OIDEQ,
4718+
ObjectIdGetDatum(ext_oid));
4719+
4720+
scan = systable_beginscan(pg_extension,
4721+
ExtensionOidIndexId, true,
4722+
SnapshotNow, 1, entry);
4723+
4724+
tuple = systable_getnext(scan);
4725+
if (!HeapTupleIsValid(tuple))
4726+
ereport(ERROR,
4727+
(errcode(ERRCODE_UNDEFINED_OBJECT),
4728+
errmsg("extension with OID %u does not exist", ext_oid)));
4729+
4730+
ownerId = ((Form_pg_extension) GETSTRUCT(tuple))->extowner;
4731+
4732+
systable_endscan(scan);
4733+
heap_close(pg_extension, AccessShareLock);
4734+
4735+
return has_privs_of_role(roleid, ownerId);
4736+
}
4737+
46914738
/*
46924739
* Fetch pg_default_acl entry for given role, namespace and object type
46934740
* (object type must be given in pg_default_acl's encoding).

‎src/backend/catalog/objectaddress.c

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,8 @@
5252
#include "commands/proclang.h"
5353
#include "commands/tablespace.h"
5454
#include "commands/trigger.h"
55+
#include "libpq/be-fsstubs.h"
56+
#include "miscadmin.h"
5557
#include "nodes/makefuncs.h"
5658
#include "parser/parse_func.h"
5759
#include "parser/parse_oper.h"
@@ -78,6 +80,7 @@ static ObjectAddress get_object_address_opcf(ObjectType objtype, List *objname,
7880
List *objargs);
7981
static bool object_exists(ObjectAddress address);
8082

83+
8184
/*
8285
* Translate an object name and arguments (as passed by the parser) to an
8386
* ObjectAddress.
@@ -688,3 +691,151 @@ object_exists(ObjectAddress address)
688691
heap_close(rel, AccessShareLock);
689692
return found;
690693
}
694+
695+
696+
/*
697+
* Check ownership of an object previously identified by get_object_address.
698+
*/
699+
void
700+
check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
701+
List *objname, List *objargs, Relation relation)
702+
{
703+
switch (objtype)
704+
{
705+
case OBJECT_INDEX:
706+
case OBJECT_SEQUENCE:
707+
case OBJECT_TABLE:
708+
case OBJECT_VIEW:
709+
case OBJECT_FOREIGN_TABLE:
710+
case OBJECT_COLUMN:
711+
case OBJECT_RULE:
712+
case OBJECT_TRIGGER:
713+
case OBJECT_CONSTRAINT:
714+
if (!pg_class_ownercheck(RelationGetRelid(relation), roleid))
715+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
716+
RelationGetRelationName(relation));
717+
break;
718+
case OBJECT_DATABASE:
719+
if (!pg_database_ownercheck(address.objectId, roleid))
720+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
721+
NameListToString(objname));
722+
break;
723+
case OBJECT_TYPE:
724+
case OBJECT_DOMAIN:
725+
case OBJECT_ATTRIBUTE:
726+
if (!pg_type_ownercheck(address.objectId, roleid))
727+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE,
728+
format_type_be(address.objectId));
729+
break;
730+
case OBJECT_AGGREGATE:
731+
case OBJECT_FUNCTION:
732+
if (!pg_proc_ownercheck(address.objectId, roleid))
733+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC,
734+
NameListToString(objname));
735+
break;
736+
case OBJECT_OPERATOR:
737+
if (!pg_oper_ownercheck(address.objectId, roleid))
738+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPER,
739+
NameListToString(objname));
740+
break;
741+
case OBJECT_SCHEMA:
742+
if (!pg_namespace_ownercheck(address.objectId, roleid))
743+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE,
744+
NameListToString(objname));
745+
break;
746+
case OBJECT_COLLATION:
747+
if (!pg_collation_ownercheck(address.objectId, roleid))
748+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_COLLATION,
749+
NameListToString(objname));
750+
break;
751+
case OBJECT_CONVERSION:
752+
if (!pg_conversion_ownercheck(address.objectId, roleid))
753+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CONVERSION,
754+
NameListToString(objname));
755+
break;
756+
case OBJECT_EXTENSION:
757+
if (!pg_extension_ownercheck(address.objectId, roleid))
758+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_EXTENSION,
759+
NameListToString(objname));
760+
break;
761+
case OBJECT_FOREIGN_SERVER:
762+
if (!pg_foreign_server_ownercheck(address.objectId, roleid))
763+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_FOREIGN_SERVER,
764+
NameListToString(objname));
765+
break;
766+
case OBJECT_LANGUAGE:
767+
if (!pg_language_ownercheck(address.objectId, roleid))
768+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_LANGUAGE,
769+
NameListToString(objname));
770+
break;
771+
case OBJECT_OPCLASS:
772+
if (!pg_opclass_ownercheck(address.objectId, roleid))
773+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPCLASS,
774+
NameListToString(objname));
775+
break;
776+
case OBJECT_OPFAMILY:
777+
if (!pg_opfamily_ownercheck(address.objectId, roleid))
778+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPFAMILY,
779+
NameListToString(objname));
780+
break;
781+
case OBJECT_LARGEOBJECT:
782+
if (!lo_compat_privileges &&
783+
!pg_largeobject_ownercheck(address.objectId, roleid))
784+
ereport(ERROR,
785+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
786+
errmsg("must be owner of large object %u",
787+
address.objectId)));
788+
break;
789+
case OBJECT_CAST:
790+
{
791+
/* We can only check permissions on the source/target types */
792+
TypeName *sourcetype = (TypeName *) linitial(objname);
793+
TypeName *targettype = (TypeName *) linitial(objargs);
794+
Oid sourcetypeid = typenameTypeId(NULL, sourcetype);
795+
Oid targettypeid = typenameTypeId(NULL, targettype);
796+
797+
if (!pg_type_ownercheck(sourcetypeid, roleid)
798+
&& !pg_type_ownercheck(targettypeid, roleid))
799+
ereport(ERROR,
800+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
801+
errmsg("must be owner of type %s or type %s",
802+
format_type_be(sourcetypeid),
803+
format_type_be(targettypeid))));
804+
}
805+
break;
806+
case OBJECT_TABLESPACE:
807+
if (!pg_tablespace_ownercheck(address.objectId, roleid))
808+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TABLESPACE,
809+
NameListToString(objname));
810+
break;
811+
case OBJECT_ROLE:
812+
if (!has_privs_of_role(roleid, address.objectId))
813+
ereport(ERROR,
814+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
815+
errmsg("must be member of role \"%s\"",
816+
NameListToString(objname))));
817+
break;
818+
case OBJECT_TSDICTIONARY:
819+
if (!pg_ts_dict_ownercheck(address.objectId, roleid))
820+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TSDICTIONARY,
821+
NameListToString(objname));
822+
break;
823+
case OBJECT_TSCONFIGURATION:
824+
if (!pg_ts_config_ownercheck(address.objectId, roleid))
825+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TSCONFIGURATION,
826+
NameListToString(objname));
827+
break;
828+
case OBJECT_FDW:
829+
case OBJECT_TSPARSER:
830+
case OBJECT_TSTEMPLATE:
831+
/* We treat these object types as being owned by superusers */
832+
if (!superuser_arg(roleid))
833+
ereport(ERROR,
834+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
835+
errmsg("must be superuser")));
836+
break;
837+
default:
838+
elog(ERROR, "unrecognized object type: %d",
839+
(int) objtype);
840+
}
841+
}

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -161,7 +161,7 @@ CREATE VIEW pg_available_extensions AS
161161

162162
CREATE VIEW pg_available_extension_versions AS
163163
SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
164-
E.relocatable, E.schema, E.requires, E.comment
164+
E.superuser, E.relocatable, E.schema, E.requires, E.comment
165165
FROM pg_available_extension_versions() AS E
166166
LEFT JOIN pg_extension AS X
167167
ON E.name = X.extname AND E.version = X.extversion;

0 commit comments

Comments
 (0)