CODEWITHSUNDEEP
postgresqllogginggoogle-cloud-sqlprivileges
Eemeli Kantola
Eemeli Kantola

Reputation: 5557

How to enable Google Cloud PostgreSQL log_statements to one database role only?

I'd like to have query logging on for one specific role only. But it doesn't seem to be possible to turn it on with the powers of cloudsqlsuperuser role that the default postgres user has:

postgres=> ALTER ROLE my_log_role SET log_statement = 'all';
ERROR:  permission denied to set parameter "log_statement"

Turning log_statement on for the entire instance is doable through Cloud SQL tools, but can this be done for select role(s) instead of all of them in a managed PostgreSQL?

Upvotes: 0

Views: 539

Answers (3)

user28499254
user28499254

Reputation: 1

i was able to get this to work for my aws aurora postgresql instance by running the following command first:

grant set on parameter log_statement to <master_user>;

then i was able to run the alter role command

Upvotes: 0

Gourav B
Gourav B

Reputation: 982

As Cloud SQL for PostgreSQL is a managed service, the "superuser" access is not provided, which is required to run the below command:

postgres=> ALTER ROLE my_log_role SET log_statement = 'all';

However,

Although you can't create database users with superuser privileges, you can create database users with the cloudsqlsuperuser role which has some of those privileges

If this is a blocker, you may consider hosting PostgreSQL on a Google Compute Engine (GCE) instance, where you will have full control.

Upvotes: 1

Adelino Silva
Adelino Silva

Reputation: 930

Only superusers can change this setting. (https://www.postgresql.org/docs/9.5/runtime-config-logging.html#GUC-LOG-STATEMENT) check if cloudsqlsuperuser is a superusers running "show is_superuser;"

Superuser restrictions https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions

Upvotes: 1

Related Questions