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;
andSELECT 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-useunits
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.
PostGIS operation | Permissions Required |
---|---|
Install PostGIS Binary | OS superuser |
CREATE EXTENSION postgis; |
Postgres superuser |
Use PostGIS |
GRANT USAGE ON SCHEMA public TO role_name;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO role_name;
GRANT SELECT ON TABLE public.spatial_ref_sys TO role_name;
|
Load pg_dump extract
|
GRANT INSERT ON TABLE public.spatial_ref_sys TO role_name;
|
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!