public inbox for [email protected]  

help / color / mirror / Atom feed

From: Joe Conway <[email protected]>
To: Edwin UY <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: has_table_privilege
Date: Sun, 15 Feb 2026 21:23:58 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+wokJ9CPLZYPzTC8tHTDab4nUnERYcqk3W+EsbdOhTc4pNL0A@mail.gmail.com>
References: <CA+wokJ9CPLZYPzTC8tHTDab4nUnERYcqk3W+EsbdOhTc4pNL0A@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: has_table_privilege
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox