PostgreSQL - default superuser privileges were removed.

Last week a colleague contacted me about a PostgreSQL installation where the privileges of the postgres user suddenly "disappeared".

I searched around a bit and found this really useful article in stackoverflow.

However, his server was Windows, so I tried and replicated this issue on a Windows 2019 server.

First I opened pg admin and tried to assign permissions to the postgres user but I got the following error:

So as suggested in the article, I stopped PostgreSQL.

I also checked the properties of the service looking for my PostgreSQL path

and the datadir

As we are going to need these later.

So using the command line I cd to the Postgres Path

cd "c:\Program Files\PostgreSQL\10\bin"

and from there I executed the following.

postgres --single -D "c:\Program Files\PostgreSQL\10\Data"

and I got the following error:

Since PostgreSQL cannot be started using an admin user I changed the profile to the postgres user using this command:

runas /nonprofile /user:postgres cmd

Therefore, a new window was opened.

From this new window I cd to the directory

cd "c:\Program Files\PostgreSQL\10\bin"

and I tried again.

postgres --single -D "c:\Program Files\PostgreSQL\10\Data"

And another error, this time related to security as the postgres user did not have access to the /Data folder, hence, it couldn't find the config file.

So I assigned FULL Control privileges to the folder.

and tried again

postgres --single -D "c:\Program Files\PostgreSQL\10\Data"

and it worked like a charm.

Then I executed the alter command.

alter user posgres with superuser ;

and once I started the service and tried pg admin again.

The postgres user was a superuser again!

One thing not to forget is to remove the postgres permissions from the /data folder.