CODEWITHSUNDEEP
postgresql-9.1postgis
DanCat
DanCat

Reputation: 2734

postgres create extension postgis error

In the process of creating the postgresql database "map" which uses the postgis extension, via CREATE EXTENSION postgis;, user "mapmaker" gets the following error:

permission denied to create extension "postgis" 
HINT:  Must be superuser to create this extension.

But user "mapmaker" is the database owner as specified by sudo -u postgres psql via the command: 

CREATE DATABASE map OWNER mapmaker; 
GRANT ALL PRIVILEGES ON DATABASE map TO mapmaker;

Once mapmaker is a superuser at the user level I no longer receive the error and the extension is created so I understand all I have to do is adjust the permission of mapmaker via the postgres user to superuser but I am interested in knowing why this is the case if the mapmaker was granted all privileges on the database map? Are extensions treated differently? In order to use extensions does a user have to be a user level superuser or can the permissions be allocated on a database level?

I did see cannot create extension without superuser role but the answer to the question did not explain why and, unfortunately, I do not have enough points to comment, hence the question.

PostgreSQL 9.1.9 PostGIS 2.0.3

Upvotes: 5

Views: 6059

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61506

In Packaging Related Objects into an Extension, the doc tells that an extension has a superuser parameter that, when set to true, indicates that only a superuser may install or upgrade the extension.

So that's the case of PostGIS, presumably because being implemented in the C language, there's no limit to what it can do to the entire cluster and data directory, not just one database. The superuser has authority over the entire cluster, which the owner of a single database does not have.

Just as the superuser privilege is required to create an individual function in the C language, it makes sense that the same rule would apply to the entire postgis extension for the same reason.

Upvotes: 6

Related Questions