Permissions required for PostGIS

PostGIS is a widely popular spatial database extension for Postgres. It's also one of my favorite tools! A recent discussion on the People, Postgres, Data Discord server highlighted that the permissions required for various PostGIS operations were not clearly explained in the PostGIS documentation. As it turned out, I didn't know exactly what was required either. The basic PostGIS install page provides resources for installing the binary on the server and the basic CREATE EXTENSION commands, but does not explain permissions required.

This post explores the permissions required for three types of PostGIS interactions:

• Install/Create PostGIS
• Use PostGIS
• Load data from pg_dump

Database and Users

I am using Postgres installed on my laptop for these tests, Postgres 13.5 and PostGIS 3.1. I created an empty database named postgis_perms and check the \du slash command in psql to see the current roles. This instance has my my ryanlambert role, a superuser, and the default postgres role. The postgres role is not used in this post outside of this example.

([local] 🐘) ryanlambert@postgis_perms=# \du
                                     List of roles
┌─────────────┬────────────────────────────────────────────────────────────┬───────────┐
│  Role name  │                         Attributes                         │ Member of │
╞═════════════╪════════════════════════════════════════════════════════════╪═══════════╡
│ postgres    │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {}        │
│ ryanlambert │ Superuser, Create role, Create DB                          │ {}        │
└─────────────┴────────────────────────────────────────────────────────────┴───────────┘

For testing permissions I created a test role named user1 and immediately switch to the new role.

CREATE ROLE user1;
SET ROLE user1;

Use the current_user value to ensure the active role was updated to verify successful switching.

SELECT current_user;

┌──────────────┐
│ current_user │
╞══════════════╡
│ user1        │
└──────────────┘

The SET ROLE <name>;, RESET ROLE; and SELECT current_user; queries are invaluable when testing Postgres permissions! See my post Postgres Permissions and Materialized Views for more examples of testing permissions in Postgres.

Install and Create PostGIS

Making the PostGIS extension available in your databases requires two steps. The first step is to install the PostGIS extension binaries on the server, the second step is to run CREATE EXTENSION postgis;. Installing the binaries requires superuser permissions at the OS level on the server running Postgres. On Ubuntu, this can done with sudo apt install, note that the Postgres version in this command must match your installed Postgres version.

sudo apt install postgresql-14-postgis-3

Users of a managed Postgres as a Service, such as those hosted by Digital Ocean or AWS, can skip this step. Either your service provider has already installed PostGIS, or it is not available.

Once the PostGIS binaries are installed on the server and available for Postgres, the PostGIS extension can be created in the database where it is needed. Attempting to create the extension as user1 results in a permission denied error with the hint that superuser is required.

SET ROLE user1;
CREATE EXTENSION postgis;
ERROR:  permission denied to create extension "postgis"
HINT:  Must be superuser to create this extension.

To install PostGIS and continue testing, I reset role to my superuser, create the extension, then switch back to user1.

RESET ROLE;
CREATE EXTENSION postgis;
SET ROLE user1;

Using PostGIS

With PostGIS created in the database, the user1 role can utilize PostGIS functions. This is due to the default permissions in the public schema to the public role. PostGIS functions are installed in the public schema by default. More on that in a moment, but more importantly this query works. It uses four (4) PostGIS functions and the use of the ST_SetSRID() and ST_Transform() functions ensures the query is able to use the public.spatial_ref_sys table that PostGIS maintains.

SELECT current_user, x AS id,
        ST_AsText(
            ST_Transform(
                ST_SetSRID(
                    ST_MakePoint(x, x * random())
                    , 4326)
                , 3857)
            ) AS geom
    FROM generate_series(1, 5) x
;

┌────┬─────────────────────────────┐
│ id │            geom             │
╞════╪═════════════════════════════╡
│  1 │ POINT(1 0.115176858967814)  │
│  2 │ POINT(2 0.670587438834097)  │
│  3 │ POINT(3 0.223317901422014)  │
│  4 │ POINT(4 0.402885315270865)  │
│  5 │ POINT(5 2.656389346066561)  │
└────┴─────────────────────────────┘

Revoke default permissions

The above commands show that user1 was able to use PostGIS functions to create some points. This works by default thanks to Postgres' liberal default permissions to the public schema. These default permissions have been identified as a security concern via CVE-2018-1058. The CIS Benchmarks for Postgres for Postgres 14 recommends reducing these defaults under Section 4.4 "Ensure excessive DML privileges are revoked" by revoking CREATE permissions from the public schema.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

There are other more restrictive approaches that take away even more of the default permissions.

REVOKE ALL ON SCHEMA public FROM PUBLIC;

For this post, I used this more restrictive REVOKE ALL so I can find which permissions are required for PostGIS.

It's possible the default permissions could change in Postgres 15.

With the above change, the user1 role can no longer use the PostGIS functions. It's worth noting that if you run the same query again we get an unexpected error message stating function not found error, instead of a permission denied error!

SET ROLE user1;
SELECT current_user, x AS id,
        ST_AsText(
            ST_Transform(
                ST_SetSRID(
                    ST_MakePoint(x, x * random())
                    , 4326)
                , 3857)
            ) AS geom
    FROM generate_series(1, 5) x
;

ERROR:  function st_makepoint(integer, double precision) does not exist
LINE 2: SELECT x AS id, ST_MakePoint(x, x * random()) AS geom
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

If I saw this in the wild, my first instinct would be to check the search_path (not the problem), and even try schema-qualifying the PostGIS functions as done in this next query.

SELECT current_user, x AS id,
        public.ST_AsText(
            public.ST_Transform(
                public.ST_SetSRID(
                    public.ST_MakePoint(x, x * random())
                    , 4326)
                , 3857)
            ) AS geom
    FROM generate_series(1, 5) x
;

By forcing Postgres to look in the public schema for those functions, the error message now provides the true error: permission denied!

ERROR:  permission denied for schema public
LINE 5:    public.ST_MakePoint(x, x * random())

Grant necessary permissions

Having found what we need to do to restrict a user from using PostGIS functions, let's give that access back explicitly.

For a role to access anything in the public schema, USAGE on the schema must also be granted. The core functionality provided by PostGIS is through its functions, so granting access to EXECUTE all functions in the public schema is easy enough. Beware - other functions in the public schema not related to PostGIS are affected by this! Last, the SELECT permission is given to the public.spatial_ref_sys table. The spatial_ref_sys table contains helpful information about SRIDs that analysts can often benefit from. So while not technically required, I see no reason to restrict SELECT access from spatial_ref_sys. Querying that table is part of normal usage of the PostGIS extension.

GRANT USAGE ON SCHEMA public TO user1;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user1;
GRANT SELECT ON TABLE public.spatial_ref_sys TO user1;

See the srid-bbox project to add a view that enhances the basic SRID data available in spatial_ref_sys! It adds an easy-to-use units column and known bounding boxes. These details are valuable when identifying which SRID serves the local area for your data with the units your spatial project needs.

With explicit permissions added for PostGIS functionality, running the query one more time as user1 works again.

SET ROLE user1;
SELECT current_user, x AS id,
        ST_AsText(
            ST_Transform(
                ST_SetSRID(
                    ST_MakePoint(x, x * random())
                    , 4326)
                , 3857)
            ) AS geom
    FROM generate_series(1, 5) x
;

┌──────────────┬────┬─────────────────────────────────────────────┐
│ current_user │ id │                    geom                     │
╞══════════════╪════╪═════════════════════════════════════════════╡
│ user1        │  1 │ POINT(111319.49079327357 76929.02881079927) │
│ user1        │  2 │ POINT(222638.98158654713 157372.0414562291) │
│ user1        │  3 │ POINT(333958.4723798207 191802.4833575314)  │
│ user1        │  4 │ POINT(445277.96317309426 171550.8087124306) │
│ user1        │  5 │ POINT(556597.4539663679 389201.7125835131)  │
└──────────────┴────┴─────────────────────────────────────────────┘

Loading data from pg_dump

The final use case for PostGIS permissions is a bit of an edge case, most likely to affect developers that are not their own DBA. The scenario is pg_dump --data-only (and/or other pg_dump options) was used to extract data from a PostGIS enabled database. The resulting .sql file is then loaded into another PostGIS enabled database (dev, QA, whatever) by a non-superuser, non-privileged role in that Postgres database. The other assumption is that the user in question has sufficient privileges required to load the data itself (not the PostGIS stuff).

Attempting this scenario fails with a permission denied error.

psql -U user1 -d new_db -f your-dump-file.sql

...
ERROR:  permission denied for table spatial_ref_sys

This happens because the output from pg_dump includes a COPY public.spatial_ref_sys command like the following. Even though there is no data loaded, this command requires INSERT permissions.

COPY public.spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) FROM stdin;
\.

There are use cases that require custom SRIDs to be loaded which would result in actual records being included in the COPY. Those use cases are on the rare side, I have never seen one in production databases that I'm aware of.

Granting the INSERT permission on the public.spatial_ref_sys table will resolve this error.

GRANT INSERT ON TABLE public.spatial_ref_sys TO user1;

If you do not want to grant the INSERT permission to this table there are a few other approaches with pg_dump to work around this issue.

• Use --exclude-table=spatial_ref_sys
• Define --table=your_table_with_data
• Remove the COPY in question from the .sql (raw format only)

Warning! Do not change PostGIS maintained records in public.spatial_ref_sys! Per the PostGIS documentation: "only srid records not packaged with PostGIS are backed up when the database is backed up so don't go around changing srids we package and expect your changes to be there."

TLDR; Permissions for PostGIS

The following table outlines the permissions required for the various scenarios explored above. The "Use PostGIS" permissions are only required when the default permissions to the public schema have been removed. It is possible there are additional edge case scenarios related to these permissions that I have not yet encountered.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!