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)