Обсуждение: Multiple Servers
I have a client who's looking to start using PostgreSQL. Unlike MySQL, Postgres doesn't have the best (in my opionion) setup for letting multiple users develop using the same server. So what I'm looking to do is setup two Postgres servers on one machine with 1 IP. One of the servers can run with only a socket, but the other needs a TCP port. My question would be, does anyone have any links or tips on how I could set up to servers to run on one machine? Thanks Chris
Chris Cameron <chris@upnix.com> writes:
> I have a client who's looking to start using PostgreSQL. Unlike MySQL,
> Postgres doesn't have the best (in my opionion) setup for letting multiple
> users develop using the same server.
?? Possibly you're just poorly informed. You can easily give each of
them their own database under a single server. Or, if you want a really
airtight separation between users, give 'em each their own postmaster
(separate port number and data directory for each postmaster). I'd say
separate postmasters would only be necessary if you expected the users
to crash their servers regularly --- which one hopes they can't --- or
if you want to give them superuser privileges without thereby trusting
them not to muck up each others' databases.
Since I don't know much about MySQL administration, I don't know what
you find superior in its setup options, but I'd be interested to hear.
regards, tom lane
I honestly like Postgres more than MySQL, but having users that can either create an unlimited amount of databases, or can only play with pre-created tables really isn't ideal. I am biased as I started my life with databases on MySQL, but to me, the way their user system is setup makes more sense. Though I know most think the opposite. Chris At 05:42 PM 8/20/2001 -0700, you wrote: >On Mon, Aug 20, 2001 at 01:10:28PM -0600, Chris Cameron wrote: > > I have a client who's looking to start using PostgreSQL. Unlike > > MySQL, Postgres doesn't have the best (in my opionion) setup for > > letting multiple users develop using the same server. > >Why don't you think so? > >We have a development system and a production system. We run a >single cluster on the development box, and each developer has a >PostgreSQL user id that has create DB but not create user >permissions. They happily whale away without any obvious problems. > >Now, if PostgreSQL were unreliable and we had to stop and start it >(like we did with Informix) a lot, then I could see running seperate >clusters on one machine, so one could be bounced without affecting >the others. It's not that hard to do, but again--why would you want >to? > > -crl >-- >Chad R. Larson (CRL22) chad@eldocomp.com > Eldorado Computing, Inc. 602-604-3100 > 5353 North 16th Street, Suite 400 > Phoenix, Arizona 85016-3228
Sorry for the interruption into this discussion, but it is very similar to the question I posted two weeks ago (http://fts.postgresql.org/db/mw/msg.html?mid=1027722). I have since revised my ideas of how my databases should be set up, ie: * Users need to have superuser access to their OWN databases * Users should not have the ability to create users or databases * Users _need_ to be able to create tables, modify tables, drop tables, etc I hope that I don't need to run multiple postmasters to get this done, as the DB machine is .. well .. needing some hardware upgrades. My own [twisted, non-programmer] logic tells me that I should be able to create a database, add a user, and grant all on <database> for that specific user. But, my SQL abilities are almost nil, and when I try to grant such things on a database, it denies me (No, I don't recall the error. Yes, I will duplicate it and provide the _exact_ error). :/ Specifics: PostgreSQL 7.1.2, OpenBSD 2.9 (DB), OpenBSD 2.8 on the client side. If anyone can give me some hints on how to do this, I would be VERY appreciative. Benny ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ You see, we're leveraging the synergies of our existing open source solution, without reliance on a single vendor. Or in english: We use samba cause NT sucks ass. --greg@rage.net
Actually, that's my exact same problem AND setup. Making a user that can create databases, but not users is a decent idea. That's what I'll be doing for my more trusted users. However, beyond that it looks like the only choice is to run multipule postmasters. Chris At 08:48 PM 8/20/2001 -0500, C. Bensend wrote: >Sorry for the interruption into this discussion, but it >is very similar to the question I posted two weeks ago >(http://fts.postgresql.org/db/mw/msg.html?mid=1027722). > >I have since revised my ideas of how my databases should >be set up, ie: > > * Users need to have superuser access to their OWN > databases > * Users should not have the ability to create users > or databases > * Users _need_ to be able to create tables, modify > tables, drop tables, etc > >I hope that I don't need to run multiple postmasters to get >this done, as the DB machine is .. well .. needing some hardware >upgrades. > >My own [twisted, non-programmer] logic tells me that I should >be able to create a database, add a user, and grant all on ><database> for that specific user. But, my SQL abilities are >almost nil, and when I try to grant such things on a database, >it denies me (No, I don't recall the error. Yes, I will >duplicate it and provide the _exact_ error). :/ > >Specifics: PostgreSQL 7.1.2, OpenBSD 2.9 (DB), OpenBSD 2.8 on >the client side. > >If anyone can give me some hints on how to do this, I would be >VERY appreciative. > >Benny > > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >You see, we're leveraging the synergies of our existing open source >solution, without reliance on a single vendor. >Or in english: We use samba cause NT sucks ass. > --greg@rage.net > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Chris Cameron <chris@upnix.com> writes:
> I honestly like Postgres more than MySQL, but having users that can either
> create an unlimited amount of databases, or can only play with pre-created
> tables really isn't ideal.
Whatever makes you think that those are the choices?
Actually, we regularly get complaints because there *isn't* any way to
make a user so unprivileged that he can't create new tables. But for
development scenarios it doesn't seem like that would be your gripe.
regards, tom lane
"C. Bensend" <benny@bennyvision.com> writes:
> I have since revised my ideas of how my databases should
> be set up, ie:
> * Users need to have superuser access to their OWN
> databases
> * Users should not have the ability to create users
> or databases
> * Users _need_ to be able to create tables, modify
> tables, drop tables, etc
Do they really need to be superusers, as opposed to just database
owners? ISTM that what you are really after is simply that each
user is the owner of his own database, and has no other special
privileges.
regards, tom lane
On Mon, 20 Aug 2001, Chris Cameron wrote:
> Actually, that's my exact same problem AND setup.
>
> Making a user that can create databases, but not users is a decent idea.
> That's what I'll be doing for my more trusted users. However, beyond that
> it looks like the only choice is to run multipule postmasters.
Well, kinda... I don't want my users to be able to create
databases. I only want them to create tables, modify
tables, etc.
_I_, as the hosting provider, will create the database for
them. And I will add their user. From then on, said user
should only be able to create/modify/drop tables, etc, WITHIN
their own database.
Is running multiple postmasters the _only_ way to achieve this?
If so, this is not so good. :/
Benny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You see, we're leveraging the synergies of our existing open source
solution, without reliance on a single vendor.
Or in english: We use samba cause NT sucks ass.
--greg@rage.net
Alright, Seems the general problem is that people (me and "Benny") want to do what can be done with MySQL and be able to add a user, give them their own DB to play with, and leave it at that. However, Postgres doesn't seem to have as slick of a way to do such a thing because to let a user have control over their own database, they would have had to of been able to create the DB. And if they were able to create the DB, they'd be able to make others. I managed to do this (albeit not very gracefully). I created the user 'test' and game him permission to create DB's. Made a database with his username, then took away his ability to create databases. This allowed him to fool with his own DB (because he created it), but he could no longer create new DB's. Still, this seems like a bit of a crummy way to accomplish such a thing. I haven't been around PostgreSQL long enough to know how development takes place, but this is a decent feature to have in a DB I figure. Thanks for all your suggestions, Chris
On Mon, 20 Aug 2001, Tom Lane wrote:
> > * Users need to have superuser access to their OWN
> > databases
> > * Users should not have the ability to create users
> > or databases
> > * Users _need_ to be able to create tables, modify
> > tables, drop tables, etc
>
> Do they really need to be superusers, as opposed to just database
> owners? ISTM that what you are really after is simply that each
> user is the owner of his own database, and has no other special
> privileges.
Hey Tom,
I guess I might be misinterpreting the term "superuser"
in reguards to the database. "The owner of his own database"
is EXACTLY what I am looking for. The user should _not_ be
able to create databases, or users, etc. They should _only_
be able to add/alter/drop tables within their _own_ database.
I would be forever indebted if someone could tell me
_how_ to achieve this. I acknowledge the fact that I am a
complete newbie to DB administration. It's a fine art that
I'm just now diving into. DBA's are artists, while I am the
strange-looking fellow in the corner with a broom. ;)
Thank you for any 2X4's to the head,
Benny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You see, we're leveraging the synergies of our existing open source
solution, without reliance on a single vendor.
Or in english: We use samba cause NT sucks ass.
--greg@rage.net
> Alright, > > Seems the general problem is that people (me and "Benny") want to do what > can be done with MySQL and be able to add a user, give them their own DB to > play with, and leave it at that. However, Postgres doesn't seem to have as > slick of a way to do such a thing because to let a user have control over > their own database, they would have had to of been able to create the DB. > And if they were able to create the DB, they'd be able to make others. > > I managed to do this (albeit not very gracefully). I created the user > 'test' and game him permission to create DB's. Made a database with his > username, then took away his ability to create databases. This allowed him > to fool with his own DB (because he created it), but he could no longer > create new DB's. > > Still, this seems like a bit of a crummy way to accomplish such a thing. I > haven't been around PostgreSQL long enough to know how development takes > place, but this is a decent feature to have in a DB I figure. This is a valid inconvenience. Seems we should allow a createdb option to set the database owner. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I'm talking off the top of my head here as I've never looked specifically at this, but it rings a bell at the back of my head. Can't you change the ownership of a database? Would it not be easy to create a user who cannot create other users or databases, then create a database for that user and than change the ownership of that database to the new user? Gary On Tuesday 21 August 2001 3:30 am, C. Bensend wrote: > On Mon, 20 Aug 2001, Chris Cameron wrote: > > Actually, that's my exact same problem AND setup. > > > > Making a user that can create databases, but not users is a decent idea. > > That's what I'll be doing for my more trusted users. However, beyond that > > it looks like the only choice is to run multipule postmasters. > > Well, kinda... I don't want my users to be able to create > databases. I only want them to create tables, modify > tables, etc. > > _I_, as the hosting provider, will create the database for > them. And I will add their user. From then on, said user > should only be able to create/modify/drop tables, etc, WITHIN > their own database. > > Is running multiple postmasters the _only_ way to achieve this? > If so, this is not so good. :/ > > Benny > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > You see, we're leveraging the synergies of our existing open source > solution, without reliance on a single vendor. > Or in english: We use samba cause NT sucks ass. > --greg@rage.net > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> I'm talking off the top of my head here as I've never looked specifically at
> this, but it rings a bell at the back of my head.
>
> Can't you change the ownership of a database?
> Would it not be easy to create a user who cannot create other users or
> databases, then create a database for that user and than change the ownership
> of that database to the new user?
Added to TODO:
* Make it easier to create a database owned by someone who can't
createdb
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, 20 Aug 2001, Chad R. Larson wrote:
> >etc. They should _only_ be able to add/alter/drop tables within their
> >_own_ database.
>
> So, create a database for the user as the user, but deny him the ability to
> create databases or users.
>
> Then he can dick with his own database to his heart's desire, but that
> would be his only sandbox.
Hey Chad && list,
This is what I ended up doing for a solution.
Yes, very workable. It was just a few more steps than
I expected (mostly because I thought I was missing
something silly). :)
Thanks for all your responses!
Benny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You see, we're leveraging the synergies of our existing open source
solution, without reliance on a single vendor.
Or in english: We use samba cause NT sucks ass.
--greg@rage.net
On Tue, 21 Aug 2001 07:05:41 -0500 (CDT) "C. Bensend" <benny@bennyvision.com> wrote: CB> > So, create a database for the user as the user, but deny him the ability to CB> > create databases or users. [...] CB> This is what I ended up doing for a solution. CB> Yes, very workable. It was just a few more steps than I've found out a small problem with this procedure: you won't be able to restore the data after a dumpall, unless you manually edit the dump first to add CREATEDB to the CREATEUSER commands and then manually remove the permissions after the restore. Hopefully one shouldn't need to restore everything so often. -- Simone Tellini E-mail: tellini@areabusiness.it http://www.areabusiness.it
"Chad R. Larson" <chad@DCFinc.com> writes:
> So, create a database for the user as the user, but deny him the ability to
> create databases or users.
This is actually a tad easier said than done, since the only way we
provide to create a database owned by a user is to create it *as* that
user. (CREATE DATABASE probably ought to have an OWNER option available
only to superusers, now that I think about it.) So you have two
possible approaches:
1. create user with createdb option, become user, create his database,
become superuser again, ALTER USER user NOCREATEDB.
2. create user without createdb option, create his database (as
yourself), then alter pg_database to make him the owner:
update pg_database set datdba =
(select usesysid from pg_shadow where usename = 'foo')
where datname = 'foo';
> Then he can dick with his own database to his heart's desire, but that
> would be his only sandbox.
You'll probably also want to ensure that each user can only connect to
his own database. The 'sameuser' option in pg_hba.conf will help here.
Don't forget to leave an escape hatch for the superuser (ie, you) to
connect to anything.
regards, tom lane