How can I get the database creation time at Flexible Postgresql database?

Major Péter 20 Reputation points
2023-10-06T07:45:06.93+00:00

We have to import databases day by day to an Azure Database for PostgreSQL flexible server. We would like to check the creation time of imported databases but I don't have permission for system objects I needed. I need the creation time of database, not the server!

At On premise databases I use the query below, but at Azure I get this error message: SQL Error [42501]: ERROR: permission denied for function pg_stat_file

SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database;

Azure Database for PostgreSQL
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ShaktiSingh-MSFT 13,836 Reputation points Microsoft Employee
    2023-10-06T09:01:29.3366667+00:00

    Hi Major Péter,

    Welcome to Microsoft Q&A forum and thanks for using Azure Services.

    As I understand, you are getting error "SQL Error [42501]: ERROR: permission denied for function pg_stat_file" when executing query in Azure Database for PostgreSQL Flexible Server.

    By default pg_stat_file() is restricted to superusers, but other users can be granted the EXECUTE permission to run this function.

    Check user:

    SELECT current_user;																											
    

    GRANT EXECUTE ON FUNCTION pg_stat_file(text) TO youruser;

    OR

    Try:

    ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON FUCNTION TO readonly;
    

    Check and let us know if this helps in your case.

    Thanks


  2. ShaktiSingh-MSFT 13,836 Reputation points Microsoft Employee
    2023-10-09T04:20:50.74+00:00

    Hi Major Péter,

    Thanks for the reply.

    Ensure to grant permission on working database, not on postgres.

    Thanks

    0 comments No comments