Thread: More initdb follies

More initdb follies

From
Peter Eisentraut
Date:
In my digging around in initdb, I came to the --username option, which
supposedly allows you to initialize the database system with another
username. Not a bad idea, really.

Obviously, you'd have to be root in that case, because you want to create
files in someone else's name. But if you are root, the backends will
refuse to execute, wisely so. So this option is totally broken.

I propose that I remove it, and that it instead be possible that you can
do

root# su -l postgres -c 'initdb ...'

if you are not a fan of logging in and out of user accounts during the
install process.

That would also remove a whole bunch of the username/id checking logic,
since you just use the user you're logged in as ($UID, $USER), and if the
backend doesn't like it, it will tell you. pg_id adieu.


Another question: Is there a reason why the system views in initdb are all
created with a CREATE TABLE and then with a CREATE RULE, instead of using
CREATE VIEW? Is that a left over from the time before there were views as
such?


Question 3: Is there a reason why the template1 database is vacuumed twice
in the process? Once before all the views are created (no analyze) and
once at the very end (with analyze).

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




Re: [HACKERS] More initdb follies

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> In my digging around in initdb, I came to the --username option, which
> supposedly allows you to initialize the database system with another
> username. Not a bad idea, really.
>
> Obviously, you'd have to be root in that case, because you want to create
> files in someone else's name. But if you are root, the backends will
> refuse to execute, wisely so. So this option is totally broken.
>
> I propose that I remove it,

Makes sense to me --- I was saying more or less the same thing, I think,
when I said that initdb should pay attention to the effective UID it's
run under *and nothing else* to determine the postgres user name/ID.
In particular, if you want to be able to run it via "su", it mustn't
assume that environment variables like LOGNAME or USER are set correctly
for the postgres user.  If it needs the user name it should look it up
from the EUID.

> Another question: Is there a reason why the system views in initdb are all
> created with a CREATE TABLE and then with a CREATE RULE, instead of using
> CREATE VIEW? Is that a left over from the time before there were views as
> such?

Probably, but it's before my time.

> Question 3: Is there a reason why the template1 database is vacuumed twice
> in the process? Once before all the views are created (no analyze) and
> once at the very end (with analyze).

I've wondered about that too.  There are some comments in initdb
suggesting that other orderings might fail, but I wouldn't be surprised
if those are obsolete.  Have you tried altering the procedure?
        regards, tom lane


Re: [HACKERS] More initdb follies

From
Bruce Momjian
Date:
> Question 3: Is there a reason why the template1 database is vacuumed twice
> in the process? Once before all the views are created (no analyze) and
> once at the very end (with analyze).

Yes, there is a reason, though I can't remember why.  We need the
analyze at the end so the system tables are completely optimized, but we
need the earlier vacuum to set some table statistics that don't get set
by the raw load used by initdb.  You can try removing the first one to
see if it works.

Seems it works without the first initdb here.  I will apply a patch to
remove the first initdb.  I think we needed it long ago for some reason.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] More initdb follies

From
Bruce Momjian
Date:
> Question 3: Is there a reason why the template1 database is vacuumed twice
> in the process? Once before all the views are created (no analyze) and
> once at the very end (with analyze).

I see the line before the first vacuum:

# If the COPY is first, the VACUUM generates an error, so we vacuum first

That may have been me adding this.  Seems it is no longer an issue. 
Removed.



--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] More initdb follies

From
"Oliver Elphick"
Date:
Peter Eisentraut wrote: >In my digging around in initdb, I came to the --username option, which >supposedly allows you
toinitialize the database system with another >username. Not a bad idea, really. > >Obviously, you'd have to be root in
thatcase, because you want to create >files in someone else's name. But if you are root, the backends will >refuse to
execute,wisely so. So this option is totally broken. > >I propose that I remove it, and that it instead be possible
thatyou can >do > >root# su -l postgres -c 'initdb ...'
 

You can already do this;  this example is from the Debian package installation
(which runs as root, of course):
       su  postgres -c "cd ${PGHOME}; . ./${PROFILE}; initdb -e ${Encoding} -l
${PGLIB} -r ${PGDATA} -u postgres"

It can be quite tricky, though, since there are a number of different su
versions around.  I would prefer it if root were able to run initdb directly
and set the ownerships as part of the process.  The ability to run as root
should only apply to initdb.

--      Vote against SPAM: http://www.politik-digital.de/spam/                ========================================
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "Let not sin therefore reign in your mortal body,
that     ye should obey it in the lusts thereof."                                              Romans 6:12 
 




Re: [HACKERS] More initdb follies

From
Peter Eisentraut
Date:
On 1999-12-09, Oliver Elphick mentioned:

>   >I propose that I remove it, and that it instead be possible that you can
>   >do
>   >
>   >root# su -l postgres -c 'initdb ...'
> 
> You can already do this;  this example is from the Debian package installation

> It can be quite tricky, though, since there are a number of different su
> versions around.  I would prefer it if root were able to run initdb directly

Of course the user would actually invoke the su himself, so he better know
what his does.

> and set the ownerships as part of the process.  The ability to run as root
> should only apply to initdb.

I don't think there is a good (let alone portable) way to set ownership of
a shell script at run time. While you could do all kinds of funny business
with file ownerships, the postgres backends will still refuse to execute.

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





Re: [HACKERS] More initdb follies

From
Peter Eisentraut
Date:
On 1999-12-08, Tom Lane mentioned:

> Makes sense to me --- I was saying more or less the same thing, I think,
> when I said that initdb should pay attention to the effective UID it's
> run under *and nothing else* to determine the postgres user name/ID.
> In particular, if you want to be able to run it via "su", it mustn't
> assume that environment variables like LOGNAME or USER are set correctly
> for the postgres user.  If it needs the user name it should look it up
> from the EUID.

(The odd thing is, that our installation instructions suggest to su as
postgres and do the install that way, yet this seems to work anyway. On my
system (GNU sh-utils), USER is only set if you 'su -', but it seems
relying on the user doing this right is way too much to ask.)

Hmm, portability issues. Can you be sure EUID is implemented everywhere?
(I guess so.) How do you determine the user name from a UID? That would be
the inverse of what pg_id does now.

An idea I just had would be to use
* First resort: `id -n -u`
* Second resort: `whoami`
* Third resort: --username option

The first two don't distinguish between su and su -, at least here. Not
sure if #3 is necessary though. One of those you should have.

On the other hand, one more thing to think about is that the only place
the superuser's UID and name are actually used, is to initialize the
catalogs. All the files will happily be created under which ever user you
run this as (as they should). Using the above three step approach, you can
choose to name your _database_ superuser whatever you want, independent of
the Unix filesystem concerns. Of course this is nothing to encourage, but
it's possible. I could install the system in my home directory in some
computing lab under my own user name, yet still name the superuser
postgres to have a standard environment within the database. I guess the
--username option does kind of work, just not as expected.

(The next thing would be the ability to choose the database superuser's
usesysid as well, which might not be so far-fetched either, because if
1) you already have 40000 users on your system when you install PostgreSQL
2) you assign the Unix user postgres the uid 400001
3) you add your 40000 users to the database
you end up with usesysid's 40000-80000 (+/- 1). Not the end of the world
but kind of dumb. The you could simply assign 0 (or 1 if zero is not
allowed) to the superuser at initdb time.)

There seem to be a lot of misconceptions (possibly caused by restrictions
in the past) about all the user names and ids of the installation files,
the data directory, the server process, the database users, the database
super user, etc. In fact, most of these can be chosen freely, the only
requirement is that the server process can access the data directory.

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