How to get PUBLIC role's granted privilege list in postgres?-CodePudding

As the documentation said:

PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

We can grant privileges for PUBLIC like this:

GRANT SELECT ON table_1 TO PUBLIC;
GRANT USAGE ON SCHEMA schema_1 TO PUBLIC;
GRANT EXECUTE ON FUNCTION func_1 TO PUBLIC;

If I want to revoke such public privileges, I should know the granted privilege list first. But I can't find a good way to get the list because PUBLIC is not a role, thus many builtin functions like has_table_privilege cannot be used.

Now I have found some tables in information_schema may help, but there are still some attributes like SCHEMA I cannot find a list for them.

How to get the PUBLIC's granted SCHEMA privilege list? Or is there a better way to get all the privileges?

-- get granted table and view privileges
SELECT table_schema, table_name, string_agg(privilege_type, ',') AS privileges 
  FROM information_schema.table_privileges 
  WHERE grantee='PUBLIC' AND table_schema NOT LIKE 'pg_%' AND table_schema != 'information_schema' 
  GROUP BY table_schema, table_name;

-- get granted function privileges
SELECT routine_schema, routine_name, string_agg(privilege_type, ',') AS privileges 
  FROM information_schema.routine_privileges 
  WHERE grantee='PUBLIC' AND routine_schema NOT LIKE 'pg_%' AND routine_schema != 'information_schema' 
  GROUP BY routine_schema, routine_name;

CodePudding user response：

Privileges for a table are stored in pg_class.relacl which is an array of aclitem.

The content of such an aclitem is documented in the manual, specifically:

An empty grantee field in an aclitem stands for PUBLIC.

So to find all tables (or other objects) that have something granted to the public role, one needs to find entries where at least one aclitem starts with = ("empty grantee field")

There is a contains operator @> for aclitems that can be used to check for specific privileges. But I couldn't find a way to specify an aclitem value that would search match all privileges granted to public (relacl @> '=*' doesn't seem to work in all cases).

So a workaround might be to simply convert the items to text and use a LIKE condition:

select c.relnamespace::regnamespace::text as table_schema, 
       c.relname as table_name,
       c.relacl
from pg_class c
where relnamespace not in ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
  and exists (select * 
              from unnest(c.relacl) as x(acl) 
              where x.acl::text like '=%')