1

Postgres 16 has user ingmar which is marked as superuser and has create role rights:

CREATE ROLE ingmar WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  NOREPLICATION
  BYPASSRLS
  ENCRYPTED PASSWORD 'md5aaaaaaa790012b7aa47017f124e263d8';

GRANT "240316_owner" TO ingmar;
GRANT eeva_owner TO ingmar WITH ADMIN OPTION;

User ingmar creates role "ingmar.e" using

CREATE ROLE "ingmar.e" LOGIN

and tries to grant eeva_owner role to it using

GRANT "eeva_owner" TO "ingmar.e"

This command throws error

ERROR: permission denied to grant role "eeva_owner" DETAIL: Only roles with the ADMIN option on role "eeva_owner" may grant this role.; Error while executing the query

How user ingmar can grant role eeva_owner to user "ingmar.e" ?

Using

PostgreSQL 16.4 (Debian 16.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0

If new non-superuser is added in Postgres 16 it can add user and invoke GRANT to eeva_owner successfully.

User ingmar was migrated from previous version where it was not superuser but had createrole rights. GRANT throws error in Postgres 16. Marking user as superuser is Postgres 16 does not fix error.

Database was migrated from old version using pg_dump/pg_restore. Users are restored using dump global objects in pgadmin and running output in version 16

In old database user didt had WITH ADMIN OPTION right.

6
  • Why even bother with GRANT "240316_owner" TO ingmar; and GRANT eeva_owner TO ingmar WITH ADMIN OPTION;? ingmar is a SUPERUSER it has all rights to begin with. Commented Sep 1, 2024 at 21:00
  • For ingmar this command fails with error message in question. Other user can add user and invoke GRANT successfully. Why this command fails for one superuser? It worked before version 16. Database was migrated from old version using pg_dump/pg_restore. Users are restored using dump global objects in pgadmin and running output in version 16. Commented Sep 2, 2024 at 6:52
  • 1
    I changed question title to Why one superuser cannot use GRANT in Postgres 16 Commented Sep 2, 2024 at 7:01
  • User ingmar was migrated from previous version where it was not superuser but had createrole rights. Invoking GRANT by this user throws error in Postgres 16. Marking user as superuser is Postgres 16 does not fix error. If new user is added in Postgres 16 it works. Commented Sep 2, 2024 at 7:19
  • 1
    Please add a minimal reproducible example Commented Sep 2, 2024 at 7:28

0

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.