how can I get permission to visit pg_current_logfile() in my Azure Cosmos DB for PostgreSQL Cluster

Question

how can I get permission to visit pg_current_logfile() in my Azure Cosmos DB for PostgreSQL Cluster

Rose 0

I want to access my log file in my database (Azure Cosmos DB for PostgreSQL Cluster), but my admin account don't have permission to visit pg_current_logfile().

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-03T16:00:47.7433333+00:00
Hi @ Rose,

As I understand that you are trying to access log file in your Database (Azure Cosmos DB for PostgreSQL Cluster), but your admin account don't have permission to visit pg_current_logfile().

By following the below steps, you can access the log files by enabling diagnostic setting.

Below are the steps to enable diagnostic settings:

Go to your Azure Cosmos DB for PostgreSQL cluster's page in the Azure portal.

Select "Diagnostic settings" under Monitoring from the left menu.

Select "Add diagnostic setting".

Enter a name for the new diagnostic setting.

Check the "PostgreSQL Server Logs" box.

Check the "Send to Log Analytics workspace" box.

Select the Log Analytics workspace you want to send the logs to.

Select "Save".

Once you have enabled the diagnostic setting, you can view and filter the logs using Kusto queries. Here are the detailed steps to do this:

On your Azure Cosmos DB for PostgreSQL cluster's page in the Azure portal, select "Logs" from the left menu.

Close the opening splash screen and the query selection screen.

Paste the following query into the query input box:

AzureDiagnostics

Select "Run".

This query will return all the PostgreSQL logs for your cluster. Please note that you may need to adjust the query to filter the logs based on your specific needs.

For your better understanding I am providing the Documentation: Logs in Azure Cosmos DB for PostgreSQL.

I hope this information helps, please let us know if you have any further queries.
Rose 0 Reputation points

2024-09-04T09:09:53.37+00:00

I followed the documentation (also set log_statments = MOD) , but after making those settings, the search for "AzureDiagnostics" still shows no data.

Could you provide me with a method or direction on how I can troubleshoot this issue? Are there any possible settings that might have been missed and not mentioned in the documentation?
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-04T10:50:43.63+00:00

I'm sorry to hear that,

However, there are a few things you can check to troubleshoot this issue:

Make sure that you have waited for at least 10-15 minutes after enabling the diagnostic setting for the logs to start appearing in the Log Analytics workspace. It may take some time for the logs to be processed and sent to the workspace.

Check if you have selected the correct Log Analytics workspace when enabling the diagnostic setting. You can verify this by going to the "Diagnostic settings" page and checking the workspace name under the "Destination" column.

Verify that the PostgreSQL Server Logs diagnostic setting is enabled for the correct cluster. You can do this by going to the "Diagnostic settings" page and checking if the setting is enabled for the correct cluster.

Check if there are any errors or warnings related to the diagnostic setting in the Azure portal. You can do this by going to the "Activity log" page and filtering for events related to the diagnostic setting.

If the issue still persists, please try to connect to your PostgreSQL database using psql or pgAdmin4 which are PostgreSQL client tools and give the query as SELECT pg_current_logfile().

I hope this information helps, please let us know if you have any further queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-05T12:05:49.54+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-06T15:15:19.4066667+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rose 0 Reputation points

2024-09-20T03:18:44.06+00:00

Hi @Sai Raghunadh M
I've check though the steps you have provided.
And I also tried to run a query as SELECT pg_current_logfile() using pgAdmin4 and the admin account, but its still giving 42501 permission denied for function.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-20T16:15:11.0366667+00:00
Hi @ Rose,

I can see that you are still facing the error (42501 permission denied for function.)

A common scenario where you might encounter the 42501 error is when a user attempts to select data from a table without having the appropriate SELECT permission.

Check if the user has SELECT permissions on the table

To resolve this error, grant SELECT permission to the user on the table.

Example:

To grant SELECT permission on the table "my_table" to the user "my_user", use the following SQL command:

GRANT SELECT ON my_table TO my_user;

I hope this information helps, please do let us know if you have any Queries.
Rose 0 Reputation points

2024-09-23T03:13:56.4233333+00:00

If this case 'SELECT pg_current_logfile()', what table should I grant to? The account can normally select data from the database, but still not having the permissino to call SELECT pg_current_logfile()
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-23T12:43:00.6466667+00:00

Hi @ Rose,

Sorry for the confusion, above (To grant SELECT permission on the table "my_table" to the user "my_user", use the following SQL command: GRANT SELECT ON my_table TO my_user;), )I just gave it as an example.

So, it seems that the default user not being an admin of those roles If you would like to use it on your cluster(s) now, can you please open a support request, and you can mention as per the product development team, this needs to be enabled it as an exception?

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-24T13:58:51.6833333+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-25T15:12:16.3866667+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-03T16:00:47.7433333+00:00

Hi @ Rose,

As I understand that you are trying to access log file in your Database (Azure Cosmos DB for PostgreSQL Cluster), but your admin account don't have permission to visit pg_current_logfile().

By following the below steps, you can access the log files by enabling diagnostic setting.

Below are the steps to enable diagnostic settings:

Go to your Azure Cosmos DB for PostgreSQL cluster's page in the Azure portal.

Select "Diagnostic settings" under Monitoring from the left menu.

Select "Add diagnostic setting".

Enter a name for the new diagnostic setting.

Check the "PostgreSQL Server Logs" box.

Check the "Send to Log Analytics workspace" box.

Select the Log Analytics workspace you want to send the logs to.

Select "Save".

Once you have enabled the diagnostic setting, you can view and filter the logs using Kusto queries. Here are the detailed steps to do this:

On your Azure Cosmos DB for PostgreSQL cluster's page in the Azure portal, select "Logs" from the left menu.

Close the opening splash screen and the query selection screen.

Paste the following query into the query input box:

AzureDiagnostics

Select "Run".

This query will return all the PostgreSQL logs for your cluster. Please note that you may need to adjust the query to filter the logs based on your specific needs.

For your better understanding I am providing the Documentation: Logs in Azure Cosmos DB for PostgreSQL.

I hope this information helps, please let us know if you have any further queries.
Rose 0 Reputation points

2024-09-04T09:09:53.37+00:00

I followed the documentation (also set log_statments = MOD) , but after making those settings, the search for "AzureDiagnostics" still shows no data.

Could you provide me with a method or direction on how I can troubleshoot this issue? Are there any possible settings that might have been missed and not mentioned in the documentation?
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-04T10:50:43.63+00:00

I'm sorry to hear that,

However, there are a few things you can check to troubleshoot this issue:

Make sure that you have waited for at least 10-15 minutes after enabling the diagnostic setting for the logs to start appearing in the Log Analytics workspace. It may take some time for the logs to be processed and sent to the workspace.

Check if you have selected the correct Log Analytics workspace when enabling the diagnostic setting. You can verify this by going to the "Diagnostic settings" page and checking the workspace name under the "Destination" column.

Verify that the PostgreSQL Server Logs diagnostic setting is enabled for the correct cluster. You can do this by going to the "Diagnostic settings" page and checking if the setting is enabled for the correct cluster.

Check if there are any errors or warnings related to the diagnostic setting in the Azure portal. You can do this by going to the "Activity log" page and filtering for events related to the diagnostic setting.

If the issue still persists, please try to connect to your PostgreSQL database using psql or pgAdmin4 which are PostgreSQL client tools and give the query as SELECT pg_current_logfile().

I hope this information helps, please let us know if you have any further queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-05T12:05:49.54+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-06T15:15:19.4066667+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rose 0 Reputation points

2024-09-20T03:18:44.06+00:00

Hi @Sai Raghunadh M
I've check though the steps you have provided.
And I also tried to run a query as SELECT pg_current_logfile() using pgAdmin4 and the admin account, but its still giving 42501 permission denied for function.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-20T16:15:11.0366667+00:00

Hi @ Rose,

I can see that you are still facing the error (42501 permission denied for function.)

A common scenario where you might encounter the 42501 error is when a user attempts to select data from a table without having the appropriate SELECT permission.

Check if the user has SELECT permissions on the table

To resolve this error, grant SELECT permission to the user on the table.

Example:

To grant SELECT permission on the table "my_table" to the user "my_user", use the following SQL command:

GRANT SELECT ON my_table TO my_user;

I hope this information helps, please do let us know if you have any Queries.
Rose 0 Reputation points

2024-09-23T03:13:56.4233333+00:00

If this case 'SELECT pg_current_logfile()', what table should I grant to? The account can normally select data from the database, but still not having the permissino to call SELECT pg_current_logfile()
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-23T12:43:00.6466667+00:00

Hi @ Rose,

Sorry for the confusion, above (To grant SELECT permission on the table "my_table" to the user "my_user", use the following SQL command: GRANT SELECT ON my_table TO my_user;), )I just gave it as an example.

So, it seems that the default user not being an admin of those roles If you would like to use it on your cluster(s) now, can you please open a support request, and you can mention as per the product development team, this needs to be enabled it as an exception?

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-24T13:58:51.6833333+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-25T15:12:16.3866667+00:00

Hi @ Rose,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

how can I get permission to visit pg_current_logfile() in my Azure Cosmos DB for PostgreSQL Cluster

Your answer