How to grant execute on pg_backup_start

Get Help
1

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

2

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
3

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
4

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
5

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