Mike,
        From a different perspective I toyed around briefly with the idea of 
creating a user called "backup" that would merely have SELECT permissions 
on all tables in all databases in order to perform a pg_dump or 
pg_dumpall.  This works fine for a pg_dump as it does a single database 
at a time.  The problem was that I couldn't figure out a way to 
automatically set SELECT permissions for the backup user when a new table 
was created in the database.  You can't set a default value in the 
pg_class table nor create a trigger on insert as it complains about 
"can't modify a system catalogue".  Maybe there is some other way to get 
around that, and I would be more than happy to hear comments on this.
        The other problem I found was that when using pg_dumpall it dumps all 
the database usernames/passwords so the "backup" user would need select 
permissions on pg_shadow which contains all of the usernames/passwords.  
Well, I kinda quit right there as far as using this restricted "backup" 
user for pg_dumpall because if it can select all users/passwords from the 
database then storing this combination in a shell script/environment 
variable isn't any more secure.  Sure, it takes one more step to get ALL 
usernames/passwords, but that doesn't seem to be worth the effort.
        Thinking about it now and seeing a shell script somebody posted a little 
while ago to do a vacuum and pg_dump it might not be such a bad idea to 
go back to the "backup" user with SELECT only permissions on the tables.  
Just not sure how to set the permissions on newly created tables by 
default, maybe it just has to be manually.
        I would greatly appreciate comments on this idea and if it is worth 
anything.  I've teetered back and forth for some time.
Tim Frank

Original Message dated 20/03/01, 6:48:08 AM
Author: Michael Ansley <[EMAIL PROTECTED]>
Re: RE: [ADMIN] Backing up postgresql databases:


Is there any reason why programs like this could not be given a simple 
properties file which contains the username and password.  This file 
could then be passed on the command line, but nobody (other than, say, 
root, or postgres) would have access to it at all.  I've seen a number of 
systems use this type of solution, and although it appears superficially 
useless (am I opening myself to be shot down or what ;-), the security of 
the file system creates (as far as I can see) reasonable safety.
Just my ¬25... 
MikeA 

>> -----Original Message----- 
>> From: Thierry Besancon [mailto:[EMAIL PROTECTED]] 
>> Sent: 20 March 2001 08:34 
>> To: Tim Frank 
>> Cc: [EMAIL PROTECTED] 
>> Subject: Re: [ADMIN] Backing up postgresql databases 
>> 
>> 
>> Dixit Tim Frank <[EMAIL PROTECTED]> (le Tue, 20 
>> Mar 2001 00:14:11 GMT) : 
>> 
>> »    Have your shell script do 
>> » 
>> » export PGUSER=username 
>> » export PGPASSWORD=password 
>> » 
>> » before you run pg_dumpall in the same script.  The 
>> user/pass would most 
>> » likely have to be a superuser to have access to all 
>> databases (this is 
>> » also not guaranteed depending on your pg_hba.conf).  Make 
>> the script 
>> » read/execute by root but not by anyone else and it will 
>> help a tiny bit 
>> » with security. 
>> 
>> Using something like "ps -e" shows the environment variables so it is 
>> as unsecure as giving the password on the commande line. 
>> 
>>         Thierry 
>> 
>> ---------------------------(end of 
>> broadcast)--------------------------- 
>> TIP 6: Have you searched our list archives? 
>> 
>> http://www.postgresql.org/search.mpl 
>> 


_________________________________________________________________________
This e-mail and any attachments are confidential and may also be 
privileged and/or copyright 
material of Intec Telecom Systems PLC (or its affiliated companies). If 
you are not an 
intended or authorised recipient of this e-mail or have received it in 
error, please delete 
it immediately and notify the sender by e-mail. In such a case, reading, 
reproducing, 
printing or further dissemination of this e-mail is strictly prohibited 
and may be unlawful. 
Intec Telecom Systems PLC. does not represent or warrant that an 
attachment hereto is free 
from computer viruses or other defects. The opinions expressed in this 
e-mail and any 
attachments may be those of the author and are not necessarily those of 
Intec Telecom 
Systems PLC. 

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses. 
________________________________________________________________________
__

---------------------------(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

Reply via email to