User Management

Cluster management permissions

Cluster Management permissions are provisioned to AWS users, and IAM policies and roles manage their access. 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 – This is an identity with specific permissions to your AWS resources. You can have an IAM user name and password and 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. Still, 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. In addition, Amazon Redshift supports identity-based policies (IAM Policies), which are policies attached to an IAM identity. For example, you may attach a permissions policy to a user to allow him to create an Amazon Cluster.

See the Access Management section in the IAM User Guide for detailed information and best practices about using IAM policies.

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

Access to database permissions

Access to the database is the ability to control 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. Therefore, be very careful when using a superuser role. AWS recommends that you do most of your work in 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. For example, superusers have database ownership privileges to all databases.

Below, we will 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

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

More info on the CREATE USER command.

Create User in a Group

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

More info on the CREATE USER command.

Drop a User

JSX
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

JSX
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

JSX
SELECT * FROM pg_user;

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

Create New Group

JSX
CREATE GROUP <group_name>;

More info on the CREATE GROUP command.

View all Groups

JSX
SELECT * FROM pg_group;

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

View all Schemas

JSX
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

JSX
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 return a list of tables in the pg_catalog schema.

Grant USAGE on a schema to a user

JSX
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

JSX
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

JSX
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

JSX
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:

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

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

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

Last grant SELECT on the <table_name> TABLE with:

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

Grant USAGE on the schema to a group

JSX
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

JSX
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

JSX
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.