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.
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!