Thread: 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:
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.
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.you should play around with phpPgAdmin for a while and let us know specifically what we can help you with.
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
Attachment
> 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