Thread: users in Postgresql
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 =======================================
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 >======================================= > > > >************
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
> 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
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
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
> 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
>> 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.
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