User Management

In the prior sections, we covered how to setup an Amazon Redshift cluster and how to configure networking and security groups. In this section, we go over some matters about User Management. As this is a relatively complicated issue, we have included various links from Amazon’s documentation for reference.

We mentioned earlier, when you create an instance you create the Master User and its login credentials. However, Redshift allows you to control further and configure your permission settings or add new users.

Access to Amazon Redshift resources has 3 levels of control:

  1. Cluster Connectivity Permissions
  2. Cluster Management Permissions
  3. Access to Database Permissions

Let’s see an overview, but first, what Is IAM?

IAM is AWS Identity and Access Management and helps you control access to AWS resources. IAM controls who can use your AWS resources, which of these resources and how.

Cluster Connectivity Permissions

In the previous section, we discussed the cluster connectivity permissions which are actually over networking access and security groups. So you are welcome to refer to Configure Networking and Security Groups.

Cluster Management Permissions

Cluster Management permissions are provisioned to AWS users, and their access is managed by IAM policies and roles. Amazon separates such access to Authentication and Access Control.

Authentication

AWS account root user – That is your AWS account when you first signed up. It provides complete access to all of your AWS resources.

We always need to be cautious with User Management and  Amazon recommends using your root credentials only to create an administrator user. An administrator user is an IAM user with full permissions to your AWS account. Then, use this administrator user to create other IAM users and roles with limited permissions.

IAM user – Is an identity with specific permissions to your AWS resources. You can have an IAM user name and password and also generate access keys for accessing AWS services programmatically either through one of the several SDKs or by using the AWS Command Line Interface (CLI).

IAM role – The IAM role is similar to an IAM user but does not associate with a specific person. An IAM role enables you to do specific things in AWS. It has no username, password or keys associated with it but instead, if a user is assigned to an IAM Role, access keys are created dynamically and provided to that user. Everything you need to know about IAM Roles is here.

Access Control

Access control is the permission you have to perform operations like creating an Amazon Redshift cluster, IP addresses, Security Groups, Snapshots and more. Amazon Redshift supports identity-based policies (IAM Policies) which are policies attached to an IAM identity. For example, you may attach permissions policy to a user to allow him to create an Amazon Cluster.

For detailed information and best practices about using IAM policies see the Access Management section in the IAM User Guide here.

You may also find a list with example policies for administering AWS resources in here.

Notes & Resources:

Access to Database Permissions

Access to the database is the ability to have control over a database’s objects like tables and views. You must be a superuser to create an Amazon Redshift user. The Master User is a superuser.

A database superuser bypasses all permission checks. Be very careful when using a superuser role. AWS recommends that you do most of your work as a role that is not a superuser. Superusers retain all privileges regardless of GRANT and REVOKE commands.

A superuser can create other superusers and users. These users can be owners of databases, tables, views, grant privileges for specific objects and resources. Superusers have database ownership privileges to all databases.

Below we are going to see some Amazon Redshift queries which can be helpful in your user management along with links with more details on each command.

Create New User

CREATE USER <user_name>;
CREATE USER <user_name> WITH PASSWORD ‘<a_password>’;

More info on the CREATE USER command.

Create User in a Group

CREATE USER <user_name> WITH PASSWORD ‘<a_password>’ IN GROUP <group_name>;

More info on the CREATE USER command.

Drop a User

DROP USER IF EXISTS <user_name>;

Note: You cannot drop a user if the user owns any database object, such as a schema, database, table, or view, or if the user has any privileges on a table, database, or group.

More info on the DROP USER command

Alter a User

ALTER USER <user_name> CREATEDB;

That command allows the USER <user_name> to create new databases. Here is more info on the ALTER USER command.

View all Users

SELECT * FROM pg_user;

To view the list of users, we query the pg_user catalog table.

Create New Group

CREATE GROUP <group_name>;

More info on the CREATE GROUP command.

View all Groups

SELECT * FROM pg_group;

To view the list of groups, we query the pg_group catalog table.

View all Schemas

SELECT * FROM pg_namespace;

To view a list of all schemas, we query the pg_namespace catalog table.

View Tables that belong to a Schema

SELECT distinct(<table_name>) FROM pg_table_def
WHERE <schema_name> = 'pg_catalog';

The above will query the pg_table_def system catalog table and will return a list of tables in the pg_catalog schema.

Grant USAGE on a schema to a user

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;

The USER will now have USAGE rights on a <schema_name> SCHEMA.

Grant SELECT privileges to a user to all tables

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;

The USER will now have SELECT permissions on all the tables in a <schema_name> SCHEMA.

Grant SELECT privileges to a user to a table

GRANT SELECT ON TABLE <schema_name>.<table_name> TO <user_name>;

The USER <user_name> will now have SELECT rights on TABLE <table_name> in the <schema_name> SCHEMA.

Grant ALL privileges to a user to all tables

GRANT ALL ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;

The USER will now have ALL rights on ALL TABLES in the <schema_name> SCHEMA.

Create a Read-only User

Create a New User with:

CREATE USER <user_name> WITH PASSWORD ‘<password>’;

Then we can grant USAGE rights on the <schema_name> SCHEMA to the <user_name> with:

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;

Last grant SELECT on the <table_name> TABLE with:

GRANT SELECT ON TABLE <schema_name>.<table_name> TO <user_name>;

 Grant USAGE on the schema to a group

GRANT USAGE ON SCHEMA <schema_name> TO <group_name>;

The GROUP <group_name> will now have USAGE rights on the <schema_name> SCHEMA.

Grant SELECT privileges to a group, to all tables

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <group_name>;

The GROUP <group_name> will now have SELECT rights on ALL TABLES in the <schema_name> SCHEMA.

Grant SELECT privileges to a group, to a table

GRANT SELECT ON TABLE <schema_name>.<table_name> TO <group_name>;

The GROUP <group_name> will now have SELECT rights on <table_name> in the <schema_name> SCHEMA.

Notes & Resources:

  • More details on superusers, here.
  • More details on Users, here.
  • More info on Creating, Altering, and Deleting Groups here.
  • More info on Creating, Altering, and Deleting Schemas here.
  • Information on Managing Database Security.
load data into any data warehouse - Blendo