Pivotal Knowledge Base


How to Restrict Database Access


  • Pivotal Greenplum Database (GPDB) 4.3.x
  • Operating System- Red Hat Enterprise Linux 6.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 trust
host    template1         gpadmin trust
host replication gpadmin 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.


  • Avatar
    Varun Thomas

    If we restricting DB for PT rebuild,
    We have to add entry for local and for smdw replication as well


    local all gpadmin trust
    local replication gpadmin trust
    host replication gpadmin trust

    Else there are chances of gppersistenrebuild failing as well.

  • Avatar
    Gurupreet Singh Bhatia

    we can also set database connection limit to zero.

  • Avatar
    Krzysztof Byszewski

    added line :host all gpadmin ::1/128 trust
    because gppersistentrebuild was faiing for me

Powered by Zendesk