Thread: permission denied to create and drop user
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.
postgres=> DROP USER foo;
ERROR: permission denied to drop role
postgres=> DROP USER postgres;
ERROR: permission denied to drop role
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.
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
ERROR: permission denied to create role
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
pgcon | | {}
postgres | | {}
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
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:
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