Managing a schema in a database

In Amazon Redshift, a schema is a namespace that groups database objects such as tables, views, stored procedures, and so on. Organizing database objects in a schema is good for security monitoring and also logically groups the objects within a cluster. In this recipe, we will create a sample schema that will be used to hold all the database objects.

Getting ready

To complete this recipe, you will need access to any SQL interface such as a SQL client or query editor.

How to do it…

1. Users can create a schema using the CREATE SCHEMA command. The following steps will enable you to set up a schema with the name finance and add the necessary access to the groups.
2. Create finance_grp, audit_grp, and finance_admin_user groups using the following command:

   create group finance_grp;
   create group audit_grp;
   create user finance_admin_usr with password '<PasswordOfYourChoice>'; 

3. Create a schema named finance with a space quota of 2 terabytes (TB), with a finance_admin_usr schema owner:

   CREATE schema finance authorization finance_admin_usr QUOTA 2 TB;

   You can also modify an existing schema using ALTER SCHEMA or DROP SCHEMA.

4. For the finance schema, grant access privileges of USAGE and ALL to the finance_grp group. Further, grant read access to the tables in the schema using a SELECT privilege for the audit_grp group:

   GRANT USAGE on SCHEMA finance TO GROUP finance_grp;
   GRANT USAGE on SCHEMA finance TO GROUP audit_grp;
   GRANT ALL ON schema finance to GROUP finance_grp;
   GRANT SELECT ON ALL TABLES IN SCHEMA finance TO GROUP audit_grp;

5. You can verify that the schema and owner group have been created by using the following code:

   select nspname as schema, usename as owner
   from pg_namespace, pg_user
   where pg_namespace.nspowner = pg_user.usesysid
   and pg_namespace.nspname ='finance';

6. Create a foo table (or view/database object) within the schema by prefixing the schema name along with the table name, as shown in the following command:

   CREATE TABLE finance.foo (bar int); 

7. Now, in order to select the foo table from the finance schema, you will have to prefix the schema name along with the table name, as shown in the following command:

   select * from finance.foo; 

   The preceding SQL code will not return any rows.

8. Assign a search path to conveniently reference the database objects directly, without requiring the complete namespace of the schema qualifier. The following command sets the search path as finance so that you don't need to qualify the schema name every time when working with database objects:

   set search_path to '$user', finance, public;

   Important note

   The search path allows a convenient way to access the database objects without having to specify the target schema in the namespace when authoring the SQL code. The search path can be configured using the search_path parameter with a comma-separated list of schema names. When referencing the database object in a SQL when no target schema is provided, the database object that is in the first available schema list is picked up. You can configure the search path by using the SET search_path command at the current session level or at the user level.

9. Now, executing the following SELECT query without the schema qualifier automatically locates the foo table in the finance schema:

   select * from foo;

   The preceding SQL code will not return any rows.

Now, the new finance schema is ready for use and you can keep creating new database objects in this schema.

Important note

A database is automatically created by default with a PUBLIC schema. Identical database object names can be used in different schemas of the database. For example, finance.customer and marketing.customer are valid table definitions that can be created without any conflict, where finance and marketing are schema names and customer is the table name. Schemas serve the key purpose of easy management through this logical grouping—for example, you can grant SELECT access to all the objects at a schema level instead of individual tables.