Thread: user is not in "pg_shadow"
Hi all - I just upgraded from 6.1 to 6.3.2, on x86 RedHat Linux 5.0 (ELF). Version 6.1 was great, I can't wait to use 6.2.3! I ran this: postgres@some_machine> psql -d template1 -c "create user some_user createdb createuser" CREATE USER postgres@some_machine> psql -e template1 < db.out \connect template1 connecting to new database: template1 select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; QUERY: select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; SELECT delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; QUERY: delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; DELETE 1 drop table tmp_pg_shadow; QUERY: drop table tmp_pg_shadow; DROP copy pg_shadow from stdin; QUERY: copy pg_shadow from stdin; \connect template1 connecting to new database: template1 create database scud; QUERY: create database scud; ERROR: createdb: database scud already exists. \connect scud connecting to new database: scud \connect - some_user connecting as new user: some_user FATAL 1: SetUserId: user "some_user" is not in "pg_shadow" Could not connect to new database. exiting #------------------- I've scoured all the documentation I can find, plus the mailing list archive, and I've yet to find any info that can help me. My question is, what is "pg_shadow" and how do I get users into it? Thanks. - Terry Terence W. Dixon The Rochester Group, Inc. Rochester, NY USA
I found a way to solve my problem. I edited the db.out file, and added some queries to create the user "some_user" to that file, so that the user would be added just before it was needed. I now understand what pg_shadow is, and that users are put into pg_shadow when using the 'CREATE USER' command. It appears that when I attempt to run this command -- psql -e template1 < db.out -- all the current users (except postgres) are deleted from the entire postgres installation, before the data is loaded from db.out, and the db.out file does not create the user(s) when it is supposed to. Perhaps someone can tell me what I'm doing wrong, or otherwise note this behavior as a bug. Thanks for all the good info. - Terry Terence W. Dixon The Rochester Group, Inc. Rochester, NY USA Terence Dixon wrote: > > Hi all - > I just upgraded from 6.1 to 6.3.2, on x86 RedHat Linux 5.0 (ELF). > Version 6.1 was great, I can't wait to use 6.2.3! > I ran this: > > postgres@some_machine> psql -d template1 -c "create user some_user > createdb createuser" > CREATE USER > postgres@some_machine> psql -e template1 < db.out > \connect template1 > connecting to new database: template1 > select datdba into table tmp_pg_shadow from pg_database where > datname = 'template1'; > QUERY: select datdba into table tmp_pg_shadow from pg_database > where datname = 'template1'; > SELECT > delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; > QUERY: delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; > DELETE 1 > drop table tmp_pg_shadow; > QUERY: drop table tmp_pg_shadow; > DROP > copy pg_shadow from stdin; > QUERY: copy pg_shadow from stdin; > \connect template1 > connecting to new database: template1 > create database scud; > QUERY: create database scud; > ERROR: createdb: database scud already exists. > \connect scud > connecting to new database: scud > \connect - some_user > connecting as new user: some_user > FATAL 1: SetUserId: user "some_user" is not in "pg_shadow" > > Could not connect to new database. exiting > > #------------------- > > I've scoured all the documentation I can find, plus the mailing list > archive, and I've yet to find any info that can help me. > > My question is, what is "pg_shadow" and how do I get users into it? > > Thanks. > - Terry > > Terence W. Dixon > The Rochester Group, Inc. > Rochester, NY > USA
As we know pg_shadow , pg_database and pg_group are System catalog Table and they are shared to all database , unlike pg_class etc ....... are local to database My question is How can i define my own System catalog or a Table that is shared by all database one like similar to pg_shadow,pg_database ......... Please give some needy Hints to make this Thanku all ............. ......... Waiting for reply
> As we know pg_shadow , pg_database and pg_group are System catalog Table > and > they are shared to all database , unlike pg_class etc ....... are local to > database > My question is > How can i define my own System catalog or a Table that is shared by all > database one like similar to pg_shadow,pg_database ......... > Please give some needy Hints to make this > Thanku all ............. I don't think that is possible. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)