has_database_privilege()

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

has_database_privilege() is a system function determining whether a user has the specified privilege for the specified database.

has_database_privilege() was added in PostgreSQL 7.3.

Usage

has_database_privilege() has two forms:

  • has_database_privilege(database TEXT or OID, privilege TEXT)
  • has_database_privilege(user TEXT or OID, database TEXT or OID, privilege TEXT)

The following privileges can be queried:

  • CREATE
  • CONNECT
  • TEMP / TEMPORARY

Multiple privileges can be queried if provided as a comma-separated list. Note that the function will always return 't' (TRUE) if one or more privileges are available.

Change history

Examples

Determine if the current user has the CONNECT privilege for the specified database:

postgres=# SELECT has_database_privilege('postgres', 'CONNECT');
 has_database_privilege 
------------------------
 t
(1 row)

Determine if the specified user has the CREATE privilege for the specified table:

postgres=# SELECT has_database_privilege('someuser', 'postgres', 'CREATE');
 has_database_privilege 
------------------------
 f
(1 row)

Query multiple privileges:

postgres=# SELECT has_database_privilege('someuser', 'postgres', 'CONNECT, CREATE');
 has_database_privilege 
------------------------
 t
(1 row)

Note that the function returns 't' (TRUE) even though the CREATE privilege is not available.

Attempting to determine an unrecognized privilege for this object type:

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

Attempting to determine privileges for a non-existent database:

postgres=# SELECT has_database_privilege('somedbname', 'CONNECT');
ERROR:  database "somedbname" does not exist

Categories

System function, User management