Re: has_table_privilege

On 2/15/26 16:19, Edwin UY wrote:
> Is there no shortcut alternative to using select has_table_privilegeso
> it will just check all possible privileges?
>
> select has_table_privilege('user_or_role','table_name','update');
> select has_table_privilege('user_or_role','table_name','select,insert');
> thought when running select
> has_table_privilege('user_or_role','table_name','select,insert,update,delete');
> it will enumerate the privilege. Isn't it not supposed to?

No, there is no builtin shortcut, But you can build your own easily enough:

8<-----------
CREATE OR REPLACE FUNCTION has_table_privileges
(
username text,
tablename text,
VARIADIC privileges text[]
)
RETURNS TABLE (privilege text, granted bool)
AS $$
SELECT u.p, has_table_privilege(username, tablename, u.p)
FROM UNNEST(privileges) AS u(p)
$$ LANGUAGE sql STRICT;

CREATE USER joe;
GRANT SELECT ON commits TO joe;
CREATE USER bob;
GRANT SELECT,INSERT,UPDATE ON commits TO bob;

SELECT * FROM has_table_privileges('joe', 'commits', 'SELECT', 'INSERT',
'UPDATE', 'DELETE', 'TRUNCATE');

privilege | granted
------------+---------
SELECT | t
INSERT | f
UPDATE | f
DELETE | f
TRUNCATE | f
(5 rows)

SELECT * FROM has_table_privileges('bob', 'commits', 'SELECT', 'INSERT',
'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER', 'MAINTAIN');
privilege | granted
------------+---------
SELECT | t
INSERT | t
UPDATE | t
DELETE | f
TRUNCATE | f
REFERENCES | f
TRIGGER | f
MAINTAIN | f
(8 rows)
8<-----------

Or alternatively if you always want to enumerate all possible privileges:

8<-----------
CREATE OR REPLACE FUNCTION has_table_privileges
(
username text,
tablename text
)
RETURNS TABLE (privilege text, granted bool)
AS $$
WITH u(p) AS
(
VALUES ('SELECT'),
('INSERT'),
('UPDATE'),
('DELETE'),
('TRUNCATE'),
('REFERENCES'),
('TRIGGER'),
('MAINTAIN')
)
SELECT u.p, has_table_privilege(username, tablename, u.p)
FROM u
$$ LANGUAGE sql STRICT;

SELECT * FROM has_table_privileges('bob', 'commits');
SELECT * FROM has_table_privileges('bob', 'commits');
privilege | granted
------------+---------
SELECT | t
INSERT | t
UPDATE | t
DELETE | f
TRUNCATE | f
REFERENCES | f
TRIGGER | f
MAINTAIN | f
(8 rows)
8<-----------

HTH,

--
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com