GCP pg_stat_statements insufficient privileges and read replicas

Question

I'm running postgres on GCP SQL service. I have a main and a read replica.

I've enabled pg_stat_statements on the main node but still I get messages that I have insufficient privileges for almost each and every row.

When i've tried to enable the extension on the read replica it gave me an error that: cannot execute CREATE EXTENSION in a read-only transaction.

All of those actions I have tried to do with the highest privilege user that I have (using a user who is a member of cloudsqlsuperuser, basically same as the default postgres user)

So I have 2 questions:

1. How do I fix the privileges issue so I can see the statistics in the table?
2. How do I enable extension on the read replica?

Thanks!

Answer 1

After having run some more tests on postgres 9.6, I have also obtained the messages <insufficient privilege>.

I have run the following query on both postgres 9.6 and 13 and obtained different results:

SELECT userid, usename, query
FROM pg_stat_statements
INNER JOIN pg_catalog.pg_user
ON userid = usesysid;

I noticed in postgres 9.6 that the queries I cannot see come from the roles/users cloudsqlagent and cloudsqladmin(preconfigured Cloud SQL postgres roles).

This does not happen with postgres 13 or better said versions 10 and higher and it is because when using EXTENSION pg_stat_statements, SQL statements from all users are visible to users with the cloudsqlsuperuser. This is the behavior of the product across different versions and it is described in the blue box of this link.

Basically only in version 9.6 the SQL statements from all users are NOT visible to users with the cloudsqlsuperuser role.

So if I enable it on the master, it should be enabled on the replica as well?

Yes, after enabling the extension in the master you can connect to the replica and check with the following command that pg_stat_statements has been enabled:

SELECT * FROM pg_extension;

If you would like a more uniform behavior across postgres versions or if you strongly need the SQL statements from all users to be visible to the cloudsqlsuperuser role, I would recommend then creating a public issue tracker with the template feature request.

I hope you find this useful.

Answer 2

On the permissions side of things, cloudsqlsuperuser is not a real superuser (but is as close as you'll get in GCP cloudsql). Due to this I've sometimes found that I've needed to explicitly grant it access to objects / roles to be able to access things.

Therefore I'd try doing:

GRANT pg_read_all_stats TO cloudsqlsuperuser;

I'm not too sure about how to enable on the read replica unfortunately.

However, you might be interested in the recently released insights feature https://cloud.google.com/sql/docs/postgres/insights-overview - I haven't been able to play with this properly yet, but from what I've seen it's pretty nifty.