Greenplum Create Users and Roles

Greenplum Database manages database access using roles. Initially, there is one superuser role—the role associated with the OS user who initialized the database instance, usually gpadmin. This user owns all of the Greenplum Database files and OS processes, so it is important to reserve the gpadmin role for system tasks only.

A role can be a user or a group. A user role can log in to a database; that is, it has the LOGIN attribute. A user or group role can become a member of a group.

Permissions can be granted to users or groups. Initially, of course, only the gpadmin role is able to create roles. You can add roles with the createuser utility command, CREATE ROLE SQL command, or the CREATE USER SQL command. The CREATE USER command is the same as the CREATE ROLE command except that it automatically assigns the role the LOGIN attribute.

Create a user with the createuser utility command

Login to the GPDB Sandbox as the gpadmin user.

Enter the createuser command and reply to the prompts:

$ createuser -P user1
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
NOTICE:  resource queue required -- using default resource queue
"pg_default"

Create a user with the CREATE USER command

Connect to the template1 database as gpadmin:

$ psql template1

Create a user with the name user2:

template1=# CREATE USER user2 WITH PASSWORD 'pivotal' NOSUPERUSER;

Display a list of roles:

template1=# \du
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 gpadmin   | Superuser, Create role, Create DB |
 gpmon     | Superuser, Create DB              |
 user1     | Create DB                         |
 user2     |

Create a users group and add the users to it

1. While connected to the template1 database as gpadmin enter the following SQL commands:

   template1=# CREATE ROLE users;
   template1=# GRANT users TO user1, user2;

2. Display the list of roles again:

   template1=# \du
                          List of roles
    Role name |            Attributes             | Member of
   -----------+-----------------------------------+-----------
    gpadmin   | Superuser, Create role, Create DB |
    gpmon     | Superuser, Create DB              |
    user1     | Create DB                         | {users}
    user2     |                                   | {users}
    users     | Cannot login                      |

3. Exit out of the psql shell:

   template1=# \q