0

I've got a backup from PostgreSQL 15 that I'm trying to get imported into PostgreSQL 16. One of the breaking changes between the versions is to "Restrict the privileges of CREATEROLE and its ability to modify other roles"

Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION permission.

This means that previous dumps of the database have role granting commands throw errors. The sql was obtained with pg_dumpall --roles-only (--no-owner has no effect):

GRANT role TO user1 GRANTED BY user0;
ERROR:  permission denied to grant privileges as role "user0"
DETAIL:  The grantor must have the ADMIN option on role "role".

I've tried granting the roles with admin so I won't have this problem when we try to grant in the future, but it puts the commands out of order so they still fail on restore:

GRANT role TO user1 GRANTED BY user0;
GRANT role TO user2 GRANTED BY user0;
GRANT role TO user3 GRANTED BY user0;
GRANT role TO user0 WITH ADMIN OPTION GRANTED BY [me];

Is there any way to leave off the GRANTED BY in the commands or any other way to fix this aside from combing through permissions in PostgreSQL 15 so they import without issues?

Edit: user0 is already a superuser

2
  • You have to restore the dump as a superuser to make that work reliably. You could probably also make it work by granting complicated permissions and membership to a normal role, but that is tricky. Commented Apr 25 at 5:31
  • @LaurenzAlbe The restore is run by decompressing the script and piping it to psql -U postgres, so it should be running as the default superuser
    – GammaGames
    Commented Apr 25 at 14:27

1 Answer 1

1

You have two options:

  1. Explicitly grant the role WITH GRANT OPTION to user0 in the old database before you dump the roles:

    GRANT role TO user0 WITH ADMIN OPTION;
    

    Then the dump will contain

    GRANT role TO user0 WITH ADMIN OPTION GRANTED BY postgres;
    GRANT role TO user1 GRANTED BY user0;
    

    and the restore will work without error.

    I tried it on PostgreSQL v15, then dumped and restored to v16.

  2. Use pg_dumpall from v16 to dump the roles. Then the restore should work without errors.

3
  • Option #1 with v15 pg_dumpall still has the commands out of order so they error, but rearranging a handful of lines manually isn't an issue when migrating versions. As you said in #2 the dump from v16 works as expected, thanks!
    – GammaGames
    Commented Apr 25 at 17:01
  • I tested your case with v15, and it worked as expected. Perhaps your real situation is different from what you wrote in the question. Commented Apr 26 at 2:44
  • Overall it's an export from 14 into 15, then granted roles WITH ADMIN OPTION, then exported from 15 into 16. So it could be something specific with that process! Thanks for the help 🙏 I'll do some more testing but in the end it's at least working
    – GammaGames
    Commented Apr 26 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.