From 8d3b421f5f7b955e7ac7d156aa74ee6a6fe4e9f6 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 4 Mar 2011 16:08:24 -0500 Subject: [PATCH] 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. --- doc/src/sgml/catalogs.sgml | 14 +- doc/src/sgml/extend.sgml | 13 ++ doc/src/sgml/ref/alter_extension.sgml | 4 +- doc/src/sgml/ref/create_extension.sgml | 21 ++- doc/src/sgml/ref/drop_extension.sgml | 2 +- src/backend/catalog/aclchk.c | 47 ++++++ src/backend/catalog/objectaddress.c | 151 +++++++++++++++++ src/backend/catalog/system_views.sql | 2 +- src/backend/commands/comment.c | 222 +++---------------------- src/backend/commands/extension.c | 185 ++++++++++++--------- src/backend/nodes/copyfuncs.c | 1 + src/backend/nodes/equalfuncs.c | 1 + src/backend/parser/gram.y | 9 + src/bin/pg_dump/pg_dump.c | 16 +- src/include/catalog/catversion.h | 2 +- src/include/catalog/objectaddress.h | 6 +- src/include/catalog/pg_proc.h | 2 +- src/include/nodes/parsenodes.h | 1 + src/include/utils/acl.h | 2 + src/test/regress/expected/rules.out | 2 +- 20 files changed, 410 insertions(+), 293 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 88eaca0bea..b14fb72cab 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6460,8 +6460,8 @@ The pg_available_extensions view lists the - extensions that are available for installation. This view can only - be read by superusers. See also the + extensions that are available for installation. + See also the pg_extension catalog, which shows the extensions currently installed. @@ -6522,8 +6522,8 @@ The pg_available_extension_versions view lists the - specific extension versions that are available for installation. This view - can only be read by superusers. See also the pg_extension catalog, which shows the extensions currently installed. @@ -6560,6 +6560,12 @@ installed + + superuser + bool + True if only superusers are allowed to install this extension + + relocatable bool diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 63a917d3c9..de0dc2663b 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -463,6 +463,19 @@ + + superuser (boolean) + + + If this parameter is true (which is the default), + only superusers can create the extension or update it to a new + version. If it is set to false, just the privileges + required to execute the commands in the installation or update script + are required. + + + + relocatable (boolean) diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index d12aee251b..e8c9608666 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -114,7 +114,9 @@ ALTER EXTENSION extension_name DROP - Only superusers can execute ALTER EXTENSION. + You must own the extension to use ALTER EXTENSION. + The ADD/DROP forms require ownership of the + added/dropped object as well. diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml index d3b5fb009b..818bc0c4b4 100644 --- a/doc/src/sgml/ref/create_extension.sgml +++ b/doc/src/sgml/ref/create_extension.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE EXTENSION extension_name +CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema ] [ VERSION version ] [ FROM old_version ] @@ -51,7 +51,12 @@ CREATE EXTENSION extension_name - Only superusers can execute CREATE EXTENSION. + Loading an extension requires the same privileges that would be + required to create its component objects. For most extensions this + means superuser or database owner privileges are needed. + The user who runs CREATE EXTENSION becomes the + owner of the extension for purposes of later privilege checks, as well + as the owner of any objects created by the extension's script. @@ -60,6 +65,18 @@ CREATE EXTENSION extension_name Parameters + + IF NOT EXISTS + + + Do not throw an error if an extension with the same name already + exists. A notice is issued in this case. Note that there is no + guarantee that the existing extension is anything like the one that + would have been created. + + + + extension_name diff --git a/doc/src/sgml/ref/drop_extension.sgml b/doc/src/sgml/ref/drop_extension.sgml index 1e09ec4c7a..979a6ebc15 100644 --- a/doc/src/sgml/ref/drop_extension.sgml +++ b/doc/src/sgml/ref/drop_extension.sgml @@ -34,7 +34,7 @@ DROP EXTENSION [ IF EXISTS ] extension_name - An extension can only be dropped by a superuser. + You must own the extension to use DROP EXTENSION. diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index db1d092796..a98f918a23 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -29,6 +29,7 @@ #include "catalog/pg_conversion.h" #include "catalog/pg_database.h" #include "catalog/pg_default_acl.h" +#include "catalog/pg_extension.h" #include "catalog/pg_foreign_data_wrapper.h" #include "catalog/pg_foreign_server.h" #include "catalog/pg_language.h" @@ -3148,6 +3149,8 @@ static const char *const no_priv_msg[MAX_ACL_KIND] = gettext_noop("permission denied for foreign server %s"), /* ACL_KIND_FOREIGN_TABLE */ gettext_noop("permission denied for foreign table %s"), + /* ACL_KIND_EXTENSION */ + gettext_noop("permission denied for extension %s"), }; static const char *const not_owner_msg[MAX_ACL_KIND] = @@ -3192,6 +3195,8 @@ static const char *const not_owner_msg[MAX_ACL_KIND] = gettext_noop("must be owner of foreign server %s"), /* ACL_KIND_FOREIGN_TABLE */ gettext_noop("must be owner of foreign table %s"), + /* ACL_KIND_EXTENSION */ + gettext_noop("must be owner of extension %s"), }; @@ -4688,6 +4693,48 @@ pg_conversion_ownercheck(Oid conv_oid, Oid roleid) return has_privs_of_role(roleid, ownerId); } +/* + * Ownership check for an extension (specified by OID). + */ +bool +pg_extension_ownercheck(Oid ext_oid, Oid roleid) +{ + Relation pg_extension; + ScanKeyData entry[1]; + SysScanDesc scan; + HeapTuple tuple; + Oid ownerId; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + /* There's no syscache for pg_extension, so do it the hard way */ + pg_extension = heap_open(ExtensionRelationId, AccessShareLock); + + ScanKeyInit(&entry[0], + ObjectIdAttributeNumber, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(ext_oid)); + + scan = systable_beginscan(pg_extension, + ExtensionOidIndexId, true, + SnapshotNow, 1, entry); + + tuple = systable_getnext(scan); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("extension with OID %u does not exist", ext_oid))); + + ownerId = ((Form_pg_extension) GETSTRUCT(tuple))->extowner; + + systable_endscan(scan); + heap_close(pg_extension, AccessShareLock); + + return has_privs_of_role(roleid, ownerId); +} + /* * Fetch pg_default_acl entry for given role, namespace and object type * (object type must be given in pg_default_acl's encoding). diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index aeb07710e8..b8b89ab7c1 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -52,6 +52,8 @@ #include "commands/proclang.h" #include "commands/tablespace.h" #include "commands/trigger.h" +#include "libpq/be-fsstubs.h" +#include "miscadmin.h" #include "nodes/makefuncs.h" #include "parser/parse_func.h" #include "parser/parse_oper.h" @@ -78,6 +80,7 @@ static ObjectAddress get_object_address_opcf(ObjectType objtype, List *objname, List *objargs); static bool object_exists(ObjectAddress address); + /* * Translate an object name and arguments (as passed by the parser) to an * ObjectAddress. @@ -688,3 +691,151 @@ object_exists(ObjectAddress address) heap_close(rel, AccessShareLock); return found; } + + +/* + * Check ownership of an object previously identified by get_object_address. + */ +void +check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, + List *objname, List *objargs, Relation relation) +{ + switch (objtype) + { + case OBJECT_INDEX: + case OBJECT_SEQUENCE: + case OBJECT_TABLE: + case OBJECT_VIEW: + case OBJECT_FOREIGN_TABLE: + case OBJECT_COLUMN: + case OBJECT_RULE: + case OBJECT_TRIGGER: + case OBJECT_CONSTRAINT: + if (!pg_class_ownercheck(RelationGetRelid(relation), roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, + RelationGetRelationName(relation)); + break; + case OBJECT_DATABASE: + if (!pg_database_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE, + NameListToString(objname)); + break; + case OBJECT_TYPE: + case OBJECT_DOMAIN: + case OBJECT_ATTRIBUTE: + if (!pg_type_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE, + format_type_be(address.objectId)); + break; + case OBJECT_AGGREGATE: + case OBJECT_FUNCTION: + if (!pg_proc_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, + NameListToString(objname)); + break; + case OBJECT_OPERATOR: + if (!pg_oper_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPER, + NameListToString(objname)); + break; + case OBJECT_SCHEMA: + if (!pg_namespace_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE, + NameListToString(objname)); + break; + case OBJECT_COLLATION: + if (!pg_collation_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_COLLATION, + NameListToString(objname)); + break; + case OBJECT_CONVERSION: + if (!pg_conversion_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CONVERSION, + NameListToString(objname)); + break; + case OBJECT_EXTENSION: + if (!pg_extension_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_EXTENSION, + NameListToString(objname)); + break; + case OBJECT_FOREIGN_SERVER: + if (!pg_foreign_server_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_FOREIGN_SERVER, + NameListToString(objname)); + break; + case OBJECT_LANGUAGE: + if (!pg_language_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_LANGUAGE, + NameListToString(objname)); + break; + case OBJECT_OPCLASS: + if (!pg_opclass_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPCLASS, + NameListToString(objname)); + break; + case OBJECT_OPFAMILY: + if (!pg_opfamily_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPFAMILY, + NameListToString(objname)); + break; + case OBJECT_LARGEOBJECT: + if (!lo_compat_privileges && + !pg_largeobject_ownercheck(address.objectId, roleid)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be owner of large object %u", + address.objectId))); + break; + case OBJECT_CAST: + { + /* We can only check permissions on the source/target types */ + TypeName *sourcetype = (TypeName *) linitial(objname); + TypeName *targettype = (TypeName *) linitial(objargs); + Oid sourcetypeid = typenameTypeId(NULL, sourcetype); + Oid targettypeid = typenameTypeId(NULL, targettype); + + if (!pg_type_ownercheck(sourcetypeid, roleid) + && !pg_type_ownercheck(targettypeid, roleid)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be owner of type %s or type %s", + format_type_be(sourcetypeid), + format_type_be(targettypeid)))); + } + break; + case OBJECT_TABLESPACE: + if (!pg_tablespace_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TABLESPACE, + NameListToString(objname)); + break; + case OBJECT_ROLE: + if (!has_privs_of_role(roleid, address.objectId)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be member of role \"%s\"", + NameListToString(objname)))); + break; + case OBJECT_TSDICTIONARY: + if (!pg_ts_dict_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TSDICTIONARY, + NameListToString(objname)); + break; + case OBJECT_TSCONFIGURATION: + if (!pg_ts_config_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TSCONFIGURATION, + NameListToString(objname)); + break; + case OBJECT_FDW: + case OBJECT_TSPARSER: + case OBJECT_TSTEMPLATE: + /* We treat these object types as being owned by superusers */ + if (!superuser_arg(roleid)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser"))); + break; + default: + elog(ERROR, "unrecognized object type: %d", + (int) objtype); + } +} diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index d00c6f9cb7..c7f43afd81 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -161,7 +161,7 @@ CREATE VIEW pg_available_extensions AS CREATE VIEW pg_available_extension_versions AS SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed, - E.relocatable, E.schema, E.requires, E.comment + E.superuser, E.relocatable, E.schema, E.requires, E.comment FROM pg_available_extension_versions() AS E LEFT JOIN pg_extension AS X ON E.name = X.extname AND E.version = X.extversion; diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c index a0a561c144..3fbeefa018 100644 --- a/src/backend/commands/comment.c +++ b/src/backend/commands/comment.c @@ -22,25 +22,11 @@ #include "catalog/pg_shdescription.h" #include "commands/comment.h" #include "commands/dbcommands.h" -#include "libpq/be-fsstubs.h" #include "miscadmin.h" -#include "parser/parse_func.h" -#include "parser/parse_type.h" -#include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" -#include "utils/rel.h" #include "utils/tqual.h" -/* - * For most object types, the permissions-checking logic is simple enough - * that it makes sense to just include it in CommentObject(). However, a few - * object types require something more complex; for those, we define helper - * functions. - */ -static void CheckAttributeComment(Relation relation); -static void CheckCastComment(List *qualname, List *arguments); - /* * CommentObject -- @@ -76,143 +62,41 @@ CommentObject(CommentStmt *stmt) } /* - * Translate the parser representation which identifies this object into - * an ObjectAddress. get_object_address() will throw an error if the + * Translate the parser representation that identifies this object into + * an ObjectAddress. get_object_address() will throw an error if the * object does not exist, and will also acquire a lock on the target * to guard against concurrent DROP operations. */ address = get_object_address(stmt->objtype, stmt->objname, stmt->objargs, &relation, ShareUpdateExclusiveLock); - /* Privilege and integrity checks. */ + /* Require ownership of the target object. */ + check_object_ownership(GetUserId(), stmt->objtype, address, + stmt->objname, stmt->objargs, relation); + + /* Perform other integrity checks as needed. */ switch (stmt->objtype) { - case OBJECT_INDEX: - case OBJECT_SEQUENCE: - case OBJECT_TABLE: - case OBJECT_VIEW: - case OBJECT_FOREIGN_TABLE: - if (!pg_class_ownercheck(RelationGetRelid(relation), GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, - RelationGetRelationName(relation)); - break; case OBJECT_COLUMN: - CheckAttributeComment(relation); - break; - case OBJECT_DATABASE: - if (!pg_database_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE, - strVal(linitial(stmt->objname))); - break; - case OBJECT_TYPE: - case OBJECT_DOMAIN: - if (!pg_type_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE, - format_type_be(address.objectId)); - break; - case OBJECT_AGGREGATE: - case OBJECT_FUNCTION: - if (!pg_proc_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, - NameListToString(stmt->objname)); - break; - case OBJECT_OPERATOR: - if (!pg_oper_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPER, - NameListToString(stmt->objname)); - break; - case OBJECT_RULE: - case OBJECT_TRIGGER: - case OBJECT_CONSTRAINT: - if (!pg_class_ownercheck(RelationGetRelid(relation), GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, - RelationGetRelationName(relation)); - break; - case OBJECT_SCHEMA: - if (!pg_namespace_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE, - strVal(linitial(stmt->objname))); - break; - case OBJECT_COLLATION: - if (!pg_collation_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_COLLATION, - NameListToString(stmt->objname)); - break; - case OBJECT_CONVERSION: - if (!pg_conversion_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CONVERSION, - NameListToString(stmt->objname)); - break; - case OBJECT_LANGUAGE: - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to comment on procedural language"))); - break; - case OBJECT_EXTENSION: - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to comment on extension"))); - break; - case OBJECT_OPCLASS: - if (!pg_opclass_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPCLASS, - NameListToString(stmt->objname)); - break; - case OBJECT_OPFAMILY: - if (!pg_opfamily_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_OPFAMILY, - NameListToString(stmt->objname)); - break; - case OBJECT_LARGEOBJECT: - if (!lo_compat_privileges && - !pg_largeobject_ownercheck(address.objectId, GetUserId())) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be owner of large object %u", - address.objectId))); - break; - case OBJECT_CAST: - CheckCastComment(stmt->objname, stmt->objargs); - break; - case OBJECT_TABLESPACE: - if (!pg_tablespace_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TABLESPACE, - strVal(linitial(stmt->objname))); - break; - case OBJECT_ROLE: - if (!has_privs_of_role(GetUserId(), address.objectId)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be member of role \"%s\" to comment upon it", - strVal(linitial(stmt->objname))))); - break; - case OBJECT_TSPARSER: - if (!superuser()) + /* + * Allow comments only on columns of tables, views, composite + * types, and foreign tables (which are the only relkinds for + * which pg_dump will dump per-column comments). In particular we + * wish to disallow comments on index columns, because the naming + * of an index's columns may change across PG versions, so dumping + * per-column comments could create reload failures. + */ + if (relation->rd_rel->relkind != RELKIND_RELATION && + relation->rd_rel->relkind != RELKIND_VIEW && + relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && + relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to comment on text search parser"))); - break; - case OBJECT_TSDICTIONARY: - if (!pg_ts_dict_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TSDICTIONARY, - NameListToString(stmt->objname)); - break; - case OBJECT_TSTEMPLATE: - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to comment on text search template"))); - break; - case OBJECT_TSCONFIGURATION: - if (!pg_ts_config_ownercheck(address.objectId, GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TSCONFIGURATION, - NameListToString(stmt->objname)); + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table, view, composite type, or foreign table", + RelationGetRelationName(relation)))); break; default: - elog(ERROR, "unrecognized object type: %d", - (int) stmt->objtype); + break; } /* @@ -574,63 +458,3 @@ GetComment(Oid oid, Oid classoid, int32 subid) return comment; } - -/* - * Check whether the user is allowed to comment on an attribute of the - * specified relation. - */ -static void -CheckAttributeComment(Relation relation) -{ - if (!pg_class_ownercheck(RelationGetRelid(relation), GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, - RelationGetRelationName(relation)); - - /* - * Allow comments only on columns of tables, views, composite types, and - * foreign tables (which are the only relkinds for which pg_dump will dump - * per-column comments). In particular we wish to disallow comments on - * index columns, because the naming of an index's columns may change - * across PG versions, so dumping per-column comments could create reload - * failures. - */ - if (relation->rd_rel->relkind != RELKIND_RELATION && - relation->rd_rel->relkind != RELKIND_VIEW && - relation->rd_rel->relkind != RELKIND_COMPOSITE_TYPE && - relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table, view, composite type, or foreign table", - RelationGetRelationName(relation)))); -} - -/* - * Check whether the user is allowed to comment on the specified cast. - */ -static void -CheckCastComment(List *qualname, List *arguments) -{ - TypeName *sourcetype; - TypeName *targettype; - Oid sourcetypeid; - Oid targettypeid; - - Assert(list_length(qualname) == 1); - sourcetype = (TypeName *) linitial(qualname); - Assert(IsA(sourcetype, TypeName)); - Assert(list_length(arguments) == 1); - targettype = (TypeName *) linitial(arguments); - Assert(IsA(targettype, TypeName)); - - sourcetypeid = typenameTypeId(NULL, sourcetype); - targettypeid = typenameTypeId(NULL, targettype); - - /* Permission check */ - if (!pg_type_ownercheck(sourcetypeid, GetUserId()) - && !pg_type_ownercheck(targettypeid, GetUserId())) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be owner of type %s or type %s", - format_type_be(sourcetypeid), - format_type_be(targettypeid)))); -} diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c index 8c812c21e2..6ff30ddc0a 100644 --- a/src/backend/commands/extension.c +++ b/src/backend/commands/extension.c @@ -70,6 +70,7 @@ typedef struct ExtensionControlFile char *comment; /* comment, if any */ char *schema; /* target schema (allowed if !relocatable) */ bool relocatable; /* is ALTER EXTENSION SET SCHEMA supported? */ + bool superuser; /* must be superuser to install? */ int encoding; /* encoding of the script file, or -1 */ List *requires; /* names of prerequisite extensions */ } ExtensionControlFile; @@ -523,6 +524,14 @@ parse_extension_control_file(ExtensionControlFile *control, errmsg("parameter \"%s\" requires a Boolean value", item->name))); } + else if (strcmp(item->name, "superuser") == 0) + { + if (!parse_bool(item->value, &control->superuser)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("parameter \"%s\" requires a Boolean value", + item->name))); + } else if (strcmp(item->name, "encoding") == 0) { control->encoding = pg_valid_server_encoding(item->value); @@ -578,6 +587,7 @@ read_extension_control_file(const char *extname) control = (ExtensionControlFile *) palloc0(sizeof(ExtensionControlFile)); control->name = pstrdup(extname); control->relocatable = false; + control->superuser = true; control->encoding = -1; /* @@ -770,6 +780,27 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control, StringInfoData pathbuf; ListCell *lc; + /* + * Enforce superuser-ness if appropriate. We postpone this check until + * here so that the flag is correctly associated with the right script(s) + * if it's set in secondary control files. + */ + if (control->superuser && !superuser()) + { + if (from_version == NULL) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to create extension \"%s\"", + control->name), + errhint("Must be superuser to create this extension."))); + else + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to update extension \"%s\"", + control->name), + errhint("Must be superuser to update this extension."))); + } + filename = get_extension_script_filename(control, from_version, version); /* @@ -1157,37 +1188,43 @@ CreateExtension(CreateExtensionStmt *stmt) List *requiredExtensions; List *requiredSchemas; Oid extensionOid; + AclResult aclresult; ListCell *lc; - /* Must be super user */ - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to create extension \"%s\"", - stmt->extname), - errhint("Must be superuser to create an extension."))); - - /* - * We use global variables to track the extension being created, so we - * can create only one extension at the same time. - */ - if (creating_extension) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("nested CREATE EXTENSION is not supported"))); - /* Check extension name validity before any filesystem access */ check_valid_extension_name(stmt->extname); /* * Check for duplicate extension name. The unique index on * pg_extension.extname would catch this anyway, and serves as a backstop - * in case of race conditions; but this is a friendlier error message. + * in case of race conditions; but this is a friendlier error message, + * and besides we need a check to support IF NOT EXISTS. */ if (get_extension_oid(stmt->extname, true) != InvalidOid) + { + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("extension \"%s\" already exists, skipping", + stmt->extname))); + return; + } + else + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("extension \"%s\" already exists", + stmt->extname))); + } + + /* + * We use global variables to track the extension being created, so we + * can create only one extension at the same time. + */ + if (creating_extension) ereport(ERROR, - (errcode(ERRCODE_DUPLICATE_OBJECT), - errmsg("extension \"%s\" already exists", stmt->extname))); + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("nested CREATE EXTENSION is not supported"))); /* * Read the primary control file. Note we assume that it does not contain @@ -1356,9 +1393,13 @@ CreateExtension(CreateExtensionStmt *stmt) } /* - * If we didn't already know user is superuser, we would probably want - * to do pg_namespace_aclcheck(schemaOid, extowner, ACL_CREATE) here. + * Check we have creation rights in target namespace. Although strictly + * speaking the extension itself isn't in the schema, it will almost + * certainly want to create objects therein, so let's just check now. */ + aclresult = pg_namespace_aclcheck(schemaOid, extowner, ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_NAMESPACE, schemaName); /* * Look up the prerequisite extensions, and build lists of their OIDs @@ -1551,16 +1592,10 @@ RemoveExtensions(DropStmt *drop) continue; } - /* - * Permission check. For now, insist on superuser-ness; later we - * might want to relax that to being owner of the extension. - */ - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop extension \"%s\"", - extensionName), - errhint("Must be superuser to drop an extension."))); + /* Permission check: must own extension */ + if (!pg_extension_ownercheck(extensionId, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_EXTENSION, + extensionName); object.classId = ExtensionRelationId; object.objectId = extensionId; @@ -1634,11 +1669,6 @@ pg_available_extensions(PG_FUNCTION_ARGS) DIR *dir; struct dirent *de; - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser to list available extensions")))); - /* check to see if caller supports us returning a tuplestore */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, @@ -1748,11 +1778,6 @@ pg_available_extension_versions(PG_FUNCTION_ARGS) DIR *dir; struct dirent *de; - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser to list available extensions")))); - /* check to see if caller supports us returning a tuplestore */ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) ereport(ERROR, @@ -1845,8 +1870,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol, { ExtensionControlFile *control; char *vername; - Datum values[6]; - bool nulls[6]; + Datum values[7]; + bool nulls[7]; /* must be a .sql file ... */ if (!is_extension_script_filename(de->d_name)) @@ -1879,17 +1904,19 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol, CStringGetDatum(control->name)); /* version */ values[1] = CStringGetTextDatum(vername); + /* superuser */ + values[2] = BoolGetDatum(control->superuser); /* relocatable */ - values[2] = BoolGetDatum(control->relocatable); + values[3] = BoolGetDatum(control->relocatable); /* schema */ if (control->schema == NULL) - nulls[3] = true; + nulls[4] = true; else - values[3] = DirectFunctionCall1(namein, + values[4] = DirectFunctionCall1(namein, CStringGetDatum(control->schema)); /* requires */ if (control->requires == NIL) - nulls[4] = true; + nulls[5] = true; else { Datum *datums; @@ -1910,13 +1937,13 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol, a = construct_array(datums, ndatums, NAMEOID, NAMEDATALEN, false, 'c'); - values[4] = PointerGetDatum(a); + values[5] = PointerGetDatum(a); } /* comment */ if (control->comment == NULL) - nulls[5] = true; + nulls[6] = true; else - values[5] = CStringGetTextDatum(control->comment); + values[6] = CStringGetTextDatum(control->comment); tuplestore_putvalues(tupstore, tupdesc, values, nulls); } @@ -1941,11 +1968,6 @@ pg_extension_update_paths(PG_FUNCTION_ARGS) ExtensionControlFile *control; ListCell *lc1; - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser to list extension update paths")))); - /* Check extension name validity before any filesystem access */ check_valid_extension_name(NameStr(*extname)); @@ -2204,6 +2226,7 @@ AlterExtensionNamespace(List *names, const char *newschema) Oid extensionOid; Oid nspOid; Oid oldNspOid = InvalidOid; + AclResult aclresult; Relation extRel; ScanKeyData key[2]; SysScanDesc extScan; @@ -2223,11 +2246,18 @@ AlterExtensionNamespace(List *names, const char *newschema) nspOid = LookupCreationNamespace(newschema); - /* this might later become an ownership test */ - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser to use ALTER EXTENSION")))); + /* + * Permission check: must own extension. Note that we don't bother to + * check ownership of the individual member objects ... + */ + if (!pg_extension_ownercheck(extensionOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_EXTENSION, + extensionName); + + /* Permission check: must have creation rights in target namespace */ + aclresult = pg_namespace_aclcheck(nspOid, GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_NAMESPACE, newschema); /* Locate the pg_extension tuple */ extRel = heap_open(ExtensionRelationId, RowExclusiveLock); @@ -2368,15 +2398,6 @@ ExecAlterExtensionStmt(AlterExtensionStmt *stmt) bool isnull; ListCell *lc; - /* - * For now, insist on superuser privilege. Later we might want to - * relax this to ownership of the extension. - */ - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser to use ALTER EXTENSION")))); - /* * We use global variables to track the extension being created, so we * can create/update only one extension at the same time. @@ -2422,6 +2443,11 @@ ExecAlterExtensionStmt(AlterExtensionStmt *stmt) heap_close(extRel, AccessShareLock); + /* Permission check: must own extension */ + if (!pg_extension_ownercheck(extensionOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_EXTENSION, + stmt->extname); + /* * Read the primary control file. Note we assume that it does not contain * any non-ASCII data, so there is no need to worry about encoding at this @@ -2658,20 +2684,15 @@ ExecAlterExtensionContentsStmt(AlterExtensionContentsStmt *stmt) Relation relation; Oid oldExtension; - /* - * For now, insist on superuser privilege. Later we might want to - * relax this to ownership of the target object and the extension. - */ - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - (errmsg("must be superuser to use ALTER EXTENSION")))); - - /* Do this next to fail on nonexistent extension */ extension.classId = ExtensionRelationId; extension.objectId = get_extension_oid(stmt->extname, false); extension.objectSubId = 0; + /* Permission check: must own extension */ + if (!pg_extension_ownercheck(extension.objectId, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_EXTENSION, + stmt->extname); + /* * Translate the parser representation that identifies the object into * an ObjectAddress. get_object_address() will throw an error if the @@ -2681,6 +2702,10 @@ ExecAlterExtensionContentsStmt(AlterExtensionContentsStmt *stmt) object = get_object_address(stmt->objtype, stmt->objname, stmt->objargs, &relation, ShareUpdateExclusiveLock); + /* Permission check: must own target object, too */ + check_object_ownership(GetUserId(), stmt->objtype, object, + stmt->objname, stmt->objargs, relation); + /* * Check existing extension membership. */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 7a1bc1562f..86a16783f7 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3289,6 +3289,7 @@ _copyCreateExtensionStmt(CreateExtensionStmt *from) CreateExtensionStmt *newnode = makeNode(CreateExtensionStmt); COPY_STRING_FIELD(extname); + COPY_SCALAR_FIELD(if_not_exists); COPY_NODE_FIELD(options); return newnode; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index d43c51e275..c234416cf5 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1650,6 +1650,7 @@ static bool _equalCreateExtensionStmt(CreateExtensionStmt *a, CreateExtensionStmt *b) { COMPARE_STRING_FIELD(extname); + COMPARE_SCALAR_FIELD(if_not_exists); COMPARE_NODE_FIELD(options); return true; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index ee4dbd3c8f..d32e480357 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -3240,9 +3240,18 @@ CreateExtensionStmt: CREATE EXTENSION name opt_with create_extension_opt_list { CreateExtensionStmt *n = makeNode(CreateExtensionStmt); n->extname = $3; + n->if_not_exists = false; n->options = $5; $$ = (Node *) n; } + | CREATE EXTENSION IF_P NOT EXISTS name opt_with create_extension_opt_list + { + CreateExtensionStmt *n = makeNode(CreateExtensionStmt); + n->extname = $6; + n->if_not_exists = true; + n->options = $8; + $$ = (Node *) n; + } ; create_extension_opt_list: diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 7c0c64cc7a..ea1818b1d5 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -45,6 +45,7 @@ #include "access/attnum.h" #include "access/sysattr.h" +#include "access/transam.h" #include "catalog/pg_cast.h" #include "catalog/pg_class.h" #include "catalog/pg_default_acl.h" @@ -7041,6 +7042,19 @@ dumpExtension(Archive *fout, ExtensionInfo *extinfo) if (!extinfo->dobj.dump || dataOnly) return; + /* + * In a regular dump, we use IF NOT EXISTS so that there isn't a problem + * if the extension already exists in the target database; this is + * essential for installed-by-default extensions such as plpgsql. + * + * In binary-upgrade mode, that doesn't work well, so instead we skip + * extensions with OIDs less than FirstNormalObjectId; those were + * presumably installed by initdb, and we assume they'll exist in the + * target installation too. + */ + if (binary_upgrade && extinfo->dobj.catId.oid < (Oid) FirstNormalObjectId) + return; + q = createPQExpBuffer(); delq = createPQExpBuffer(); labelq = createPQExpBuffer(); @@ -7051,7 +7065,7 @@ dumpExtension(Archive *fout, ExtensionInfo *extinfo) if (!binary_upgrade) { - appendPQExpBuffer(q, "CREATE EXTENSION %s WITH SCHEMA %s;\n", + appendPQExpBuffer(q, "CREATE EXTENSION IF NOT EXISTS %s WITH SCHEMA %s;\n", qextname, fmtId(extinfo->namespace)); } else diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 7efe477ab0..fe7ccf4ba0 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201103032 +#define CATALOG_VERSION_NO 201103041 #endif diff --git a/src/include/catalog/objectaddress.h b/src/include/catalog/objectaddress.h index 36d18ce0bc..109a8a3ef5 100644 --- a/src/include/catalog/objectaddress.h +++ b/src/include/catalog/objectaddress.h @@ -27,7 +27,11 @@ typedef struct ObjectAddress int32 objectSubId; /* Subitem within object (eg column), or 0 */ } ObjectAddress; -ObjectAddress get_object_address(ObjectType objtype, List *objname, +extern ObjectAddress get_object_address(ObjectType objtype, List *objname, List *objargs, Relation *relp, LOCKMODE lockmode); +extern void check_object_ownership(Oid roleid, + ObjectType objtype, ObjectAddress address, + List *objname, List *objargs, Relation relation); + #endif /* PARSE_OBJECT_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index bec45e1275..96a463398c 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4282,7 +4282,7 @@ DESCR("less-equal-greater"); /* Extensions */ DATA(insert OID = 3082 ( pg_available_extensions PGNSP PGUID 12 10 100 0 f f f t t s 0 0 2249 "" "{19,25,25}" "{o,o,o}" "{name,default_version,comment}" _null_ pg_available_extensions _null_ _null_ _null_ )); DESCR("list available extensions"); -DATA(insert OID = 3083 ( pg_available_extension_versions PGNSP PGUID 12 10 100 0 f f f t t s 0 0 2249 "" "{19,25,16,19,1003,25}" "{o,o,o,o,o,o}" "{name,version,relocatable,schema,requires,comment}" _null_ pg_available_extension_versions _null_ _null_ _null_ )); +DATA(insert OID = 3083 ( pg_available_extension_versions PGNSP PGUID 12 10 100 0 f f f t t s 0 0 2249 "" "{19,25,16,16,19,1003,25}" "{o,o,o,o,o,o,o}" "{name,version,superuser,relocatable,schema,requires,comment}" _null_ pg_available_extension_versions _null_ _null_ _null_ )); DESCR("list available extension versions"); DATA(insert OID = 3084 ( pg_extension_update_paths PGNSP PGUID 12 10 100 0 f f f t t s 1 0 2249 "19" "{19,25,25,25}" "{i,o,o,o}" "{name,source,target,path}" _null_ pg_extension_update_paths _null_ _null_ _null_ )); DESCR("list an extension's version update paths"); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 824403c69b..287e9f523f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1557,6 +1557,7 @@ typedef struct CreateExtensionStmt { NodeTag type; char *extname; + bool if_not_exists; /* just do nothing if it already exists? */ List *options; /* List of DefElem nodes */ } CreateExtensionStmt; diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index 1e9cf7fbed..c0f7b64d80 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -196,6 +196,7 @@ typedef enum AclObjectKind ACL_KIND_FDW, /* pg_foreign_data_wrapper */ ACL_KIND_FOREIGN_SERVER, /* pg_foreign_server */ ACL_KIND_FOREIGN_TABLE, /* pg_foreign_table */ + ACL_KIND_EXTENSION, /* pg_extension */ MAX_ACL_KIND /* MUST BE LAST */ } AclObjectKind; @@ -315,5 +316,6 @@ extern bool pg_conversion_ownercheck(Oid conv_oid, Oid roleid); extern bool pg_ts_dict_ownercheck(Oid dict_oid, Oid roleid); extern bool pg_ts_config_ownercheck(Oid cfg_oid, Oid roleid); extern bool pg_foreign_server_ownercheck(Oid srv_oid, Oid roleid); +extern bool pg_extension_ownercheck(Oid ext_oid, Oid roleid); #endif /* ACL_H */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 29fd474011..02043ab42c 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1279,7 +1279,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem viewname | definition ---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); - pg_available_extension_versions | SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, e.relocatable, e.schema, e.requires, e.comment FROM (pg_available_extension_versions() e(name, version, relocatable, schema, requires, comment) LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); + pg_available_extension_versions | SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, e.superuser, e.relocatable, e.schema, e.requires, e.comment FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); pg_available_extensions | SELECT e.name, e.default_version, x.extversion AS installed_version, e.comment FROM (pg_available_extensions() e(name, default_version, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname))); pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); -- 2.40.0