Message-ID: <039f4bde-9195-40b1-ae91-75d50dfc4f57@joeconway.com>
Date: Sun, 15 Feb 2026 21:23:58 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: has_table_privilege
To: Edwin UY <edwin.uy@gmail.com>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>
References: 
 <CA+wokJ9CPLZYPzTC8tHTDab4nUnERYcqk3W+EsbdOhTc4pNL0A@mail.gmail.com>
Content-Language: en-US
From: Joe Conway <mail@joeconway.com>
In-Reply-To: 
 <CA+wokJ9CPLZYPzTC8tHTDab4nUnERYcqk3W+EsbdOhTc4pNL0A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/039f4bde-9195-40b1-ae91-75d50dfc4f57%40joeconway.com>
Precedence: bulk

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