Fix for pg_upgrade user flag

On Sat, May 7, 2011 at 8:56 AM, Bruce Momjian <bruce@momjian.us> wrote:

The attached, applied patch checks that the pg_upgrade user specified is
a super-user.  It also reports the error message when the post-pg_ctl
connection fails.

This was prompted by a private bug report from EnterpriseDB.

It strikes me that it's fairly crazy to think you're going to be able
to catch all the possible errors the server might throw this way.
Don't we need some way of letting the actual server errors leak out?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, May 7, 2011 at 9:50 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, May 7, 2011 at 8:56 AM, Bruce Momjian <bruce@momjian.us> wrote:

The attached, applied patch checks that the pg_upgrade user specified is
a super-user.  It also reports the error message when the post-pg_ctl
connection fails.

This was prompted by a private bug report from EnterpriseDB.

It strikes me that it's fairly crazy to think you're going to be able
to catch all the possible errors the server might throw this way.
Don't we need some way of letting the actual server errors leak out?

Or, hmm. Maybe you just did that. If so, never mind. :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:

On Sat, May 7, 2011 at 9:50 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, May 7, 2011 at 8:56 AM, Bruce Momjian <bruce@momjian.us> wrote:

The attached, applied patch checks that the pg_upgrade user specified is
a super-user. ?It also reports the error message when the post-pg_ctl
connection fails.

This was prompted by a private bug report from EnterpriseDB.

It strikes me that it's fairly crazy to think you're going to be able
to catch all the possible errors the server might throw this way.
Don't we need some way of letting the actual server errors leak out?

Or, hmm. Maybe you just did that. If so, never mind. :-)

What I did was to report the errors of our first database probe after we
started the server --- for some reason, that code was not reporting the
libpq error message, while all other failed connections did.

The second change was to only run pg_upgrade as a database super-user,
and hopefully that will avoid odd pg_dump error messages.

One question I have is why we even bother to allow the database username
to be specified? Shouldn't we just hard-code that to 'postgres'? Is
there any reason to allow another username to be used? You can't drop
the postgres user but you can remove super-user permissions from it so
maybe we have to continue allowing it:

postgres=> drop user postgres;
ERROR: cannot drop role postgres because it is required by the database system
postgres=> alter user postgres nosuperuser;
ALTER ROLE

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:

One question I have is why we even bother to allow the database username
to be specified? Shouldn't we just hard-code that to 'postgres'?

Only if you want to render pg_upgrade unusable by a significant fraction
of people. "postgres" is not the hard wired name of the bootstrap
superuser.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

One question I have is why we even bother to allow the database username
to be specified? Shouldn't we just hard-code that to 'postgres'?

Only if you want to render pg_upgrade unusable by a significant fraction
of people. "postgres" is not the hard wired name of the bootstrap
superuser.

I was really wondering if I should be using that hard-coded name, rather
than allowing the user to supply it. They have to compile in a
different name, and I assume that name is accessible somewhere.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian wrote:
Tom Lane wrote:

Bruce Momjian writes:

One question I have is why we even bother to allow the database
username to be specified? Shouldn't we just hard-code that to
'postgres'?

Only if you want to render pg_upgrade unusable by a significant
fraction of people. "postgres" is not the hard wired name of the
bootstrap superuser.

I was really wondering if I should be using that hard-coded name,
rather than allowing the user to supply it. They have to compile in
a different name, and I assume that name is accessible somewhere.

At home, on my ubuntu machine, I built and ran initdb without
specifying a superuser. It used my OS login of "kevin". Then,

test=# \du
List of roles
Role name | Attributes | Member
of
-----------+------------------------------------------------+--------
---
kevin | Superuser, Create role, Create DB, Replication | {}

test=# create user xxx superuser;
CREATE ROLE
test=# \c test xxx
You are now connected to database "test" as user "xxx".
test=# alter user kevin rename to yyy;
ALTER ROLE
test=# \du
List of roles
Role name | Attributes | Member
of
-----------+------------------------------------------------+--------
---
xxx | Superuser, Replication | {}
yyy | Superuser, Create role, Create DB, Replication | {}

If I run pg_upgrade now, what will it pick? How?

-Kevin

On lör, 2011-05-07 at 13:50 -0400, Bruce Momjian wrote:

I was really wondering if I should be using that hard-coded name,
rather than allowing the user to supply it. They have to compile in a
different name, and I assume that name is accessible somewhere.

"postgres" is not compiled in. It's whatever user you run initdb under.
In particular, in the regression tests, it is probably not "postgres".

Kevin Grittner wrote:

Bruce Momjian wrote:
Tom Lane wrote:

Bruce Momjian writes:

One question I have is why we even bother to allow the database
username to be specified? Shouldn't we just hard-code that to
'postgres'?

Only if you want to render pg_upgrade unusable by a significant
fraction of people. "postgres" is not the hard wired name of the
bootstrap superuser.

I was really wondering if I should be using that hard-coded name,
rather than allowing the user to supply it. They have to compile in
a different name, and I assume that name is accessible somewhere.

At home, on my ubuntu machine, I built and ran initdb without
specifying a superuser. It used my OS login of "kevin". Then,

test=# \du
List of roles
Role name | Attributes | Member
of
-----------+------------------------------------------------+--------
---
kevin | Superuser, Create role, Create DB, Replication | {}

test=# create user xxx superuser;
CREATE ROLE
test=# \c test xxx
You are now connected to database "test" as user "xxx".
test=# alter user kevin rename to yyy;
ALTER ROLE
test=# \du
List of roles
Role name | Attributes | Member
of
-----------+------------------------------------------------+--------
---
xxx | Superuser, Replication | {}
yyy | Superuser, Create role, Create DB, Replication | {}

If I run pg_upgrade now, what will it pick? How?

Good point --- you would need the user flag in pg_upgrade. Thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Peter Eisentraut wrote:

On l?r, 2011-05-07 at 13:50 -0400, Bruce Momjian wrote:

I was really wondering if I should be using that hard-coded name,
rather than allowing the user to supply it. They have to compile in a
different name, and I assume that name is accessible somewhere.

"postgres" is not compiled in. It's whatever user you run initdb under.
In particular, in the regression tests, it is probably not "postgres".

Thanks. I get confused because the 'postgres' database is hardcoded in,
but not the username. Not sure why I am so easily confused.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 05/07/2011 06:48 PM, Bruce Momjian wrote:

"postgres" is not compiled in. It's whatever user you run initdb under.
In particular, in the regression tests, it is probably not "postgres".

Thanks. I get confused because the 'postgres' database is hardcoded in,
but not the username. Not sure why I am so easily confused.

There is a requirement for a known database name, but no requirement for
a known superuser name.

cheers

andrew