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

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

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

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:

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

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

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

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 :(

The Hermit Hacker wrote:

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...? :)

I think Micha is right. The postmaster can handle multiple connections
as the read of the startup packet is done a fragment at a time without
blocking so there is no DoS problem until the postmaster runs out of
sockets. I think this is less of a problem than loads of
unauthenticated, resource hungry backends forked by the postmaster.

In changing the authentication methods for 6.3 I've had to add the
ability for the postmaster to do non-blocking writes as well as reads so
that a two-way (non-blocking) dialog can take place between frontend and
postmaster.

Having said that, I won't fix (for 6.3 anyway) other parts of the
postmaster that do blocked I/O - the ident lookup in particular.
However, it is at least under the control of the DBA whether or not
ident is used.

Phil

The Hermit Hacker wrote:

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

Forking is not so bad... but isn't there any exec also? And of course
it's a difference if your machine is overloaded by processes or if it's
only one service that doesn't respond becouse the access-controling code
is disabled.

Second question... if we speak only about forking postmaster, or it's
about forking-execing-opening files-reading-etc stuff? If it's only
fork, I would totally agree with you, otherwise I'm not sure which is
worse...

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

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.

Todd A. Brandys
brandys@eng3.hep.uiuc.edu

On Fri, 16 Jan 1998, Phil Thompson wrote:

The Hermit Hacker wrote:

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...? :)

I think Micha is right. The postmaster can handle multiple connections
as the read of the startup packet is done a fragment at a time without
blocking so there is no DoS problem until the postmaster runs out of
sockets. I think this is less of a problem than loads of
unauthenticated, resource hungry backends forked by the postmaster.

In changing the authentication methods for 6.3 I've had to add the
ability for the postmaster to do non-blocking writes as well as reads so
that a two-way (non-blocking) dialog can take place between frontend and
postmaster.

Having said that, I won't fix (for 6.3 anyway) other parts of the
postmaster that do blocked I/O - the ident lookup in particular.
However, it is at least under the control of the DBA whether or not
ident is used.

Phil

One way or another PostgreSQL is subject to attack. If we do continue on
with postmaster doing authentication, then the postmaster should be made
capable of performing queries against the system catalog. This would
allow HBA to be incorporated into the system catalog (pg_user or
whatever). Why would we do this? It will make PostgreSQL easier to
administer. The user will not have to edit files to make a change. HBA's
characteristics could be partially handled by adding a:

FROM (<host1> | <group1>) ... [, (<hostn> | <groupn>)]

clause to the 'CREATE USER' statement. Right now the only way this could be
achieved would be to pass authentication down to the backend processes,
as the postmaster can not execute SQL statements.

Todd A. Brandys
brandys@eng3.hep.uiuc.edu

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@candle.pha.pa.us

So, if postmaster can't access the system tables for authentication
purposes, why not have one child forked, connected to the main
postmaster process via some IPC mechanism, whose only purpose
in life it is to answer SQL-queries for postmaster.

mjl

On Sun, 18 Jan 1998, Bruce Momjian wrote:

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@candle.pha.pa.us

I agree that we should do the check for the 'World-readable'
pg_user and give a warning if someone attempts to assign a password.
I still think the admin should be given an option in the dbinit script to
choose whether or no to run the 'REVOKE'. At this point it would be easy
to inform the admin what the trade-offs are, and we will have his/her
undivided attention (They will be more apt to read about it to get past the
prompt.).

These changes should not take long to make. I need to get the current
CVS version (I will do so tonight), and I should have the changes
(performed and tested) in a day or so.

Todd A. Brandys
brandys@eng3.hep.uiuc.edu

On Mon, 19 Jan 1998, Martin J. Laubach wrote:

So, if postmaster can't access the system tables for authentication
purposes, why not have one child forked, connected to the main
postmaster process via some IPC mechanism, whose only purpose
in life it is to answer SQL-queries for postmaster.

mjl

I would have to say "footprint". All the code for the backend has been
incorporated into the postmaster. The postmaster is nothing more than a
backend run with some special command line options. The postgres executable
is over 1 MB large and is a static executable. This is quite a bit of
(virtual) memory. I realize that memory is cheap these days, but to
have all the backend code included in with the postmaster and not have it
useable is a waste of resources. To then have a second instance running
would take more resources.

If we must maintain user authorization in the postmaster, then I believe
it would be best to strip this code from the postgres executable to
create a postmaster with a smaller footprint. Furthermore, the postmaster
should be made capable of performing heapscans, so that it could at least
view the data in the system catalog. Then all the data needed for each
authentication method (or a single method if we incorporated the best
aspects of each method into one super authentication method), could be
stored in the system catalog. This information could then be managed by the
CREATE/ALTER USER statements, which would alleviate the need to edit flat
files for system configuration.

Todd A. Brandys
brandys@eng3.hep.uiuc.edu

I agree that we should do the check for the 'World-readable'
pg_user and give a warning if someone attempts to assign a password.
I still think the admin should be given an option in the dbinit script to
choose whether or no to run the 'REVOKE'. At this point it would be easy
to inform the admin what the trade-offs are, and we will have his/her
undivided attention (They will be more apt to read about it to get past the
prompt.).

These changes should not take long to make. I need to get the current
CVS version (I will do so tonight), and I should have the changes
(performed and tested) in a day or so.

Sure, why not ask the admin. Saves him a step when he tries to do the
first password. I just think we should also check when doing a password
change, which makes sense.

--
Bruce Momjian
maillist@candle.pha.pa.us