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