How to Install and Manage PostGIS with a Non-Superuser Role

Prerequisite: PostgreSQL Version

This guide assumes you are using PostgreSQL version 14 or later, which supports the necessary commands for PostGIS installation and management. Ensure your PostgreSQL server is up-to-date before proceeding.

This guide ensures that PostGIS is installed and configured properly for a specific user, such as administrator, while avoiding common issues.

1. Ensure Superuser Access

sudo -i -u postgres
psql --dbname=financethat

2. Create a Role for PostGIS Management

CREATE ROLE administrator WITH LOGIN PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE financethat TO administrator;

3. Install PostGIS

To install PostGIS on Ubuntu, first ensure you have the required PostgreSQL version installed. Then, use the following commands:

sudo apt update
sudo apt install postgis postgresql-14-postgis-3

Replace 14 with your PostgreSQL version if different. After installation, enable PostGIS in your database as shown below.

To install PostGIS on macOS, you can use brew (Homebrew package manager). Follow these steps:

brew update
brew install postgresql postgis

After installation, start the PostgreSQL server and enable PostGIS in your database as shown below.

CREATE EXTENSION postgis;

Verify the installation:

SELECT PostGIS_Full_Version();

4. Transfer Ownership of PostGIS to administrator

ALTER EXTENSION postgis OWNER TO administrator;
ALTER SCHEMA public OWNER TO administrator;

5. Grant Additional Permissions (if needed)

If other users need to access PostGIS functionality:

GRANT USAGE ON SCHEMA public TO other_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO other_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO other_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO other_user;

6. Restore a Database Dump with PostGIS

If restoring from an AWS RDS or similar environment:

  1. Check for rdsadmin references in the dump file:
    2. gunzip -c your_dump.sql.gz | grep -i rdsadmin
  2. Remove or replace rdsadmin references:
    3. REVOKE ALL ON SCHEMA public FROM rdsadmin;
GRANT ALL ON SCHEMA public TO administrator;
  3. Restore the modified dump file:
    4. gunzip -c your_dump.sql.gz | psql --username=administrator --dbname=financethat

7. Verify PostGIS Installation

Log in as administrator:

psql --username=administrator --dbname=financethat

Test PostGIS functionality:

SELECT PostGIS_Full_Version();
SELECT ST_AsText(ST_MakePoint(0, 0));

Check for installed extensions:

\\dx

8. Notes for Future Use

  • Always ensure the target user (administrator) has sufficient privileges for the database and schema.
  • When restoring from AWS RDS, clean up rdsadmin references in the dump file.
  • If recreating PostGIS, drop the extension and recreate it under the target user's ownership:
    • DROP EXTENSION postgis CASCADE;
CREATE EXTENSION postgis;

This process ensures a smooth PostGIS installation and management under a non-superuser role like administrator. Let me know if further clarifications are needed!

Comments

Post a Comment

Popular posts from this blog

Managing Python Projects with Pipenv and Pyenv: A Comprehensive Guide

Image
In the ever-evolving world of Python development, managing project dependencies and Python versions can become a complex task. Tools like Pipenv and Pyenv have been created to simplify these processes, but understanding how they work individually and together is crucial for efficient project management. This comprehensive guide will delve into the functionalities of Pipenv and Pyenv, highlight their differences, and demonstrate how to effectively use them in your Python projects. Table of Contents Introduction to Pipenv Key Features of Pipenv Pipenv General Commands Pipfile vs requirements.txt Managing Dependencies in a Django Application Creating a Pipfile and Pipfile.lock Example Pipfile Explained Automatic Updating of Pipfile and Pipfile.lock Setting Up the Project with Pipenv Introduction to Pyenv Key...
Read more

Differences Between List, Dictionary, and Tuple in Python

In Python, lists, dictionaries, and tuples are all built-in data structures that store collections of items. While they share similarities, each has its distinct characteristics and use cases. This post provides a comprehensive comparison. Comparison Table Aspect List Dictionary Tuple Definition An ordered collection of elements. A collection of key-value pairs. An ordered and immutable collection of elements. Access Method Index-based access ( my_list[0] ). Key-based access ( my_dict['key'] ). Index-based access ( my_tuple[0] ). Mutability Mutable: Elements can be added, removed, or changed. Mutable (for values): Keys/values can be added, modified, or removed, but keys must be immutable. Immutable: Elements cannot be changed once assigned. Order Guarantee Maintains th...
Read more

Implementing Throttling in Django REST Framework.

Implementing Throttling in Django REST Framework to Manage High Traffic and Prevent Abuse Introduction In high-traffic applications, it’s essential to protect server resources and ensure fair usage among users. Throttling is a method used in Django REST Framework (DRF) to limit the rate of requests, helping prevent abuse and manage load. In this article, we’ll explore how to implement throttling in DRF, including ways to apply it to specific views and dynamically whitelist IPs. What is Throttling? Throttling controls the rate of requests an API endpoint can receive from a single client. It’s a valuable tool for: Preventing abuse by limiting request rates for each user or IP address. Managing server load by avoiding high request volumes that can overwhelm the application. In Django REST Framework, throttling is easy to configure with built-in classes like UserRateThrottl...
Read more