Contents
has_column_privilege()
has_column_privilege() is a system function determining whether a user has a privilege for a particular column of a table.
has_column_privilege() was added in PostgreSQL 8.4.
Usage
has_column_privilege() has two forms:
has_column_privilege(table TEXT or OID, column TEXT or SMALLINT, privilege TEXT)has_column_privilege(user TEXT or OID, table TEXT or OID, column TEXT or SMALLINT, privilege TEXT)
The column can be specified by name or its number, which corresponds to the attnum column value in the pg_attribute for this column (note this number may differ from the apparent current position of the column within the table, so is of limited practical use for ad-hoc queries).
The following privileges can be queried:
SELECTINSERTUPDATEREFERENCES
Note that when providing the table an OID, or the column as a SMALLINT, the function will return NULL if no matching object is found. If the table and column names are provided as a string, an error will be raised if no matching object is found.
Change history
- PostgreSQL 8.4
- added (commit 7449427a)
Examples
Determine if the current user has the SELECT privilege for a particular column in the specified table:
postgres=# SELECT has_column_privilege('foo', 'id', 'SELECT');
has_column_privilege
----------------------
t
(1 row)
The same, but specifying the column number:
postgres=# SELECT has_column_privilege('foo', 1::SMALLINT, 'SELECT');
has_column_privilege
----------------------
t
(1 row)
Determine if the specified user has the UPDATE privilege for a particular column in the specified table:
postgres=# SELECT has_column_privilege('someuser', 'foo', 'id', 'UPDATE');
has_column_privilege
----------------------
f
(1 row)
Attempting to determine an unrecognized privilege for this object type:
postgres=# SELECT has_column_privilege('foo', 'id', 'DELETE');
ERROR: unrecognized privilege type: "DELETE"
Attempting to determine privileges for a non-existent table:
postgres=# SELECT has_column_privilege('bar', 'id', 'SELECT');
ERROR: relation "bar" does not exist
Attempting to determine privileges for a non-existent column:
postgres=# SELECT has_column_privilege('foo', 'bar', 'SELECT');
ERROR: column "bar" of relation "foo" does not exist
Attempting to determine privileges for a non-existent table OID:
postgres=# SELECT has_column_privilege(9999, 'id', 'SELECT'); has_column_privilege ---------------------- (1 row)
References
- PostgreSQL documentation: Access Privilege Inquiry Functions