
How can i add a user (root for example) in pg_shadow?

Atte.

=======================================
Carlos A. Vicente Altamirano
Centro de Asistencia Tecnica de RedUNAM
Depto. de Operacion de la Red
DGSCA-UNAM
Tel. 6228526-28
=======================================



Mon, 1 Nov 1999 15:41:58 -0600 (CST)
Carlos Vicente Altamirano  <altacar@redes.unam.mx>
users in Postgresql
Use the command createuser in your pgsql/bin directory.   Not sure how to 
do it in SQL, maybe alter user or create user?

At 05:41 PM 11/1/99, Carlos Vicente Altamirano wrote:

>How can i add a user (root for example) in pg_shadow?
>
>Atte.
>
>=======================================
>Carlos A. Vicente Altamirano
>Centro de Asistencia Tecnica de RedUNAM
>Depto. de Operacion de la Red
>DGSCA-UNAM
>Tel. 6228526-28
>=======================================
>
>
>
>************


Mon, 01 Nov 1999 23:33:04 -0400
Charles Tassell <ctassell@isn.net>
Re: [GENERAL] users in Postgresql
The "SQL" command (it isn't really part of the standard) is CREATE USER.
The bin/createuser is a script that calls psql and issues a create user
command. (Another confused user on the ever-growing list. How can we make
this clearer?)

Btw., although direct UPDATEs to pg_shadow will seemingly succeed, you do
not want to do that. That's a bug.

	-Peter

On Mon, 1 Nov 1999, Charles Tassell wrote:

> Use the command createuser in your pgsql/bin directory.   Not sure how to 
> do it in SQL, maybe alter user or create user?
> 
> At 05:41 PM 11/1/99, Carlos Vicente Altamirano wrote:
> 
> >How can i add a user (root for example) in pg_shadow?
> >
> >Atte.
> >
> >=======================================
> >Carlos A. Vicente Altamirano
> >Centro de Asistencia Tecnica de RedUNAM
> >Depto. de Operacion de la Red
> >DGSCA-UNAM
> >Tel. 6228526-28
> >=======================================
> >
> >
> >
> >************
> 
> 
> ************
> 
> 

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 2 Nov 1999 10:20:33 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [GENERAL] users in Postgresql
> Btw., although direct UPDATEs to pg_shadow will seemingly succeed, you do
> not want to do that. That's a bug.

Peter, would you explain your statement please!

Why somebody is able to UPDATE pg_shadow to create an user if that's a bug?
And _why_ that's a bug?

Gerald


Tue, 02 Nov 1999 17:34:34 +0100 (CET)
postgres@taifun.interface-business.de
Re: [GENERAL] users in Postgresql
May Tom (or anyone) correct me if I'm wrong, but I think this is what's
going on:

The contents of the pg_shadow table are written through to a file on disk
called pg_pwd, so all the backends can easily access it. However, this
write through is not automatic. The create user and alter user commands
take care of that, but if you update pg_shadow directly, your changes will
not be seen by currently active backends.

	-Peter

On Tue, 2 Nov 1999 postgres@taifun.interface-business.de wrote:

> > Btw., although direct UPDATEs to pg_shadow will seemingly succeed, you do
> > not want to do that. That's a bug.
> 
> Peter, would you explain your statement please!
> 
> Why somebody is able to UPDATE pg_shadow to create an user if that's a bug?
> And _why_ that's a bug?
> 
> Gerald
> 
> 

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 2 Nov 1999 18:34:17 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [GENERAL] users in Postgresql
IMHO because this is _current_ implementation issue
you cannot be sure that some/all system catalogs will not change
in the future - making your program _very_ unhappy :)


postgres@taifun.interface-business.de wrote:
> 
> > Btw., although direct UPDATEs to pg_shadow will seemingly succeed, you do
> > not want to do that. That's a bug.
> 
> Peter, would you explain your statement please!
> 
> Why somebody is able to UPDATE pg_shadow to create an user if that's a bug?
> And _why_ that's a bug?
> 
> Gerald
> 
> ************

-- 
Marcin Grondecki
ojciec@mtl.pl

*** I'm not a complete idiot - some parts are missing

Tue, 02 Nov 1999 18:49:35 +0100
Marcin Grondecki <ojciec@mtl.pl>
Re: [GENERAL] users in Postgresql
> May Tom (or anyone) correct me if I'm wrong, but I think this is what's
> going on:
> 
> The contents of the pg_shadow table are written through to a file on disk
> called pg_pwd, so all the backends can easily access it. However, this
> write through is not automatic. The create user and alter user commands
> take care of that, but if you update pg_shadow directly, your changes will
> not be seen by currently active backends.

Your changes never get to the file, ever, not just current backends.

CREATE USER sql command updates the file, but an UPDATE on pg_shadow
does not.

We use a file because the postmaster does the password authentication,
and we don't have any database connection the postmaster.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 2 Nov 1999 13:21:35 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [GENERAL] users in Postgresql
>> The contents of the pg_shadow table are written through to a file on disk
>> called pg_pwd, so all the backends can easily access it. However, this
>> write through is not automatic. The create user and alter user commands
>> take care of that, but if you update pg_shadow directly, your changes will
>> not be seen by currently active backends.
> 
> Your changes never get to the file, ever, not just current backends.
> 
> CREATE USER sql command updates the file, but an UPDATE on pg_shadow
> does not.

IMHO, that's a bug:
It's not forbidden to update or insert into pg_shadow by rule, but if
I do that I will get inconsistent authentication data.
Why not revoke INSERT and UPDATE on pg_shadow?
Or better:
Why not use a trigger on pg_shadow, to handle pg_pwd correctly?
The trigger code is allways in "create/alter user" command handler.

The code should be as near as possible on data!

> We use a file because the postmaster does the password authentication,
> and we don't have any database connection the postmaster.

pg_shadow is a file too, but not in text format like pg_pwd.

Gerald.

Thu, 04 Nov 1999 09:09:29 +0100 (CET)
postgres@taifun.interface-business.de
Re: [GENERAL] users in Postgresql
On Thu, 4 Nov 1999 postgres@taifun.interface-business.de wrote:

> > CREATE USER sql command updates the file, but an UPDATE on pg_shadow
> > does not.
> 
> IMHO, that's a bug:
> It's not forbidden to update or insert into pg_shadow by rule, but if
> I do that I will get inconsistent authentication data.
> Why not revoke INSERT and UPDATE on pg_shadow?

That way the postgres superuser (the one that would ideally be
adding/removing users) can still access it. Access control doesn't apply
to table owners. And I'm not even sure if the CREATE USER command doesn't
depend on the insert privilege existing (vs the create user privilege of
the one that's executing it). It's not all that clear.

> Or better:
> Why not use a trigger on pg_shadow, to handle pg_pwd correctly?
> The trigger code is allways in "create/alter user" command handler.

I was thinking about some sort of internal hook that sees any access to
pg_shadow and redirects it to a file. Don't even have the table anymore.
Sort of like /dev/* devices are handled by the kernel.

I was going about looking into this a little, but since I have never
played with the backend I cannot promise a result in finite time.

	-Peter

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Thu, 4 Nov 1999 10:26:24 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [GENERAL] users in Postgresql
On 1999-11-02, Bruce Momjian mentioned:

> CREATE USER sql command updates the file, but an UPDATE on pg_shadow
> does not.

How about INSERT INTO pg_shadow? Or how do you judge the following excerpt
from the createuser script:

QUERY="insert into pg_shadow \
        (usename, usesysid, usecreatedb, usetrace, usesuper, usecatupd) \
       values \
         ('$NEWUSER', $SYSID, '$CANCREATE', 'f', '$CANADDUSER','f')"

Fortunately (perhaps), I am getting rid of this as we're speaking. The one
feature the createuser script has over the CREATE USER "SQL" command is
that you can pick your system ID. Ignoring the question whether or not
this has any real purpose, it seems this is almost like rolling dice since
you cannot ever reliably change that later. (And I'm not even talking
about the fact that the sysid is a primary key and there is no referential
integrity enforced.)

So is anyone strictly opposed to yanking that feature? And perhaps
removing all references to user sysids in the (user) documentation?

	-Peter

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Sun, 7 Nov 1999 17:44:10 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] users in Postgresql