Example for controlling user and group access
This example creates user groups and users and then grants them various permissions for an Amazon Redshift database that connects to a web application client. This example assumes three groups of users: regular users of a web application, power users of a web application, and web developers.
-
Create the groups where the users will be assigned. The following set of commands creates three different user groups:
create group webappusers; create group webpowerusers; create group webdevusers;
-
Create several database users with different permissions and add them to the groups.
-
Create two users and add them to the WEBAPPUSERS group:
create user webappuser1 password 'webAppuser1pass' in group webappusers; create user webappuser2 password 'webAppuser2pass' in group webappusers;
-
Create a web developer user and add it to the WEBDEVUSERS group:
create user webdevuser1 password 'webDevuser2pass' in group webdevusers;
-
Create a superuser. This user will have administrative rights to create other users:
create user webappadmin password 'webAppadminpass1' createuser;
-
-
Create a schema to be associated with the database tables used by the web application, and grant the various user groups access to this schema:
-
Create the WEBAPP schema:
create schema webapp;
-
Grant USAGE permissions to the WEBAPPUSERS group:
grant usage on schema webapp to group webappusers;
-
Grant USAGE permissions to the WEBPOWERUSERS group:
grant usage on schema webapp to group webpowerusers;
-
Grant ALL permissions to the WEBDEVUSERS group:
grant all on schema webapp to group webdevusers;
The basic users and groups are now set up. You can now alter the users and groups.
-
-
For example, the following command alters the search_path parameter for the WEBAPPUSER1.
alter user webappuser1 set search_path to webapp, public;
The SEARCH_PATH specifies the schema search order for database objects, such as tables and functions, when the object is referenced by a simple name with no schema specified.
-
You can also add users to a group after creating the group, such as adding WEBAPPUSER2 to the WEBPOWERUSERS group:
alter group webpowerusers add user webappuser2;