pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
createdb -D xxxx not working
7+ messages / 4 participants
[nested] [flat]

* createdb -D xxxx not working
@ 2000-01-08 06:02  Chris Griffin <cgriffin@websales.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Chris Griffin @ 2000-01-08 06:02 UTC (permalink / raw)
  To: pgsql-sql


 This is a transcript of what I am trying:

# setenv PGDATA2 /web/sites/1.192/db-data
# initlocation PGDATA2
initlocation: input argument points to /web/sites/1.192/db-data
We are initializing the database area with username root (uid=0).
This user will own all the files and must also own the server process.

Creating Postgres database system directory /web/sites/1.192/db-data

Creating Postgres database system directory /web/sites/1.192/db-data/base

# createdb -D PGDATA2
ERROR:  Unable to locate path 'PGDATA2/root'
        This may be due to a missing environment variable in the server
createdb: database creation failed on root.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] createdb -D xxxx not working
@ 2000-01-08 06:47  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Griffin <cgriffin@websales.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2000-01-08 06:47 UTC (permalink / raw)
  To: Chris Griffin <cgriffin@websales.com>; +Cc: pgsql-sql

Chris Griffin <cgriffin@websales.com> writes:
> # setenv PGDATA2 /web/sites/1.192/db-data
> # initlocation PGDATA2
> # createdb -D PGDATA2

Surely you want $PGDATA2 in the latter two lines?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] createdb -D xxxx not working
@ 2000-01-11 13:26  Peter Eisentraut <peter_e@gmx.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Peter Eisentraut @ 2000-01-11 13:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Griffin <cgriffin@websales.com>; pgsql-sql

On 2000-01-08, Tom Lane mentioned:

> Chris Griffin <cgriffin@websales.com> writes:
> > # setenv PGDATA2 /web/sites/1.192/db-data
> > # initlocation PGDATA2
> > # createdb -D PGDATA2
> 
> Surely you want $PGDATA2 in the latter two lines?

You might recall me mentioning that the whole alternative location
business doesn't work in the first place. I'll fix that this week; Chris
will have to wait until the next release.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] createdb -D xxxx not working
@ 2000-01-11 15:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2000-01-11 15:14 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql; pgsql-hackers@postgreSQL.org

[hackers added to cc: list]

Peter Eisentraut <peter_e@gmx.net> writes:
> On 2000-01-08, Tom Lane mentioned:
>> Chris Griffin <cgriffin@websales.com> writes:
>>>> # setenv PGDATA2 /web/sites/1.192/db-data
>>>> # initlocation PGDATA2
>>>> # createdb -D PGDATA2
>> 
>> Surely you want $PGDATA2 in the latter two lines?

> You might recall me mentioning that the whole alternative location
> business doesn't work in the first place. I'll fix that this week; Chris
> will have to wait until the next release.

Fine.  BTW, am I the only one who thinks that it's really silly for
initlocation to expect to be given an unexpanded environment variable
name?  That's not normal practice (either elsewhere in Postgres, or
anywhere else that I know of).  It's confusing because it's not how
an ordinary Unix user would expect a shell command to behave, and it
doesn't buy any functionality that I can see.  I'd vote for taking
out the auto-expansion, so that the correct invocation becomes
	initlocation $PGDATA2
which is what an average user would expect.

Actually, after looking at what initlocation really does, which is
darn near nothing, I wonder whether we shouldn't dispense with it
altogether...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [HACKERS] Re: [SQL] createdb -D xxxx not working
@ 2000-01-11 15:57  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Thomas Lockhart @ 2000-01-11 15:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql; pgsql-hackers@postgreSQL.org

> >> Surely you want $PGDATA2 in the latter two lines?
> > You might recall me mentioning that the whole alternative location
> > business doesn't work in the first place.

I'm not recalling the details here; what is the problem? It works for
me (but then I wrote it ;)

> Fine.  BTW, am I the only one who thinks that it's really silly for
> initlocation to expect to be given an unexpanded environment variable
> name?  That's not normal practice (either elsewhere in Postgres, or
> anywhere else that I know of).  It's confusing because it's not how
> an ordinary Unix user would expect a shell command to behave, and it
> doesn't buy any functionality that I can see.

It is not silly at all, unless you want to shoot holes in the basic
premise of the alternate location. This is described in the docs, but
the short form is:

initlocation is used to create the directory structure *with correct
permissions* for an alternate location. It takes an environment
variable because usually the backend should have that same environment
variable defined to ensure consistancy and as a security measure. The
environment variable can be expanded or not; initlocation does the
right thing in either case.

createdb takes a "-D" argument, which specifies the alternate
location. Unless allowed at compile-time, via the
ALLOW_ABSOLUTE_DBPATHS variable, all references to an alternate
location must refer to an environment variable, to give the dbadmin
control over *where* alternate locations will appear. The mechanism is
enforced by the backend, by looking for a directory delimiter in the
datpath field of pg_database, then expanding the first field as an
environment variable. On my system, with one database in an alternate
location, this table looks like:

test=# select * from pg_database;
  datname   | datdba | encoding |   datpath    
------------+--------+----------+--------------
 template1  |    100 |        0 | template1
 postgres   |    100 |        0 | postgres
 regression |    100 |        0 | regression
 test       |    100 |        0 | PGDATA2/test
(4 rows)

So, this works:

[postgres@golem parser]$ createdb -D PGDATA2 test2
CREATE DATABASE

But this does not (but can be enabled at compile-time if the sysadmin
wants to allow users to scatter databases everywhere (?!!)):

[postgres@golem regress]$ createdb -D /opt/postgres/data2 test2
ERROR:  The path '/opt/postgres/data2/test2' is invalid.
This may be due to a missing environment variable on the server.
createdb: Database creation failed.

> I'd vote for taking
> out the auto-expansion, so that the correct invocation becomes
>         initlocation $PGDATA2
> which is what an average user would expect.

But the average user does not necessarily have access to the PGDATA2
environment variable! This is usually a sysadmin function.

> Actually, after looking at what initlocation really does, which is
> darn near nothing, I wonder whether we shouldn't dispense with it
> altogether...

?? I hope the explanation above helps...

                    - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [HACKERS] Re: [SQL] createdb -D xxxx not working
@ 2000-01-12 03:30  Peter Eisentraut <peter_e@gmx.net>
  parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
  0 siblings, 1 reply; 7+ messages in thread

From: Peter Eisentraut @ 2000-01-12 03:30 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org

On 2000-01-11, Thomas Lockhart mentioned:

> > >> Surely you want $PGDATA2 in the latter two lines?
> > > You might recall me mentioning that the whole alternative location
> > > business doesn't work in the first place.
> 
> I'm not recalling the details here; what is the problem? It works for
> me (but then I wrote it ;)
> 

Darn, now I already rewrote the thing to where I considered it working. We
had several on-and-off discussions (mostly Tom and I) about it during the
last two months at least, including users complaining.

The sort of scheme I came up with scraps the environment variable stuff
(since it's not quite safe either and has several bugs in the code that
create a bunch of problems along the way) and lets the following
happen (example paths):

CREATE DATABASE foo;	--> /usr/local/pgsql/data/base/foo/pg_class
CREATE DATABASE foo WITH LOCATION 'bar';
			--> /usr/local/pgsql/data/base/bar/pg_class
CREATE DATABASE foo WITH LOCATION '/some/where';
			--> /some/where/pg_class
CREATE DATABASE foo WITH LOCATION 'foo/bar';
			is disabled. I suppose I could stick the
environment variable deal back in so that users don't have to remember any
complicated paths, but this is the dbadmin's job anyway, so he should be
able to remember it.

Anyway, in order for a path to be allowed it has to be listed in
PG_ALTLOC, which is a colon-delimited, marginally wildcard enabled list of
allowed locations. In some future life this could be included in a
configuration file.


> initlocation is used to create the directory structure *with correct
> permissions* for an alternate location. It takes an environment

I think one of the problems was actually that the path initlocation
assumed and the one createdb worked with were different.

> variable because usually the backend should have that same environment
> variable defined to ensure consistancy and as a security measure. The
> environment variable can be expanded or not; initlocation does the
> right thing in either case.

It's false security though, since for complete security the dbadmin would
have to delete all other environment variables altogether.

> > I'd vote for taking
> > out the auto-expansion, so that the correct invocation becomes
> >         initlocation $PGDATA2
> > which is what an average user would expect.
> 
> But the average user does not necessarily have access to the PGDATA2
> environment variable! This is usually a sysadmin function.

The average user doesn't even have access to the machine the database is
running on, so he can't do any initlocation either way. Then again the
average user doesn't create databases either. But there is no real point
for initlocation since a mere mkdir() call in createdb() will do the job.


Now I just got done with that coding 10 minutes ago but now that someone
actually spoke up in defence of this mechanism I'm going to wait and see
what you think about the revised (or any) scheme.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [HACKERS] Re: [SQL] createdb -D xxxx not working
@ 2000-01-12 16:26  Thomas Lockhart <lockhart@alumni.caltech.edu>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Thomas Lockhart @ 2000-01-12 16:26 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org

> Darn, now I already rewrote the thing to where I considered it working. We
> had several on-and-off discussions (mostly Tom and I) about it during the
> last two months at least, including users complaining.

afaik I've been on the hackers list most of the time, and don't recall
significant discussion.

> The sort of scheme I came up with scraps the environment variable stuff
> (since it's not quite safe either and has several bugs in the code that
> create a bunch of problems along the way) and lets the following
> happen (example paths):
> 
> CREATE DATABASE foo;    --> /usr/local/pgsql/data/base/foo/pg_class
> CREATE DATABASE foo WITH LOCATION 'bar';
>                         --> /usr/local/pgsql/data/base/bar/pg_class
> CREATE DATABASE foo WITH LOCATION '/some/where';
>                         --> /some/where/pg_class
> CREATE DATABASE foo WITH LOCATION 'foo/bar';
>                         is disabled. I suppose I could stick the
> environment variable deal back in so that users don't have to remember any
> complicated paths, but this is the dbadmin's job anyway, so he should be
> able to remember it.

Huh? The "environment variable deal" is intended to provide security
and easier operation. The examples above which allow specifying
absolute paths (e.g. "/some/where/pg_class") are specifically
disallowed by default, for a reason. If there were bugs in that, let's
talk about it, but I'm *very* uncomfortable making wholesale changes
without a discussion. And what exactly were "several bugs in the
code"?? Better be specific; that's my code we're talking about :/

It sounds like you and Tom Lane have had discussions on this, but I'm
not certain it was with a clear understanding of what the environment
variables provided. Before killing the capability, I'd like to make
sure that we understand what it did.

otoh, you have a new proposal (the first I've heard of it afaik)...

> Anyway, in order for a path to be allowed it has to be listed in
> PG_ALTLOC, which is a colon-delimited, marginally wildcard enabled list of
> allowed locations. In some future life this could be included in a
> configuration file.

That sounds like a good capability. There is no reason (yet) why the
environment variable mechanism can not use this too; e.g.

  setenv PG_ALTLOC PGDATA2:/home/peter/unsafe/unprotected/open/path

(just had to put that last one in :)))

> > initlocation is used to create the directory structure *with correct
> > permissions* for an alternate location. It takes an environment
> I think one of the problems was actually that the path initlocation
> assumed and the one createdb worked with were different.

I'm not sure to what you are referring. To the fact that there is an
implicit "/base" in the actual path? e.g. 

initlocation PGDATA2
createdb -D PGDATA2 test

gives an actual path $PGDATA2/base/pg_class ? That's a
security/integrity benefit since:

1) there is not likely to be a random environment variable which
happens to point to a valid directory which *also* has a subdirectory
called "base".

2) it reduces the chance that a user/dbadmin will not use initlocation
to create the database area, hence reducing the chance that a
user/dbadmin has not set the permissions correctly.

> > variable because usually the backend should have that same environment
> > variable defined to ensure consistancy and as a security measure. The
> > environment variable can be expanded or not; initlocation does the
> > right thing in either case.
> It's false security though, since for complete security the dbadmin would
> have to delete all other environment variables altogether.

Theoretically true (though low-risk per above discussion), but this
would be addressed by your PG_ALTLOC augmentation, which I like btw.

> > > I'd vote for taking
> > > out the auto-expansion, so that the correct invocation becomes
> > >         initlocation $PGDATA2
> > > which is what an average user would expect.
> > But the average user does not necessarily have access to the PGDATA2
> > environment variable! This is usually a sysadmin function.
> The average user doesn't even have access to the machine the database is
> running on, so he can't do any initlocation either way. Then again the
> average user doesn't create databases either. But there is no real point
> for initlocation since a mere mkdir() call in createdb() will do the job.

Hmm. Until I see what you are actually doing, I can't comment on
whether this is indeed adequate. But decoupling the location creation
from database creation give the dbadmin control over how permissions
are set and where databases can be created. I'm not sure that is the
case if it is all done within the backend at database creation time.

> Now I just got done with that coding 10 minutes ago but now that someone
> actually spoke up in defence of this mechanism I'm going to wait and see
> what you think about the revised (or any) scheme.

PG_ALTLOC seems to be a great feature. But afaict the environment
variable feature is useful, safe (as much or more so than absolute
paths, anyway), can coexist, and is a convenience. 

I'd like to continue the discussion until I'm convinced, or least
beaten into submission ;)

                          - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-01-12 16:26 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-08 06:02 createdb -D xxxx not working Chris Griffin <cgriffin@websales.com>
2000-01-08 06:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-11 13:26   ` Peter Eisentraut <peter_e@gmx.net>
2000-01-11 15:14     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-11 15:57       ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-01-12 03:30         ` Peter Eisentraut <peter_e@gmx.net>
2000-01-12 16:26           ` Thomas Lockhart <lockhart@alumni.caltech.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