public inbox for [email protected]  

help / color / mirror / Atom feed

has_table_privilege
2+ messages / 2 participants
[nested] [flat]

* has_table_privilege
@ 2026-02-15 21:19  Edwin UY <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Edwin UY @ 2026-02-15 21:19 UTC (permalink / raw)
  To: Pgsql-admin <[email protected]>

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');

I 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?


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: has_table_privilege
@ 2026-02-16 02:23  Joe Conway <[email protected]>
  parent: Edwin UY <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Joe Conway @ 2026-02-16 02:23 UTC (permalink / raw)
  To: Edwin UY <[email protected]>; Pgsql-admin <[email protected]>

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2026-02-16 02:23 UTC | newest]

Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-15 21:19 has_table_privilege Edwin UY <[email protected]>
2026-02-16 02:23 ` Joe Conway <[email protected]>

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