How to grant execute on pg_backup_start

i am trying to setup pgbackrest with ansible and i have stumbled upon GRANTing EXECUTE on the function pg_backup_start

i have the following ansible snippet:

  - name: GRANT EXECUTE on function pg_start_backup
    community.postgresql.postgresql_privs:
      database: postgres
      privs: EXECUTE
      schema: pg_catalog
      objs: "pg_start_backup(label text: fast boolean)"
      type: function
      roles: ${dockeruser}
      login_user: ${db_admin_user}
      login_password: ${db_admin_password}
      login_host: localhost

which gives the following error. which indicates that the function doesnt exist

ā”‚     "invocation": {
ā”‚         "module_args": {
ā”‚             "ca_cert": null,
ā”‚             "connect_params": {},
ā”‚             "database": "postgres",
ā”‚             "fail_on_role": true,
ā”‚             "grant_option": null,
ā”‚             "login_host": "localhost",
ā”‚             "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
ā”‚             "login_unix_socket": "",
ā”‚             "login_user": "postgres",
ā”‚             "objs": "pg_start_backup(label text: fast boolean)",
ā”‚             "password": "",
ā”‚             "port": 5432,
ā”‚             "privs": "EXECUTE",
ā”‚             "roles": "docker-service",
ā”‚             "schema": "pg_catalog",
ā”‚             "session_role": null,
ā”‚             "ssl_cert": null,
ā”‚             "ssl_key": null,
ā”‚             "ssl_mode": "prefer",
ā”‚             "state": "present",
ā”‚             "target_roles": null,
ā”‚             "trust_input": true,
ā”‚             "type": "function"
ā”‚         }
ā”‚     },
ā”‚     "msg": "Cannot execute SQL 'GRANT EXECUTE ON function \"pg_catalog\".\"pg_start_backup\"(label text, fast boolean) TO \"docker-service\";': function pg_catalog.pg_start_backup(text, boolean) does not exist\n"
ā”‚ }

but when i try with the following ansible:

  - name: GRANT EXECUTE on function pg_start_backup
    community.postgresql.postgresql_privs:
      database: postgres
      privs: EXECUTE
      schema: pg_catalog
      objs: "pg_start_backup"
      type: function
      roles: ${dockeruser}
      login_user: ${db_admin_user}
      login_password: ${db_admin_password}
      login_host: localhost

i get the following error:

ā”‚     "changed": false,
ā”‚     "invocation": {
ā”‚         "module_args": {
ā”‚             "ca_cert": null,
ā”‚             "connect_params": {},
ā”‚             "database": "postgres",
ā”‚             "fail_on_role": true,
ā”‚             "grant_option": null,
ā”‚             "login_host": "localhost",
ā”‚             "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
ā”‚             "login_unix_socket": "",
ā”‚             "login_user": "postgres",
ā”‚             "objs": "pg_start_backup",
ā”‚             "password": "",
ā”‚             "port": 5432,
ā”‚             "privs": "EXECUTE",
ā”‚             "roles": "docker-service",
ā”‚             "schema": "pg_catalog",
ā”‚             "session_role": null,
ā”‚             "ssl_cert": null,
ā”‚             "ssl_key": null,
ā”‚             "ssl_mode": "prefer",
ā”‚             "state": "present",
ā”‚             "target_roles": null,
ā”‚             "trust_input": true,
ā”‚             "type": "function"
ā”‚         }
ā”‚     },
ā”‚     "msg": "Illegal function / procedure signature: \"pg_start_backup\"."
ā”‚ }

i have search through internet and chatgpt for an example but my skills fails me :slight_smile:

i tried to grant for a function that has no paramters pg_backend_pid

  - name: GRANT EXECUTE on function pg_start_backup
    community.postgresql.postgresql_privs:
      database: postgres
      privs: EXECUTE
      schema: pg_catalog
      objs: "pg_backend_pid()"
      type: function
      roles: ${dockeruser}
      login_user: ${db_admin_user}
      login_password: ${db_admin_password}
      login_host: localhost

which succeeds

Hi Freddik,

I hope you are well,

It seems that the Postgres function name is not correct.

Please, look at the official docs https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP

1 Like

hi sergio,

thanks!! i have read the same document and looked at the function signature in pgadmin without realizing that i had it wrong. the brain just switched the name to the correct.
i copied it from a guide and it was for an older postregres.
the worst thing is that i actually had changed the name to the correct spelling but had tried with another signature

anyway many thanks for your extra eyes. it is tested and works :tada:

1 Like

Yes, the former way is deprecated, pg_start_backup() was added in PostgreSQL 8.0 and replaced in PostgreSQL 15 by [pg_backup_start()](https://pgpedia.info/p/pg_backup_start.html).

Iā€™m glad your PB is working well!!

Cheers!

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.