Extension Management and File Structure in PostgreSQL

<application>pg_dump</application> is used, the <command>CREATE EXTENSION</command> command will be included in the dump, followed by the set of <command>GRANT</command> and <command>REVOKE</command> statements necessary to set the privileges on the objects to what they were at the time the dump was taken. </para> <para> <productname>PostgreSQL</productname> does not currently support extension scripts issuing <command>CREATE POLICY</command> or <command>SECURITY LABEL</command> statements. These are expected to be set after the extension has been created. All RLS policies and security labels on extension objects will be included in dumps created by <application>pg_dump</application>. </para> <para> The extension mechanism also has provisions for packaging modification scripts that adjust the definitions of the SQL objects contained in an extension. For example, if version 1.1 of an extension adds one function and changes the body of another function compared to 1.0, the extension author can provide an <firstterm>update script</firstterm> that makes just those two changes. The <command>ALTER EXTENSION UPDATE</command> command can then be used to apply these changes and track which version of the extension is actually installed in a given database. </para> <para> The kinds of SQL objects that can be members of an extension are shown in the description of <link linkend="sql-alterextension"><command>ALTER EXTENSION</command></link>. Notably, objects that are database-cluster-wide, such as databases, roles, and tablespaces, cannot be extension members since an extension is only known within one database. (Although an extension script is not prohibited from creating such objects, if it does so they will not be tracked as part of the extension.) Also notice that while a table can be a member of an extension, its subsidiary objects such as indexes are not directly considered members of the extension. Another important point is that schemas can belong to extensions, but not vice versa: an extension as such has an unqualified name and does not exist <quote>within</quote> any schema. The extension's member objects, however, will belong to schemas whenever appropriate for their object types. It may or may not be appropriate for an extension to own the schema(s) its member objects are within. </para> <para> If an extension's script creates any temporary objects (such as temp tables), those objects are treated as extension members for the remainder of the current session, but are automatically dropped at session end, as any temporary object would be. This is an exception to the rule that extension member objects cannot be dropped without dropping the whole extension. </para> <sect2 id="extend-extensions-files"> <title>Extension Files</title> <indexterm> <primary>control file</primary> </indexterm> <para> The <command>CREATE EXTENSION</command> command relies on a control file for each extension, which must be named the same as the extension with a suffix of <literal>.control</literal>, and must be placed in the installation's <literal>SHAREDIR/extension</literal> directory. There must also be at least one <acronym>SQL</acronym> script file, which follows the naming pattern <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.sql</literal> (for example, <literal>foo--1.0.sql</literal> for version <literal>1.0</literal> of extension <literal>foo</literal>). By default, the script file(s) are also placed in the <literal>SHAREDIR/extension</literal> directory; but the control file can specify a different directory for the script file(s). </para> <para> Additional locations for extension control files can be configured using the

This section details the management and file structure of extensions in PostgreSQL. It explains how pg_dump handles extensions, including CREATE EXTENSION commands and privilege statements. The text notes that PostgreSQL doesn't support CREATE POLICY or SECURITY LABEL statements in extension scripts, but these are included in dumps. It describes the use of update scripts for modifying existing extensions and the ALTER EXTENSION UPDATE command for applying changes. The passage also outlines what SQL objects can be part of an extension, emphasizing that database-cluster-wide objects cannot be extension members. It explains the relationship between extensions and schemas, and how temporary objects are handled within extensions. Finally, it details the file structure required for extensions, including the control file and SQL script files, and their placement in the PostgreSQL directory structure.