Thread: Problems with user-level security
Maybe it's just me, but I think pg_hba.conf needs a user column in it. On the one hand, I need local sameuser password because I have untrusted users who should only have access to their own databases. On the other hand, I do a nightly pg_dumpall as part of the backups so that I don't have to dump the actual database working areas. For that to work, I need to say local all password so that my backup script will work. What I *really* want is for an extra column to be added to pg_hba.conf to specify the user of interest. Like this: all local sameuser password backups local all password pgsql local all password Does this make any sense? Is there some way to achieve this I am missing? The only way I can do backups at the moment is introduce a race condition so that anyone can connect to any database they like at certain times of day. Not good.
I agree a user column would VERY nice especially when you have several machines doing replication or users that are connecting with ODBC and have to use password instead of crypt authentication. Nick Sayer wrote: > Maybe it's just me, but I think pg_hba.conf needs a user column in it. > > On the one hand, I need > > local sameuser password > > because I have untrusted users who should only have access to their own > databases. > > On the other hand, I do a nightly pg_dumpall as part of the backups so that > I don't have to dump the actual database working areas. For that to work, I > need to say > > local all password > > so that my backup script will work. > > What I *really* want is for an extra column to be added to pg_hba.conf to > specify the user of interest. Like this: > > all local sameuser password > backups local all password > pgsql local all password > > Does this make any sense? Is there some way to achieve this I am missing? > The only way I can do backups at the moment is introduce a race condition > so that anyone can connect to any database they like at certain times of > day. Not good. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Tue, 6 Nov 2001, Nick Sayer wrote: > Maybe it's just me, but I think pg_hba.conf needs a user column in it. > > On the one hand, I need > > local sameuser password > > because I have untrusted users who should only have access to their own > databases. > > On the other hand, I do a nightly pg_dumpall as part of the backups so that > I don't have to dump the actual database working areas. For that to work, I > need to say > > local all password > > so that my backup script will work. > > What I *really* want is for an extra column to be added to pg_hba.conf to > specify the user of interest. Like this: > > all local sameuser password > backups local all password > pgsql local all password > > Does this make any sense? Is there some way to achieve this I am missing? > The only way I can do backups at the moment is introduce a race condition > so that anyone can connect to any database they like at certain times of > day. Not good. If you're using password, couldn't you do this by specifying the file to look up the passwords in (the optional last parameter) on the local all line and then only put the backups/pgsql password in it.
Stephan Szabo wrote: > On Tue, 6 Nov 2001, Nick Sayer wrote: [...] >> What I *really* want is for an extra column to be added to pg_hba.conf >> to specify the user of interest. Like this: >> >> all local sameuser password >> backups local all password >> pgsql local all password >> >> Does this make any sense? Is there some way to achieve this I am >> missing? The only way I can do backups at the moment is introduce a >> race condition so that anyone can connect to any database they like at >> certain times of day. Not good. > > If you're using password, couldn't you do this by specifying the file > to look up the passwords in (the optional last parameter) on the local > all line and then only put the backups/pgsql password in it. That solution works perfectly. I now have local sameuser password local all password pg_superusers and have added the backup user to pg_superusers using pg_passwd. I am a happy camper! Especially nice is that I can empty out the pg_shadow password for the superusers.
Nick Sayer writes: > On the one hand, I need > > local sameuser password > > because I have untrusted users who should only have access to their own > databases. > > On the other hand, I do a nightly pg_dumpall as part of the backups so that > I don't have to dump the actual database working areas. For that to work, I > need to say > > local all password > > so that my backup script will work. If you are only concerned about local access then you should consider using 'ident' authentication. In 7.1 this only works for TCP/IP connections (so you have to go through 'localhost'), but in 7.2 it will also work with local connections. Another possibility is to use 'local sameuser trust' and set a filesystem permission on the socket. (See unix_socket_* parameters in postgresql.conf.) In 7.2 we'll also have PAM, so you'll be able to do whatever you want. -- Peter Eisentraut peter_e@gmx.net
Nick Sayer <nsayer@quack.kfu.com> writes:
> Is there some way to achieve this I am missing?
For password-style authentication, you can use a secondary auth file to
get that effect:
local sameuser password
local all password crossauth
where $PGDATA/crossauth lists the users who should be allowed to connect
to databases other than their own:
backups
pgsql
The map file can serve a similar purpose for ident-based auth. AFAIK
there's no similar capability for Kerberos or PAM auth methods :-(
regards, tom lane
At 06:19 PM 11/6/01 -0800, Nick Sayer wrote:
>On the other hand, I do a nightly pg_dumpall as part of the backups so that
>I don't have to dump the actual database working areas. For that to work, I
>need to say
>
>local all password
>
>so that my backup script will work.
I started an IDENT server on my machines and then added a rule that said if
"ident" said the user was postgres, it had access to anything.
I don't know if that's secure enough for you, but it works for us on our
production network inside our firewall.
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228