Thread: all postgres users do "not exist"
Hello,
We are using (PostgreSQL) 8.0.1 for Solaris. Apparently our
postgres users were wiped out somehow, for I cannot even log in as
superuser "postgres". Our pg_hba.conf settings are as loose as possible
("trust"). This problem started mid session after we ran a .sql script
to upgrade a DSpace digital library database within the postgres system.
This action should not have affected postgres users in any way. We did
not modify the template1 database (which affects users?) or anything of
that sort. The only other oddity of our installation I can think of is
that fact that the local data directory (/usr/local/pgsql/data) points
to another data directory at the root of the postgres unix account which
really contains the database data (/home/local/pgsql/data). Is there
any way to reinitialize while preserving the existing data directory
(dbinit??)?
Thanks,
Will Cameron
clusterdb droplang pg_config pg_resetxlog vacuumdb
createdb dropuser pg_controldata pg_restore
createlang ecpg pg_ctl postgres
createuser initdb pg_dump postmaster
dropdb ipcclean pg_dumpall psql
bash-2.05$ psql -l
psql: FATAL: user "postgres" does not exist
bash-2.05$ who am i
postgres pts/5 Nov 15 00:18 (g83-sb1-1.csc.villanova.edu)
bash-2.05$ su postgres
Password:
bash-2.05$ export LD_LIBRARY_PATH
bash-2.05$ LD_LIBRARY_PATH=/usr/local/lib
bash-2.05$ psql -l
psql: FATAL: user "postgres" does not exist
pg_hba.conf:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
host dspace dspace 127.0.0.1 255.255.255.255 md5
We are using (PostgreSQL) 8.0.1 for Solaris. Apparently our
postgres users were wiped out somehow, for I cannot even log in as
superuser "postgres". Our pg_hba.conf settings are as loose as possible
("trust"). This problem started mid session after we ran a .sql script
to upgrade a DSpace digital library database within the postgres system.
This action should not have affected postgres users in any way. We did
not modify the template1 database (which affects users?) or anything of
that sort. The only other oddity of our installation I can think of is
that fact that the local data directory (/usr/local/pgsql/data) points
to another data directory at the root of the postgres unix account which
really contains the database data (/home/local/pgsql/data). Is there
any way to reinitialize while preserving the existing data directory
(dbinit??)?
Thanks,
Will Cameron
clusterdb droplang pg_config pg_resetxlog vacuumdb
createdb dropuser pg_controldata pg_restore
createlang ecpg pg_ctl postgres
createuser initdb pg_dump postmaster
dropdb ipcclean pg_dumpall psql
bash-2.05$ psql -l
psql: FATAL: user "postgres" does not exist
bash-2.05$ who am i
postgres pts/5 Nov 15 00:18 (g83-sb1-1.csc.villanova.edu)
bash-2.05$ su postgres
Password:
bash-2.05$ export LD_LIBRARY_PATH
bash-2.05$ LD_LIBRARY_PATH=/usr/local/lib
bash-2.05$ psql -l
psql: FATAL: user "postgres" does not exist
pg_hba.conf:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
host dspace dspace 127.0.0.1 255.255.255.255 md5
Sponsored Link
Mortgage rates as low as 4.625% - $150,000 loan for $579 a month. Intro-*Terms
Will Cameron <novawcameron@yahoo.com> writes:
> We are using (PostgreSQL) 8.0.1 for Solaris. Apparently our
> postgres users were wiped out somehow, for I cannot even log in as
> superuser "postgres". Our pg_hba.conf settings are as loose as possible
> ("trust"). This problem started mid session after we ran a .sql script
> to upgrade a DSpace digital library database within the postgres system.
Better take a closer look at what that script did.
Meanwhile, you can probably get out of this by starting a standalone
postgres session and using it to recreate your SQL users.
regards, tom lane
Hello,
I'm not sure what exactly you mean by "starting a standalone
postgres session and using it to recreate your SQL users". Do you mean running initdb on a new data directory and then trying to create new users? I'm fairly certain we already tried that. Since we cannot log into the postgres user at all we can't run createuser. We can start and stop the database but cannot log in with any users. We ran the script in this fashion:
Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm not sure what exactly you mean by "starting a standalone
postgres session and using it to recreate your SQL users". Do you mean running initdb on a new data directory and then trying to create new users? I'm fairly certain we already tried that. Since we cannot log into the postgres user at all we can't run createuser. We can start and stop the database but cannot log in with any users. We ran the script in this fashion:
psql -f etc/database_schema_12-13.sql [DSpace database name] -h localhost
The script just contains sql commands to create tables and sequences within the database specified. Again, I don't know much about the template1 database but we did not touch it (that we know of) if it does contain user accounts. I'm thinking perhaps since the local data directory actually just points to another one which has the real data that psql might have been confused.
/usr/local/pgsql/data
lrwxrwxrwx 1 root other 22 Apr 18 2006 data -> /home/local/pgsql/data
/home/local/pgsql/data
drwx------ 10 postgres staff 512 Nov 15 14:10 data
Thanks,
Will
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Will Cameron writes:
> We are using (PostgreSQL) 8.0.1 for Solaris. Apparently our
> postgres users were wiped out somehow, for I cannot even log in as
> superuser "postgres". Our pg_hba.conf settings are as loose as possible
> ("trust"). This problem started mid session after we ran a .sql script
> to upgrade a DSpace digital library database within the postgres system.
Better take a closer look at what that script did.
Meanwhile, you can probably get out of this by starting a standalone
postgres session and using it to recreate your SQL users.
regards, tom lane
Sponsored Link
Mortgage rates as low as 4.625% - $150,000 loan for $579 a month. Intro-*Terms
Will Cameron <novawcameron@yahoo.com> writes: > I'm not sure what exactly you mean by "starting a standalone postgres > session and using it to recreate your SQL users". Do you mean running > initdb on a new data directory and then trying to create new users? No, I mean stopping the postmaster, running a stand-alone backend as documented in http://www.postgresql.org/docs/8.0/static/app-postgres.html and issuing a CREATE USER command for each account you find is missing from pg_shadow. You could probably just recreate the postgres superuser this way, and then go back to normal operating mode for the rest of the recovery. Note you'll want to use the SYSID option of CREATE USER to make sure the users are recreated with the same pg_shadow.usesysid they had before, else their owned objects will not be correctly linked to them. Look into the owner columns of pg_class etc to determine the values you need. regards, tom lane