permission denied to create and drop user

On 2/2/21 1:58 PM, Joao Miguel Ferreira wrote:

Hello all,

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.

Please see my results bellow:

root@deb10tp:/# id
uid=0(root) gid=0(root) groups=0(root)
root@deb10tp:/# pwd
/
root@deb10tp:/# su postgres
postgres@deb10tp:/$ psql
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.

Not sure how you can do above when below the postgres user does not have
LOGIN attribute?

What where the exact steps you took when you did the install?

postgres=> DROP USER foo;
ERROR:  permission denied to drop role
postgres=> DROP USER postgres;
ERROR:  permission denied to drop role
postgres=> CREATE USER foo;
ERROR:  permission denied to create role
postgres=> \du
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 pgcon     |            | {}
 postgres  |            | {}

How do I get a superuser/postgres that is capable to CREATE / DROP
another user? or a user that might allowed to do anything?

Thank you
Joao

--
Adrian Klaver
adrian.klaver@aklaver.com

Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.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

Hi,

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

Joao Miguel Ferreira <joao.miguel.c.ferreira@gmail.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?

On 2/2/21 2:43 PM, Joao Miguel Ferreira wrote:

Hi,

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?

That would be attributing AI abilities to pg_dumpall that it does not
have. Those commands got there from someone in the original database
tinkering with grants.

--
Adrian Klaver
adrian.klaver@aklaver.com