Re: [HACKERS] Re: New pg_pwd patch and stuff

>
> > Well, I can create the table quite easily. The issue is what type of
> > flack we will get by haveing pg_user non-readable, and removing the user
>
> What if we were to put the pg_user accessibility to the admin setting up
> PostgreSQL (at least until pg_privileges could become a reality.). If you
> look in dbinit--toward the end of the script--I run a SQL command to revoke
> all privileges from public on the pg_user table. If you are not going to
> use the pg_pwd scheme for authentication, then you don't need to run this
> command. All we need do for now is print out a little message saying that if
> you use HBA or Kerberos, then say No to blocking the PUBLIC from accessing
> pg_user. We also say that if you choose to block access to pg_user, these
> are the consequences. When a better privileges method is developed this
> question in the dbinit script can be eliminated.
>
> I myself would choose to block access to the pg_user relation. Others may not
> want it this way. Using the above scenario, the user would have an informed
> choice that would be taken care of at initialization.

This is exactly what I was thinking yesterday.

I recommend something different. What if we just skip your REVOKE
command in initdb, but we add a check in user.c, and if they try to set
a non-NULL password and they have pg_user as world-readable, we prevent
them from doing it, and tell them explicitly the REVOKE command the
database administrator must issue to allow passwords to be set.

The advantage of this is that they can use the other database USER commands,
just not the password commands, and they can easily change their mind.
It puts the checking for world-readble pg_user at the proper place. I
am afraid if we didn't someone would answer y to world-readable pg_user,
then start assigning passwords.

We can also change psql.c to do a \d lookup with pg_user, and if it
fails, we do another SELECT without pg_user showing just user-ids. That
way, the administrator will get usernames, but non-priv users will not.

--
Bruce Momjian
maillist(at)candle(dot)pha(dot)pa(dot)us