has_schema_privilege()

Overview

The has_schema_privilege function checks whether the current user has specific privileges on a schema.

Syntax

There are two versions of the has_schema_privilege function:

Version 1

Version 2

See the breakdown of both versions:

• schema: The name of the schema for which you want to check privileges. It can be any string value or string columns from other tables.
• user: The name of the user who has the privileges. It can be any string value.
• privilege: The privilege parameter 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 letters for the privilege name (e.g., CREATE or create are both valid).

Output

Both versions of the has_schema_privilege function will always return a TRUE (t) value.

Examples

Case #1: 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."

This query will return TRUE, which means the current user has a create privilege on the "public" schema.

Case #2: Check for USAGE Privilege

You can also use the has_schema_privilege function to check for the usage privilege on a schema. For example, to check if the current user can create objects in the "public" schema:

This query will return TRUE, which means the current user has usage privilege on the "public" schema.