Re: has_table_privilege - Mailing list pgsql-admin

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