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

Question

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;

Answer 1

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

Answer 2

Hi Major Péter,

Thanks for the reply.

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

Thanks