1

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!

Max
  • 907
  • 2
  • 13
  • 27
  • I have created a Cloud SQL postgres instance (13), enabled the extension `pg-stat-statements` and then with the user postgres queried all the `pg-stat-statements` table(SELECT * FROM pg_stat_statements;) and did not receive any error messages. My postgres user is just like this: Role name: postgres, List of role attributes: Create role, Create DB, Member of:{cloudsqlsuperuser} What is the version of postgres you are using? What is the query that you are running on the pg_stat_statements? In which column does the permission message appear? – Antonio Ramirez Mar 24 '21 at 22:44
  • Besides since the read replica database is in read-only mode, you won't be able to install the extension on it directly. However, if you install the extension in the master, it will be replicated to the read replica. This is true for PostgreSQL generally, not just Cloud SQL instances. I have tested it and I can see the extension as well in the replica. – Antonio Ramirez Mar 24 '21 at 23:13
  • So if I enable it on the master, it should be enabled on the replica as well? I'm using postgres 9.6, maybe thats the reason it does not work for me? – Max Mar 25 '21 at 10:13

2 Answers2

1

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.

Michael
  • 2,189
  • 16
  • 23
  • Unfortunately query insights are not available on read replicas :( Running `GRANT pg_read_all_stats TO cloudsqlsuperuser;` did not help either, or does it not help for queries that have already been run? – Max Mar 06 '21 at 22:15
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.

Antonio Ramirez
  • 943
  • 6
  • 15