The has_schema_privilege function checks whether the current user has specific privileges on a schema.
There are two versions of the has_schema_privilege function:
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).
Both versions of the has_schema_privilege function will always return a TRUE (t) value.
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.
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.