Thread: Re: can't alter user with the postgres user
Katy, > 2) CREATE USER ...: > Shall the new user be allowed to create databases? > (y/n) y > Shall the new user be allowed to create more new > users? (y/n) y > ERROR: CREATE USER: permission denied. > createuser: creation of 'carl' failed. What about doing it from psql? Also from psql: SELECT * FROM pg_user; -- -Josh Berkus Aglio Database Solutions San Francisco
Hi Josh Yes, I see the problem now.... The password in the pg_user table is different from what we are thinking postgres's password to be... How do I change the password in this table to be the correct one, or vice versa? Katy system=> SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper ----------+----------+-------------+--------- postgres | 1 | t | f | usecatupd | passwd | valuntil | useconfig +-----------+----------+----------+----------- | f | ******** | | (1 row) system=> CREATE USER carl WITH PASSWORD 'moll454'; ERROR: CREATE USER: permission denied Katy --- Josh Berkus <josh@agliodbs.com> wrote: > Katy, > > > 2) CREATE USER ...: > > Shall the new user be allowed to create databases? > > (y/n) y > > Shall the new user be allowed to create more new > > users? (y/n) y > > ERROR: CREATE USER: permission denied. > > createuser: creation of 'carl' failed. > > What about doing it from psql? > > Also from psql: > > SELECT * FROM pg_user; > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
Ok, I spoke too soon, I think... It seems that pg_user is a view and shows 8 *'s regardless of what password you have postgres set up to. I did an 'ALTER USER postgres WITH PASSWORD 'blah'; ' with the password we have been using in Webmin for accessing the database, which interestingly seemed to work (while logged in as postgres, the message after I pressed 'enter' was 'ALTER USER' then another prompt). But, I still can't create a user while logged in as postgres (in Webmin or psql) and webmin isn't picking up the changes that were made to postgres with 'ALTER USER'. Perhaps 'ALTER USER' isn't doing what I think it should be doing?? Still lost Katy --- Katy Earl <khaetee@yahoo.com> wrote: > Hi Josh > > Yes, I see the problem now.... The password in the > pg_user table is different from what we are thinking > postgres's password to be... How do I change the > password in this table to be the correct one, or > vice > versa? > > Katy > > system=> SELECT * FROM pg_user; > usename | usesysid | usecreatedb | usesuper > ----------+----------+-------------+--------- > postgres | 1 | t | f > > | usecatupd | passwd | valuntil | useconfig > +-----------+----------+----------+----------- > | f | ******** | | > (1 row) > > system=> CREATE USER carl WITH PASSWORD 'moll454'; > ERROR: CREATE USER: permission denied > > Katy > > --- Josh Berkus <josh@agliodbs.com> wrote: > > Katy, > > > > > 2) CREATE USER ...: > > > Shall the new user be allowed to create > databases? > > > (y/n) y > > > Shall the new user be allowed to create more new > > > users? (y/n) y > > > ERROR: CREATE USER: permission denied. > > > createuser: creation of 'carl' failed. > > > > What about doing it from psql? > > > > Also from psql: > > > > SELECT * FROM pg_user; > > > > -- > > -Josh Berkus > > Aglio Database Solutions > > San Francisco __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
Katy, > We thought that the postgres user was the superuser, > but I suppose something must've gone wrong at some > point, perhaps at installation.... Will we have to > reinstall, then? Or what other options are there? The > only other superuser is that for linux (root...). Is "postgres" the only line in pg_users, or is there perhaps a superuser with a different name? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Yes, 'postgres' is the only line in pg_user. Thanks Katy --- Josh Berkus <josh@agliodbs.com> wrote: > Katy, > > > We thought that the postgres user was the > superuser, > > but I suppose something must've gone wrong at some > > point, perhaps at installation.... Will we have to > > reinstall, then? Or what other options are there? > The > > only other superuser is that for linux (root...). > > Is "postgres" the only line in pg_users, or is there > perhaps a superuser with > a different name? __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
Katy, > Yes, 'postgres' is the only line in pg_user. Wow. Frankly, I'm not sure how it's possible to install postgresql without a superuser. Perhaps your tech accidentally made himself the superuser, then deleted his account to "fix" it? In any case, I think re-installation will be your easiest way to fix this. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Wow. Frankly, I'm not sure how it's possible to install postgresql without a
> superuser.
You can't. The original superuser account must have been deleted, or
perhaps altered to not have superuser privs anymore.
> In any case, I think re-installation will be your easiest way to fix this.
I believe it's possible to create a new superuser from a standalone
backend. However, if this is a test installation with no valuable data
in it, a re-install probably is the easiest path.
regards, tom lane