Thread: users in Postgresql

users in Postgresql

From
Carlos Vicente Altamirano
Date:
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
=======================================



Re: [GENERAL] users in Postgresql

From
Charles Tassell
Date:
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
>=======================================
>
>
>
>************


Re: [GENERAL] users in Postgresql

From
Peter Eisentraut
Date:
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


Re: [GENERAL] users in Postgresql

From
postgres@taifun.interface-business.de
Date:
> 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


Re: [GENERAL] users in Postgresql

From
Peter Eisentraut
Date:
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


Re: [GENERAL] users in Postgresql

From
Marcin Grondecki
Date:
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

Re: [GENERAL] users in Postgresql

From
Bruce Momjian
Date:
> 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

Re: [GENERAL] users in Postgresql

From
postgres@taifun.interface-business.de
Date:
>> 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.

Re: [GENERAL] users in Postgresql

From
Peter Eisentraut
Date:
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