has_table_privilege()

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

has_table_privilege() is a system function determining whether a user has the specified privilege for the specified table.

has_table_privilege() was added in PostgreSQL 7.2.

Usage

has_table_privilege() has two forms:

  • has_table_privilege(table TEXT or OID, privilege TEXT)
  • has_table_privilege(user TEXT or OID, table TEXT or OID, privilege TEXT)

The following privileges can be queried:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • REFERENCES
  • TRIGGER
  • TRUNCATE

Change history

Examples

Determine if the current user has the UPDATE privilege for the specified table:

postgres=# SELECT has_table_privilege('foo', 'UPDATE');
 has_table_privilege 
---------------------
 t
(1 row)

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

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

Query multiple privileges:

postgres=# SELECT has_table_privilege('foo', 'INSERT, DELETE, UPDATE WITH GRANT OPTION');
 has_table_privilege 
---------------------
 t
(1 row)

Attempting to determine an unrecognized privilege for this object type:

postgres=# SELECT has_table_privilege('foo', 'CREATE');
ERROR:  unrecognized privilege type: "CREATE"

Attempting to determine privileges for a non-existent table:

postgres=# SELECT has_table_privilege('bar', 'DELETE');
ERROR:  relation "bar" does not exist

Categories

System function, User management