pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedEmbedded App and User Authentication
7+ messages / 3 participants
[nested] [flat]
* Embedded App and User Authentication
@ 2006-08-30 22:56 Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Jeffrey J. Early @ 2006-08-30 22:56 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Embedded App and User Authentication
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
@ 2006-08-31 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 16:38 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-08-31 14:26 UTC (permalink / raw)
To: jearly@alum.ups.edu; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Embedded App and User Authentication
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-08-31 16:38 ` Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 17:33 ` Re: Embedded App and User Authentication John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jeffrey J. Early @ 2006-08-31 16:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Embedded App and User Authentication
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 16:38 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
@ 2006-08-31 17:33 ` John DeSoi <desoi@pgedit.com>
2006-08-31 18:49 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
0 siblings, 1 reply; 7+ messages in thread
From: John DeSoi @ 2006-08-31 17:33 UTC (permalink / raw)
To: jearly@alum.ups.edu; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Embedded App and User Authentication
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 16:38 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 17:33 ` Re: Embedded App and User Authentication John DeSoi <desoi@pgedit.com>
@ 2006-08-31 18:49 ` Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 19:53 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Jeffrey J. Early @ 2006-08-31 18:49 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Embedded App and User Authentication
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 16:38 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 17:33 ` Re: Embedded App and User Authentication John DeSoi <desoi@pgedit.com>
2006-08-31 18:49 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
@ 2006-08-31 19:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 20:05 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-08-31 19:53 UTC (permalink / raw)
To: jearly@alum.ups.edu; +Cc: John DeSoi <desoi@pgedit.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Embedded App and User Authentication
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 16:38 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 17:33 ` Re: Embedded App and User Authentication John DeSoi <desoi@pgedit.com>
2006-08-31 18:49 ` Re: Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 19:53 ` Re: Embedded App and User Authentication Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-08-31 20:05 ` Jeffrey J. Early <jearly@alum.ups.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Jeffrey J. Early @ 2006-08-31 20:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John DeSoi <desoi@pgedit.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-08-31 20:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-30 22:56 Embedded App and User Authentication Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 16:38 ` Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 17:33 ` John DeSoi <desoi@pgedit.com>
2006-08-31 18:49 ` Jeffrey J. Early <jearly@alum.ups.edu>
2006-08-31 19:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 20:05 ` Jeffrey J. Early <jearly@alum.ups.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox