Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'

Update of /usr/local/cvsroot/pgsql/src/bin/initdb
In directory hub.org:/tmp/cvs-serv23999/bin/initdb

Modified Files:
initdb.sh
Log Message:

Modify initdb.sh so that it makes pg_user readable through view db_user
Modify psql so that it uses db_user instead of pg_user
GRANT SELECT on pg_class

I don't know if I like the new name db_user? What do other people
think? I thought pg_user_no_pwd would be nice.

create view pg_users as ...
ERROR: Illegal class name: pg_users -- pg_ is reserved for system catalogs

--
Bruce Momjian
maillist@candle.pha.pa.us

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Update of /usr/local/cvsroot/pgsql/src/bin/initdb
In directory hub.org:/tmp/cvs-serv23999/bin/initdb

Modified Files:
initdb.sh
Log Message:

Modify initdb.sh so that it makes pg_user readable through view db_user
Modify psql so that it uses db_user instead of pg_user
GRANT SELECT on pg_class

I don't know if I like the new name db_user? What do other people
think? I thought pg_user_no_pwd would be nice.

create view pg_users as ...
ERROR: Illegal class name: pg_users -- pg_ is reserved for system catalogs

Oops, yep.

I guess I just liked that the pg_ stuff was all system stuff. Do you
get this error when creating it from postgres, as initdb does? That
would be a solution. Worked here.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Mon, 23 Feb 1998, Bruce Momjian wrote:

Update of /usr/local/cvsroot/pgsql/src/bin/initdb
In directory hub.org:/tmp/cvs-serv23999/bin/initdb

Modified Files:
initdb.sh
Log Message:

Modify initdb.sh so that it makes pg_user readable through view db_user
Modify psql so that it uses db_user instead of pg_user
GRANT SELECT on pg_class

I don't know if I like the new name db_user? What do other people
think? I thought pg_user_no_pwd would be nice.

create view pg_users as ...
ERROR: Illegal class name: pg_users -- pg_ is reserved for system catalogs

Oops, yep.

I guess I just liked that the pg_ stuff was all system stuff. Do you
get this error when creating it from postgres, as initdb does? That
would be a solution. Worked here.

My thought was more splitting the difference between a system
table (pg_) vs system view (db_) *shrug* Okay, I was grasping here :)

create view pg_users as ...
ERROR: Illegal class name: pg_users -- pg_ is reserved for system catalogs

Oops, yep.

I guess I just liked that the pg_ stuff was all system stuff. Do you
get this error when creating it from postgres, as initdb does? That
would be a solution. Worked here.

My thought was more splitting the difference between a system
table (pg_) vs system view (db_) *shrug* Okay, I was grasping here :)

I am afraid the db_user is going to confuse people, and they are going
to start asking how it got there, or they are going to delete it and ask
why \d doesn't work.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I don't know if I like the new name db_user? What do other people
think? I thought pg_user_no_pwd would be nice.

create view pg_users as ...
ERROR: Illegal class name: pg_users -- pg_ is reserved for system catalogs

Oops, yep.

I guess I just liked that the pg_ stuff was all system stuff. Do you
get this error when creating it from postgres, as initdb does? That
would be a solution. Worked here.

even if running postgres directly from pgsql account.

But I think it's right to have views/tables named db_
or sys_ too. Other RDBMS's have them too (Oracle).

And I don't know if ALL the places where IsSystemRelationName()
is used are happy with things that in fact aren't really
hardwired system catalogs.

Just let's choose one prefix for all of them and use
that then. Maybe we would like to restrict the use of
this prefix to superusers only. As I think there could
be more information in the catalogs that we want to
hide from users in the future, a group of db_ views,
where all the completely open catalogs are just mapped
by SELECT * wouldn't be a bad idea.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

I don't know if I like the new name db_user? What do other people
think? I thought pg_user_no_pwd would be nice.

create view pg_users as ...
ERROR: Illegal class name: pg_users -- pg_ is reserved for system catalogs

Oops, yep.

I guess I just liked that the pg_ stuff was all system stuff. Do you
get this error when creating it from postgres, as initdb does? That
would be a solution. Worked here.

even if running postgres directly from pgsql account.

Here's what I did:

echo 'create view pg_x as select * from pg_user' |aspg postgres -F -Q -D
/u/pg/data template1

I am suggesting we do this in initdb, just like the others.

But I think it's right to have views/tables named db_
or sys_ too. Other RDBMS's have them too (Oracle).

And I don't know if ALL the places where IsSystemRelationName()
is used are happy with things that in fact aren't really
hardwired system catalogs.

Just let's choose one prefix for all of them and use
that then. Maybe we would like to restrict the use of
this prefix to superusers only. As I think there could
be more information in the catalogs that we want to
hide from users in the future, a group of db_ views,
where all the completely open catalogs are just mapped
by SELECT * wouldn't be a bad idea.

Can I suggest pgview_ then? Another problem is that a \d on an empty
database, \d is going to show this view, isn't it. Kind of strange to
me.

I have a solution. Create the view in initdb as pguser_no_pwd, then
after that execute an update statement on pg_class:

update pg_class
set relname = 'pg_user_no_pwd'
where relname = 'pguser_no_pwd';

We are using Jan's cache fix already. I just tried it and it works.
And it means it doesn't show up in \d, and a user can't accidentally
delete it. Sounds like a real winner.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Mon, 23 Feb 1998, Bruce Momjian wrote:

Can I suggest pgview_ then? Another problem is that a \d on an empty
database, \d is going to show this view, isn't it. Kind of strange to
me.

I modified psql already such that:

relname !~ '^[pd][bg]_'

is ignored :)

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

On Mon, 23 Feb 1998, Bruce Momjian wrote:

Can I suggest pgview_ then? Another problem is that a \d on an empty
database, \d is going to show this view, isn't it. Kind of strange to
me.

I modified psql already such that:

relname !~ '^[pd][bg]_'

is ignored :)

I still disagree. Why not keep the system stuff pg_? This can be done,
and it keeps things consistent. The above check also finds pb_ and dg_.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

The Hermit Hacker wrote:

On Mon, 23 Feb 1998, Bruce Momjian wrote:

Can I suggest pgview_ then? Another problem is that a \d on an empty
database, \d is going to show this view, isn't it. Kind of strange to
me.

I modified psql already such that:

relname !~ '^[pd][bg]_'

is ignored :)

Are pb_, db_, dg_ system prefixes now and user can't

CREATE TABLE dg_i_like_this_name...

?

Vadim

On Mon, 23 Feb 1998, Bruce Momjian wrote:

On Mon, 23 Feb 1998, Bruce Momjian wrote:

Can I suggest pgview_ then? Another problem is that a \d on an empty
database, \d is going to show this view, isn't it. Kind of strange to
me.

I modified psql already such that:

relname !~ '^[pd][bg]_'

is ignored :)

I still disagree. Why not keep the system stuff pg_? This can be done,
and it keeps things consistent. The above check also finds pb_ and dg_.

Actually, I'm not married to db_* for views...it was a "quick fix"
to ensure that things still worked. Whatever we decide on, both Julie and
Peter, at a minimum, need to know relatively soon. I know in Julie's
case, she does do a call to pg_user...I let her know tonight that she
needs to change it to db_user, for the *current* code...

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

I don't know if I like the new name db_user? What do other people
think? I thought pg_user_no_pwd would be nice.

Same with me. I think the name should definitely be in the restricted name
space starting with
pg_ !! How about pg_user beeing the view and having a restricted
pg_user_security ?
I still think we should hash out the password in both versions, since most
users will use
the same passwords as everywhere else. This is just to help superusers who
want to stay honest.

Andreas

Bruce wrote:

And I don't know if ALL the places where IsSystemRelationName()
is used are happy with things that in fact aren't really
hardwired system catalogs.

Can I suggest pgview_ then? Another problem is that a \d on an empty
database, \d is going to show this view, isn't it. Kind of strange to
me.

I have a solution. Create the view in initdb as pguser_no_pwd, then
after that execute an update statement on pg_class:

update pg_class
set relname = 'pg_user_no_pwd'
where relname = 'pguser_no_pwd';

We are using Jan's cache fix already. I just tried it and it works.
And it means it doesn't show up in \d, and a user can't accidentally
delete it. Sounds like a real winner.

Sounds really good - if we can be sure that the pg_ prefix of
a view never collides with the IsSystemRelationName() tests
somewhere (there are many). You got me. Let's leave all
postgres specific stuff in pg_*.

But as it was done in most UN*X's, could we rename the
pg_user containing the password into pg_shadow and then
create a view pg_user that just stars out the password field?
This way no existing application code (not even the JDBC
etc.) needs any changes, except for the createuser etc.
tools that always get installed with the new release.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

I wrote:

Bruce wrote:

I have a solution. Create the view in initdb as pguser_no_pwd, then
after that execute an update statement on pg_class:

update pg_class
set relname = 'pg_user_no_pwd'
where relname = 'pguser_no_pwd';

We are using Jan's cache fix already. I just tried it and it works.
And it means it doesn't show up in \d, and a user can't accidentally
delete it. Sounds like a real winner.

Sounds really good - if we can be sure that the pg_ prefix of
a view never collides with the IsSystemRelationName() tests
somewhere (there are many). You got me. Let's leave all
postgres specific stuff in pg_*.

But here it doesn't work. The backend crashes during the
rewriting since something doesn't match any longer. Here's
another solution that also asures that the views select rule
is created with the correct varno names and that the rule
itself is named as expected:

CREATE TABLE xpg_usr (
usename name,
usesysid int4,
usecreatedb bool,
usetrace bool,
usesuper bool,
usecatupd bool,
passwd text,
valuntil abstime);

UPDATE pg_class SET relname = 'pg_usr'
WHERE relname = 'xpg_usr';

CREATE RULE _RETpg_usr AS ON SELECT TO pg_usr
DO INSTEAD
SELECT usename, usesysid, usecreatedb,
usetrace, usesuper, usecatupd,
'********'::text as passwd, valuntil
FROM pg_user;

REVOKE ALL ON pg_user FROM public;

It doesn't look that elegant as creating a view with SELECT *
and another rule that hides the password. But this seems to
be the only way to create a view with a pg_ name cleanly.

The GRANT on pg_class in current initdb.sh is obsolete
(change of acldefault() return value). And if the public
pg_usr view has the pg_ prefix, there is no need for an
explicit grant on that too.

A comment in pg_user.h should remind us to update initdb.sh
when the structure of pg_user is to be changed. But since
changes to system catalogs require dump/reload releases, I
expect we will have a beta phase. And during that those
things will likely show up and can easy get fixed.

But as it was done in most UN*X's, could we rename the
pg_user containing the password into pg_shadow and then
create a view pg_user that just stars out the password field?
This way no existing application code (not even the JDBC
etc.) needs any changes, except for the createuser etc.
tools that always get installed with the new release.

Still vote for this. And as soon as we finally choose one
name for the public pg_user view we must fix createuser.sh,
createdb.sh and so on to make their checks on the public
accessible view so they still print the proper error messages
instead of

ERROR: pg_user: Permission denied.
createuser: database access failed.

Only createuser/destroyuser need to access the real user
catalog on the insert/delete.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

On Tue, 24 Feb 1998, Jan Wieck wrote:

CREATE TABLE xpg_usr (
usename name,
usesysid int4,
usecreatedb bool,
usetrace bool,
usesuper bool,
usecatupd bool,
passwd text,
valuntil abstime);

UPDATE pg_class SET relname = 'pg_usr'
WHERE relname = 'xpg_usr';

CREATE RULE _RETpg_usr AS ON SELECT TO pg_usr
DO INSTEAD
SELECT usename, usesysid, usecreatedb,
usetrace, usesuper, usecatupd,
'********'::text as passwd, valuntil
FROM pg_user;

REVOKE ALL ON pg_user FROM public;

Okay, just so that I don't mess things up in the translation...do
you want to send me an appropriate patch for this?

But as it was done in most UN*X's, could we rename the
pg_user containing the password into pg_shadow and then
create a view pg_user that just stars out the password field?
This way no existing application code (not even the JDBC
etc.) needs any changes, except for the createuser etc.
tools that always get installed with the new release.

Still vote for this. And as soon as we finally choose one
name for the public pg_user view we must fix createuser.sh,
createdb.sh and so on to make their checks on the public
accessible view so they still print the proper error messages
instead of

No arguments here...can you include this as part of your patch
too?

On Tue, 24 Feb 1998, Jan Wieck wrote:

CREATE TABLE xpg_usr (
usename name,
usesysid int4,
usecreatedb bool,
usetrace bool,
usesuper bool,
usecatupd bool,
passwd text,
valuntil abstime);

UPDATE pg_class SET relname = 'pg_usr'
WHERE relname = 'xpg_usr';

CREATE RULE _RETpg_usr AS ON SELECT TO pg_usr
DO INSTEAD
SELECT usename, usesysid, usecreatedb,
usetrace, usesuper, usecatupd,
'********'::text as passwd, valuntil
FROM pg_user;

REVOKE ALL ON pg_user FROM public;

Okay, just so that I don't mess things up in the translation...do
you want to send me an appropriate patch for this?

But as it was done in most UN*X's, could we rename the
pg_user containing the password into pg_shadow and then
create a view pg_user that just stars out the password field?
This way no existing application code (not even the JDBC
etc.) needs any changes, except for the createuser etc.
tools that always get installed with the new release.

Still vote for this. And as soon as we finally choose one
name for the public pg_user view we must fix createuser.sh,
createdb.sh and so on to make their checks on the public
accessible view so they still print the proper error messages
instead of

No arguments here...can you include this as part of your patch
too?

Will do so.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Actually, I'm not married to db_* for views...it was a "quick fix"
to ensure that things still worked. Whatever we decide on, both Julie and
Peter, at a minimum, need to know relatively soon. I know in Julie's
case, she does do a call to pg_user...I let her know tonight that she
needs to change it to db_user, for the *current* code...

Good. I didn't want the db_ namespace pollution. I will call it
pg_user_no_passwd, and make it a view, not a rule. Is that OK with
everyone?

Are the only things you changed psql.c and initdb.sh?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

We are using Jan's cache fix already. I just tried it and it works.
And it means it doesn't show up in \d, and a user can't accidentally
delete it. Sounds like a real winner.

Sounds really good - if we can be sure that the pg_ prefix of
a view never collides with the IsSystemRelationName() tests
somewhere (there are many). You got me. Let's leave all
postgres specific stuff in pg_*.

OK, we are basically creating it with a different name, then moving in
into the pg_ namespace with UPDATE pg_class.

But as it was done in most UN*X's, could we rename the
pg_user containing the password into pg_shadow and then
create a view pg_user that just stars out the password field?
This way no existing application code (not even the JDBC
etc.) needs any changes, except for the createuser etc.
tools that always get installed with the new release.

The only problem with that is that the database administrator now should
deal with pg_shadow, and not pg_user, and pg_user is not a real table
anymore. Actually, in Unix, this is true too. I don't think we can
change the real table to pg_shadow this close to a release, can we?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Tue, 24 Feb 1998, Bruce Momjian wrote:

Actually, I'm not married to db_* for views...it was a "quick fix"
to ensure that things still worked. Whatever we decide on, both Julie and
Peter, at a minimum, need to know relatively soon. I know in Julie's
case, she does do a call to pg_user...I let her know tonight that she
needs to change it to db_user, for the *current* code...

Good. I didn't want the db_ namespace pollution. I will call it
pg_user_no_passwd, and make it a view, not a rule. Is that OK with
everyone?

Works for me...

Are the only things you changed psql.c and initdb.sh?

Yes...

On Tue, 24 Feb 1998, Bruce Momjian wrote:

Actually, I'm not married to db_* for views...it was a "quick fix"
to ensure that things still worked. Whatever we decide on, both Julie and
Peter, at a minimum, need to know relatively soon. I know in Julie's
case, she does do a call to pg_user...I let her know tonight that she
needs to change it to db_user, for the *current* code...

Good. I didn't want the db_ namespace pollution. I will call it
pg_user_no_passwd, and make it a view, not a rule. Is that OK with
everyone?

Works for me...

How? When I create a view the way Bruce explained (update pg_class),
my backend crashes on SELECT FROM view during the rewrite. For some
reason the rewrite handler cannot get the rule locks correctly.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #