Thread: newbie authentication/automated backup (pg_dumpall) questions
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
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
I believe you can define the PGPASSWORD environment variable and pg_dumpall
will use it for hands off authentication. Since you are using crypt
authentication, the password probably has to be crypt()ed, but don't quote
me on that.
Also, I believe specifying IP/mask for "local" types is irrelevant:
> local all 127.0.0.1 255.0.0.0 ident sameuser
"local" means using the Unix domain socket, and not a TCP connection.
Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
> From: Alan <alan@ufies.org>
> Date: Wed, 17 Oct 2001 10:28:18 -0700
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] newbie authentication/automated backup (pg_dumpall)
> questions
>
> 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)
>