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(at)debis(dot)com (Jan Wieck) #