How to Restrict Database Access

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article describes how to restrict database access so only management tools and superusers can connect to the database.

 

Environment

Resolution

1. Start GPDB in "restricted mode".

From gpstart documentation:

-R (restricted mode)
   Starts Greenplum Database in restricted mode (only database 
   superusers are allowed to connect).

This will allow all database superusers to connect to the database from wherever they are connecting (of course, depending on the rules in pg_hba.conf).

Please note that "gpmon" (the user which gpperfmon uses) is also superuser.

2. Restrict access via pg_hba.conf

The minimum pg_hba.conf in order for database utilities to work is:

host    postgres          gpadmin         127.0.0.1/32 trust
host    template1         gpadmin         127.0.0.1/32 trust
host    replication       gpadmin         0.0.0.0/0 trust
host    all               gpadmin         ::1/128 trust 

This allows the following access:

• user gpadmin from localhost (socket, not local connection) to database "postgres"
• user gpadmin from localhost (socket, not local connection) to database "template1"

Access to both "template1" and "postgres" is necessary as some utilities use "template1" as connection database, but others use "postgres" database.

This will allow connections for user "gpadmin" only from the localhost and only to these two databases. 

Note- If there are any crontab jobs that use superuser login, it is good practice to either remove them from crontab temporarily (comment out) or stop crontab completely.

Additional Information

+ Environment:

• Pivotal Greenplum Database (GPDB) 4.3.x
• Operating System- Red Hat Enterprise Linux 6.x