Re: Re: database access - Mailing list pgsql-general

Tom,

    Your response puzzled me slightly until I combed the docs with a
slightly finer toothed comb than before.  The only references where I
remembered seeing "sameuser" were in reference to the ident
authentication type, but you are correct, it is also a possible value for
a database.
    My question would be if I used "sameuser" as the database then would my
database superusers be allowed to also connect to databases different
from their usernames? My first guess would be that they wouldn't because
their names wouldn't match all the DB names.  The thinking for using
individual password files to name valid users was that I would need a way
to also allow my DB superusers to connect, and would therefore have to
name them in the separate password file.

    While we are on this "permissions" topic, I have another related
question.  I am contemplating creating a "backup user" with a separate
user/pass that is not a superuser.  The reason being if I want password
authentication for my localhost then I would have to provide a user/pass
in order to do backup with a dump.  I am not too keen on using a
superuser account if it is going to be stored in a script, or environment
variable, or stuck at the top of the dump when using echo, as other
suggestions on the list have led me to believe.
    So, my question is, is there any way to automatically grant certain
rights (read) on any new tables that are created in any database?  If I
could automatically grant read to my backup user on all new tables then
in theory a backup could be run with this account without requiring
superuser privledges.

    Sorry this turned out to be a long question, but I know these issues
come up again and again without a solid answer from what I could find on
the list archives.  Maybe I'm just talking silly with these ideas, so
please let me know if you wouldn't mind.

Thanks in advance to everyone.

Tim Frank

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 13/03/01, 12:09:51 AM, tgl@sss.pgh.pa.us (Tom Lane) wrote regarding
Re: [GENERAL] Re: database access:


> >> Howcome that all users i create have permission to view and edit all
> >> databases that i create. I want 1 user for each database. Anyone?

> The "sameuser" option in pg_hba.conf may offer a solution.  This allows
> access only to a database named the same as the user.

>                       regards, tom lane

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster