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:
![](https://blog.dbplatz.com/content/images/2021/03/image.png)
So as suggested in the article, I stopped PostgreSQL.
![](https://blog.dbplatz.com/content/images/2021/03/image-1.png)
I also checked the properties of the service looking for my PostgreSQL path
![](https://blog.dbplatz.com/content/images/2021/03/image-5.png)
and the datadir
![](https://blog.dbplatz.com/content/images/2021/03/image-6.png)
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:
![](https://blog.dbplatz.com/content/images/2021/03/image-7.png)
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.
![](https://blog.dbplatz.com/content/images/2021/03/image-8.png)
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"
![](https://blog.dbplatz.com/content/images/2021/03/image-9.png)
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.
![](https://blog.dbplatz.com/content/images/2021/03/image-10.png)
and tried again
postgres --single -D "c:\Program Files\PostgreSQL\10\Data"
![](https://blog.dbplatz.com/content/images/2021/03/image-11.png)
and it worked like a charm.
Then I executed the alter command.
alter user posgres with superuser ;
![](https://blog.dbplatz.com/content/images/2021/03/image-12.png)
and once I started the service and tried pg admin again.
![](https://blog.dbplatz.com/content/images/2021/03/image-13.png)
The postgres user was a superuser again!
One thing not to forget is to remove the postgres permissions from the /data folder.
Want to thank me?
![Buy Me A Coffee](https://cdn.buymeacoffee.com/buttons/v2/default-blue.png)