Enable log_statement = 'ddl' in postgres

[bickelj]

I don't think we have this enabled in production and it would be sort of a "level 0" of audit logs in comparison to #1559, #1612, #1613. I would not expect these to be stored in the database or surfaced via API, but eventually perhaps a log aggregation tool could provide an interface.

[bickelj]

I thought this would be straightforward, but I cannot seem to find how to set the log_statement option with DO's managed pg. I also see this, which hints that it's already enabled: https://ideas.digitalocean.com/managed-database/p/allow-us-to-specify-which-postgres-logs-we-want

[bickelj]

I did a bit of digging and then asked DO.

Looking directly at the related settings via psql, I see log_statement = 'none':

pdc=> select name, setting, unit from pg_settings where name like '%statement%';
               name                | setting | unit 
-----------------------------------+---------+------
 log_min_duration_statement        | 1000    | ms
 log_min_error_statement           | error   | 
 log_statement                     | none    | 
 log_statement_sample_rate         | 1       | 
 log_statement_stats               | off     | 
 pg_stat_statements.max            | 5000    | 
 pg_stat_statements.save           | on      | 
 pg_stat_statements.track          | top     | 
 pg_stat_statements.track_planning | off     | 
 pg_stat_statements.track_utility  | on      | 
 pgaudit.log_nested_statements     | on      | 
 pgaudit.log_statement             | on      | 
 pgaudit.log_statement_once        | off     | 
 statement_timeout                 | 0       | ms
(14 rows)

I thought I remembered seeing the option in the advanced PG options in the API docs here but I'm not seeing it.

Asked here

[bickelj]

Digital Ocean managed PostgreSQL does not support log_statement = 'ddl' but they say they're working on it in conjunction with the pgaudit extension.

[bickelj]

We'll need to run our own postgres instance, e.g. on a DO droplet, assume that schema changes will be shown elsewhere (such as the migrations table), or wait for the feature mentioned in this reply from DO:

I hope you’re doing well! Thank you for reaching out to DigitalOcean Support — we’re always here to help.

I understand you’d like to update the log_statement setting in your Managed Database cluster. Rest assured, we’re happy to provide guidance.

At the moment, log_statement is only available as part of the pgaudit feature. This feature is currently under development and going through internal validation before release.

We appreciate your patience — we recommend checking back in a few months, either through this ticket or by keeping an eye on our Changelog for updates.

In the meantime, you may consider using log_min_duration_statement as an alternative. This setting logs statements that exceed a specified duration (in milliseconds). Setting it to -1 will disable it entirely.

We sincerely apologize for any inconvenience this may cause. If you’d like to see features like this prioritized, feel free to share your feedback on our Ideas Portal.

Thanks again for reaching out, and please don’t hesitate to let us know if you have any other questions!

log_min_duration_statement does not really solve because if we set it to 0ms, we'll log a million undesired statements for every one desired statement, and if we set it higher, we'll log a thousand undesired statements and miss the desired ones.

[slifty]

We moved this to Later from Blocked just because Blocked indicates it is something we are prioritizing to accomplish now; this may still be blocked if / when we pick it up again.