Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8d3b421

Browse files
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 casethe calling user only needs enough permissions to execute the commandsin the extension's installation script. The superuser property is alsoenforced in the same way for ALTER EXTENSION UPDATE cases.In other ALTER EXTENSION cases and DROP EXTENSION, test ownership ofthe extension rather than superuserness. ALTER EXTENSION ADD/DROP needsto insist on ownership of the target object as well; to do that withoutduplicating code, refactor comment.c's big switch for permissions checksinto a separate function in objectaddress.c.I also removed the superuserness checks in pg_available_extensions andrelated functions; there's no strong reason why everybody shouldn'tbe able to see that info.Also invent an IF NOT EXISTS variant of CREATE EXTENSION, and use thatin 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 languagesinto extensions. I'll make those changes in a separate commit; this isjust putting the core infrastructure in place.
1 parent4442e19 commit8d3b421

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
staticconstchar*constnot_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
returnhas_privs_of_role(roleid,ownerId);
46894694
}
46904695

4696+
/*
4697+
* Ownership check for an extension (specified by OID).
4698+
*/
4699+
bool
4700+
pg_extension_ownercheck(Oidext_oid,Oidroleid)
4701+
{
4702+
Relationpg_extension;
4703+
ScanKeyDataentry[1];
4704+
SysScanDescscan;
4705+
HeapTupletuple;
4706+
OidownerId;
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+
returnhas_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
staticboolobject_exists(ObjectAddressaddress);
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
returnfound;
690693
}
694+
695+
696+
/*
697+
* Check ownership of an object previously identified by get_object_address.
698+
*/
699+
void
700+
check_object_ownership(Oidroleid,ObjectTypeobjtype,ObjectAddressaddress,
701+
List*objname,List*objargs,Relationrelation)
702+
{
703+
switch (objtype)
704+
{
705+
caseOBJECT_INDEX:
706+
caseOBJECT_SEQUENCE:
707+
caseOBJECT_TABLE:
708+
caseOBJECT_VIEW:
709+
caseOBJECT_FOREIGN_TABLE:
710+
caseOBJECT_COLUMN:
711+
caseOBJECT_RULE:
712+
caseOBJECT_TRIGGER:
713+
caseOBJECT_CONSTRAINT:
714+
if (!pg_class_ownercheck(RelationGetRelid(relation),roleid))
715+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
716+
RelationGetRelationName(relation));
717+
break;
718+
caseOBJECT_DATABASE:
719+
if (!pg_database_ownercheck(address.objectId,roleid))
720+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_DATABASE,
721+
NameListToString(objname));
722+
break;
723+
caseOBJECT_TYPE:
724+
caseOBJECT_DOMAIN:
725+
caseOBJECT_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+
caseOBJECT_AGGREGATE:
731+
caseOBJECT_FUNCTION:
732+
if (!pg_proc_ownercheck(address.objectId,roleid))
733+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_PROC,
734+
NameListToString(objname));
735+
break;
736+
caseOBJECT_OPERATOR:
737+
if (!pg_oper_ownercheck(address.objectId,roleid))
738+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_OPER,
739+
NameListToString(objname));
740+
break;
741+
caseOBJECT_SCHEMA:
742+
if (!pg_namespace_ownercheck(address.objectId,roleid))
743+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_NAMESPACE,
744+
NameListToString(objname));
745+
break;
746+
caseOBJECT_COLLATION:
747+
if (!pg_collation_ownercheck(address.objectId,roleid))
748+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_COLLATION,
749+
NameListToString(objname));
750+
break;
751+
caseOBJECT_CONVERSION:
752+
if (!pg_conversion_ownercheck(address.objectId,roleid))
753+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CONVERSION,
754+
NameListToString(objname));
755+
break;
756+
caseOBJECT_EXTENSION:
757+
if (!pg_extension_ownercheck(address.objectId,roleid))
758+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_EXTENSION,
759+
NameListToString(objname));
760+
break;
761+
caseOBJECT_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+
caseOBJECT_LANGUAGE:
767+
if (!pg_language_ownercheck(address.objectId,roleid))
768+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_LANGUAGE,
769+
NameListToString(objname));
770+
break;
771+
caseOBJECT_OPCLASS:
772+
if (!pg_opclass_ownercheck(address.objectId,roleid))
773+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_OPCLASS,
774+
NameListToString(objname));
775+
break;
776+
caseOBJECT_OPFAMILY:
777+
if (!pg_opfamily_ownercheck(address.objectId,roleid))
778+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_OPFAMILY,
779+
NameListToString(objname));
780+
break;
781+
caseOBJECT_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+
caseOBJECT_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+
Oidsourcetypeid=typenameTypeId(NULL,sourcetype);
795+
Oidtargettypeid=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+
caseOBJECT_TABLESPACE:
807+
if (!pg_tablespace_ownercheck(address.objectId,roleid))
808+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_TABLESPACE,
809+
NameListToString(objname));
810+
break;
811+
caseOBJECT_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+
caseOBJECT_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+
caseOBJECT_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+
caseOBJECT_FDW:
829+
caseOBJECT_TSPARSER:
830+
caseOBJECT_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
CREATEVIEWpg_available_extension_versionsAS
163163
SELECTE.name,E.version, (X.extnameIS 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_extensionAS X
167167
ONE.name=X.extnameANDE.version=X.extversion;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp