Azure PostgreSQL Flexible server - permission denied for table pg_authid

Question

Hello,

I am trying to get the object count of all the schema objects in PostgreSQL as part of validation after migrating from Oracle to Azure PostgreSQL. But, when I am executing the select statement, I am getting below error as the admin user.

permission denied for table pg_authid

Is it not accessible in Azure PostgreSQL cluster? Is there any other way I can get the consolidated count of all object types in a schema.

Thanks

Answer 1

Thanks for your question.

Please find the query in the blog below if it’s helpful in your scenario.

https://dataedo.com/kb/query/postgresql/list-of-tables-by-the-number-of-rows

You should get the table_schema, table_name and rows details

Let us know if this helps!

Answer 2

@Sivakesava Naidu, Kuchi What table are you querying from, can you please share more information?

Can you try something like this

select count(*)

from information_schema.tables;

Regards

Geetha

Answer 3

Hi Geetha,

I am trying to run below command to get count of all object types (Tables, Indexes, Procedures, Functions etc..).

Below is the error I am getting.

permission denied for table pg_authid

WITH schema_objects AS (SELECT

c.oid,

nspname AS schema_name,

CASE WHEN relkind='r' THEN 'TABLE'

WHEN relkind='i' THEN 'INDEX'

WHEN relkind='S' THEN 'SEQUENCE GENERATOR'

WHEN relkind='t' THEN 'TOAST TABLE'

WHEN relkind='v' THEN 'VIEW'

WHEN relkind='m' THEN 'MATERIALIZED VIEW'

WHEN relkind='c' THEN 'COMPOSITE TYPE'

WHEN relkind='f' THEN 'FOREIGN TABLE'

WHEN relkind='p' THEN 'PARTITIONED TABLE'

WHEN relkind='i' THEN 'PARTITIONED INDEX'

END AS type

FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid

INNER JOIN pg_class AS c ON n.oid=c.relnamespace

WHERE (nspname='public' OR rolname='postgres')

UNION ALL SELECT

pt.oid,

nspname AS schema_name,

CASE WHEN typtype='b' THEN 'BASE TYPE'

WHEN typtype='c' THEN 'COMPOSITE TYPE'

WHEN typtype='d' THEN 'DOMAIN'

WHEN typtype='e' THEN 'ENUMERATION TYPE'

WHEN typtype='r' THEN 'RANGE TYPE' END AS type

FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid

INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace

WHERE (nspname='public' OR rolname='postgres')

UNION ALL SELECT

pc.oid,

nspname AS schema_name,

CASE WHEN prokind='f' THEN 'FUNCTION'

WHEN prokind='p' THEN 'PROCEDURE'

WHEN prokind='a' THEN 'AGGREGATE FUNCTION'

WHEN prokind='w' THEN 'WINDOW FUNCTION' END AS type

FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid

INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace

WHERE (nspname='public' OR rolname='postgres'))

SELECT schema_name, type,

Count(*) FILTER (WHERE EXISTS (SELECT 1

FROM pg_catalog.pg_depend d

WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND

d.objid=schema_objects.oid)) AS number_of_objects_in_an_extension,

Count(*) FILTER (WHERE NOT EXISTS (SELECT 1

FROM pg_catalog.pg_depend d

WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND

d.objid=schema_objects.oid)) AS number_of_user_defined_objects,

Count(*) AS total_number_of_objects

FROM schema_objects

GROUP BY CUBE (schema_name, type)

ORDER BY schema_name, type, Count(*) DESC;

Answer 4

Hi @Sivakesava Naidu, Kuchi, Thanks for providing additional information.

Please check the document https://learn.microsoft.com/EN-us/azure/postgresql/single-server/how-to-create-users only Microsoft is part of the super user role.",  you are getting the error "permission denied for table pg_authid" because pg_audit contains database passwords, hence it has restrictive permissions. So only a superuser can access that table.

Regards

Geetha