Re: [HACKERS] Re: [COMMITTERS] 'pgsql/src/bin/initdb initdb.sh' - Mailing list pgsql-hackers

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