I need help with postgres and post a private thread with the permission of ariq:

Re: Postgres



quote:
--------------------------------------------------------------------------------

afriq wrote on 08-22-2001 03:33 PM:


quote:
--------------------------------------------------------------------------------

microsol wrote on 08-22-2001 03:15 PM:
Hello,
finally i got php right, now pgadmin works.
the only problem is that i haven't got a clue about postgres.
I followed the instructions to activate postgres. now i have in my conf file that all local connections are set to trust.
i've created three databases and one user. and that's where i have problems (it's not that easy like mysql )
can you explain me how i have to setup the pgAdmin and the user so that user1 can connect to pgAdmin and ONLY sees his databases? ...

--------------------------------------------------------------------------------



Take a look at this config example - the important thing is to specify the database, otherwise this guy will be able to see all db's. What I could not figure out, was how to allow one user to see all his databases ...



// The $cfgServers array starts with $cfgServers[1]. Do not use $cfgServers[0].
// You can disable a server config entry by setting host to ''.
$cfgServers[1]['local'] = true;
$cfgServers[1]['host'] = 'localhost';
$cfgServers[1]['port'] = '5432';
$cfgServers[1]['adv_auth'] = true;
$cfgServers[1]['stduser'] = 'eastcon';
$cfgServers[1]['stdpass'] = 'passwrd';
$cfgServers[1]['user'] = '';
$cfgServers[1]['password'] = '';
$cfgServers[1]['only_db'] = 'eastcon_news'; // if set to a db-name, only this db is accessible

$cfgServers[2]['local'] = false;
$cfgServers[2]['host'] = '';
$cfgServers[2]['port'] = '5432';
$cfgServers[2]['adv_auth'] = true;
$cfgServers[2]['stduser'] = '';
$cfgServers[2]['stdpass'] = '';
$cfgServers[2]['user'] = '';
$cfgServers[2]['password'] = '';
$cfgServers[2]['only_db'] = ''; // if set to a db-name, only this db is accessible

$cfgServers[3]['local'] = false;
$cfgServers[3]['host'] = '';
$cfgServers[3]['port'] = '5432'; // Leave blank for default port
$cfgServers[3]['adv_auth'] = true;
$cfgServers[3]['stduser'] = '';
$cfgServers[3]['stdpass'] = '';
$cfgServers[3]['user'] = '';
$cfgServers[3]['password'] = '';
$cfgServers[3]['only_db'] = ''; // if set to a db-name, only this db is accessible


I'm also not too sure about the diffs between the stduser and user - best to check out the pgAdmin documentation ...

rgrds,

Johan


--------------------------------------------------------------------------------




That's not the problem. If adv-auth is set to true, pgAdmin should use the stduser and stdpass (THIS MUST BE A USER WHICH HAS THE ONLY RIGHT TO READ THE DB TO CHECK THE USER/PASSWORD COMBINATION) and then connect to the database(s) which belong to the user/password and shows it/them to him. It's just like a central installation like phpMyAdmin for mysql.
My question is how to do this at the command line per SSH.
I need some exact commands because all i read until now (Marcs Mailing Lists, postgresql.bteg.net, ca.postgresql.org and much more), is very confusing for me and doesnt give me clear answers.
So:
I have three databases:
db1,db2 and db3
I have one user i created from the command line:
user password
Now, what do i have to type exactly to give this user FULL access to these three databases?
How do i have to adjust the hba_conf file exactly to give ONLY local access.
How do i change the admin password for the superuser?
When i connect with user/password by pgAdmin i get the following error:
"Warning: Unable to connect to PostgreSQL server: FATAL 1: SetUserId: user 'password=' is not in 'pg_shadow'"
I am shure i did create this user/password but i am not shure about the pg_shadow and how to use it.

Now, can anybody answer this?
Thx for your time.

Never mind. I found out myself after dedicating 3 days to postgres :nuts: :nuts:
Not the easiest db to deal with :bawling: