Question

How To Set Database-Wide Parameters In Managed PostgreSQL

Posted November 15, 2021 65 views
DigitalOcean Managed PostgreSQL Database

I would like to enable some features in a PostgreSQL database. I am able to create a database cluster, and inside that cluster I can create a user newuser and a database newdb. While using psql connected to the new database as the new user, I can run commands like CREATE EXTENSION postgis; and SET postgis.gdal_enabled_drivers = 'GTiff';. However, I cannot set that parameter as a database-wide parameter as either the new user or doadmin with the command ALTER DATABASE newdb SET postgis.gdal_enabled_drivers = 'GTiff'; as I get the following error: ERROR: permission denied to set parameter "postgis.gdal_enabled_drivers".

doadmin is the owner of newdb, and we don’t get superuser access to these databases as far as I know.

How can I set this parameter?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
1 answer

Hello,

As this is a managed service your database user does not have admin privileges.

What I could suggest is getting in touch with the DigitalOcean support team and request the change on their side:

https://www.digitalocean.com/support/

Keep in mind that not all changes would be possible.

In case that you want to have a full access to manage your PostgreSQL instance, you could install PostgreSQL on a Droplet as per the instructions here:

https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart

Hope that helps!
- Bobby.

by Mark Drake
PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. This quickstart guide demonstrates how to install Postgres on an Ubuntu 20.04 server. It also provides instructions for general database administration.