postgess and password field question

rhaggert

I have 2 questions

1) I use postgres now. I was using mysql. In mysql I could say something like this
INSERT INTO users_table(user, pass) VALUES('some_guy', PASSWORD('some_pass'));

is there a postgres equivilent to PASSWORD that I use to use in mysql?

2) Suppose I have a 3 tables poeple, players, and coaches. Now any one who is any person in in the people table. if some one is a coach they are in the coach table and reference some one in the people table. and like wise for players. now GIVEN an id to a record in people is there a very elegent way to find if they are a coach or a player and find the related record.

Thanks for any help
Ryan

Sxooter

Here's the quick driving lesson for postgresql.

First, you have \? and \h

\? lists all the back slash commands, like \d to display tables and all. Easy, simple, fun.

\h lists the SQL syntax for postgresql's SQL commands

You should see:

create user
drop user
alter user

\h alter user

will give you your answer.

rhaggert

is the above for internal users with postgres only
becuse when i say PASSWORD and refer to mysql i refer to it was a function that would do (i think) md5 encryption of the data and then throw it in the data base. so there is sone encrypted data.

later i would use
SELECT stuff FROM t1 WHERE user=$user AND pass=PASSWORD('$pass')

would the above still help me out with this problem?

Sxooter

As of 7.3.x (maybe 7.2 as well, I'm not sure) when you set a password it stores it as an md5 hash.

Try this as the postgres super user:

alter user someguy with password 'test';
select * from pg_shadow;

rhaggert

I don't have super user privileges
so I can't do that

rhaggert

also.. i dont want to do anything with user account information inside postgres.

there is a table with the attributes
id SERAIAL PRIMARY KEY,
the_user TEXT NOT NULL,
pass TEXT NOT NULL

now when i say insert . I want it to work like it did with mysql..

INSERT INTO users VALUES(NULL, 'some_user', PASSWORD('his_password'))

password puts an md5 crypt on it.

so you see nothing to do with user accounts on the data base system. just the data in the tables.

Thanks in advance for any help

Sxooter

Oh, I see. You need to use the contrib/pgcrypto package. Did you build form tar balls or install packages? If a package, you may need to download the tar ball just to get the contrib directory and the pgcypto dir in there.

rhaggert

im not the sys admin so i wouldnt know

what ever was default got installed

and i know the sys admin only uses rpms so its what ever is default fron the postgres rpm from rh8.0 distro.. maybe its been updated
i dont know

do you think its better for the database to do the encryption or to do the encryption from a php script?

Sxooter

Doing it in PHP will make it more portable between databases, so I'd do it there probably.

rhaggert

Thanks
yea I should just let php do it that way the database system never has to worry about doing the work and i could use any dbms then...

thanks for the help