|Pivotal Greenplum (GPDB)||4.3.x|
How to restrict database access so only management tools and superusers can connect to the database.
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.