Thread: restricting non superuser from accessing other databases

restricting non superuser from accessing other databases

From
David Garamond
Date:
I am setting up a single PostgreSQL installation to be used by several
users. Can I restrict a database user from connecting and creating
objects in other databases but his/her own? So far I can only restrict a
user from creating more databases or users.

(Yes, I have set up a proper pg_hba.conf, but once a user is connected,
he can switch to another database, e.g. with "\c otherdb" in psql).

--
dave


Re: restricting non superuser from accessing other

From
Oliver Elphick
Date:
On Tue, 2004-09-07 at 11:28, David Garamond wrote:
> I am setting up a single PostgreSQL installation to be used by several
> users. Can I restrict a database user from connecting and creating
> objects in other databases but his/her own? So far I can only restrict a
> user from creating more databases or users.
>
> (Yes, I have set up a proper pg_hba.conf, but once a user is connected,
> he can switch to another database, e.g. with "\c otherdb" in psql).

Not unless pg_hba.conf allows it.  You could set up explicit
database/user combinations there.

Another thing you can do is to delete the public schema in new
databases.  The public schema is, by default, accessible to all users;
other schemas are accessible only to their creators unless permissions
are granted on them.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "For whosoever shall call upon the name of the Lord
      shall be saved."         Romans 10:13


Re: restricting non superuser from accessing other databases

From
David Garamond
Date:
Oliver Elphick wrote:
>>I am setting up a single PostgreSQL installation to be used by several
>>users. Can I restrict a database user from connecting and creating
>>objects in other databases but his/her own? So far I can only restrict a
>>user from creating more databases or users.
>>
>>(Yes, I have set up a proper pg_hba.conf, but once a user is connected,
>>he can switch to another database, e.g. with "\c otherdb" in psql).
>
> Not unless pg_hba.conf allows it.  You could set up explicit
> database/user combinations there.

Thanks! So I must modify and kill -HUP postmaster everytime a new db is
added. Is there something like this in pg_hba.conf?

  local owndb all md5

where "owndb" means only allow a user to connect only to db he/she owns.

--
dave

Re: restricting non superuser from accessing other

From
Oliver Elphick
Date:
On Tue, 2004-09-07 at 14:35, David Garamond wrote:
> Oliver Elphick wrote:
> >>I am setting up a single PostgreSQL installation to be used by several
> >>users. Can I restrict a database user from connecting and creating
> >>objects in other databases but his/her own? So far I can only restrict a
> >>user from creating more databases or users.
> >>
> >>(Yes, I have set up a proper pg_hba.conf, but once a user is connected,
> >>he can switch to another database, e.g. with "\c otherdb" in psql).
> >
> > Not unless pg_hba.conf allows it.  You could set up explicit
> > database/user combinations there.
>
> Thanks! So I must modify and kill -HUP postmaster everytime a new db is
> added. Is there something like this in pg_hba.conf?
>
>   local owndb all md5
>
> where "owndb" means only allow a user to connect only to db he/she owns.

No.  You would have to have:

        local his_db that_user md5

for each user/database combination.


There is an option db_user_namespace in postgresql.conf, which is
normally off.  See
http://www.postgresql.org/docs/7.4/interactive/runtime-config.html under
section 16.4.1.  I haven't ever used this facility.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "For whosoever shall call upon the name of the Lord
      shall be saved."         Romans 10:13


Re: restricting non superuser from accessing other

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> On Tue, 2004-09-07 at 14:35, David Garamond wrote:
>> Thanks! So I must modify and kill -HUP postmaster everytime a new db is
>> added. Is there something like this in pg_hba.conf?
>>
>> local owndb all md5

> No.  You would have to have:
>         local his_db that_user md5
> for each user/database combination.

CVS-tip documentation alleges that "sameuser" does what David wants,
at least as long as he names databases the same as their owners.

I'm too lazy to look to see if it's in any released versions ...

            regards, tom lane

Re: restricting non superuser from accessing other

From
Oliver Elphick
Date:
On Tue, 2004-09-07 at 15:38, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > On Tue, 2004-09-07 at 14:35, David Garamond wrote:
> >> Thanks! So I must modify and kill -HUP postmaster everytime a new db is
> >> added. Is there something like this in pg_hba.conf?
> >>
> >> local owndb all md5
>
> > No.  You would have to have:
> >         local his_db that_user md5
> > for each user/database combination.
>
> CVS-tip documentation alleges that "sameuser" does what David wants,
> at least as long as he names databases the same as their owners.
>
> I'm too lazy to look to see if it's in any released versions .

I had overlooked that.  It is in 7.4, at least:

database

        Specifies which databases this record matches. The value all
        specifies that it matches all databases. The value sameuser
        specifies that the record matches if the requested database has
        the same name as the requested user. The value samegroup
        specifies that the requested user must a member of the group
        with the same name as the requested database. Otherwise, this is
        the name of a specific PostgreSQL database. Multiple database
        names can be supplied by separating them with commas. A file
        containing database names can be specified by preceding the file
        name with @. The file must be in the same directory as
        pg_hba.conf.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "For whosoever shall call upon the name of the Lord
      shall be saved."         Romans 10:13