> 
> 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.


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

Mon, 23 Feb 1998 14:41:13 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
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) #


Mon, 23 Feb 1998 21:13:51 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 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

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)

Mon, 23 Feb 1998 15:17:08 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
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 :)



Mon, 23 Feb 1998 15:23:54 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> > > 
> > > 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)

Mon, 23 Feb 1998 15:26:47 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> > > 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) #


Mon, 23 Feb 1998 21:35:21 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> > > > 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)

Mon, 23 Feb 1998 15:55:12 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
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 


Mon, 23 Feb 1998 23:26:42 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> 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)

Mon, 23 Feb 1998 22:51:51 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
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

Tue, 24 Feb 1998 11:01:59 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
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 


Tue, 24 Feb 1998 00:38:43 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'

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) #



Tue, 24 Feb 1998 11:24:59 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'

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) #



Tue, 24 Feb 1998 13:46:06 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
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?




Tue, 24 Feb 1998 08:14:11 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> 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) #


Tue, 24 Feb 1998 14:23:22 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 	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)

Tue, 24 Feb 1998 09:59:01 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> > 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)

Tue, 24 Feb 1998 10:18:59 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
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...



Tue, 24 Feb 1998 10:19:39 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> 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) #


Tue, 24 Feb 1998 16:45:00 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
On Tue, 24 Feb 1998, Jan Wieck wrote:

> > 
> > 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.

	Ppl are taking me a slight bit too literally :(  "Works for
me"...I like the idea...not necessarily implemented it though :)



Tue, 24 Feb 1998 10:47:45 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> On Tue, 24 Feb 1998, Jan Wieck wrote:
> 
> > > 
> > > 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.
> 
> 	Ppl are taking me a slight bit too literally :(  "Works for
> me"...I like the idea...not necessarily implemented it though :)

    I already have the pg_shadow + pg_user-view diff ready. Works
    really! Must run a regression test and send it after that
    succeeded. It updates createuser, destroyuser and initdb too.


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) #


Tue, 24 Feb 1998 16:58:07 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'

Bruce wrote:
>
> >     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?

    We  can.  Here  it is. ALL regression tests except for float8
    and geometry (only fp differences  -  diffs  checked)  passed
    'ok'. Checked that createuser and destroyuser work.

    This  patch  changes  the  catalog pg_user into pg_shadow and
    creates a view pg_user that definitely works. Oridinary  user
    cannot access pg_shadow.

    Since your greatest need (to stay with all system stuff named
    pg_) is met, you might like it too.


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) #


diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/catalog/Makefile ./backend/catalog/Makefile
*** /usr/local/pgsql/sup/pgsql/src/backend/catalog/Makefile	Wed Jan  7 13:36:12 1998
--- ./backend/catalog/Makefile	Tue Feb 24 14:25:33 1998
***************
*** 27,33 ****
  GENBKI= ./genbki.sh
  
  GLOBALBKI_SRCS= $(addprefix ../../include/catalog/, \
!                   pg_database.h pg_variable.h pg_user.h \
                    pg_group.h pg_log.h \
                   )
  
--- 27,33 ----
  GENBKI= ./genbki.sh
  
  GLOBALBKI_SRCS= $(addprefix ../../include/catalog/, \
!                   pg_database.h pg_variable.h pg_shadow.h \
                    pg_group.h pg_log.h \
                   )
  
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/catalog/aclchk.c ./backend/catalog/aclchk.c
*** /usr/local/pgsql/sup/pgsql/src/backend/catalog/aclchk.c	Tue Feb 24 10:22:24 1998
--- ./backend/catalog/aclchk.c	Tue Feb 24 14:54:08 1998
***************
*** 32,38 ****
  #include "catalog/pg_aggregate.h"
  #include "catalog/pg_proc.h"
  #include "catalog/pg_type.h"
! #include "catalog/pg_user.h"
  #include "parser/parse_agg.h"
  #include "parser/parse_func.h"
  #include "utils/syscache.h"
--- 32,38 ----
  #include "catalog/pg_aggregate.h"
  #include "catalog/pg_proc.h"
  #include "catalog/pg_type.h"
! #include "catalog/pg_shadow.h"
  #include "parser/parse_agg.h"
  #include "parser/parse_func.h"
  #include "utils/syscache.h"
***************
*** 396,409 ****
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_aclcheck: user \"%s\" not found",
  			 usename);
! 	id = (AclId) ((Form_pg_user) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * for the 'pg_database' relation, check the usecreatedb field before
  	 * checking normal permissions
  	 */
  	if (strcmp(DatabaseRelationName, relname) == 0 &&
! 		(((Form_pg_user) GETSTRUCT(htp))->usecreatedb))
  	{
  
  		/*
--- 396,409 ----
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_aclcheck: user \"%s\" not found",
  			 usename);
! 	id = (AclId) ((Form_pg_shadow) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * for the 'pg_database' relation, check the usecreatedb field before
  	 * checking normal permissions
  	 */
  	if (strcmp(DatabaseRelationName, relname) == 0 &&
! 		(((Form_pg_shadow) GETSTRUCT(htp))->usecreatedb))
  	{
  
  		/*
***************
*** 417,428 ****
  
  	/*
  	 * Deny anyone permission to update a system catalog unless
! 	 * pg_user.usecatupd is set.  (This is to let superusers protect
  	 * themselves from themselves.)
  	 */
  	if (((mode & ACL_WR) || (mode & ACL_AP)) &&
  		IsSystemRelationName(relname) &&
! 		!((Form_pg_user) GETSTRUCT(htp))->usecatupd)
  	{
  		elog(DEBUG, "pg_aclcheck: catalog update to \"%s\": permission denied",
  			 relname);
--- 417,428 ----
  
  	/*
  	 * Deny anyone permission to update a system catalog unless
! 	 * pg_shadow.usecatupd is set.  (This is to let superusers protect
  	 * themselves from themselves.)
  	 */
  	if (((mode & ACL_WR) || (mode & ACL_AP)) &&
  		IsSystemRelationName(relname) &&
! 		!((Form_pg_shadow) GETSTRUCT(htp))->usecatupd)
  	{
  		elog(DEBUG, "pg_aclcheck: catalog update to \"%s\": permission denied",
  			 relname);
***************
*** 432,438 ****
  	/*
  	 * Otherwise, superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_user) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_aclcheck: \"%s\" is superuser",
--- 432,438 ----
  	/*
  	 * Otherwise, superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_shadow) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_aclcheck: \"%s\" is superuser",
***************
*** 531,542 ****
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_ownercheck: user \"%s\" not found",
  			 usename);
! 	user_id = (AclId) ((Form_pg_user) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * Superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_user) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_ownercheck: user \"%s\" is superuser",
--- 531,542 ----
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_ownercheck: user \"%s\" not found",
  			 usename);
! 	user_id = (AclId) ((Form_pg_shadow) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * Superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_shadow) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_ownercheck: user \"%s\" is superuser",
***************
*** 597,608 ****
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_func_ownercheck: user \"%s\" not found",
  			 usename);
! 	user_id = (AclId) ((Form_pg_user) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * Superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_user) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_ownercheck: user \"%s\" is superuser",
--- 597,608 ----
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_func_ownercheck: user \"%s\" not found",
  			 usename);
! 	user_id = (AclId) ((Form_pg_shadow) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * Superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_shadow) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_ownercheck: user \"%s\" is superuser",
***************
*** 638,649 ****
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_aggr_ownercheck: user \"%s\" not found",
  			 usename);
! 	user_id = (AclId) ((Form_pg_user) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * Superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_user) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_aggr_ownercheck: user \"%s\" is superuser",
--- 638,649 ----
  	if (!HeapTupleIsValid(htp))
  		elog(ERROR, "pg_aggr_ownercheck: user \"%s\" not found",
  			 usename);
! 	user_id = (AclId) ((Form_pg_shadow) GETSTRUCT(htp))->usesysid;
  
  	/*
  	 * Superusers bypass all permission-checking.
  	 */
! 	if (((Form_pg_shadow) GETSTRUCT(htp))->usesuper)
  	{
  #ifdef ACLDEBUG_TRACE
  		elog(DEBUG, "pg_aggr_ownercheck: user \"%s\" is superuser",
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/commands/copy.c ./backend/commands/copy.c
*** /usr/local/pgsql/sup/pgsql/src/backend/commands/copy.c	Thu Feb 19 13:05:25 1998
--- ./backend/commands/copy.c	Tue Feb 24 14:39:35 1998
***************
*** 32,38 ****
  #include <access/genam.h>
  #include <catalog/pg_type.h>
  #include <catalog/catname.h>
! #include <catalog/pg_user.h>
  #include <commands/copy.h>
  #include "commands/trigger.h"
  #include <storage/fd.h>
--- 32,38 ----
  #include <access/genam.h>
  #include <catalog/pg_type.h>
  #include <catalog/catname.h>
! #include <catalog/pg_shadow.h>
  #include <commands/copy.h>
  #include "commands/trigger.h"
  #include <storage/fd.h>
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/commands/dbcommands.c ./backend/commands/dbcommands.c
*** /usr/local/pgsql/sup/pgsql/src/backend/commands/dbcommands.c	Wed Feb  4 09:33:29 1998
--- ./backend/commands/dbcommands.c	Tue Feb 24 14:45:45 1998
***************
*** 25,31 ****
  #include "utils/elog.h"
  #include "catalog/catname.h"
  #include "catalog/pg_proc.h"
! #include "catalog/pg_user.h"
  #include "catalog/pg_database.h"
  #include "utils/syscache.h"
  #include "commands/dbcommands.h"
--- 25,31 ----
  #include "utils/elog.h"
  #include "catalog/catname.h"
  #include "catalog/pg_proc.h"
! #include "catalog/pg_shadow.h"
  #include "catalog/pg_database.h"
  #include "utils/syscache.h"
  #include "commands/dbcommands.h"
***************
*** 211,219 ****
  	userName = GetPgUserName();
  	utup = SearchSysCacheTuple(USENAME, PointerGetDatum(userName),
  							   0, 0, 0);
! 	*userIdP = ((Form_pg_user) GETSTRUCT(utup))->usesysid;
! 	use_super = ((Form_pg_user) GETSTRUCT(utup))->usesuper;
! 	use_createdb = ((Form_pg_user) GETSTRUCT(utup))->usecreatedb;
  
  	/* Check to make sure user has permission to use createdb */
  	if (!use_createdb)
--- 211,219 ----
  	userName = GetPgUserName();
  	utup = SearchSysCacheTuple(USENAME, PointerGetDatum(userName),
  							   0, 0, 0);
! 	*userIdP = ((Form_pg_shadow) GETSTRUCT(utup))->usesysid;
! 	use_super = ((Form_pg_shadow) GETSTRUCT(utup))->usesuper;
! 	use_createdb = ((Form_pg_shadow) GETSTRUCT(utup))->usecreatedb;
  
  	/* Check to make sure user has permission to use createdb */
  	if (!use_createdb)
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/commands/define.c ./backend/commands/define.c
*** /usr/local/pgsql/sup/pgsql/src/backend/commands/define.c	Fri Feb 13 18:28:37 1998
--- ./backend/commands/define.c	Tue Feb 24 14:39:46 1998
***************
*** 52,58 ****
  #include <commands/defrem.h>
  #include <optimizer/xfunc.h>
  #include <tcop/dest.h>
! #include <catalog/pg_user.h>
  
  static char *defGetString(DefElem *def);
  static int	defGetTypeLength(DefElem *def);
--- 52,58 ----
  #include <commands/defrem.h>
  #include <optimizer/xfunc.h>
  #include <tcop/dest.h>
! #include <catalog/pg_shadow.h>
  
  static char *defGetString(DefElem *def);
  static int	defGetTypeLength(DefElem *def);
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/commands/proclang.c ./backend/commands/proclang.c
*** /usr/local/pgsql/sup/pgsql/src/backend/commands/proclang.c	Wed Jan  7 13:36:17 1998
--- ./backend/commands/proclang.c	Tue Feb 24 14:39:49 1998
***************
*** 11,17 ****
  
  #include "access/heapam.h"
  #include "catalog/catname.h"
! #include "catalog/pg_user.h"
  #include "catalog/pg_proc.h"
  #include "catalog/pg_language.h"
  #include "utils/syscache.h"
--- 11,17 ----
  
  #include "access/heapam.h"
  #include "catalog/catname.h"
! #include "catalog/pg_shadow.h"
  #include "catalog/pg_proc.h"
  #include "catalog/pg_language.h"
  #include "utils/syscache.h"
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/commands/user.c ./backend/commands/user.c
*** /usr/local/pgsql/sup/pgsql/src/backend/commands/user.c	Thu Feb 19 22:31:39 1998
--- ./backend/commands/user.c	Tue Feb 24 14:52:21 1998
***************
*** 21,27 ****
  #include <miscadmin.h>
  #include <catalog/catname.h>
  #include <catalog/pg_database.h>
! #include <catalog/pg_user.h>
  #include <libpq/crypt.h>
  #include <access/heapam.h>
  #include <access/xact.h>
--- 21,27 ----
  #include <miscadmin.h>
  #include <catalog/catname.h>
  #include <catalog/pg_database.h>
! #include <catalog/pg_shadow.h>
  #include <libpq/crypt.h>
  #include <access/heapam.h>
  #include <access/xact.h>
***************
*** 38,44 ****
  /*---------------------------------------------------------------------
   * UpdatePgPwdFile
   *
!  * copy the modified contents of pg_user to a file used by the postmaster
   * for user authentication.  The file is stored as $PGDATA/pg_pwd.
   *---------------------------------------------------------------------
   */
--- 38,44 ----
  /*---------------------------------------------------------------------
   * UpdatePgPwdFile
   *
!  * copy the modified contents of pg_shadow to a file used by the postmaster
   * for user authentication.  The file is stored as $PGDATA/pg_pwd.
   *---------------------------------------------------------------------
   */
***************
*** 56,66 ****
    tempname = (char*)malloc(strlen(filename) + 12);
    sprintf(tempname, "%s.%d", filename, MyProcPid);
  
!   /* Copy the contents of pg_user to the pg_pwd ASCII file using a the SEPCHAR
     * character as the delimiter between fields.  Then rename the file to its
     * final name.
     */
!   sprintf(sql, "copy %s to '%s' using delimiters %s", UserRelationName, tempname, CRYPT_PWD_FILE_SEPCHAR);
    pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
    rename(tempname, filename);
    free((void*)tempname);
--- 56,66 ----
    tempname = (char*)malloc(strlen(filename) + 12);
    sprintf(tempname, "%s.%d", filename, MyProcPid);
  
!   /* Copy the contents of pg_shadow to the pg_pwd ASCII file using a the SEPCHAR
     * character as the delimiter between fields.  Then rename the file to its
     * final name.
     */
!   sprintf(sql, "copy %s to '%s' using delimiters %s", ShadowRelationName, tempname, CRYPT_PWD_FILE_SEPCHAR);
    pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
    rename(tempname, filename);
    free((void*)tempname);
***************
*** 76,90 ****
  /*---------------------------------------------------------------------
   * DefineUser
   *
!  * Add the user to the pg_user relation, and if specified make sure the
   * user is specified in the desired groups of defined in pg_group.
   *---------------------------------------------------------------------
   */
  void DefineUser(CreateUserStmt *stmt) {
  
    char*            pg_user;
!   Relation         pg_user_rel;
!   TupleDesc        pg_user_dsc;
    HeapScanDesc     scan;
    HeapTuple        tuple;
    Datum            datum;
--- 76,90 ----
  /*---------------------------------------------------------------------
   * DefineUser
   *
!  * Add the user to the pg_shadow relation, and if specified make sure the
   * user is specified in the desired groups of defined in pg_group.
   *---------------------------------------------------------------------
   */
  void DefineUser(CreateUserStmt *stmt) {
  
    char*            pg_user;
!   Relation         pg_shadow_rel;
!   TupleDesc        pg_shadow_dsc;
    HeapScanDesc     scan;
    HeapTuple        tuple;
    Datum            datum;
***************
*** 101,134 ****
    if (!(inblock = IsTransactionBlock()))
      BeginTransactionBlock();
  
!   /* Make sure the user attempting to create a user can insert into the pg_user
     * relation.
     */
    pg_user = GetPgUserName();
!   if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK) {
      UserAbortTransactionBlock();
      elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
!                pg_user, UserRelationName);
      return;
    }
  
!   /* Scan the pg_user relation to be certain the user doesn't already exist.
     */
!   pg_user_rel = heap_openr(UserRelationName);
!   pg_user_dsc = RelationGetTupleDescriptor(pg_user_rel);
!   /* Secure a write lock on pg_user so we can be sure of what the next usesysid
     * should be.
     */
!   RelationSetLockForWrite(pg_user_rel);
  
!   scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
    while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
!     datum = heap_getattr(tuple, Anum_pg_user_usename, pg_user_dsc, &n);
  
      if (!exists && !strncmp((char*)datum, stmt->user, strlen(stmt->user)))
        exists = true;
  
!     datum = heap_getattr(tuple, Anum_pg_user_usesysid, pg_user_dsc, &n);
      if ((int)datum > max_id)
        max_id = (int)datum;
  
--- 101,134 ----
    if (!(inblock = IsTransactionBlock()))
      BeginTransactionBlock();
  
!   /* Make sure the user attempting to create a user can insert into the pg_shadow
     * relation.
     */
    pg_user = GetPgUserName();
!   if (pg_aclcheck(ShadowRelationName, pg_user, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK) {
      UserAbortTransactionBlock();
      elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
!                pg_user, ShadowRelationName);
      return;
    }
  
!   /* Scan the pg_shadow relation to be certain the user doesn't already exist.
     */
!   pg_shadow_rel = heap_openr(ShadowRelationName);
!   pg_shadow_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
!   /* Secure a write lock on pg_shadow so we can be sure of what the next usesysid
     * should be.
     */
!   RelationSetLockForWrite(pg_shadow_rel);
  
!   scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
    while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
!     datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_shadow_dsc, &n);
  
      if (!exists && !strncmp((char*)datum, stmt->user, strlen(stmt->user)))
        exists = true;
  
!     datum = heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_shadow_dsc, &n);
      if ((int)datum > max_id)
        max_id = (int)datum;
  
***************
*** 137,144 ****
    heap_endscan(scan);
  
    if (exists) {
!     RelationUnsetLockForWrite(pg_user_rel);
!     heap_close(pg_user_rel);
      UserAbortTransactionBlock();
      elog(ERROR, "defineUser: user \"%s\" has already been created", stmt->user);
      return;
--- 137,144 ----
    heap_endscan(scan);
  
    if (exists) {
!     RelationUnsetLockForWrite(pg_shadow_rel);
!     heap_close(pg_shadow_rel);
      UserAbortTransactionBlock();
      elog(ERROR, "defineUser: user \"%s\" has already been created", stmt->user);
      return;
***************
*** 146,152 ****
  
    /* Build the insert statment to be executed.
     */
!   sprintf(sql, "insert into %s(usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd,passwd", UserRelationName);
  /*  if (stmt->password)
      strcat(sql, ",passwd"); -- removed so that insert empty string when no password */
    if (stmt->validUntil)
--- 146,152 ----
  
    /* Build the insert statment to be executed.
     */
!   sprintf(sql, "insert into %s(usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd,passwd", ShadowRelationName);
  /*  if (stmt->password)
      strcat(sql, ",passwd"); -- removed so that insert empty string when no password */
    if (stmt->validUntil)
***************
*** 186,193 ****
    /* This goes after the UpdatePgPwdFile to be certain that two backends to not
     * attempt to write to the pg_pwd file at the same time.
     */
!   RelationUnsetLockForWrite(pg_user_rel);
!   heap_close(pg_user_rel);
  
    if (IsTransactionBlock() && !inblock)
      EndTransactionBlock();
--- 186,193 ----
    /* This goes after the UpdatePgPwdFile to be certain that two backends to not
     * attempt to write to the pg_pwd file at the same time.
     */
!   RelationUnsetLockForWrite(pg_shadow_rel);
!   heap_close(pg_shadow_rel);
  
    if (IsTransactionBlock() && !inblock)
      EndTransactionBlock();
***************
*** 197,204 ****
  extern void AlterUser(AlterUserStmt *stmt) {
  
    char*            pg_user;
!   Relation         pg_user_rel;
!   TupleDesc        pg_user_dsc;
    HeapScanDesc     scan;
    HeapTuple        tuple;
    Datum            datum;
--- 197,204 ----
  extern void AlterUser(AlterUserStmt *stmt) {
  
    char*            pg_user;
!   Relation         pg_shadow_rel;
!   TupleDesc        pg_shadow_dsc;
    HeapScanDesc     scan;
    HeapTuple        tuple;
    Datum            datum;
***************
*** 214,242 ****
    if (!(inblock = IsTransactionBlock()))
      BeginTransactionBlock();
  
!   /* Make sure the user attempting to create a user can insert into the pg_user
     * relation.
     */
    pg_user = GetPgUserName();
!   if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
      UserAbortTransactionBlock();
      elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
!                pg_user, UserRelationName);
      return;
    }
  
!   /* Scan the pg_user relation to be certain the user exists.
     */
!   pg_user_rel = heap_openr(UserRelationName);
!   pg_user_dsc = RelationGetTupleDescriptor(pg_user_rel);
!   /* Secure a write lock on pg_user so we can be sure that when the dump of
     * the pg_pwd file is done, there is not another backend doing the same.
     */
!   RelationSetLockForWrite(pg_user_rel);
  
!   scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
    while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
!     datum = heap_getattr(tuple, Anum_pg_user_usename, pg_user_dsc, &n);
  
      if (!strncmp((char*)datum, stmt->user, strlen(stmt->user))) {
        exists = true;
--- 214,242 ----
    if (!(inblock = IsTransactionBlock()))
      BeginTransactionBlock();
  
!   /* Make sure the user attempting to create a user can insert into the pg_shadow
     * relation.
     */
    pg_user = GetPgUserName();
!   if (pg_aclcheck(ShadowRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
      UserAbortTransactionBlock();
      elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
!                pg_user, ShadowRelationName);
      return;
    }
  
!   /* Scan the pg_shadow relation to be certain the user exists.
     */
!   pg_shadow_rel = heap_openr(ShadowRelationName);
!   pg_shadow_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
!   /* Secure a write lock on pg_shadow so we can be sure that when the dump of
     * the pg_pwd file is done, there is not another backend doing the same.
     */
!   RelationSetLockForWrite(pg_shadow_rel);
  
!   scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
    while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
!     datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_shadow_dsc, &n);
  
      if (!strncmp((char*)datum, stmt->user, strlen(stmt->user))) {
        exists = true;
***************
*** 247,254 ****
    heap_endscan(scan);
  
    if (!exists) {
!     RelationUnsetLockForWrite(pg_user_rel);
!     heap_close(pg_user_rel);
      UserAbortTransactionBlock();
      elog(ERROR, "alterUser: user \"%s\" does not exist", stmt->user);
      return;
--- 247,254 ----
    heap_endscan(scan);
  
    if (!exists) {
!     RelationUnsetLockForWrite(pg_shadow_rel);
!     heap_close(pg_shadow_rel);
      UserAbortTransactionBlock();
      elog(ERROR, "alterUser: user \"%s\" does not exist", stmt->user);
      return;
***************
*** 256,262 ****
  
    /* Create the update statement to modify the user.
     */
!   sprintf(sql, "update %s set", UserRelationName);
    sql_end = sql;
    if (stmt->password) {
      sql_end += strlen(sql_end);
--- 256,262 ----
  
    /* Create the update statement to modify the user.
     */
!   sprintf(sql, "update %s set", ShadowRelationName);
    sql_end = sql;
    if (stmt->password) {
      sql_end += strlen(sql_end);
***************
*** 296,303 ****
  
    UpdatePgPwdFile(sql);
  
!   RelationUnsetLockForWrite(pg_user_rel);
!   heap_close(pg_user_rel);
  
    if (IsTransactionBlock() && !inblock)
      EndTransactionBlock();
--- 296,303 ----
  
    UpdatePgPwdFile(sql);
  
!   RelationUnsetLockForWrite(pg_shadow_rel);
!   heap_close(pg_shadow_rel);
  
    if (IsTransactionBlock() && !inblock)
      EndTransactionBlock();
***************
*** 307,313 ****
  extern void RemoveUser(char* user) {
  
    char*            pg_user;
!   Relation         pg_user_rel,
                     pg_rel;
    TupleDesc        pg_dsc;
    HeapScanDesc     scan;
--- 307,313 ----
  extern void RemoveUser(char* user) {
  
    char*            pg_user;
!   Relation         pg_shadow_rel,
                     pg_rel;
    TupleDesc        pg_dsc;
    HeapScanDesc     scan;
***************
*** 324,356 ****
    if (!(inblock = IsTransactionBlock()))
      BeginTransactionBlock();
  
!   /* Make sure the user attempting to create a user can delete from the pg_user
     * relation.
     */
    pg_user = GetPgUserName();
!   if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
      UserAbortTransactionBlock();
      elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
!                pg_user, UserRelationName);
      return;
    }
  
!   /* Perform a scan of the pg_user relation to find the usesysid of the user to
     * be deleted.  If it is not found, then return a warning message.
     */
!   pg_user_rel = heap_openr(UserRelationName);
!   pg_dsc = RelationGetTupleDescriptor(pg_user_rel);
!   /* Secure a write lock on pg_user so we can be sure that when the dump of
     * the pg_pwd file is done, there is not another backend doing the same.
     */
!   RelationSetLockForWrite(pg_user_rel);
  
!   scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
    while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
!     datum = heap_getattr(tuple, Anum_pg_user_usename, pg_dsc, &n);
  
      if (!strncmp((char*)datum, user, strlen(user))) {
!       usesysid = (int)heap_getattr(tuple, Anum_pg_user_usesysid, pg_dsc, &n);
        ReleaseBuffer(buffer);
        break;
      }
--- 324,356 ----
    if (!(inblock = IsTransactionBlock()))
      BeginTransactionBlock();
  
!   /* Make sure the user attempting to create a user can delete from the pg_shadow
     * relation.
     */
    pg_user = GetPgUserName();
!   if (pg_aclcheck(ShadowRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
      UserAbortTransactionBlock();
      elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
!                pg_user, ShadowRelationName);
      return;
    }
  
!   /* Perform a scan of the pg_shadow relation to find the usesysid of the user to
     * be deleted.  If it is not found, then return a warning message.
     */
!   pg_shadow_rel = heap_openr(ShadowRelationName);
!   pg_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
!   /* Secure a write lock on pg_shadow so we can be sure that when the dump of
     * the pg_pwd file is done, there is not another backend doing the same.
     */
!   RelationSetLockForWrite(pg_shadow_rel);
  
!   scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
    while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
!     datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_dsc, &n);
  
      if (!strncmp((char*)datum, user, strlen(user))) {
!       usesysid = (int)heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_dsc, &n);
        ReleaseBuffer(buffer);
        break;
      }
***************
*** 359,366 ****
    heap_endscan(scan);
  
    if (usesysid == -1) {
!     RelationUnsetLockForWrite(pg_user_rel);
!     heap_close(pg_user_rel);
      UserAbortTransactionBlock();
      elog(ERROR, "removeUser: user \"%s\" does not exist", user);
      return;
--- 359,366 ----
    heap_endscan(scan);
  
    if (usesysid == -1) {
!     RelationUnsetLockForWrite(pg_shadow_rel);
!     heap_close(pg_shadow_rel);
      UserAbortTransactionBlock();
      elog(ERROR, "removeUser: user \"%s\" does not exist", user);
      return;
***************
*** 399,406 ****
    if (dbase)
      free((void*)dbase);
  
!   /* Since pg_user is global over all databases, one of two things must be done
!    * to insure complete consistency.  First, pg_user could be made non-global.
     * This would elminate the code above for deleting database and would require
     * the addition of code to delete tables, views, etc owned by the user.
     *
--- 399,406 ----
    if (dbase)
      free((void*)dbase);
  
!   /* Since pg_shadow is global over all databases, one of two things must be done
!    * to insure complete consistency.  First, pg_shadow could be made non-global.
     * This would elminate the code above for deleting database and would require
     * the addition of code to delete tables, views, etc owned by the user.
     *
***************
*** 414,428 ****
     *
     */
  
!   /* Remove the user from the pg_user table
     */
!   sprintf(sql, "delete from %s where usename = '%s'", UserRelationName, user);
    pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
  
    UpdatePgPwdFile(sql);
  
!   RelationUnsetLockForWrite(pg_user_rel);
!   heap_close(pg_user_rel);
  
    if (IsTransactionBlock() && !inblock)
      EndTransactionBlock();
--- 414,428 ----
     *
     */
  
!   /* Remove the user from the pg_shadow table
     */
!   sprintf(sql, "delete from %s where usename = '%s'", ShadowRelationName, user);
    pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
  
    UpdatePgPwdFile(sql);
  
!   RelationUnsetLockForWrite(pg_shadow_rel);
!   heap_close(pg_shadow_rel);
  
    if (IsTransactionBlock() && !inblock)
      EndTransactionBlock();
***************
*** 431,455 ****
  /*
   * CheckPgUserAclNotNull
   *
!  * check to see if there is an ACL on pg_user
   */
  static void CheckPgUserAclNotNull()
  {
  HeapTuple htp;
  
! 	htp = SearchSysCacheTuple(RELNAME, PointerGetDatum(UserRelationName),
  							  0, 0, 0);
  	if (!HeapTupleIsValid(htp))
  	{
  		elog(ERROR, "IsPgUserAclNull: class \"%s\" not found",
! 			 UserRelationName);
  	}
  
  	if (heap_attisnull(htp, Anum_pg_class_relacl))
  	{
! 		elog(NOTICE, "To use passwords, you have to revoke permissions on pg_user");
  		elog(NOTICE, "so normal users can not read the passwords.");
! 		elog(ERROR, "Try 'REVOKE ALL ON pg_user FROM PUBLIC'");
  	}
  	
  	return;
--- 431,455 ----
  /*
   * CheckPgUserAclNotNull
   *
!  * check to see if there is an ACL on pg_shadow
   */
  static void CheckPgUserAclNotNull()
  {
  HeapTuple htp;
  
! 	htp = SearchSysCacheTuple(RELNAME, PointerGetDatum(ShadowRelationName),
  							  0, 0, 0);
  	if (!HeapTupleIsValid(htp))
  	{
  		elog(ERROR, "IsPgUserAclNull: class \"%s\" not found",
! 			 ShadowRelationName);
  	}
  
  	if (heap_attisnull(htp, Anum_pg_class_relacl))
  	{
! 		elog(NOTICE, "To use passwords, you have to revoke permissions on pg_shadow");
  		elog(NOTICE, "so normal users can not read the passwords.");
! 		elog(ERROR, "Try 'REVOKE ALL ON pg_shadow FROM PUBLIC'");
  	}
  	
  	return;
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/libpq/auth.c ./backend/libpq/auth.c
*** /usr/local/pgsql/sup/pgsql/src/backend/libpq/auth.c	Wed Feb  4 09:33:38 1998
--- ./backend/libpq/auth.c	Tue Feb 24 14:52:50 1998
***************
*** 605,611 ****
  
  /*
   * Use the local flat password file if clear passwords are used and the file is
!  * specified.  Otherwise use the password in the pg_user table, encrypted or
   * not.
   */
  
--- 605,611 ----
  
  /*
   * Use the local flat password file if clear passwords are used and the file is
!  * specified.  Otherwise use the password in the pg_shadow table, encrypted or
   * not.
   */
  
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/libpq/crypt.c ./backend/libpq/crypt.c
*** /usr/local/pgsql/sup/pgsql/src/backend/libpq/crypt.c	Wed Feb  4 09:33:39 1998
--- ./backend/libpq/crypt.c	Tue Feb 24 14:53:38 1998
***************
*** 1,7 ****
  /*-------------------------------------------------------------------------
   *
   * crypt.c--
!  *        Look into pg_user and check the encrypted password with the one
   *        passed in from the frontend.
   *
   * Modification History
--- 1,7 ----
  /*-------------------------------------------------------------------------
   *
   * crypt.c--
!  *        Look into pg_shadow and check the encrypted password with the one
   *        passed in from the frontend.
   *
   * Modification History
***************
*** 119,125 ****
  
    /* We want to delete the flag file before reading the contents of the pg_pwd
     * file.  If result == 0 then the unlink of the reload file was successful.
!    * This means that a backend performed a COPY of the pg_user file to
     * pg_pwd.  Therefore we must now do a reload.
     */
    if (!pwd_cache || !result) {
--- 119,125 ----
  
    /* We want to delete the flag file before reading the contents of the pg_pwd
     * file.  If result == 0 then the unlink of the reload file was successful.
!    * This means that a backend performed a COPY of the pg_shadow file to
     * pg_pwd.  Therefore we must now do a reload.
     */
    if (!pwd_cache || !result) {
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/parser/gram.y ./backend/parser/gram.y
*** /usr/local/pgsql/sup/pgsql/src/backend/parser/gram.y	Thu Feb 19 13:05:45 1998
--- ./backend/parser/gram.y	Tue Feb 24 14:57:11 1998
***************
*** 88,94 ****
  	char				chr;
  	char				*str;
  	bool				boolean;
! 	bool*				pboolean;	/* for pg_user privileges */
  	List				*list;
  	Node				*node;
  	Value				*value;
--- 88,94 ----
  	char				chr;
  	char				*str;
  	bool				boolean;
! 	bool*				pboolean;	/* for pg_shadow privileges */
  	List				*list;
  	Node				*node;
  	Value				*value;
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/rewrite/rewriteHandler.c ./backend/rewrite/rewriteHandler.c
*** /usr/local/pgsql/sup/pgsql/src/backend/rewrite/rewriteHandler.c	Mon Feb 23 14:21:42 1998
--- ./backend/rewrite/rewriteHandler.c	Tue Feb 24 14:45:52 1998
***************
*** 32,38 ****
  
  #include "utils/syscache.h"
  #include "utils/acl.h"
! #include "catalog/pg_user.h"
  
  static void ApplyRetrieveRule(Query *parsetree, RewriteRule *rule,
  				  int rt_index, int relation_level,
--- 32,38 ----
  
  #include "utils/syscache.h"
  #include "utils/acl.h"
! #include "catalog/pg_shadow.h"
  
  static void ApplyRetrieveRule(Query *parsetree, RewriteRule *rule,
  				  int rt_index, int relation_level,
***************
*** 827,833 ****
  						view->rd_rel->relowner);
  	}
  	StrNCpy(uname.data,
! 			((Form_pg_user) GETSTRUCT(utup))->usename.data,
  			NAMEDATALEN);
  
  	/*
--- 827,833 ----
  						view->rd_rel->relowner);
  	}
  	StrNCpy(uname.data,
! 			((Form_pg_shadow) GETSTRUCT(utup))->usename.data,
  			NAMEDATALEN);
  
  	/*
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/utils/adt/acl.c ./backend/utils/adt/acl.c
*** /usr/local/pgsql/sup/pgsql/src/backend/utils/adt/acl.c	Tue Feb 24 10:22:34 1998
--- ./backend/utils/adt/acl.c	Tue Feb 24 14:46:02 1998
***************
*** 19,25 ****
  #include "utils/acl.h"
  #include "utils/syscache.h"
  #include "catalog/catalog.h"
! #include "catalog/pg_user.h"
  #include "miscadmin.h"
  
  static char *getid(char *s, char *n);
--- 19,25 ----
  #include "utils/acl.h"
  #include "utils/syscache.h"
  #include "catalog/catalog.h"
! #include "catalog/pg_shadow.h"
  #include "miscadmin.h"
  
  static char *getid(char *s, char *n);
***************
*** 158,164 ****
  									  0, 0, 0);
  			if (!HeapTupleIsValid(htp))
  				elog(ERROR, "aclparse: non-existent user \"%s\"", name);
! 			aip->ai_id = ((Form_pg_user) GETSTRUCT(htp))->usesysid;
  			break;
  		case ACL_IDTYPE_GID:
  			aip->ai_id = get_grosysid(name);
--- 158,164 ----
  									  0, 0, 0);
  			if (!HeapTupleIsValid(htp))
  				elog(ERROR, "aclparse: non-existent user \"%s\"", name);
! 			aip->ai_id = ((Form_pg_shadow) GETSTRUCT(htp))->usesysid;
  			break;
  		case ACL_IDTYPE_GID:
  			aip->ai_id = get_grosysid(name);
***************
*** 285,291 ****
  				pfree(tmp);
  			}
  			else
! 				strncat(p, (char *) &((Form_pg_user)
  									  GETSTRUCT(htp))->usename,
  						sizeof(NameData));
  			break;
--- 285,291 ----
  				pfree(tmp);
  			}
  			else
! 				strncat(p, (char *) &((Form_pg_shadow)
  									  GETSTRUCT(htp))->usename,
  						sizeof(NameData));
  			break;
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/utils/cache/syscache.c ./backend/utils/cache/syscache.c
*** /usr/local/pgsql/sup/pgsql/src/backend/utils/cache/syscache.c	Thu Feb 12 14:36:23 1998
--- ./backend/utils/cache/syscache.c	Tue Feb 24 14:48:38 1998
***************
*** 48,54 ****
  #include "catalog/pg_type.h"
  #include "catalog/pg_rewrite.h"
  #include "catalog/pg_aggregate.h"
! #include "catalog/pg_user.h"
  #include "storage/large_object.h"
  #include "catalog/pg_listener.h"
  
--- 48,54 ----
  #include "catalog/pg_type.h"
  #include "catalog/pg_rewrite.h"
  #include "catalog/pg_aggregate.h"
! #include "catalog/pg_shadow.h"
  #include "storage/large_object.h"
  #include "catalog/pg_listener.h"
  
***************
*** 254,275 ****
  		sizeof(FormData_pg_listener),
  		NULL,
  	(ScanFunc) NULL},
! 	{UserRelationName,			/* USENAME */
  		1,
! 		{Anum_pg_user_usename,
  			0,
  			0,
  		0},
! 		sizeof(FormData_pg_user),
  		NULL,
  	(ScanFunc) NULL},
! 	{UserRelationName,			/* USESYSID */
  		1,
! 		{Anum_pg_user_usesysid,
  			0,
  			0,
  		0},
! 		sizeof(FormData_pg_user),
  		NULL,
  	(ScanFunc) NULL},
  	{GroupRelationName,			/* GRONAME */
--- 254,275 ----
  		sizeof(FormData_pg_listener),
  		NULL,
  	(ScanFunc) NULL},
! 	{ShadowRelationName,			/* USENAME */
  		1,
! 		{Anum_pg_shadow_usename,
  			0,
  			0,
  		0},
! 		sizeof(FormData_pg_shadow),
  		NULL,
  	(ScanFunc) NULL},
! 	{ShadowRelationName,			/* USESYSID */
  		1,
! 		{Anum_pg_shadow_usesysid,
  			0,
  			0,
  		0},
! 		sizeof(FormData_pg_shadow),
  		NULL,
  	(ScanFunc) NULL},
  	{GroupRelationName,			/* GRONAME */
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/utils/init/globals.c ./backend/utils/init/globals.c
*** /usr/local/pgsql/sup/pgsql/src/backend/utils/init/globals.c	Wed Feb  4 09:34:08 1998
--- ./backend/utils/init/globals.c	Tue Feb 24 14:44:00 1998
***************
*** 110,116 ****
  	DatabaseRelationName,
  	GroupRelationName,
  	LogRelationName,
! 	UserRelationName,
  	VariableRelationName,
  	0
  };
--- 110,116 ----
  	DatabaseRelationName,
  	GroupRelationName,
  	LogRelationName,
! 	ShadowRelationName,
  	VariableRelationName,
  	0
  };
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/utils/init/miscinit.c ./backend/utils/init/miscinit.c
*** /usr/local/pgsql/sup/pgsql/src/backend/utils/init/miscinit.c	Wed Feb  4 09:34:08 1998
--- ./backend/utils/init/miscinit.c	Tue Feb 24 14:46:07 1998
***************
*** 32,38 ****
  #include "miscadmin.h"			/* where the declarations go */
  
  #include "catalog/catname.h"
! #include "catalog/pg_user.h"
  #include "catalog/pg_proc.h"
  #include "utils/syscache.h"
  
--- 32,38 ----
  #include "miscadmin.h"			/* where the declarations go */
  
  #include "catalog/catname.h"
! #include "catalog/pg_shadow.h"
  #include "catalog/pg_proc.h"
  #include "utils/syscache.h"
  
***************
*** 339,344 ****
  	if (!HeapTupleIsValid(userTup))
  		elog(FATAL, "SetUserId: user \"%s\" is not in \"%s\"",
  			 userName,
! 			 UserRelationName);
! 	UserId = (Oid) ((Form_pg_user) GETSTRUCT(userTup))->usesysid;
  }
--- 339,344 ----
  	if (!HeapTupleIsValid(userTup))
  		elog(FATAL, "SetUserId: user \"%s\" is not in \"%s\"",
  			 userName,
! 			 ShadowRelationName);
! 	UserId = (Oid) ((Form_pg_shadow) GETSTRUCT(userTup))->usesysid;
  }
diff -c -r /usr/local/pgsql/sup/pgsql/src/backend/utils/misc/superuser.c ./backend/utils/misc/superuser.c
*** /usr/local/pgsql/sup/pgsql/src/backend/utils/misc/superuser.c	Mon Sep  8 04:32:00 1997
--- ./backend/utils/misc/superuser.c	Tue Feb 24 14:46:13 1998
***************
*** 17,23 ****
  
  #include <postgres.h>
  #include <utils/syscache.h>
! #include <catalog/pg_user.h>
  
  bool
  superuser(void)
--- 17,23 ----
  
  #include <postgres.h>
  #include <utils/syscache.h>
! #include <catalog/pg_shadow.h>
  
  bool
  superuser(void)
***************
*** 33,37 ****
  	utup = SearchSysCacheTuple(USENAME, PointerGetDatum(UserName),
  							   0, 0, 0);
  	Assert(utup != NULL);
! 	return ((Form_pg_user) GETSTRUCT(utup))->usesuper;
  }
--- 33,37 ----
  	utup = SearchSysCacheTuple(USENAME, PointerGetDatum(UserName),
  							   0, 0, 0);
  	Assert(utup != NULL);
! 	return ((Form_pg_shadow) GETSTRUCT(utup))->usesuper;
  }
diff -c -r /usr/local/pgsql/sup/pgsql/src/bin/createuser/createuser.sh ./bin/createuser/createuser.sh
*** /usr/local/pgsql/sup/pgsql/src/bin/createuser/createuser.sh	Wed May  7 04:59:46 1997
--- ./bin/createuser/createuser.sh	Tue Feb 24 15:01:24 1998
***************
*** 203,209 ****
      CANADDUSER=f
  fi
  
! QUERY="insert into pg_user \
          (usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd) \
         values \
           ('$NEWUSER', $SYSID, '$CANCREATE', 't', '$CANADDUSER','t')"
--- 203,209 ----
      CANADDUSER=f
  fi
  
! QUERY="insert into pg_shadow \
          (usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd) \
         values \
           ('$NEWUSER', $SYSID, '$CANCREATE', 't', '$CANADDUSER','t')"
diff -c -r /usr/local/pgsql/sup/pgsql/src/bin/destroyuser/destroyuser.sh ./bin/destroyuser/destroyuser.sh
*** /usr/local/pgsql/sup/pgsql/src/bin/destroyuser/destroyuser.sh	Wed May  7 04:59:52 1997
--- ./bin/destroyuser/destroyuser.sh	Tue Feb 24 15:01:58 1998
***************
*** 182,188 ****
      done
  fi
  
! QUERY="delete from pg_user where usename = '$DELUSER'"
  
  $PSQL -c "$QUERY" template1
  if [ $? -ne 0 ]
--- 182,188 ----
      done
  fi
  
! QUERY="delete from pg_shadow where usename = '$DELUSER'"
  
  $PSQL -c "$QUERY" template1
  if [ $? -ne 0 ]
diff -c -r /usr/local/pgsql/sup/pgsql/src/bin/initdb/initdb.sh ./bin/initdb/initdb.sh
*** /usr/local/pgsql/sup/pgsql/src/bin/initdb/initdb.sh	Tue Feb 24 10:22:35 1998
--- ./bin/initdb/initdb.sh	Tue Feb 24 16:42:13 1998
***************
*** 351,371 ****
  echo "vacuum" | postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "^DEBUG:"
  
! echo "COPY pg_user TO '$PGDATA/pg_pwd' USING DELIMITERS '\\t'" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
  
! echo "GRANT SELECT ON pg_class TO PUBLIC" |\
! 	 postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
! 
! echo "CREATE RULE pg_user_hide_pw as on SELECT to pg_user.passwd DO INSTEAD SELECT '********' as passwd;" | \
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
! 
! echo "create view db_user as select * from pg_user;" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
! echo "grant select on db_user to public" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
  
--- 351,382 ----
  echo "vacuum" | postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "^DEBUG:"
  
! echo "COPY pg_shadow TO '$PGDATA/pg_pwd' USING DELIMITERS '\\t'" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
  
! echo "creating public pg_user view"
! echo "CREATE TABLE xpg_user (		\
! 	    usename	name,		\
! 	    usesysid	int4,		\
! 	    usecreatedb	bool,		\
! 	    usetrace	bool,		\
! 	    usesuper	bool,		\
! 	    usecatupd	bool,		\
! 	    passwd		text,		\
! 	    valuntil	abstime);" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
! echo "UPDATE pg_class SET relname = 'pg_user' WHERE relname = 'xpg_user';" |\
! 	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
! 	grep -v "'DEBUG:"
! echo "CREATE RULE _RETpg_user AS ON SELECT TO pg_user DO INSTEAD	\
! 	    SELECT usename, usesysid, usecreatedb, usetrace,		\
! 	           usesuper, usecatupd, '********'::text as passwd,	\
! 		   valuntil FROM pg_shadow;" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
! echo "REVOKE ALL on pg_shadow FROM public" |\
  	postgres -F -Q -D$PGDATA template1 2>&1 > /dev/null |\
  	grep -v "'DEBUG:"
  
diff -c -r /usr/local/pgsql/sup/pgsql/src/include/catalog/catname.h ./include/catalog/catname.h
*** /usr/local/pgsql/sup/pgsql/src/include/catalog/catname.h	Tue Nov 18 11:23:10 1997
--- ./include/catalog/catname.h	Tue Feb 24 14:35:39 1998
***************
*** 35,43 ****
  #define  ProcedureRelationName "pg_proc"
  #define  RelationRelationName "pg_class"
  #define  RewriteRelationName "pg_rewrite"
  #define  StatisticRelationName "pg_statistic"
  #define  TypeRelationName "pg_type"
- #define  UserRelationName "pg_user"
  #define  VariableRelationName "pg_variable"
  #define  VersionRelationName "pg_version"
  #define  AttrDefaultRelationName "pg_attrdef"
--- 35,43 ----
  #define  ProcedureRelationName "pg_proc"
  #define  RelationRelationName "pg_class"
  #define  RewriteRelationName "pg_rewrite"
+ #define  ShadowRelationName "pg_shadow"
  #define  StatisticRelationName "pg_statistic"
  #define  TypeRelationName "pg_type"
  #define  VariableRelationName "pg_variable"
  #define  VersionRelationName "pg_version"
  #define  AttrDefaultRelationName "pg_attrdef"
diff -c -r /usr/local/pgsql/sup/pgsql/src/include/catalog/pg_attribute.h ./include/catalog/pg_attribute.h
*** /usr/local/pgsql/sup/pgsql/src/include/catalog/pg_attribute.h	Thu Feb 19 13:05:54 1998
--- ./include/catalog/pg_attribute.h	Tue Feb 24 14:30:35 1998
***************
*** 275,281 ****
  DATA(insert OID = 0 ( 1255 cmax				29 0  4  -6 0 -1 -1 t f i f f));
  
  /* ----------------
!  *		pg_user
   * ----------------
   */
  DATA(insert OID = 0 ( 1260 usename		19  0 NAMEDATALEN   1 0 -1 -1 f f i f f));
--- 275,281 ----
  DATA(insert OID = 0 ( 1255 cmax				29 0  4  -6 0 -1 -1 t f i f f));
  
  /* ----------------
!  *		pg_shadow
   * ----------------
   */
  DATA(insert OID = 0 ( 1260 usename		19  0 NAMEDATALEN   1 0 -1 -1 f f i f f));
diff -c -r /usr/local/pgsql/sup/pgsql/src/include/catalog/pg_class.h ./include/catalog/pg_class.h
*** /usr/local/pgsql/sup/pgsql/src/include/catalog/pg_class.h	Mon Jan 19 09:36:11 1998
--- ./include/catalog/pg_class.h	Tue Feb 24 14:31:23 1998
***************
*** 125,131 ****
  DESCR("");
  DATA(insert OID = 1259 (  pg_class 83		  PGUID 0 0 0 f f r 14 0 0 f _null_ ));
  DESCR("");
! DATA(insert OID = 1260 (  pg_user 86		  PGUID 0 0 0 f t r 8  0 0 f _null_ ));
  DESCR("");
  DATA(insert OID = 1261 (  pg_group 87		  PGUID 0 0 0 f t s 3  0 0 f _null_ ));
  DESCR("");
--- 125,131 ----
  DESCR("");
  DATA(insert OID = 1259 (  pg_class 83		  PGUID 0 0 0 f f r 14 0 0 f _null_ ));
  DESCR("");
! DATA(insert OID = 1260 (  pg_shadow 86		  PGUID 0 0 0 f t r 8  0 0 f _null_ ));
  DESCR("");
  DATA(insert OID = 1261 (  pg_group 87		  PGUID 0 0 0 f t s 3  0 0 f _null_ ));
  DESCR("");
***************
*** 146,152 ****
  #define RelOid_pg_attribute		1249
  #define RelOid_pg_proc			1255
  #define RelOid_pg_class			1259
! #define RelOid_pg_user			1260
  #define RelOid_pg_group			1261
  #define RelOid_pg_database		1262
  #define RelOid_pg_variable		1264
--- 146,152 ----
  #define RelOid_pg_attribute		1249
  #define RelOid_pg_proc			1255
  #define RelOid_pg_class			1259
! #define RelOid_pg_shadow		1260
  #define RelOid_pg_group			1261
  #define RelOid_pg_database		1262
  #define RelOid_pg_variable		1264
diff -c -r /usr/local/pgsql/sup/pgsql/src/include/catalog/pg_type.h ./include/catalog/pg_type.h
*** /usr/local/pgsql/sup/pgsql/src/include/catalog/pg_type.h	Wed Feb 11 15:25:43 1998
--- ./include/catalog/pg_type.h	Tue Feb 24 14:31:53 1998
***************
*** 212,218 ****
  DATA(insert OID = 75 (	pg_attribute PGUID 1 1 t b t \054 1249 0 foo bar foo bar c _null_));
  DATA(insert OID = 81 (	pg_proc		 PGUID 1 1 t b t \054 1255 0 foo bar foo bar c _null_));
  DATA(insert OID = 83 (	pg_class	 PGUID 1 1 t b t \054 1259 0 foo bar foo bar c _null_));
! DATA(insert OID = 86 (	pg_user		 PGUID 1 1 t b t \054 1260 0 foo bar foo bar c _null_));
  DATA(insert OID = 87 (	pg_group	 PGUID 1 1 t b t \054 1261 0 foo bar foo bar c _null_));
  DATA(insert OID = 88 (	pg_database  PGUID 1 1 t b t \054 1262 0 foo bar foo bar c _null_));
  DATA(insert OID = 90 (	pg_variable  PGUID 1 1 t b t \054 1264 0 foo bar foo bar c _null_));
--- 212,218 ----
  DATA(insert OID = 75 (	pg_attribute PGUID 1 1 t b t \054 1249 0 foo bar foo bar c _null_));
  DATA(insert OID = 81 (	pg_proc		 PGUID 1 1 t b t \054 1255 0 foo bar foo bar c _null_));
  DATA(insert OID = 83 (	pg_class	 PGUID 1 1 t b t \054 1259 0 foo bar foo bar c _null_));
! DATA(insert OID = 86 (	pg_shadow	 PGUID 1 1 t b t \054 1260 0 foo bar foo bar c _null_));
  DATA(insert OID = 87 (	pg_group	 PGUID 1 1 t b t \054 1261 0 foo bar foo bar c _null_));
  DATA(insert OID = 88 (	pg_database  PGUID 1 1 t b t \054 1262 0 foo bar foo bar c _null_));
  DATA(insert OID = 90 (	pg_variable  PGUID 1 1 t b t \054 1264 0 foo bar foo bar c _null_));
diff -c -r /usr/local/pgsql/sup/pgsql/src/include/utils/acl.h ./include/utils/acl.h
*** /usr/local/pgsql/sup/pgsql/src/include/utils/acl.h	Tue Feb 24 10:22:45 1998
--- ./include/utils/acl.h	Tue Feb 24 14:58:30 1998
***************
*** 39,45 ****
  typedef uint8 AclIdType;
  
  #define ACL_IDTYPE_WORLD		0x00
! #define ACL_IDTYPE_UID			0x01	/* user id - from pg_user */
  #define ACL_IDTYPE_GID			0x02	/* group id - from pg_group */
  
  /*
--- 39,45 ----
  typedef uint8 AclIdType;
  
  #define ACL_IDTYPE_WORLD		0x00
! #define ACL_IDTYPE_UID			0x01	/* user id - from pg_shadow */
  #define ACL_IDTYPE_GID			0x02	/* group id - from pg_group */
  
  /*

Tue, 24 Feb 1998 17:19:07 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> > 
> > 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.

The more I think about it, the more I like pg_shadow.  Maybe we can do
that in enough time for testing.  No one's code has to change then, and
this is a big win.

-- 
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)

Tue, 24 Feb 1998 13:49:36 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> > >     my backend crashes on SELECT FROM view during the rewrite. For some
> > >     reason the rewrite handler cannot get the rule locks correctly.
> > 
> > 	Ppl are taking me a slight bit too literally :(  "Works for
> > me"...I like the idea...not necessarily implemented it though :)
> 
>     I already have the pg_shadow + pg_user-view diff ready. Works
>     really! Must run a regression test and send it after that
>     succeeded. It updates createuser, destroyuser and initdb too.

Great news.  Go for it.  Please back out the psql db_ changes and any
initdb stuff we don't need anymore.

-- 
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)

Tue, 24 Feb 1998 14:20:45 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
On Tue, 24 Feb 1998, The Hermit Hacker 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...

It's only one or two lines to change (and the queries involved are similar
to those in psql), so as soon as we decide on what the view is to be
called, I'll make the changes.

-- 
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk


Tue, 24 Feb 1998 19:22:19 +0000 (GMT)
Peter T Mount <psqlhack@maidast.demon.co.uk>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
On Tue, 24 Feb 1998, Bruce Momjian wrote:

> 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?

I like the pg_shadow idea.

One thing though, although the v6.3 driver is not backward compatible with
pre 6.3 backends (because of the network protocol changes), it should be
forward compatible. However, if we change the real table to pg_shadow
after 6.3, it would break the DatabaseMetaData class.

If we change the table's name now, we won't have this problem, and
existing code won't break either.

-- 
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk


Tue, 24 Feb 1998 19:31:44 +0000 (GMT)
Peter T Mount <psqlhack@maidast.demon.co.uk>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'
> 
> > > >     my backend crashes on SELECT FROM view during the rewrite. For some
> > > >     reason the rewrite handler cannot get the rule locks correctly.
> > > 
> > > 	Ppl are taking me a slight bit too literally :(  "Works for
> > > me"...I like the idea...not necessarily implemented it though :)
> > 
> >     I already have the pg_shadow + pg_user-view diff ready. Works
> >     really! Must run a regression test and send it after that
> >     succeeded. It updates createuser, destroyuser and initdb too.
> 
> Great news.  Go for it.  Please back out the psql db_ changes and any
> initdb stuff we don't need anymore.

Again, this is very good.  I had not thought of making pg_user a
different name, but that is the perfect solution.  Here is a case where
many heads get a nice solution.  PostgreSQL is full of those areas where
people get good ideas for certain areas, and the whole project gets
better.


-- 
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)

Tue, 24 Feb 1998 14:52:37 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh'