Pivotal Knowledge Base

Follow

How to restrict database access

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x

Purpose

How to restrict database access so only management tools and superusers can connect to the database.

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

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.

Comments

  • Avatar
    Varun Thomas

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

    eg:

    local all gpadmin 0.0.0.0/0 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.

Powered by Zendesk