Re: New pg_pwd patch and stuff

On Sun, 11 Jan 1998, Bruce Momjian wrote:

> >     Wait, let me just get this straight here...pg_user is, by default,
> > unreadable by the general public, but is changeable just using a simple
> > grant/revoke??
> >
> >     If so, I'm confused as to why this is a bad thing?  Bruce?  Sort
> > of seems to me that its like the TCP/Unix Socket argument...go to the most
> > secure first, then let the one setting it up downgrade as they feel is
> > appropriate...no?
>
> OK, general question.  Does pg_user need to be readable?  Do
> non-postgres users want to see who owns each table?  I don't know.

    Erk...hrmmm...my understanding is that if pg_user is non-readable, then
doing a \d to list tables won't tell me who owns any of the tables...which
could be a problem if multiple users have access to the same database, but
have "personal tables"?

    Actually, right now I think that this is one of the potential problems
I brought up previous...

    If I create a database, *anyone* that is a user (createuser <>) has access
to that database...granted that I can use the 'revoke' command to restrict
table access, there should be some means of restricting a database (and its
tables) to the owner of that database...

    On top of that, a table/database should be restricted by default...for
example, this should not happen:

> createdb scrappy
> psql
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: scrappy

scrappy=> \q
> su
Password:
# su - acctng
> psql scrappy
> ~scrappy/pgsql/bin/psql scrappy
Connection to database 'scrappy' failed.
FATAL 1:SetUserId: user "acctng" is not in "pg_user"
> logout
# exit
> createuser acctng
Enter user's postgres ID or RETURN to use unix user ID: 1010 ->
Is user "acctng" allowed to create databases (y/n) n
Is user "acctng" allowed to add users? (y/n) n
createuser: acctng was successfully added
don't forget to create a database for acctng
> su
Password:
# su - acctng
> ~scrappy/pgsql/bin/psql scrappy
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: scrappy

scrappy=> \d
WARN:pg_user: Permission denied.
scrappy=>

    I shouldn't be able to get into the database itself...right now, there
really isn't any "cross database" boundaries...

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