Cloud

has_schema_privilege

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

Syntax

The has_schema_privilege function has two available syntax versions:

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

The function returns a boolean value: TRUE if the user has the specified privilege, FALSE otherwise.

Parameters

  • schema: Name of the schema to check privileges for (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 to check for in the schema. The function currently 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

This example uses the has_schema_privilege() function to determine whether the current user has the create privilege on a schema named public:

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

The query returns TRUE, which means that the current user has a create privilege on the public schema.

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

Check for USAGE privilege

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, run:

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

The query returns TRUE, which means the current user has usage privilege on the public schema.

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