While pg_dump must not be considered a backup tool (please use physical backups for this), it is widely used to dump and reload data. One of the main advantages is, that you can do this across major versions of PostgreSQL. Other advantages are the plenty of options you have for dumping data out of a PostgreSQL database: Only the schema, only the data, only specific tables and much more. While pg_dump will also dump the statements to create the extensions which are present in the source database, there is something you need to be aware of when it comes to permissions and ownership.

As usual, we’ll start by creating a little test case:

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 u         |

We’ve created a new user with the login privilege and now we want to use this user to create an extension in the postgres database:

postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create extension pg_trgm;
ERROR:  permission denied to create extension "pg_trgm"
HINT:  Must have CREATE privilege on current database to create this extension.

The hint in the error message is pretty clear: For being able to do this we need the “create” privilege on the database, so:

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant create on database postgres to u;
GRANT
postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create extension pg_trgm;
CREATE EXTENSION

Using the corresponding meta command of psql, you’ll not see any ownership information of the extension just installed:

postgres=> \dx
                                    List of installed extensions
  Name   | Version |   Schema   |                            Description                            
---------+---------+------------+-------------------------------------------------------------------
 pg_trgm | 1.6     | public     | text similarity measurement and index searching based on trigrams
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

You can see this, however, in the pg_extension catalog table, which lists all the extensions currently installed in the database you’re connected to:

postgres=> select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13575 | plpgsql |       10 |           11 | f              | 1.0        |           | 
 16390 | pg_trgm |    16388 |         2200 | t              | 1.6        |           | 
(2 rows)

Looking at this, it becomes clear that the “plgsql” extension (which was already there when we started) does not have the same owner as the “pg_trgm” extension we’ve just installed. We can easily get the names of the those users (or roles) by joining pg_extension to pg_user:

postgres=> select e.extname, u.usename 
             from pg_extension e 
             join pg_user u on e.extowner = u.usesysid;
 extname | usename  
---------+----------
 plpgsql | postgres
 pg_trgm | u
(2 rows)

Not a big surprise: The “plgsql” extension is owned by the postgres superuser, and the “pg_trgm” extension is owned by our “u” user. Let’s dump this database, drop the extension we’ve installed and the reload the dump and the verify the ownership of the extension:

postgres=> \! pg_dump > a.dmp
postgres=> drop extension pg_trgm;
DROP EXTENSION
postgres=> \! psql < a.dmp
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
postgres=> select e.extname, u.usename from pg_extension e join pg_user u on e.extowner = u.usesysid;
 extname | usename  
---------+----------
 plpgsql | postgres
 pg_trgm | postgres
(2 rows)

Now we have the surprise: The extension is not anymore owned by “u”, but by “postgres” because we’ve loaded the dump with the postgres superuser. This is a known behavior/limitation of pg_dump, and if you look into the dump file it becomes clear why it is that way:

postgres=> \! cat a.dmp
--
-- PostgreSQL database dump
--

-- Dumped from database version 18devel
-- Dumped by pg_dump version 18devel

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- PostgreSQL database dump complete
--

There is nothing which attempts to change the ownership of the extension (and all the objects it comes with), or to create the extension with a specific owner. Loading the dump with the “u” user will result in the correct permissions of the extension:

postgres=> drop extension pg_trgm;
ERROR:  must be owner of extension pg_trgm
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop extension pg_trgm;
DROP EXTENSION
postgres=# \! psql -U u postgres < a.dmp
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
postgres=# select e.extname, u.usename from pg_extension e join pg_user u on e.extowner = u.usesysid;
 extname | usename  
---------+----------
 plpgsql | postgres
 pg_trgm | u
(2 rows)

This is not a big deal if you are aware of it, but this might be a surprise if you aren’t.

Post Views: 230
Thumbnail [60x60]
by
Daniel Westermann