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
- PostgreSQL 8.4
- able to accept a comma-separated list of privilege types (commit 7449427a)
- PostgreSQL 7.2
- added (commit c9499e68)
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
References
- PostgreSQL documentation: Access Privilege Inquiry Functions
Useful links
- How to See Inherited Permissions for a User - August 2021 blog article by Richard Yen