Re: permission denied to create and drop user

Hi,

On Tue, Feb 2, 2021 at 10:30 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Joao Miguel Ferreira <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> writes:
> > I have just installed postgresql on Debian stable (from debian apt
> > archives). It seems that the postgres user does not have permissions to
> > DROP/CREATE USER. I was expecting the postgres user to be a superuser but
> > something seems weird. my postgres user does not have the usual superuser
> > attributes.
>
> That is weird. Maybe just drop the cluster and re-initdb?
>
> It might be worth checking the debian postgres package's documentation to
> see if they're throwing you some kind of curveball. One thing I was about
> to suggest is that the bootstrap superuser might not be named postgres
> (it'll be named after whichever OS user ran initdb). However, your "\du"
> output pretty clearly shows you have no superuser, and that's just odd.
>
> regards, tom lane
>

I'm sorry about the confusion. I have just realized that the loss of
superuser attributes was due to my dump file.

Debian does the right thing. During "apt install" it is possible to see the
log lines stating that the superuser is actually postgres. that seems fine.

But... my dump file contains some agressive commands that are actually
making a reall mess. here they are:

CREATE ROLE pgcon;
ALTER ROLE pgcon WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
NOREPLICATION NOBYPASSRLS;

that is why I messed up my database. Lucky it is not critical at all. I can
start all over again, no problem.

maybe "pg_dumpall" has options to avoid those changes?