Обсуждение: giving users access to specific databases

Hi,

I'm hoping someone can tell me how to go about this, or if a solution is
even possible with my current set up.  I realize this question may go beyond
pure postgres topics and have to do more with how my hosting company has
their servers configures, but this group seemed like my best option  for
help.  If anyone has suggestions on what other groups might be helpful to
post this question to, I would really appreciate it.

I do my database & application work on a shared Linux server provided by my
hosting company.  It appears they have just one installation of postgres on
the server and that all of their customers on that server are able to create
databases and users under their account.  I'm not quite sure how they have
enabled specific user accounts for access to my specific part of the server,
but I do know that when I log in I have access to a cpanel interface, email
configuration, all of my files on the server, access to create new postgres
databases and a link to phpPgAdmin.  The difficulty I have is that in order
to give another of the developers access to a specific database, I must give
them the username and password to my entire account on their server.  I've
spent weeks corresponding with them on how I can give individual access to
just a specific database.  We finally tried installing a separate instance
of phpPgAdmin in my web directory.  I can get access to my databases through
this, but it turns out I still need to use my main username and password to
access the databases since we realized they needed to tighten security so
other people on the server couldn't get access to my databases.   Below is a
copy of the last email I got from the hosting company.  If anyone has any
suggestions on what would fix this problem I would really appreciate it. I'm
not set on having to use phpPgAdmin, but I would like some sort of graphical
interface available so less-technical users can update information in the
database without having to learn sql.

Subject: Separate login for phpPgAdmin
The only way with phpPgAdmin is to allow you access to all databases
including those owned by you.  This can cause a security issue where others
would have access to your database as well.  Unlike phpmyadmin, phpPgAdmin
does not seem to allow you to login to a database with a database username
unless the above option is enabled and therefore creating a security risk.
The way Postgres is set to label owners it uses your CPANEL login as the
owner which is different than how MySql tracks the databases.  Due to this
you would not be able to use phpPgAdmin with other users.

Thanks in advance,
Courtenay

On 4/1/06, postgresql@teska.net <postgresql@teska.net> wrote:
> Hi,
>
> I'm hoping someone can tell me how to go about this, or if a solution is
> even possible with my current set up.  I realize this question may go beyond
> pure postgres topics and have to do more with how my hosting company has
> their servers configures, but this group seemed like my best option  for
> help.  If anyone has suggestions on what other groups might be helpful to
> post this question to, I would really appreciate it.
>
> I do my database & application work on a shared Linux server provided by my
> hosting company.  It appears they have just one installation of postgres on
> the server and that all of their customers on that server are able to create
> databases and users under their account.  I'm not quite sure how they have
> enabled specific user accounts for access to my specific part of the server,
> but I do know that when I log in I have access to a cpanel interface, email
> configuration, all of my files on the server, access to create new postgres
> databases and a link to phpPgAdmin.

Do you get the option to create a new database user? You could create
a new user and give that user access to your database.

--
Postgresql & php tutorials
http://www.designmagick.com/

On Friday 31 March 2006 18:51, chris smith wrote:
> On 4/1/06, postgresql@teska.net <postgresql@teska.net> wrote:
> > Hi,
> >
> > I'm hoping someone can tell me how to go about this, or if a solution is
> > even possible with my current set up.  I realize this question may go
> > beyond pure postgres topics and have to do more with how my hosting
> > company has their servers configures, but this group seemed like my best
> > option  for help.  If anyone has suggestions on what other groups might
> > be helpful to post this question to, I would really appreciate it.
> >
> > I do my database & application work on a shared Linux server provided by
> > my hosting company.  It appears they have just one installation of
> > postgres on the server and that all of their customers on that server are
> > able to create databases and users under their account.  I'm not quite
> > sure how they have enabled specific user accounts for access to my
> > specific part of the server, but I do know that when I log in I have
> > access to a cpanel interface, email configuration, all of my files on the
> > server, access to create new postgres databases and a link to phpPgAdmin.
>
> Do you get the option to create a new database user? You could create
> a new user and give that user access to your database.
>

Yeah, we're just window dressing atop the postgresql authentication scheme,
which means we allow you to do anything you want that postgresql itself
allows.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

2006-04-01 (토), 21:19 -0500, Robert Treat 쓰시길:
> On Friday 31 March 2006 18:51, chris smith wrote:
> > On 4/1/06, postgresql@teska.net <postgresql@teska.net> wrote:
> > > Hi,
> > >
> > > I'm hoping someone can tell me how to go about this, or if a solution is
> > > even possible with my current set up.  I realize this question may go
> > > beyond pure postgres topics and have to do more with how my hosting
> > > company has their servers configures, but this group seemed like my best
> > > option  for help.  If anyone has suggestions on what other groups might
> > > be helpful to post this question to, I would really appreciate it.
> > >
> > > I do my database & application work on a shared Linux server provided by
> > > my hosting company.  It appears they have just one installation of
> > > postgres on the server and that all of their customers on that server are
> > > able to create databases and users under their account.  I'm not quite
> > > sure how they have enabled specific user accounts for access to my
> > > specific part of the server, but I do know that when I log in I have
> > > access to a cpanel interface, email configuration, all of my files on the
> > > server, access to create new postgres databases and a link to phpPgAdmin.
> >
> > Do you get the option to create a new database user? You could create
> > a new user and give that user access to your database.
> >
>
> Yeah, we're just window dressing atop the postgresql authentication scheme,
> which means we allow you to do anything you want that postgresql itself
> allows.
>
you should play around with phpPgAdmin for a while and let us know
specifically what we can help you with.

> you should play around with phpPgAdmin for a while and let us know
> specifically what we can help you with.

I've managed to figure out how to fix most of my user access problems, but
I'm still having trouble limiting the list of databases users see when they
log in through phpPgAdmin.

I noticed in the config.inc.php file that I can set a user's view to only
show the databases that they own (via the $conf['owned_only'] = true;
parameter).  What I would like to do is limit the users to seeing the
databases for which they are a user for.  For example I would like to set a
parameter that was something like $conf['user_only'] = true.  This will be
particularly helpful for double checking which users have access to what
databases and also to eliminate confusion for less technically adept users
who would have to wade through all sorts of databases owned by other people
on the shared server my hosting company provides.

Thanks,
Courtenay

postgresql@teska.net wrote:

you should play around with phpPgAdmin for a while and let us know
specifically what we can help you with.   

in pg_hba.conf you can set it so that users have access to any database if they are in a group (or do we say "role" now?) that has the same name as the database.

This is nifty because it lets you control everything after editing pg_hba.conf only once.  For each database create a group of the same name. Give users access by putting them into the group, revoke access by taking them out.

I also generally grant that particular group NO permissions, actual permissions to work with tables are granted by membership in more specialized groups.

I've managed to figure out how to fix most of my user access problems, but
I'm still having trouble limiting the list of databases users see when they
log in through phpPgAdmin.

I noticed in the config.inc.php file that I can set a user's view to only
show the databases that they own (via the $conf['owned_only'] = true;
parameter).  What I would like to do is limit the users to seeing the
databases for which they are a user for.  For example I would like to set a
parameter that was something like $conf['user_only'] = true.  This will be
particularly helpful for double checking which users have access to what
databases and also to eliminate confusion for less technically adept users
who would have to wade through all sorts of databases owned by other people
on the shared server my hosting company provides.

Thanks,
Courtenay


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

> you should play around with phpPgAdmin for a while and let us know
> specifically what we can help you with.

I've managed to figure out how to fix most of my user access problems, but
I'm still having trouble limiting the list of databases users see when they
log in through phpPgAdmin.

I noticed in the config.inc.php file that I can set a user's view to only
show the databases that they own (via the $conf['owned_only'] = true;
parameter).  What I would like to do is limit the users to seeing the
databases for which they are a user for.  For example I would like to set a
parameter that was something like $conf['user_only'] = true.  This will be
particularly helpful for double checking which users have access to what
databases and also to eliminate confusion for less technically adept users
who would have to wade through all sorts of databases owned by other people
on the shared server my hosting company provides.

Thanks,
Courtenay