Count rows in all schemas (with permission granted) in all tables

Question

I'm trying to count the rows in all the tables in the database. Since the answer I'd like to get must differentiate between different schemas I also take into account the schema that a particular table is located in.

This answer has been most helpful but it turns out I don't have access privilege to all schemas in the database.

I know that I can check my privileges for a particular table or schema by executing the following query:

select count(*) from (
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='data' and privilege_type = 'SELECT') as foo

and checking if the output is equal to or greater than one.

My code at the moment is as follows:

CREATE or replace function rowcount_all(schema_name text default 'public')
  RETURNS table(table_name_var text, cnt bigint) as
$$
declare
 table_name_var text;
begin
  for table_name_var in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
  loop
    if (
    select count(*) from (
    SELECT grantee, privilege_type 
    FROM information_schema.role_table_grants 
    WHERE table_name=table_name_var and privilege_type = 'SELECT') as foo
  ) >= 1 then 
    RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
       table_name_var, schema_name, table_name_var);
    end if;
  END loop;
end
$$ language plpgsql;

While executing the following query

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),
    ('schema2'), ('schema3'), ('schema4')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

I get an error that ERROR: permission denied for relation table1 where table1 is in the schema that I don't have access to. I presume that my logic in the IF statement somehow doesn't filter out the tables I don't have access to.

Answer 1

information_schema, being an SQL-standard representation of the database metadata, is useful if you're trying to build something portable, but often a little unwieldy if you're just trying to administer a Postgres server.

The easiest way to check privileges is to use the privilege check functions. I believe this will return only the tables you are able to query:

select oid::regclass::text
from pg_class
where relkind = 'r'
  and relnamespace = schema_name::regnamespace
  and has_schema_privilege(relnamespace, 'USAGE')
  and has_any_column_privilege(oid, 'SELECT')

Note that yours is one of the rare cases where has_any_column_privilege() is useful (compared with the more obvious has_table_privilege()), since you don't need full table privileges for a select count(*), just access to one of the columns (but it doesn't matter which).

Note also that the oid::regclass::text will return a table name which is already quoted and schema-qualified (if necessary), so your format() call can use a simple %s instead of %I.%I.

If you can make do with an approximate and slightly out-of-date record count, you can bypass the privilege checks entirely (and save yourself a whole lot of table scans) by querying the stats from the last VACUUM run:

select
  oid::regclass::text,
  reltuples
from pg_class
where relkind = 'r'
  and relnamespace = schema_name::regnamespace