pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: help with pg_hba.conf
4+ messages / 2 participants
[nested] [flat]
* Re: help with pg_hba.conf
@ 2009-05-22 13:03 Isaac Dover <isaacdover@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Isaac Dover @ 2009-05-22 13:03 UTC (permalink / raw)
To: pgsql-sql
possibly this answers my question, and what i am doing is indeed the most
appropriate?
"Databases are physically separated and access control is managed at the
connection level."
from 8.3 docs, section 20.1
thanks,
Isaac
On Fri, May 22, 2009 at 2:37 AM, Isaac Dover <isaacdover@gmail.com> wrote:
> Hello, to this point i've been working with pg_hba.conf authentication
> defaults as installed with PostgreSQL 8.3. I'm trying to better understand
> "best practice" for managing connections to databases (I've grown accustomed
> to the MSSQL EM method of assigning user privileges). As far as i can tell,
> pg_hba.conf is the only manner in which to prevent users from connecting to
> other users' databases. I've restricted roles to connecting only using
> sameuser:
>
> host sameuser all 192.168.168.0/24 md5
>
> this works fine until a user connects and creates a new database. Pg shows
> that the owner of the database is the currently connected user, but the user
> can't connect to it, as the hba.conf file has the sameuser restriction. I
> was hoping that (somehow, magically) the owner of the database could always
> connect to the databases he/she owns.
>
> Is hba.conf the only way to restrict users connections to specific
> databases? Are there privileges I can grant without having to maintain this
> file?
>
> I've spent quite some time researching this, even with the documentation,
> but I'm wondering what I'm missing.
>
> Thanks,
> Isaac
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help with pg_hba.conf
@ 2009-05-22 16:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Isaac Dover <isaacdover@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2009-05-22 16:31 UTC (permalink / raw)
To: Isaac Dover <isaacdover@gmail.com>; +Cc: pgsql-sql
Isaac Dover <isaacdover@gmail.com> writes:
>> As far as i can tell,
>> pg_hba.conf is the only manner in which to prevent users from connecting to
>> other users' databases. I've restricted roles to connecting only using
>> sameuser:
>>
>> host sameuser all 192.168.168.0/24 md5
In recent releases (certainly 8.3) the better approach is probably to
use CONNECT privilege to grant or deny access. However that does have
some drawbacks if you intend to let users create their own databases
--- they have to remember to set the privileges properly on new DBs.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help with pg_hba.conf
@ 2009-05-22 17:39 Isaac Dover <isaacdover@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Isaac Dover @ 2009-05-22 17:39 UTC (permalink / raw)
To: pgsql-sql
thank you _SO_ much. I'm not sure how I overlooked that, but it is far
easier to manage using connect. I'm assuming that even if tables have public
privileges granted, that they are not visible to people not granted connect
privileges to the database?
Thanks,
Isaac
On Fri, May 22, 2009 at 12:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Isaac Dover <isaacdover@gmail.com> writes:
> >> As far as i can tell,
> >> pg_hba.conf is the only manner in which to prevent users from connecting
> to
> >> other users' databases. I've restricted roles to connecting only using
> >> sameuser:
> >>
> >> host sameuser all 192.168.168.0/24 md5
>
> In recent releases (certainly 8.3) the better approach is probably to
> use CONNECT privilege to grant or deny access. However that does have
> some drawbacks if you intend to let users create their own databases
> --- they have to remember to set the privileges properly on new DBs.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help with pg_hba.conf
@ 2009-05-22 18:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Isaac Dover <isaacdover@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2009-05-22 18:34 UTC (permalink / raw)
To: Isaac Dover <isaacdover@gmail.com>; +Cc: pgsql-sql
Isaac Dover <isaacdover@gmail.com> writes:
> thank you _SO_ much. I'm not sure how I overlooked that, but it is far
> easier to manage using connect. I'm assuming that even if tables have public
> privileges granted, that they are not visible to people not granted connect
> privileges to the database?
Right, if you can't get into the database then the permissions of
objects within it don't matter...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-05-22 18:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-22 13:03 Re: help with pg_hba.conf Isaac Dover <isaacdover@gmail.com>
2009-05-22 16:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-05-22 17:39 ` Isaac Dover <isaacdover@gmail.com>
2009-05-22 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox