Re: newbie authentication/automated backup (pg_dumpall) questions - Mailing list pgsql-general
From | Jason Earl |
---|---|
Subject | Re: newbie authentication/automated backup (pg_dumpall) questions |
Date | |
Msg-id | 20011018181906.83628.qmail@web10007.mail.yahoo.com Whole thread Raw |
In response to | newbie authentication/automated backup (pg_dumpall) questions (Alan <alan@ufies.org>) |
List | pgsql-general |
Well, your pretty close to where you want to be (uh, you're editting the right file :). Usually the comments in the pg_hba.conf file are more than enough to get you on the right track, but if you have already removed all of the comments then that isn't particularly helpful. See: http://www.postgresql.org/idocs/index.php?client-authentication.html Or install the postgresql-doc package (I would recommend it) and see: file:///usr/share/doc/postgresql-doc/html/client-authentication.html Depending on what you want to do, here's the trick: First of all, you want to give your local users access. This is especially useful for scripts like pg_dumpall. This is accomplished with a local record like this: # local access local all trust In this example I have chosen to "trust" local users. That way it doesn't matter what my Unix username is, if I tell PostgreSQL that I am postgres then I get full db administrator access. So: psql processdata -U postgres gives me the works, even if I am logged on as someone other than postgres. You can replace "trust" with "ident" if you want to only allow users to log onto PostgreSQL using their Unix username. Or, since you are using Debian you can use "peer" which does the same thing, but doesn't require that you be running identd. This option is not a standard PostgreSQL option, although it was talked about a while back, and it probably will become a standard option perhaps with a different name (you have been warned). See /usr/share/doc/postgresql/README.Debian.gz for more information. If you are only allowing access to the database via Unix sockets then you are done. However, some software packages require that you connect via TCP/IP even when you are on the same machine. In that case you will need a host record as well. To add an entry for the localhost simply add: #localhost host all 127.0.0.1 255.255.255.255 trust This gives localhost access to "all" databases, and once again tells PostgreSQL to "trust" the users. You might want to consider changing that to "ident" or even "crypt" depending on what your needs are. I hope this is helpful, Jason Earl --- Alan <alan@ufies.org> wrote: > Hi everyone. > > Just got postgres 7.1.3 (debian unstable) going > after an upgrade from > 7.0.x. I have things *mostly* working now, with a > few questions > regarding authentication. > > What I'd like to have is the following two > situations dealt with: > > - unsupervised backups using something like > pg_dumpall that can run > from cron either as root or the postgres user (su > -c "pg_dumpall...") > - access to the database through web apps such as > message boards or > similar using the Pg module from a webserver > > In 7.0 you could run pg_dumpall as the postgres > user, so cron took care > of backups very nicely, and from the webserver > running as a different > user (www-data) using Pg::connectdb(...) and passing > the postgresql > user/pass (the shell username/password that is). No > one without > postgres shell account access could access the > database which is fine by > me. This all worked fine. > > Now 7.1 is here and I'm lost :( I've never done any > real "user > management" using postgres other than setting a > password in the shell > for the postgres user. > > Currently my situation is this: > > /etc/postgres/pg_hba.conf > > local all > crypt > local all 127.0.0.1 255.0.0.0 > ident sameuser > > With this I can set up a cgi with the line: > Pg::connectdb("dbname=$database user=$dbuser > password=$dbpass"); > > And properly connect via my webserver user > (www-data) to postgres just > dandy. > > However, what I can't do is automated backups :( In > fact, I can't seem > to run pg_dumpall at all! > > ----------------- > postgres@master:~$ pg_dumpall > -- > -- pg_dumpall (7.1.3) > -- > \connect template1 > DELETE FROM pg_shadow WHERE usesysid <> (SELECT > datdba FROM pg_database > WHERE datname = 'template0'); > > Password: > psql: Password authentication failed for user > 'postgres' > > DELETE FROM pg_group; > > Password: [password] > Password: [password] > > -- > -- Database ufies > -- > \connect template1 postgres > CREATE DATABASE "ufies" WITH TEMPLATE = template0 > ENCODING = > 'SQL_ASCII'; > \connect ufies postgres > Connection to database 'ufies' failed. > fe_sendauth: no password supplied > > pg_dump failed on ufies, exiting > postgres@master:~$ > ----------------- > > Note that above I only put in the password the > second and third time, not > the first time (ufies is the name of the main db > BTW). > > It was suggested to me on IRC that passing -h > 127.0.0.1 would solve my > problems, but I get: > > ----------------- > postgres@master:~$ pg_dumpall -h 127.0.0.1 > -- > -- pg_dumpall (7.1.3) -h 127.0.0.1 > -- > \connect template1 > DELETE FROM pg_shadow WHERE usesysid <> (SELECT > datdba FROM pg_database > WHERE datname = 'template0'); > > psql: Peer authentication failed for user 'postgres' > > DELETE FROM pg_group; > > psql: Peer authentication failed for user 'postgres' > psql: Peer authentication failed for user 'postgres' > postgres@master:~$ > ----------------- > > > I've looked through the manuals and list archives, > but I couldn't find > something similar to this :( If anyone has any > advice (even which FM to > read :) I'd certainly appreciate it! > > TIA > > Alan > > > -- > Arcterex <arcterex@userfriendly.org> -==- > http://arcterex.net > "I used to herd dairy cows. Now I herd lusers. Apart > from the isolation, I > think I preferred the cows. They were better > conversation, easier to milk, and > if they annoyed me enough, I could shoot them and > eat them." -Rodger Donaldson > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com
pgsql-general by date: