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
Import Notes
Reply to msg id not found: Pine.NEB.3.96.971220171958.11118A-100000@thelab.hub.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:
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:
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 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 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
Sorry for the response delay. I was out of town.
I don't believe that pg_user needs to be readable by users in general. They
don't really need to know who else has access to the DB, and they certainly
don't need to know what access they do have (e.g. usesuper and createuser).
As for the suggestion that the passwords don't need to be in the cache, this is
incorrect. For the system (as I have designed it) to work, the postmaster must
check at each login to see if the user has a password. Using another relation
along with a select to look up the password from pg_user is not as efficient,
and it is not possible from the postmaster. In order for this to work, each
time that pg_user or pg_password (if we use a 2nd relation) is modified, a join
must be performed between the two (essentially perform a select on a view that
performs the join) before the data can be copied to the pg_pwd file for the
postmaster to use. I don't even know if the copy command will work with a view.
For these reasons I still believe that pg_user should just remain non-accessible
to the general public.
Todd A. Brandys
Import Notes
Resolved by subject fallback
On Tue, 13 Jan 1998, todd brandys wrote:
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.----------------------------------
What is it that you need to implement in JDBC for which a general user needs
to be able to see tables that other users own? If this is some type of admin,
'stuff' that the postgres user will execute, then he/she will be able to run it
no problem.
It's a call that's part of the JDBC specification. so it can be called by
user code, or admin code.
Here's what I have on it.
Interface java.sql.DatabaseMetaData
public abstract ResultSet getTablePrivileges(String catalog,
String schemaPattern,
String tableNamePattern)
throws SQLException
Get a description of the access rights for each table available
in a catalog. Note that a table privilege applies to one or
more columns in the table. It would be wrong to assume that
this priviledge applies to all columns (this may be true for
some systems but is not true for all.)
Only privileges matching the schema and table name criteria are
returned. They are ordered by TABLE_SCHEM, TABLE_NAME, and
PRIVILEGE.
Each privilige description has the following columns:
1. TABLE_CAT String => table catalog (may be null)
2. TABLE_SCHEM String => table schema (may be null)
3. TABLE_NAME String => table name
4. GRANTOR => grantor of access (may be null)
5. GRANTEE String => grantee of access
6. PRIVILEGE String => name of access (SELECT, INSERT, UPDATE,
REFRENCES, ...)
7. IS_GRANTABLE String => "YES" if grantee is permitted to grant
to others; "NO" if not; null if unknown
Parameters:
catalog - a catalog name; "" retrieves those without a
catalog; null means drop catalog name from the selection
criteria
schemaPattern - a schema name pattern; "" retrieves those
without a schema
tableNamePattern - a table name pattern
Returns:
ResultSet - each row is a table privilege description
Throws: SQLException
if a database-access error occurs.
See Also:
getSearchStringEscape
--
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
Import Notes
Reply to msg id not found: 199801140520.AA05741@eng3.hep.uiuc.edu | Resolved by subject fallback
Sorry for the response delay. I was out of town.
I don't believe that pg_user needs to be readable by users in general. They
don't really need to know who else has access to the DB, and they certainly
don't need to know what access they do have (e.g. usesuper and createuser).As for the suggestion that the passwords don't need to be in the cache, this is
incorrect. For the system (as I have designed it) to work, the postmaster must
check at each login to see if the user has a password. Using another relation
along with a select to look up the password from pg_user is not as efficient,
and it is not possible from the postmaster. In order for this to work, each
time that pg_user or pg_password (if we use a 2nd relation) is modified, a join
must be performed between the two (essentially perform a select on a view that
performs the join) before the data can be copied to the pg_pwd file for the
postmaster to use. I don't even know if the copy command will work with a view.
For these reasons I still believe that pg_user should just remain non-accessible
to the general public.Todd A. Brandys
Can't we create a function to get the info:
create function get_passwd returns text as
'select passwd from pg_password'
language 'sql';
And this will return a null for password not found, and a valid password
for others. I don't think a view will work. I think you would have to
do a SELECT ... INTO and do a COPY from that temp table. Sounds like
some work.
Now this is done ONLY when a password changed is made, or a user is
deleted or added. Is that correct? Doesn't sound like too much of a
hit to me. Now if it was done for every connection, we would have big
troubles.
--
Bruce Momjian
maillist@candle.pha.pa.us
On Wed, 14 Jan 1998, Bruce Momjian wrote:
Sorry for the response delay. I was out of town.
I don't believe that pg_user needs to be readable by users in general. They
don't really need to know who else has access to the DB, and they certainly
don't need to know what access they do have (e.g. usesuper and createuser).As for the suggestion that the passwords don't need to be in the cache, this is
incorrect. For the system (as I have designed it) to work, the postmaster must
check at each login to see if the user has a password. Using another relation
along with a select to look up the password from pg_user is not as efficient,
and it is not possible from the postmaster. In order for this to work, each
time that pg_user or pg_password (if we use a 2nd relation) is modified, a join
must be performed between the two (essentially perform a select on a view that
performs the join) before the data can be copied to the pg_pwd file for the
postmaster to use. I don't even know if the copy command will work with a view.
For these reasons I still believe that pg_user should just remain non-accessible
to the general public.Todd A. Brandys
Can't we create a function to get the info:
create function get_passwd returns text as
'select passwd from pg_password'
language 'sql';And this will return a null for password not found, and a valid password
for others. I don't think a view will work. I think you would have to
do a SELECT ... INTO and do a COPY from that temp table. Sounds like
some work.Now this is done ONLY when a password changed is made, or a user is
deleted or added. Is that correct? Doesn't sound like too much of a
hit to me. Now if it was done for every connection, we would have big
troubles.
Just curious here, but why couldn't this be done in postgres vs
postmaster? Essentially, the way I'm seeing things right now, we'd added
processing to the "startup loop" inside of the postmaster...while
postmaster is authenticating one user, the next person trying to connect
would have to wait that little bit longer...
Fork off the postgres process first, then authenticate inside of
there...which would get rid of the problem with pg_user itself being a
text file vs a relation...no?
I wish ppl would quite dropping off CC's :( Especially when this stuff
*should* be being discussed in pgsql-hackers@postgresql.org...
As for the pg_privileges relation and whatnot...is this something we want
for v6.3 (can it be done in 2weeks?) or wait until after v6.3 is released?
On Thu, 15 Jan 1998, todd brandys wrote:
Show quoted text
wouldn't that pose a problem with setting up someone other the
the superuser to create databases? *Raised eyebrows*Again I feel that PostgreSQL should have a pg_privileges relation which would
handle the CREATE DATABASE privilege. There would be no problem then.Todd A. Brandys
brandys@eng3.hep.uiuc.edu
Import Notes
Reply to msg id not found: 199801152025.AA00454@eng3.hep.uiuc.edu | Resolved by subject fallback
Fork off the postgres process first, then authenticate inside of
there...which would get rid of the problem with pg_user itself being a
text file vs a relation...no?
Yes, yes, yes. This is how authentication should be done (for HBA, etc.)
Furthermore, we could reduce the footprint of the postmaster drastically. It
would only need to accept a socket connection and fork the backend. This
scenario would also allow the postmaster to be run as the root user. Good
things could only come of this method.
The only reason I put my authentication scheme where it is, is that all the
other authentication schemes take place in the postmaster, and to work things
properly, use of my scheme (checking to see if there is a password or not) must
come first.
Todd A. Brandys
brandys@eng3.hep.uiuc.edu
Import Notes
Resolved by subject fallback
As for the pg_privileges relation and whatnot...is this something we want
for v6.3 (can it be done in 2weeks?) or wait until after v6.3 is released?
I don't think (realistically) that such a task could be done in two weeks. No.
Rather, we should wait until after release 6.3, and then maybe spend some time
debating on what the pg_privileges table should look like. After the table is
created (the easy part), then it becomes a hunt to find all the places where
privileges are checked and change to the code in these spot (not too bad really).
Finally, we have to develop the code for governing column permissions (this is
the most difficult part of the project). The query processor must be changed
to check the permissions of each column in all commands.
This would be a tall order for two weeks. Especially, to be certain that we
had a consensus among the hacker community that what was being done was being
done in the best way possible.
Todd A. Brandys
brandys@eng3.hep.uiuc.edu
Import Notes
Resolved by subject fallback
I wish ppl would quite dropping off CC's :( Especially when this stuff
*should* be being discussed in pgsql-hackers@postgresql.org...As for the pg_privileges relation and whatnot...is this something we want
for v6.3 (can it be done in 2weeks?) or wait until after v6.3 is released?
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
name from psql's \d output.
On Thu, 15 Jan 1998, todd brandys wrote:
wouldn't that pose a problem with setting up someone other the
the superuser to create databases? *Raised eyebrows*Again I feel that PostgreSQL should have a pg_privileges relation which would
handle the CREATE DATABASE privilege. There would be no problem then.Todd A. Brandys
brandys@eng3.hep.uiuc.edu
--
Bruce Momjian
maillist@candle.pha.pa.us
As for the pg_privileges relation and whatnot...is this something we want
for v6.3 (can it be done in 2weeks?) or wait until after v6.3 is released?I don't think (realistically) that such a task could be done in two weeks. No.
Rather, we should wait until after release 6.3, and then maybe spend some time
debating on what the pg_privileges table should look like. After the table is
created (the easy part), then it becomes a hunt to find all the places where
privileges are checked and change to the code in these spot (not too bad really).
Finally, we have to develop the code for governing column permissions (this is
the most difficult part of the project). The query processor must be changed
to check the permissions of each column in all commands.This would be a tall order for two weeks. Especially, to be certain that we
had a consensus among the hacker community that what was being done was being
done in the best way possible.
I believe doing permissions on VIEWS would be much simpler than
column-level permissions. That way, you create the view with the
columns you need, and give that to the user.
--
Bruce Momjian
maillist@candle.pha.pa.us
todd brandys wrote:
Fork off the postgres process first, then authenticate inside of
there...which would get rid of the problem with pg_user itself being a
text file vs a relation...no?Yes, yes, yes. This is how authentication should be done (for HBA, etc.)
No, no, no! For security reasons, you can't fork (and exec)
unauthenticated processes. Especially HBA authentication should be done
to consume as low resources as possbile. Otherwise you open a giant door
for so infamously called Denial of Service attacks. Afterwards, every
hacker will know that to bring your system running postgres to it's
knees he just have to try to connect to 5432 port very frequently. "OK",
you might say, "I have this firewall". "OK", I say, "so what's that HBA
for?".
So it's the postmaster's role to deny as much connections as possible.
Unless we speak of non-execing postgres childs?
Mike
--
WWW: http://www.lodz.pdi.net/~mimo tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz * Bugaj 66 m.54 * 95-200 Pabianice * POLAND
On Fri, 16 Jan 1998, Micha3 Mosiewicz wrote:
No, no, no! For security reasons, you can't fork (and exec)
unauthenticated processes. Especially HBA authentication should be done
to consume as low resources as possbile. Otherwise you open a giant door
for so infamously called Denial of Service attacks. Afterwards, every
hacker will know that to bring your system running postgres to it's
knees he just have to try to connect to 5432 port very frequently. "OK",
you might say, "I have this firewall". "OK", I say, "so what's that HBA
for?".So it's the postmaster's role to deny as much connections as possible.
Unless we speak of non-execing postgres childs?
Hrmmmm...i don't quite agree with this. postmaster can handle one
connection at a time, and then has to pass it off to the postgres backend
process...DoS attacks are easier now then by forking before HBA. I just have
to continuously open a connection to port 5432...so, while postmaster is
handling that connection, checking HBA, checking a password...no other new
connections can happen. Can't think of a stronger DoS then that...? :)
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Fork off the postgres process first, then authenticate
inside of
there...which would get rid of the problem with pg_user itself
being a
text file vs a relation...no?
Yes, yes, yes. This is how authentication should be done (for HBA,
etc.)
No, no, no! For security reasons, you can't fork (and exec)
unauthenticated processes. Especially HBA authentication should be
done
to consume as low resources as possbile.
Startup time for a valid connect client is now < 0.16 secs, so is this
really a threat ?
I would say might leave hba to postmaster (since postgres don't need to
know about it)
then fork off postgres and do the rest of the authentication.
Running postgres as root though is a **very** bad idea.
Remember that we have user defined Functions !
no, yes, yes
Andreas
Import Notes
Resolved by subject fallback
On Fri, 16 Jan 1998, Zeugswetter Andreas DBT wrote:
Running postgres as root though is a **very** bad idea.
Remember that we have user defined Functions !
postmaster nor postgres will run as root, so I'm not sure where
you are coming up with a "Running postgres as root..." problem :(
