postgresql - ERROR: must be owner of table, but I am superuser - Stack Overflow

admin管理员组

文章数量:1332352

I have an postgres 16 DB in Docker container with volume. I wanted to hide standard postgres user and changed it's name to super (as example). Now I try to make something with any tables (DDL operations like ALTER TABLE) but it throws:

must be owner of table ...

I tried to give some privileges to my user but it throws for all commands:

permission denied

I have tries:

ALTER USER super WITH SUPERUSER;

But got:

Only roles with the SUPERUSER attribute may alter roles with the SUPERUSER attribute.

Then I run query:

SELECT rolname, rolsuper FROM pg_roles WHERE rolname = 'super';

It shows me:

rolename | rolesuper
--------------------
super    | true

Well. My role is superuser. I've even tried:

ALTER ROLE super SET ROLE super;

But it still shows:

Only roles with the SUPERUSER attribute may alter roles with the SUPERUSER attribute.

I cannot understand what can I do to fix that and have DDL or SUPERUSER role.

I have an postgres 16 DB in Docker container with volume. I wanted to hide standard postgres user and changed it's name to super (as example). Now I try to make something with any tables (DDL operations like ALTER TABLE) but it throws:

must be owner of table ...

I tried to give some privileges to my user but it throws for all commands:

permission denied

I have tries:

ALTER USER super WITH SUPERUSER;

But got:

Only roles with the SUPERUSER attribute may alter roles with the SUPERUSER attribute.

Then I run query:

SELECT rolname, rolsuper FROM pg_roles WHERE rolname = 'super';

It shows me:

rolename | rolesuper
--------------------
super    | true

Well. My role is superuser. I've even tried:

ALTER ROLE super SET ROLE super;

But it still shows:

Only roles with the SUPERUSER attribute may alter roles with the SUPERUSER attribute.

I cannot understand what can I do to fix that and have DDL or SUPERUSER role.

• postgresql
• ddl
• postgresql-16

Share Improve this question Follow edited Nov 21, 2024 at 20:44 Alexander Plekhanov asked Nov 21, 2024 at 0:06 Alexander PlekhanovAlexander Plekhanov 6599 bronze badges 10

• What role are you when you do ALTER USER super WITH SUPERUSER;? – Adrian Klaver Commented Nov 21, 2024 at 0:25
• I wanted to hide standard postgres user That sounds like security through obscurity. Why do you think this has any value? – Frank Heikens Commented Nov 21, 2024 at 1:20
• @AdrianKlaver I have always been super (I have no other superusers). – Alexander Plekhanov Commented Nov 21, 2024 at 9:40
• @FrankHeikens I think it's not the unknown. I know the new superuser name, and I want no one to know it except me, so they can't guess the password. Of course, I have a host check on my database, but I'm afraid that in some cases it won't be enough. But of course I could have missed something. – Alexander Plekhanov Commented Nov 21, 2024 at 9:42
• How does your question relate to dcl? ("DCL is the language for command lines and batch files of RSX and VMS operating systems; it stands for Digital Command Language (Digital was a computer and software vendor 1958-1998).") – HABO Commented Nov 21, 2024 at 14:13

 |  Show 5 more comments

3 Answers 3

Sorted by: Reset to default 0

The case is simple: your super user (the renamed postgres user) is no superuser.

You can verify that with \du in psql or with the query

SELECT usesuper FROM pg_user WHERE usename = 'super';

Nothing in your question clarifies the current role you are operating with. The remaining explanation: you are actually operating with a different role, which lacks the superuser privilege. Check with:

SELECT current_user, session_user;

current_user is the relevant one.

Well, one sleepless night and I found the answer. Let's inspect my docker compose file I use to deploy my app with db:

networks:
  app-network:
    driver: bridge

services:
  app-db:
    container_name: 'app-db'
    image: postgres:16
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
      POSTGRES_HOST_AUTH_METHOD: md5
    ports:
      - "5432:5432"
    networks:
      - app-network
    volumes:
      - postgres-data:/var/lib/postgresql/data

  app:
    ...

I've renamed postgres user in db to super but I did not renamed it in docker compose file. I thought if I have a db container volume, all data will be stored in it, including users and roles. And it was but... It seems that superuser has its own rules. So now my postgres db knows about super user. It knows that it is SUPERUSER, but it is not, because on fresh deploy with docker-compose up DB was created not by super, but by postgres user (from docker compose file). When I changed pg user credentials to super in docker compose file, it works fine. Also, I don't want to store my credentials in docker compose file, because I want to store this file in git repository. So I change db credentials in this file before any deploy from now on. Thanks everyone and good luck!

本文标签： postgresqlERROR must be owner of tablebut I am superuserStack Overflow