Обсуждение: Embedded App and User Authentication
I have an embedded application on MacOS X that bundles the postgresql apps and a database cluster with the OSX application, but am running into troubles when trying to launch the application under different usernames. In particular, if I create a database with 'initdb' under a username user1, I can launch postmaster pointing to that database cluster on any system I'm logged into with the username user1. However, as soon as I move to a system where I'm logged in as user2, postmaster will fail to launch with the error message "FATAL: role "user2" does not exist" . Does anybody know of a way around this? I can't seem to find a way to turn this type of authentication off. I also looked into creating a 'ROLE' that would give anybody access, but can't see how to do that. Thanks for your help, Jeffrey
"Jeffrey J. Early" <jearly@alum.ups.edu> writes:
> I have an embedded application on MacOS X that bundles the postgresql apps
> and a database cluster with the OSX application, but am running into
> troubles when trying to launch the application under different usernames.
> In particular, if I create a database with 'initdb' under a username user1,
> I can launch postmaster pointing to that database cluster on any system I'm
> logged into with the username user1. However, as soon as I move to a system
> where I'm logged in as user2, postmaster will fail to launch with the error
> message "FATAL: role "user2" does not exist" .
You really need to redo the initdb on each installed system anyway,
so my advice is "don't do it that way".
PG isn't designed to be used as an embedded database. Perhaps you'd be
better off with SQLite?
regards, tom lane
on 8/31/06 7:26 AM, Tom Lane at tgl@sss.pgh.pa.us wrote: > "Jeffrey J. Early" <jearly@alum.ups.edu> writes: >> I have an embedded application on MacOS X that bundles the postgresql apps >> and a database cluster with the OSX application, but am running into >> troubles when trying to launch the application under different usernames. > >> In particular, if I create a database with 'initdb' under a username user1, >> I can launch postmaster pointing to that database cluster on any system I'm >> logged into with the username user1. However, as soon as I move to a system >> where I'm logged in as user2, postmaster will fail to launch with the error >> message "FATAL: role "user2" does not exist" . > > You really need to redo the initdb on each installed system anyway, > so my advice is "don't do it that way". > > PG isn't designed to be used as an embedded database. Perhaps you'd be > better off with SQLite? > > regards, tom lane I would love to use SQLite, but unfortunately I need PostGIS functionality for geometric data types and spatial indexing. I've explored all the other options I could find and it seems like it has to be PostgreSQL. I'll take other solutions if you think there are some. Given that it looks like I'm stuck with PostgreSQL, do you know any way around this authentication. Picking up a database cluster and moving it from system to system *does* seem to work without a hitch as long as the username is the same on each system. So it seems to me there has to be a fairly simple solution to make this work. Thanks, Jeffrey
I'm assuming you are running initdb without specifying a user name (and thus it defaults to the current user). Have you tried running initdb (and connecting with) a single user name, e.g. postgres? I'm not sure if it will work, but maybe worth a shot. On Aug 31, 2006, at 12:38 PM, Jeffrey J. Early wrote: > Picking up a database cluster and moving it from system to system > *does* > seem to work without a hitch as long as the username is the same on > each > system. So it seems to me there has to be a fairly simple solution > to make > this work. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
John, Hey thanks! I figured out the answer while responding to your suggestion. I had started to write: Yes, I gave that a shot too, but it didn't work. The problem isn't connecting to the database, but just launching postmaster (with pg_ctl) pointing to that database cluster. Launching fails if the "user launching postmaster" != "the user specified in initdb". It turns out the problem is with launching postmaster via pg_ctl -- in fact, pg_ctl seems to do this user authentication test, but postmaster doesn't. So I found that if I launch postmaster directly, no problem. Nice security :-) Thanks for your suggestions, Jeffrey PS - I still have to do a full blown test where I actually move it to a different machine, but initial tests look solid. on 8/31/06 10:33 AM, John DeSoi at desoi@pgedit.com wrote: > I'm assuming you are running initdb without specifying a user name > (and thus it defaults to the current user). Have you tried running > initdb (and connecting with) a single user name, e.g. postgres? > > I'm not sure if it will work, but maybe worth a shot. > > On Aug 31, 2006, at 12:38 PM, Jeffrey J. Early wrote: > >> Picking up a database cluster and moving it from system to system >> *does* >> seem to work without a hitch as long as the username is the same on >> each >> system. So it seems to me there has to be a fairly simple solution >> to make >> this work. > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
"Jeffrey J. Early" <jearly@alum.ups.edu> writes:
> It turns out the problem is with launching postmaster via pg_ctl -- in fact,
> pg_ctl seems to do this user authentication test, but postmaster doesn't. So
> I found that if I launch postmaster directly, no problem. Nice security :-)
The postmaster may start fine, but are you able to log in? The initial
superuser account is still going to have the old username, so you might
have some issues around obtaining superuser privileges.
regards, tom lane
on 8/31/06 12:53 PM, Tom Lane at tgl@sss.pgh.pa.us wrote: > "Jeffrey J. Early" <jearly@alum.ups.edu> writes: >> It turns out the problem is with launching postmaster via pg_ctl -- in fact, >> pg_ctl seems to do this user authentication test, but postmaster doesn't. So >> I found that if I launch postmaster directly, no problem. Nice security :-) > > The postmaster may start fine, but are you able to log in? The initial > superuser account is still going to have the old username, so you might > have some issues around obtaining superuser privileges. > > regards, tom lane Seems to work okay. I just successfully embedded it in my app and ran it on a different machine with a different username. I can start postmaster, and login with no problems. When I login I can just specify the user that I want to login as. Basic process is this: 1. initdb -D data -U genericuser 2. nohup postmaster -D data >postgres.log 2>&1 </dev/null & 3. createdb test -U genericuser 4. psql -d test -f data.sql -U genericuser > loaddata.log 5. psql -d test -U genericuser So if you replace step 2 with: pg_ctl start -w -D $data -s -l postgres.log Then the database won't start and this won't work. But otherwise I seem to have completely access as I need it on any and all machines. Jeffrey