GRANT (Schema)

Grants privileges on a schema to a database user or role.

Note: If a schema was created with Inherited Privileges enabled, any privileges you grant the schema are inherited by all the objects in the table. Otherwise, you need to grant privileges on each object in the table. For more information see CREATE SCHEMA

New users do not have access to schema PUBLIC by default. You must grant USAGE on the PUBLIC schema to all users you create.

Syntax

GRANT { ... { CREATE | USAGE } [ , ... ] | ALL [ PRIVILEGES ] }
 ... | { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRUNCATE } [ ,... ] ... }
 ... ON SCHEMA [db-name.]schema [ , ... ] 
 ... TO { username | role | PUBLIC } [ , ... ] 
 ... [ WITH GRANT OPTION ]

Parameters

CREATE

Grants the user read access to the schema and the right to create tables and views within the schema.

 
USAGE

Grants the user access to the objects contained within the schema. This allows the user to look up objects within the schema. Note that the user must also be granted access to the individual objects. See the GRANT TABLE and GRANT VIEW statements.

 
SELECT
 With Inherited Privileges enabled on the schema, grants the user SELECT privileges on any column of any table in the schema. See Inherit Privileges on a Schema. 
INSERT
 With Inherited Privileges enabled on the schema, grants the user privileges to INSERT tuples into tables in the schema and use the COPY command to load data into the tables. See Inherit Privileges on a Schema. 
UPDATE
 With Inherited Privileges enabled on the schema, grants the user privileges to UPDATE tuples in a schema table. See Inherit Privileges on a Schema. 
DELETE
 With Inherited Privileges enabled on the schema, grants the user privileges to DELETE rows from a schema table. See Inherit Privileges on a Schema. 
REFERENCES
 With Inherited Privileges enabled on the schema, grants the ability to create a foreign key constraint. You must have this privilege on both the referencing and referenced tables. You also need USAGE on the schema that contains the table. See Inherit Privileges on a Schema. 
TRUNCATE
 With Inherited Privileges enabled on the schema, grants the user TRUNCATE privileges on rows from a schema table. See Inherit Privileges on a Schema. 
ALL

Grants the user CREATE and USAGE privileges on the schema.

 
PRIVILEGES

Used for SQL standard compatibility.

 
[db-name.]

[Optional] Specifies the current database name. Using a database name prefix is optional, and does not affect the command in any way. You must be connected to the specified database. 

schema

Identifies the schema to which you are granting privileges.

 
username

Grants the privilege to a specific user.

 
role

Grants the privilege to a specific role.

 
PUBLIC

Grants the privilege to all users.

 
WITH GRANT OPTION

Allows the recipient of the privilege to grant it to other users.

Examples

This example shows how to grant user Joe usage on schema online_sales.

=> CREATE USER Joe;
CREATE USER
=> GRANT USAGE ON SCHEMA online_sales TO Joe;
GRANT PRIVILEGE

See Also