Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
reinstalling postgresql-common does not help either :( –  arthur Mar 26 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 at 10:32
2  
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. –  Craig Ringer Mar 26 at 10:43

1 Answer 1

up vote 3 down vote accepted

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

ALTER USER postgres SUPERUSER;

(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.

share|improve this answer
    
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 at 10:35
    
@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). –  Craig Ringer Mar 26 at 10:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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