Solution to the pg_user passwd problem !?? (c)

I like it!!! I'm going to do a fresh buildl and try this out, and see if
Julie/I can get that ODBC driver working with this...*cross fingers*

Bruce...do see anyting particularly bad about this?

On Thu, 19 Feb 1998, Zeugswetter Andreas SARZ wrote:

Hi all,

What about:
grant select on pg_user to public;
create rule pg_user_hide_pw as on
select to pg_user.passwd
do instead select '********' as passwd;

When I see this, the word 'genius' comes to mind. What a brilliantly
elegant solution to the problem.

Then if I do:
select * from pg_user;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
--------+--------+-----------+--------+--------+---------+--------+---------
-------------------
postgres| 6|t |t |t |t |********|Sat Jan
31 07:00:00 2037 NFT
zeus | 60|t |t |f |t |********|
(2 rows)

Also the \d works for all users !

Only "disadvantage" is that noone can read passwd without first dropping the
rule pg_user_hide_pw,
I consider this a feature though ;-)

Since the userauthentication bypasses the rewrite mechanism the logins,
alter user .. and others do work !

Can all of you try to crack this ?

(c) Andreas Zeugswetter

Copyright by Andreas Zeugswetter 1998 contributed to the postgresql project
;-)
Wow, I am actually proud of this (so far, and hope it holds what I think it
does)

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

Hi all,

What about:
grant select on pg_user to public;
create rule pg_user_hide_pw as on
select to pg_user.passwd
do instead select '********' as passwd;

Then if I do:
select * from pg_user;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
--------+--------+-----------+--------+--------+---------+--------+---------
-------------------
postgres| 6|t |t |t |t |********|Sat Jan
31 07:00:00 2037 NFT
zeus | 60|t |t |f |t |********|
(2 rows)

Also the \d works for all users !

Only "disadvantage" is that noone can read passwd without first dropping the
rule pg_user_hide_pw,
I consider this a feature though ;-)

Since the userauthentication bypasses the rewrite mechanism the logins,
alter user .. and others do work !

Can all of you try to crack this ?

Cracked!

create table get_passwds (usename name, passwd text);
insert into get_passwds select usename, passwd from pg_user;
select * from get_passwds;
usename|passwd
-------+------
pgsql |
wieck |test
(2 rows)

Sorry, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Okay :-(

But: I think this is an error in the rewrite system. I think this query
should get rewritten !
Can we fix this ?

Andreas

On Thu, 19 Feb 1998, Jan Wieck wrote:

Hi all,

What about:
grant select on pg_user to public;
create rule pg_user_hide_pw as on
select to pg_user.passwd
do instead select '********' as passwd;

Then if I do:
select * from pg_user;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd |valuntil
--------+--------+-----------+--------+--------+---------+--------+---------
-------------------
postgres| 6|t |t |t |t |********|Sat Jan
31 07:00:00 2037 NFT
zeus | 60|t |t |f |t |********|
(2 rows)

Also the \d works for all users !

Only "disadvantage" is that noone can read passwd without first dropping the
rule pg_user_hide_pw,
I consider this a feature though ;-)

Since the userauthentication bypasses the rewrite mechanism the logins,
alter user .. and others do work !

Can all of you try to crack this ?

Cracked!

create table get_passwds (usename name, passwd text);
insert into get_passwds select usename, passwd from pg_user;
select * from get_passwds;
usename|passwd
-------+------
pgsql |
wieck |test
(2 rows)

Okay, its moving in the right direction though...is there a flaw in the
rules system that allows this to get around this in this manner? Nice
thing about this is that it doesn't require any 'front end' rewrites,
which is a good thing...

IMHO, this is an acceptable solution to the problem for v6.3s
release...better then ripping out the code altogether...

Well, seeing as Jan is one of the rewrite/rules system experts, let's
ask him.

Okay :-(

But: I think this is an error in the rewrite system. I think this query
should get rewritten !
Can we fix this ?

Andreas

----------
Von: Jan Wieck[SMTP:jwieck@debis.com]
Antwort an: Jan Wieck
Gesendet: Donnerstag, 19. Februar 1998 15:53
An: Zeugswetter Andreas SARZ
Cc: pgsql-hackers@hub.org
Betreff: Re: [HACKERS] Solution to the pg_user passwd problem !?? (c)

Hi all,

What about:
grant select on pg_user to public;
create rule pg_user_hide_pw as on
select to pg_user.passwd
do instead select '********' as passwd;

Then if I do:
select * from pg_user;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd

|valuntil

--------+--------+-----------+--------+--------+---------+--------+-------
--

-------------------
postgres| 6|t |t |t |t |********|Sat

Jan

31 07:00:00 2037 NFT
zeus | 60|t |t |f |t |********|
(2 rows)

Also the \d works for all users !

Only "disadvantage" is that noone can read passwd without first dropping

the

rule pg_user_hide_pw,
I consider this a feature though ;-)

Since the userauthentication bypasses the rewrite mechanism the logins,
alter user .. and others do work !

Can all of you try to crack this ?

Cracked!

create table get_passwds (usename name, passwd text);
insert into get_passwds select usename, passwd from pg_user;
select * from get_passwds;
usename|passwd
-------+------
pgsql |
wieck |test
(2 rows)

Sorry, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

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

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

* Teardrops keep falling on my head ... * :-(

Andreas

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

* Teardrops keep falling on my head ... * :-(

It was brilliant, even if it doesn't fully solve the problem.

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

Andreas wrote:

Okay :-(

But: I think this is an error in the rewrite system. I think this query
should get rewritten !
Can we fix this ?

Can all of you try to crack this ?

Cracked!

create table get_passwds (usename name, passwd text);
insert into get_passwds select usename, passwd from pg_user;
select * from get_passwds;
usename|passwd
-------+------
pgsql |
wieck |test
(2 rows)

Right - it's an error in the rewrite system. The retrieve
(SELECT) rule rewriting is only done if the command in the
query is a SELECT. But this time it's an INSERT and the
rewrite system only looks for rules on the result relation
(as far as I understood the coding). It doesn't process the
complete rangetable and no rewriting is done at all.

As a side effect it is also impossible to

INSERT INTO tab SELECT anything FROM view;

And this should really get fixed. I'll take a deep breath and
then a look at it. But that will take some time.

Until later, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

On Thu, 19 Feb 1998, Bruce Momjian wrote:

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

* Teardrops keep falling on my head ... * :-(

It was brilliant, even if it doesn't fully solve the problem.

It solves enough of the problem for v6.3's release...I still think
its important to get it so that a 'grant select' can be imposed on a view
that looks at a table that as a 'revoke all' on it...this would fix the
problem cleanly, and completely...

Then you could do a 'copy all_users to stdout;' vs a 'copy
pg_users to stdout;'

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

* Teardrops keep falling on my head ... * :-(

It was brilliant, even if it doesn't fully solve the problem.

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

Not only that it was brilliant - as stated in my last post it
also showed that there is a bug in the rewrite system that
must get fixed so we can use views in INSERT...SELECT!
Please add this to the TODO.

For the pg_user permission problem I can think about another
solution. It's a feature still on my personal wishlist -
setuid views.

Until later, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

On Thu, 19 Feb 1998, Bruce Momjian wrote:

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

* Teardrops keep falling on my head ... * :-(

It was brilliant, even if it doesn't fully solve the problem.

It solves enough of the problem for v6.3's release...I still think
its important to get it so that a 'grant select' can be imposed on a view
that looks at a table that as a 'revoke all' on it...this would fix the
problem cleanly, and completely...

Then you could do a 'copy all_users to stdout;' vs a 'copy
pg_users to stdout;'

Copy on a view doesn't return anything! I think it's in the
coding of the copy command - as far as I remember the copy
directly accesses the table bypassing the rule system. But
the table underlying the view is empty and the values are
faked into the view via the rewrite rule system.

The 'grant select' on views is a IMHO urgent required
feature. I'll take a look on the code checking permissions
and the rewrite system.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

* Teardrops keep falling on my head ... * :-(

It was brilliant, even if it doesn't fully solve the problem.

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

Not only that it was brilliant - as stated in my last post it
also showed that there is a bug in the rewrite system that
must get fixed so we can use views in INSERT...SELECT!
Please add this to the TODO.

For the pg_user permission problem I can think about another
solution. It's a feature still on my personal wishlist -
setuid views.

Added to TODO:

* Allow INSERT INTO ... SELECT ... FROM view to work

We already had:

* Allow VIEW permissions to be set separately from the underlying tables

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

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

OK, I have committed code that removes the REVOKE from initdb, and does
not allow them to do any adding or altering of users if there is a
password involved AND the ACL for pg_user is null. It prints a nice
message telling them they need to issue the REVOKE command so normal
users can't read the passwords.

If they use user passwords psql \d does fail if you do this. Do we want
to duplicate the query without the pg_user reference if the \d query
fails?

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

OK, I have committed code that removes the REVOKE from initdb, and does
not allow them to do any adding or altering of users if there is a
password involved AND the ACL for pg_user is null. It prints a nice
message telling them they need to issue the REVOKE command so normal
users can't read the passwords.

I put the REVOKE back in, with the appropriate rule rewrite...I've
tried it here and it works cleanly, and just masks out the passwd
entry...doesn't compensate for the 'copy' problem, but its better then
expecting the admin to go do the revoke on his own :(

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

On Thu, 19 February 1998, at 15:55:07, Jan Wieck wrote:

Thus spake Brett McCormick

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

That's what I do. I have both a Tcl and a Python function to encrypt
the password and that's how I store them. You still want to protect
them somewhat but it isn't quite as critical as plain tex passwords.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

I wrote:

The 'grant select' on views is a IMHO urgent required
feature. I'll take a look on the code checking permissions
and the rewrite system.

Interesting - first of all an unprivileged user cannot create
any view "pg_rewrite: Permission denied". I think this is
absolutely wrong.

Anyway - if we add a flag to the rangetable entry that tells
the executor in ExecCheckPerms() if this rte came from the
rewriting due to a view or not, it can skip the permission
check on that and the tests will pass.

But then we'll run into a little security hole problem. If
the permissions only rely on access to the view, the view
owner (or public as long as ACL_WORLD_DEFAULT contains
ACL_RD) can select throug the view. So we must check on view
creation that the creator of the view has proper permissions
to what the view selects. And in addition if not all objects
the view selects are granted to public, we should
automagically revoke public from the view so the creator must
explicitly grant access to the view.

Anything forgotten?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Crypt sounds good. I like that Idea. I also use the unix crypt
function to password protect httpd.

Right now this is done with PERL doing the encryption and sticking
it into the database in a char field. Then, another prog dumps
the passwords periodically into the httpd password file.

This would allow me to export the crypted passwords to the webserver
as well? It would be convenient to be able to use the same password
in both places.

------- OTHER WISE ---- CAN WE STEAL IDEAS FROM OTHERS? --------

I'm also running MySQL (trying to switch to PG, but waiting for ODBC
to catch up), I like the mysql feature of inserting passords by:

insert into user (host,user,password)
values ("domain.com","dlorenso",password('psswrd');

then if I

select (host,user,password) * from user;

I will get something like:

...
| domain.com | dlorenso | 2cfcb63e23e2d463 |
...

Dante

.------------------------------------------.-----------------------.
| _ dlorenso@afai.com - D. Dante Lorenso | Network Administrator |
| | | ___ _ _ ___ __ _ ___ ___ | |
| | |__ / o \| '_|/ o_\| \ |\_ _\/ o \ | Accounting Firms |
| |____|\___/|_| \___/|_|\_|\___|\___/ | Associated, inc. |
| http://www.afai.com/~dlorenso | http://www.afai.com/ |
'------------------------------------------'-----------------------'
-----Original Message-----
From: Brett McCormick <brett@work.chicken.org>
To: Jan Wieck <jwieck@debis.com>
Cc: Zeugswetter Andreas SARZ <Andreas.Zeugswetter@telecom.at>;
pgsql-hackers@hub.org <pgsql-hackers@hub.org>
Date: Thursday, February 19, 1998 12:53 PM
Subject: Re: [HACKERS] Solution to the pg_user passwd problem !?? (c)

does it matter if people are able to see the passwords? I mean,
if the passwords are stored in a table (preferable encrypted), and
the table is only readable (select, insert, etc...) by the superuser
or those of equal grant rights), then who cares?

Dante

.------------------------------------------.-----------------------.
| _ dlorenso@afai.com - D. Dante Lorenso | Network Administrator |
| | | ___ _ _ ___ __ _ ___ ___ | |
| | |__ / o \| '_|/ o_\| \ |\_ _\/ o \ | Accounting Firms |
| |____|\___/|_| \___/|_|\_|\___|\___/ | Associated, inc. |
| http://www.afai.com/~dlorenso | http://www.afai.com/ |
'------------------------------------------'-----------------------'

-----Original Message-----
From: Brett McCormick <brett@work.chicken.org>
To: Jan Wieck <jwieck@debis.com>
Cc: Zeugswetter Andreas SARZ <Andreas.Zeugswetter@telecom.at>;
pgsql-hackers@hub.org <pgsql-hackers@hub.org>
Date: Thursday, February 19, 1998 12:53 PM
Subject: Re: [HACKERS] Solution to the pg_user passwd problem !?? (c)

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

On Thu, 19 February 1998, at 15:55:07, Jan Wieck wrote:

I don't know what the problem with using crypt was. It may be because
he passes a random salt to the user, and the user makes the password
packet with the given salt and returns it to the backend. If we use
crypt, we have to send a plaintext password over the network, don't we?

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

I wrote:

The 'grant select' on views is a IMHO urgent required
feature. I'll take a look on the code checking permissions
and the rewrite system.

Interesting - first of all an unprivileged user cannot create
any view "pg_rewrite: Permission denied". I think this is
absolutely wrong.

Anyway - if we add a flag to the rangetable entry that tells
the executor in ExecCheckPerms() if this rte came from the
rewriting due to a view or not, it can skip the permission
check on that and the tests will pass.

But then we'll run into a little security hole problem. If
the permissions only rely on access to the view, the view
owner (or public as long as ACL_WORLD_DEFAULT contains
ACL_RD) can select throug the view. So we must check on view
creation that the creator of the view has proper permissions
to what the view selects. And in addition if not all objects
the view selects are granted to public, we should
automagically revoke public from the view so the creator must
explicitly grant access to the view.

Anything forgotten?

No, I think these are the valid issues.

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

OK, I have committed code that removes the REVOKE from initdb, and does
not allow them to do any adding or altering of users if there is a
password involved AND the ACL for pg_user is null. It prints a nice
message telling them they need to issue the REVOKE command so normal
users can't read the passwords.

I put the REVOKE back in, with the appropriate rule rewrite...I've
tried it here and it works cleanly, and just masks out the passwd
entry...doesn't compensate for the 'copy' problem, but its better then
expecting the admin to go do the revoke on his own :(

Sorry, don't like it. First, by doing a REVOKE ALL and GRANT SELECT,
you have the same permissions as default, except the pg_user permissions
are not null and therefore my check allows it.

Second, if COPY works, then passwords are not secure, and there is no
reason for the feature. Either a feature is secure and valuable, or
unsecure and worse then unvaluable because people think it is secure,
and it is not.

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

On Thu, 19 February 1998, at 15:55:07, Jan Wieck wrote:

I don't know what the problem with using crypt was. It may be because
he passes a random salt to the user, and the user makes the password
packet with the given salt and returns it to the backend. If we use
crypt, we have to send a plaintext password over the network, don't we?

But, aren't we doing that now?

On Thu, 19 Feb 1998, Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

The command
copy pg_user to stdout;

will also show the cleartext password and I think it is hard to do a rewrite
here,
since this would also affect the pg_dump ?

OK, I have committed code that removes the REVOKE from initdb, and does
not allow them to do any adding or altering of users if there is a
password involved AND the ACL for pg_user is null. It prints a nice
message telling them they need to issue the REVOKE command so normal
users can't read the passwords.

I put the REVOKE back in, with the appropriate rule rewrite...I've
tried it here and it works cleanly, and just masks out the passwd
entry...doesn't compensate for the 'copy' problem, but its better then
expecting the admin to go do the revoke on his own :(

Sorry, don't like it. First, by doing a REVOKE ALL and GRANT SELECT,
you have the same permissions as default, except the pg_user permissions
are not null and therefore my check allows it.

Second, if COPY works, then passwords are not secure, and there is no
reason for the feature. Either a feature is secure and valuable, or
unsecure and worse then unvaluable because people think it is secure,
and it is not.

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

The problem being that it appears that 'copy' overrides/ignores
the rewrite rules, which kind of invalidates having them, doesn't it?
What would it take to have copy follow them as select does?

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

On Thu, 19 February 1998, at 15:55:07, Jan Wieck wrote:

I don't know what the problem with using crypt was. It may be because
he passes a random salt to the user, and the user makes the password
packet with the given salt and returns it to the backend. If we use
crypt, we have to send a plaintext password over the network, don't we?

But, aren't we doing that now?

Yes, we are using crypt. We are picking a random salt, using crypt to
encrypt the cleartext password, then sending the salt to the frontend,
and asking them to supply a password crypted with our requested salt.

Anyway to do this while storing encrypted passwords?

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

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

The problem being that it appears that 'copy' overrides/ignores
the rewrite rules, which kind of invalidates having them, doesn't it?
What would it take to have copy follow them as select does?

Copy does a heap scan by itself. Doesn't care about rules.
Instead copy must do a SELECT internal. Don't know if that
is really what copy should do.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

The problem being that it appears that 'copy' overrides/ignores
the rewrite rules, which kind of invalidates having them, doesn't it?
What would it take to have copy follow them as select does?

Hard, because COPY does not do joins and does not go through the
optimizer and executor. It dumps single tables. Tough to yank that
through rewrite.

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

I don't think so...but I'rather have the obviuos "select * from
pg_user" closed off, and the more obscure "copy pg_user to stdout" still
there then have both wide open...its a half measure, but its better then
no measure...

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

Don't think so - not really hard tested.

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

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

On Thu, 19 Feb 1998, Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

I don't think so...but I'rather have the obviuos "select * from
pg_user" closed off, and the more obscure "copy pg_user to stdout" still
there then have both wide open...its a half measure, but its better then
no measure...

But it is not secure. Why have passwords then?

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

Don't think so - not really hard tested.

If you think you can get it in with light testing by the beta people,
Marc might bite on it.

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

On Thu, 19 Feb 1998, Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

I don't think so...but I'rather have the obviuos "select * from
pg_user" closed off, and the more obscure "copy pg_user to stdout" still
there then have both wide open...its a half measure, but its better then
no measure...

But it is not secure. Why have passwords then?

passswords had to get in there at *some* point...they are there
now, now we have to extend the security to the next level. Better to move
forward 1 step at a time. If we remove the REVOKE altogether, the
passwords are still there, but there is *0* security instead of 50%
security...

So, I think we should leave the REVOKE/GRANT in initdb, and work
at having grant/revoke work on a view (such that a view overrides the
revoke of all on pg_user) so that it is appliable *after* v6.3 is
released, and available as (if possible) a patch for just after...

We aren't hurting anything by leaving the REVOKE/GRANT in place,
but I think we are if we remove it and just leave it wide open...

On Thu, 19 Feb 1998, Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Just curious, but why don't the copy command fall under the same
grant/revoke restrictions in the first place? It sounds to me like we are
backing off of the problem instead of addressing it...

grant/revoke works for copy.

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

Don't think so - not really hard tested.

If you think you can get it in with light testing by the beta people,
Marc might bite on it.

There is nothing wrong with doing what we did last time...we have,
what, 7 post-release patches for v6.2.1?

passswords had to get in there at *some* point...they are there
now, now we have to extend the security to the next level. Better to move
forward 1 step at a time. If we remove the REVOKE altogether, the
passwords are still there, but there is *0* security instead of 50%
security...

If we remove the REVOKE, then people will not use passwords by mistake,
thinking they are secure. To use them, they have to issue a REVOKE, and
then they are secure.

What am I missing here?

So, I think we should leave the REVOKE/GRANT in initdb, and work
at having grant/revoke work on a view (such that a view overrides the
revoke of all on pg_user) so that it is appliable *after* v6.3 is
released, and available as (if possible) a patch for just after...

We aren't hurting anything by leaving the REVOKE/GRANT in place,
but I think we are if we remove it and just leave it wide open...

Again, am I missing something?

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

[Marc]

I don't think so...but I'rather have the obviuos "select * from
pg_user" closed off, and the more obscure "copy pg_user to stdout" still
there then have both wide open...its a half measure, but its better then
no measure...

[Bruce]

But it is not secure. Why have passwords then?

[Marc]

passswords had to get in there at *some* point...they are there
now, now we have to extend the security to the next level. Better to move
forward 1 step at a time. If we remove the REVOKE altogether, the
passwords are still there, but there is *0* security instead of 50%
security...

Wrong. It's still *0* security, but with the illusion of working
security in the eyes of anyone who doesn't know better -- and you're
trying to keep them from knowing better. If you go this way, cases
*will* occur where people think their data secure, and then someone
gains access to it who shouldn't. Security by obscurity never was,
and never will be a good idea.

Leave wide open looking wide open, and document it. Say something
like "This release has a password field in the pg_user table, but it
isn't actually useful as a security measure. It's there because we
intend to use it in a secure manner in future. Meanwhile, a secure
installation of the current version can be achieved by ...".

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

Bruce Momjian wrote:

But it is not secure. Why have passwords then?

I think is better have the encrypted passwords and the salt in pg_user.
I don't know if this will be bing a security hole :(

If we do this, then what does the frontend pass us?

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

I was thinking in the backend pass the salt stored in pg_user to the
frontend, but doing that is (almost) the same as having the password
stored in clear text. It was a bad idea :(

Robson.

Bruce said:

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

Don't think so - not really hard tested.

If you think you can get it in with light testing by the beta people,
Marc might bite on it.

So I'll give it a try...

Hacking, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Bruce said:

Ah, okay, so when we have it setup so that a view overrides the
'grant' of a select, then we're fine?

Yep, but can we do that in nine days, and be sure it is tested?

Don't think so - not really hard tested.

If you think you can get it in with light testing by the beta people,
Marc might bite on it.

So I'll give it a try...

I have been hoping to get you back into the rewrite system so we can get
a feeling of what does and doesn't work properly there. Here is a good
excuse.

I did some work on it with aggregates and subselects, and cleaned up
some stuff.

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

Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

On Thu, 19 February 1998, at 15:55:07, Jan Wieck wrote:

I don't know what the problem with using crypt was. It may be because
he passes a random salt to the user, and the user makes the password
packet with the given salt and returns it to the backend. If we use
crypt, we have to send a plaintext password over the network, don't we?

But, aren't we doing that now?

Yes, we are using crypt. We are picking a random salt, using crypt to
encrypt the cleartext password, then sending the salt to the frontend,
and asking them to supply a password crypted with our requested salt.

A benefit of this approach (which I think is valuable) is that, as far
as sniffing the network is concerned, one-time passwords are used.

Anyway to do this while storing encrypted passwords?

How UNIX specific is crypt()? At the moment the same password can be
used with clients that support crypt(), and send the password encrypted,
and those that don't, and send the password in clear.

If you want to store encrypted passwords then the encryption method used
must be supported by all types of client - or be included as part of the
PostgreSQL distribution.

Phil

On Thu, 19 Feb 1998, Tom I Helbekkmo wrote:

[Marc]

I don't think so...but I'rather have the obviuos "select * from
pg_user" closed off, and the more obscure "copy pg_user to stdout" still
there then have both wide open...its a half measure, but its better then
no measure...

[Bruce]

But it is not secure. Why have passwords then?

[Marc]

passswords had to get in there at *some* point...they are there
now, now we have to extend the security to the next level. Better to move
forward 1 step at a time. If we remove the REVOKE altogether, the
passwords are still there, but there is *0* security instead of 50%
security...

Wrong. It's still *0* security, but with the illusion of working
security in the eyes of anyone who doesn't know better -- and you're
trying to keep them from knowing better. If you go this way, cases
*will* occur where people think their data secure, and then someone
gains access to it who shouldn't. Security by obscurity never was,
and never will be a good idea.

Leave wide open looking wide open, and document it. Say something
like "This release has a password field in the pg_user table, but it
isn't actually useful as a security measure. It's there because we
intend to use it in a secure manner in future. Meanwhile, a secure
installation of the current version can be achieved by ...".

I concede the argument...you guys win...*groan*

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

Leave wide open looking wide open, and document it. Say something
like "This release has a password field in the pg_user table, but it
isn't actually useful as a security measure. It's there because we
intend to use it in a secure manner in future. Meanwhile, a secure
installation of the current version can be achieved by ...".

I concede the argument...you guys win...*groan*

OK, back out those patches, nice and slowly. Keep your hands where I
can see them. :-)

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

Bruce Momjian wrote:

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

On Thu, 19 February 1998, at 15:55:07, Jan Wieck wrote:

I don't know what the problem with using crypt was. It may be because
he passes a random salt to the user, and the user makes the password
packet with the given salt and returns it to the backend. If we use
crypt, we have to send a plaintext password over the network, don't we?

But, aren't we doing that now?

Yes, we are using crypt. We are picking a random salt, using crypt to
encrypt the cleartext password, then sending the salt to the frontend,
and asking them to supply a password crypted with our requested salt.

Anyway to do this while storing encrypted passwords?

Standard salt is two characters, so an adversary might be able to
watch and record which salts produced which replies. Even with a
single login, a brute force attack might still be able to get the
user's password. A stronger challenge-response system might be more
secure. It should be possible for the server to authenticate a user
without having to store the user's password.

Then again, this is all starting to sound like Kerberos, so if
Postgres had Kerberos authentication (which I think it does), then
this could be used for the ultra-high security authentication system.

Ocie Mitchell

On Thu, 19 Feb 1998, Bruce Momjian wrote:

Leave wide open looking wide open, and document it. Say something
like "This release has a password field in the pg_user table, but it
isn't actually useful as a security measure. It's there because we
intend to use it in a secure manner in future. Meanwhile, a secure
installation of the current version can be achieved by ...".

I concede the argument...you guys win...*groan*

OK, back out those patches, nice and slowly. Keep your hands where I
can see them. :-)

*rofl* ... its done now :)

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

What about a public/private key mechanism, like ssh?

On Thu, 19 February 1998, at 15:25:56, ocie@paracel.com wrote:

------- OTHER WISE ---- CAN WE STEAL IDEAS FROM OTHERS? --------

I'm also running MySQL (trying to switch to PG, but waiting for ODBC
to catch up), I like the mysql feature of inserting passords by:

insert into user (host,user,password)
values ("domain.com","dlorenso",password('psswrd');

Hmmm... When I read this, another (related) idea pops into my head.
It's probably nothing, but:

what about defining a new type 'passwd', with
an input (or output) routine which does the crypting?

Cheers,
Ronald

------- OTHER WISE ---- CAN WE STEAL IDEAS FROM OTHERS? --------

I'm also running MySQL (trying to switch to PG, but waiting for ODBC
to catch up), I like the mysql feature of inserting passords by:

insert into user (host,user,password)
values ("domain.com","dlorenso",password('psswrd');

Hmmm... When I read this, another (related) idea pops into my head.
It's probably nothing, but:

what about defining a new type 'passwd', with
an input (or output) routine which does the crypting?

If they do a binary cursor, does the output function get called?

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

------- OTHER WISE ---- CAN WE STEAL IDEAS FROM OTHERS? --------

I'm also running MySQL (trying to switch to PG, but waiting for ODBC
to catch up), I like the mysql feature of inserting passords by:

insert into user (host,user,password)
values ("domain.com","dlorenso",password('psswrd');

Hmmm... When I read this, another (related) idea pops into my head.
It's probably nothing, but:

what about defining a new type 'passwd', with
an input (or output) routine which does the crypting?

If they do a binary cursor, does the output function get called?

Oops, I haven't thought about that. I guess it doesn't :(
Ok, what about an input routine that does the crypting?

One down, one to go...

Cheers,
Ronald

------- OTHER WISE ---- CAN WE STEAL IDEAS FROM OTHERS? --------

I'm also running MySQL (trying to switch to PG, but waiting for ODBC
to catch up), I like the mysql feature of inserting passords by:

insert into user (host,user,password)
values ("domain.com","dlorenso",password('psswrd');

Hmmm... When I read this, another (related) idea pops into my head.
It's probably nothing, but:

what about defining a new type 'passwd', with
an input (or output) routine which does the crypting?

If they do a binary cursor, does the output function get called?

Oops, I haven't thought about that. I guess it doesn't :(
Ok, what about an input routine that does the crypting?

One down, one to go...

Cheers,
Ronald

Then, the backend can't find the original password to test the random
salt on.
--
Bruce Momjian
maillist@candle.pha.pa.us

On Thu, 19 Feb 1998, Phil Thompson wrote:

How UNIX specific is crypt()? At the moment the same password can be
used with clients that support crypt(), and send the password encrypted,
and those that don't, and send the password in clear.

If you want to store encrypted passwords then the encryption method used
must be supported by all types of client - or be included as part of the
PostgreSQL distribution.

To implement the current crypt authentication in the JDBC driver, we had
to supply a crypt class.

src/interfaces/jdbc/postgresql/util/UnixCrypt.java

Phil

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

What about a public/private key mechanism, like ssh?

Yes, just use ssh as-is and run psql etc in a ssh tunnel
when running over insecure nets.

Making cryptography part of pgsql is not good idea for two reasons:
1. It is bloat
2. It make us targets of legal-problems (US-export-control etc).

IMHO, even Kerberous should be removed.

info about ssh is available at:

http://www.cs.hut.fi/ssh/ free version
http://www.ssh.fi/ commercial (+mac/windows) versions

regards,
--
---------------------------------------------
G�ran Thyni, sysadm, JMS Bildbasen, Kiruna

On Thu, Feb 26, 1998 at 04:35:18PM -0000, Goran Thyni wrote:

Yes, just use ssh as-is and run psql etc in a ssh tunnel
when running over insecure nets.

It is not the case that ssh is a complete replacement for anything and
everything in the way of authentication, authorization and encryption.
Among other things, "just use an ssh tunnel" means you have to have
had a previous out-of-band exchange of keys between the end-points for
the authentication to be completely secure. Thus, ssh does not by
itself scale very well. (This is, of course, the reason why Kerberos
is supported as an authentication method in recent versions of ssh.
This is good for people who use Kerberos 5, the version that ssh now
supports, but only people inside the US can legally do that, and for
those of us who still use Kerberos IV (I legally run the version from
KTH, in Sweden, both at home and at work), ssh won't always cut it.)

IMHO, even Kerberous should be removed.

I strongly disagree. On the off chance that the ability to utilize
cryptographic software not even included in the release somehow makes
PosgreSQL a weapon in the eyes of the US government, I guess it would
be necessary to remove it, but I can't believe that their laws can be
so mind-bogglingly stupid? If this is the case, shouldn't /bin/cat be
export restricted, because you can use it to stuff text into software
that encrypts it?

Kerberos itself is export restricted, because it contains software
that implements cryptographic functions (specifically, the Top Secret
algorithm called DES, which it would mean the end of civilization as
we know it if anyone outside the US found out how it works), but this
surely cannot mean that anything that can use Kerberos is tainted?

The operating system I use the most, NetBSD, comes with the ability to
use Kerberos authentication present in various program sources, but
the actual Kerberos IV source code is in a separate, export restricted
package. At a US site, when this is installed, you can compile the
sources in question with proper -D flags to enable the calls to the
Kerberos functions. I've always understood this to be entirely legal.

Anyway, if it should at some point be decided to strip PostgreSQL of
any way of even calling user supplied cryptographic functions, PLEASE
don't do this without creating a clean, documented framework for
adding authentication methods to the system, so that those of us who
need secure authentication can reintegrate Kerberos and the like on
our own...

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

Anyway, if it should at some point be decided to strip PostgreSQL of
any way of even calling user supplied cryptographic functions, PLEASE
don't do this without creating a clean, documented framework for
adding authentication methods to the system, so that those of us who
need secure authentication can reintegrate Kerberos and the like on
our own...

Don't worry. Kerberos support is not going to be change, only improved
if we can. I always liked Kerberos.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Brett McCormick <brett@work.chicken.org> writes:

Have we considering using the unix crypt function for passwords? That
way it wouldn't matter (as much) if people saw the password, and would
still be (somewhat less) secure.

I would MUCH rather see MD5, salted with the username.

--Michael

Goran Thyni <goran@bildbasen.se> writes:

IMHO, even Kerberous should be removed.

No, it should be fixed, not removed. I would very much like to use krb5
authentication.

My main problem with the whole authentication system is that there can only
be one global method. That is, everything is password based, on one
password per user.

I would think a good method would have the (username, auth-data) stored
together, but you could have multiple (username, auth-data) tuples. For
instance, I might have a Kerberos5 authentication:

usename authtype authdata
--------------- --------------- ------------------------------
explorer krb5 explorer@FLAME.ORG
explorer md5 4f1929cbca91deadbeef0123...
explorer cryptpw UNIX-standard crypted string
explorer plainpw password

Then I can control how someone connects down to a method. I could
allow different methods for different trust levels. I might trust my
local, server machine with just a password, but deny access to the rest
of the net unless they use Kerberos5.

--Michael