[SQL] pg_dumpall and password access
I'm trying to run pg_dumpall to backup all my users' bases but since I have configure pg_hba.conf to "passwd" pg_dumpall always fails: su-2.03$ pg_dumpall >backup.dat # I'm the postgres unix user. Password: psql: Password authentication failed for user 'postgres' Password: psql: Password authentication failed for user 'postgres' Password: psql: Password authentication failed for user 'postgres' # I type my password on the next line Password: Password: psql: Password authentication failed for user 'postgres' Connection to database 'sympa' failed. fe_sendauth: no password supplied pg_dump failed on sympa, exiting I need to have a password identification since I don't want the "common" users to be able to connect to any database. Beside I cannot leave the "local" to the "trust" auth scheme because every users are able to run programs on the machine hosting the database. Is there any configuration tips or workaround known for my problem ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Integrity and Inheritance
I wanted to build the following schema : - one "generic" document table with a column doc_id ; - a couple of "specific" document tables inheriting from doc ; - a table refering a document by it's id with and integrity constraint on it. In SQL : CREATE TABLE doc ( doc_id serial PRIMARY KEY, ); CREATE TABLE lexique ( ) INHERITS (doc); CREATE TABLE word_doc ( id serial PRIMARY KEY, doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE, ); What I tried to do next is to insert a new "lexique" entry, and then a bunch of "word_doc" rows refering this entry. The last part fails with a constraint violation this is quite normal since the trigger in backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". What should I do ? Should I consider another way to do what I want, rewrite a trigger to replace the system one ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Integrity reference and inheritance
I wanted to build the following schema : - one "generic" document table with a column doc_id ; - a couple of "specific" document tables inheriting from doc ; - a table refering a document by it's id with and integrity constraint on it. In SQL : CREATE TABLE doc ( doc_id serial PRIMARY KEY, ); CREATE TABLE lexique ( ) INHERITS (doc); CREATE TABLE word_doc ( id serial PRIMARY KEY, doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE, ); What I tried to do next is to insert a new "lexique" entry, and then a bunch of "word_doc" rows refering this entry. The last part fails with a constraint violation this is quite normal since the trigger in backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". What should I do ? Should I consider another way to do what I want, rewrite a trigger to replace the system one ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Integrity reference and inheritance
Sorry for the double post, I messed up in my addresses. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pg_ctl start hangs
When restarting postgres this morning I notice the pg_ctl start hangs forever while the database seems to be up and working. Eventually I found out that the start process involves a "psql -l" which waits for me to enter a password since I have "local all password" in my pg_hba.conf. I change back to trust but since I have users with shell access on the server I'd would like to switch back to "password". I used the following command to start the database: /usr/local/bin/pg_ctl start -s -w -l /usr/local/pgsql/errlog -o "-i" My configuration: FreeBSD 4.3, Postgresql 7.1.2 -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Inheritance
I have a table (A) with a few "sibbling" tables (B and C for instance). When I make the following query : select id from A* where [condition] pgsql returns ids taken from A, B or C (which is what I want). Is there a way to know to what table the id actually belong ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF
