I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I have a completeley fresh installation of postgres with only the template database and a single user 'postgres' that can do pretty much nothing. How can I make postgres user superuser again?

I tried to reinstall the server under ubuntu 12.04 with aptitude reinstall postgresql-9.1 but the role seems to be unaffected. Most probably because the reinstallation does not affect the files where the roles are recorded.

  • reinstalling postgresql-common does not help either :(
    – arthur
    Mar 26, 2014 at 10:20
  • had to apt-get --purge remove postgresql-9.1 to drop the config files. After the reinstallation the postgres became superuser again. I wonder which config option makes the postgres user to be a superuser
    – arthur
    Mar 26, 2014 at 10:32
  • 3
    WARNING to other readers: Running the command given in the comment above command may (depending on the exact PostgreSQL packages in use) UNRECOVERABLY DELETE YOUR DATABASE. Do not run it on a system with data you care about. Mar 26, 2014 at 10:43

2 Answers 2


Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install.

You need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser.

There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit single user mode, and start the server back up.

The exact details are a little distro and version dependent, but something like:

service postgresql stop
sudo -u postgres postgres --single /var/lib/pgsql/9.1/main/data


(control-D to exit)

service postgresql start

On some systems you might need to use an explicit path to the postgres executable, like /usr/pgsql-9.1/bin/postgres. Again, paths may vary based on version and distro.

  • Alter user... means for e.g., alter user createdb? That does not work, because postgres is a regular user now and cannot change roles...
    – arthur
    Mar 26, 2014 at 10:35
  • 1
    @arthur Read the whole answer. See the bit about single user mode ? (I forced Stack Overflow to disable syntax highlighting, maybe that'll make it clearer). Mar 26, 2014 at 10:37

For Windows it is necessary to:

  • create a local user called 'postgres'
  • assign write rights for this user on the data folder
  • stop the postgres service from services
  • open a command prompt as postgres user (e.g. runas /user:postgres cmd)
  • run postgres in single user mode

    "<PATHTOPOSTGRES>\postgres.exe" --single -D "<PATHTODATA>/data"

    where the relevant database is inferred from the username (postgres)

  • Execute the necessary commands to get your user back to abnormal (super):

    backend> ALTER USER postgres SUPERUSER;
    backend> ALTER USER postgres LOGIN;
    backend> ALTER USER postgres REPLICATION;
    backend> ALTER USER postgres CREATEDB;
    backend> ALTER USER postgres CREATEROLE;
    backend> ALTER USER postgres INHERIT;
    backend> ALTER USER postgres CONNECTION LIMIT -1;
    backend> ALTER USER postgres VALID UNTIL 'infinity';
  • Ctrl-C to exit

  • start the postgres service from services

Note the 'bug' in earlier PGAdmin 16.1 related to opening a user's properties might have been the cause.

  • Since 9.x it is no longer necessary to create a dedicated Windows user to run Postgres.
    – user1822
    Jun 2, 2015 at 13:59
  • If it is run by a user with administrative rights you will receive the following error message: "Execution of PostgreSQL by a user with administrative permissions is not permitted" - the name postgres simplifies the command line as the database utilised defaults to the user name.
    – OvG
    Jun 2, 2015 at 14:09
  • 1
    If you use pg_ctl instead of postgres you can do all that using an administrator account as well, because pg_ctl will make sure that the additional privileges are "dropped" before running Postgres
    – user1822
    Jun 2, 2015 at 14:11
  • If it was documented how pg_ctl could be run in single user mode that would be true.
    – OvG
    Jun 2, 2015 at 14:15
  • Interesting. I wasn't aware that pg_ctl can not start Postgres in single user mode. -o "--single" does not seem to work.
    – user1822
    Jun 2, 2015 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.