has_schema_privilege()

A system function determining whether a user has a privilege for a schema

has_schema_privilege() is a system function determining whether a user has the specified privilege for the specified schema.

has_schema_privilege() was added in PostgreSQL 7.3.

Usage

has_schema_privilege() has two forms:

  • has_schema_privilege(schema TEXT or OID, privilege TEXT)
  • has_schema_privilege(user TEXT or OID, schema TEXT or OID, privilege TEXT)

The following privileges can be queried:

  • CREATE
  • USAGE

Change history

Examples

Determine if the current user has the USAGE privilege for the specified schema:

postgres=# SELECT has_schema_privilege('public', 'USAGE');
 has_schema_privilege 
----------------------
 t
(1 row)

Determine if the specified user has the USAGE privilege for the specified schema:

postgres=# SELECT has_schema_privilege('someuser', 'foo', 'USAGE');
 has_schema_privilege
----------------------
 f
(1 row)

Attempting to determine an unrecognized privilege for this object type:

postgres=# SELECT has_schema_privilege('public', 'DELETE');
ERROR:  unrecognized privilege type: "DELETE"

Attempting to determine privileges for a non-existent function:

postgres=# SELECT has_schema_privilege('foo', 'USAGE');
ERROR:  schema "foo" does not exist

Categories

Schema (namespace), System function, User management