We use Postgres 9.5 on RDS and have a use case to turn off SQL logging when running bulk inserts. Bulk insert SQL statement is large and not a lot of benefit of logging it since we know it will take more time than our set log_min_duration.
As mentioned in this post, I tried doing set_config via psql client. Unfortunately I get permission denied. Logged in user via psql is a member of rds_superuser.
SELECT current_setting('log_statement');
┌─────────────────┐
│ current_setting │
├─────────────────┤
│ mod │
└─────────────────┘
(1 row)
SELECT set_config('log_statement', 'mod', false);
ERROR: 42501: permission denied to set parameter "log_statement"
LOCATION: set_config_option, guc.c:5804
Any ideas on why we get permission denied and secondly do folks know if its possible to change this setting at run time in postgres on RDS
RDS uses a different permission model to vanilla Postgres, so the rds_superuser role doesn't actually have all the permissions that a "real" superuser would have. In effect, RDS is a "managed" database hosting solution, and full access would give you abilities that would break or compromise that management. Instead, this role is granted a list of specific permissions.
For most configuration, you have to use what Amazon call "Parameter Groups", which are managed through the AWS console or API.
There is a specific page on logging in Postgres RDS which gives some examples of using Parameter Groups to set the log_statement option.
This means you can't change the setting dynamically in your database connection, e.g. in a pl/pgsql function, but you could change it as part of a batch job using the AWS API or official CLI client.