Thread: Re: New pg_pwd patch and stuff
> > It has to be this way, otherwise it would be possible for user to see > > other users' passwords in pg_user. I spoke to you all about this when I > > first started. I was going to make a separate relation (pg_password), > > but I was convinced not to since there is a one to one correlation > > between users and passwords. At this point I sent email to the effect > > that pg_user could no longer be readable by the group 'public'. If it > > was readable by public, then the passwords would have to be encrypted in > > pg_user. If this is the case, then the frontends will have to pass an > > unencrypted password over the network. Again this degrades the security > > of PostgreSQL. > > > > The real solution to this problem would be to create a pg_privileges > > relation, overhauling the privileges system entirely. Then we could > > just restrict access to the password column of pg_user. However, I > > would suggest that the entire pg_privileges table be cached in shared > > memory to speed things up. I am unsure if the catalog table are cached > > in shared memory or not (They really should be, but then this would > > probably require some logging to files in case of system crash). > > > > In the meantime, there should really be nothing that the average user > > will need from pg_user. The '\d' is the only problem I have encountered > > thus far, and I hope to solve that problem soon. Therefore, if you > > really, really need something from pg_user, then you need to have select > > privileges given to you explicitly, or you could explicitly give them to > > public. This would, however, give public the ability to see user > > passwords (If you are using, HBA only, then just give public the select > > over pg_user). > > 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. -- Bruce Momjian maillist@candle.pha.pa.us
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
> > 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: Yes, I agree we should be able to restrict who gets into which databases. It is on the TODO list. * More access control over who can create tables and access the database The reason it doesn't get complained about more is that many commercial databases have similar lack of funciontality. -- Bruce Momjian maillist@candle.pha.pa.us
On Mon, 12 Jan 1998, Bruce Momjian wrote:
> Yes, I agree we should be able to restrict who gets into which
> databases. It is on the TODO list.
>
> * More access control over who can create tables and access the database
>
> The reason it doesn't get complained about more is that many commercial
> databases have similar lack of funciontality.
*nod*
On Sun, 11 Jan 1998, Bruce Momjian wrote: > 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. I'd say yes, as we have stuff in JDBC yet to implement that will access this table. -- 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
On Mon, 12 Jan 1998, Bruce Momjian wrote: > Yes, I agree we should be able to restrict who gets into which > databases. It is on the TODO list. > > * More access control over who can create tables and access the database > > The reason it doesn't get complained about more is that many commercial > databases have similar lack of funciontality. Although not perfect, we can do this now by using different password files for each database, and having an entry for each database in pg_hba.conf -- 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
> > On Mon, 12 Jan 1998, Bruce Momjian wrote: > > Yes, I agree we should be able to restrict who gets into which > > databases. It is on the TODO list. > > > > * More access control over who can create tables and access the database > > > > The reason it doesn't get complained about more is that many commercial > > databases have similar lack of funciontality. > > Although not perfect, we can do this now by using different password files > for each database, and having an entry for each database in pg_hba.conf Someone sent in a patch to pg_hba.conf that allows use of the % character to say people can only access databases with their name on it. Marc will apply it soon, and Marc, we need a manual page mention for it. -- Bruce Momjian maillist@candle.pha.pa.us