​
Overview

The has_schema_privilege()has_schema_privilege is an access privilege inquiry function that checks whether the current user has specific privileges on a schema.

​
Syntax

There are two available syntax versions of the has_schema_privilege function:

SELECT has_schema_privilege('user', 'schema', 'privilege');

No matter what syntax version you choose, the has_schema_privilege() function will always return TRUE (t).

​
Parameters

The following parameters are required to execute this function:

  • schema: name of the schema for which you want to check privileges (can be any string value or string columns from other tables)
  • user: name of the user who has the privileges (can be any string value)
  • privilege: specifies the specific privilege you want to check for in the schema (currently, the function supports create and usage)
The comparison for the privilege is case-insensitive, so you can use lowercase or uppercase notation for the privilege name

​
Examples

​
Check for CREATE Privilege

In this example, we will use the has_schema_privilege() function to determine if the current user has the create privilege on a schema named “public”:

SELECT has_schema_privilege('public', 'create');

By executing the query above, we will get TRUE, which means that the current user has a create privilege on the “public” schema.

 has_schema_privilege 
----------------------
 t

​
Check for USAGE Privilege

You can also use the has_schema_privilege() function to check for the usage privilege on a schema. For example, in order to check if the current user can create objects in the “public” schema, you can execute the following code:

SELECT has_schema_privilege('cahyo', 'public', 'USAGE');

The query above will return TRUE, which means the current user has usage privilege on the “public” schema.

 has_schema_privilege 
----------------------
 t
current_schemanullif