Thread: Login problem

Login problem

From
"Danjel Jungersen"
Date:
Hi...

First: I'm a complete newbie on sql-servers ;-)

I have installed pgsql on a OpenBSD, and also phpPgAdmin.

I had some trouble doing that, so I'm not 100% what I did, regarding
to database names, users, passwords...

My problem is that i cannot get throught the login, I keep getting a
login failed.
This happen from both phpPgAdmin and psql...

Is there anywhere I can see what databases that are created, and
which users are created?
Is there anyway to see the users passwords, or to "reset" them ?

I have spent the whole day trying to get through, googling and
man'ing, but to no use :(

Therefore I hope to hear from you ;-)

Best regards
Danjel Jungersen

Re: Login problem

From
John DeSoi
Date:
On Dec 17, 2005, at 11:10 AM, Danjel Jungersen wrote:

> Is there anywhere I can see what databases that are created, and
> which users are created?
> Is there anyway to see the users passwords, or to "reset" them ?


Modify your pg_hba.conf file to trust local connections. Then you can
login with psql without a password. From there you can use the ALTER
USER command to reset passwords. Don't forget to reverse your
pg_hba.conf changes when you are done.

http://www.postgresql.org/docs/8.0/interactive/client-
authentication.html#AUTH-PG-HBA-CONF


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: Login problem

From
"Danjel Jungersen"
Date:
On 17 Dec 2005 at 22:51, John DeSoi wrote:

>
> On Dec 17, 2005, at 11:10 AM, Danjel Jungersen wrote:
>
> > Is there anywhere I can see what databases that are created, and
> > which users are created?
Below are instructions on how to login without a password, and a
point in the direction on how to change it, but I keep getting:
psql: FATAL: User "me" does not exist.

After some tries I got pass the database does not exist ;-)

Is there a user that is allways there, or can I see a list somewhere
?
The "postmaster" is mentioned, so I have tried that "root" and
"Postmaster", no luck...

TIA
Danjel

> > Is there anyway to see the users passwords, or to "reset" them ?
>
>
> Modify your pg_hba.conf file to trust local connections. Then you can
> login with psql without a password. From there you can use the ALTER
> USER command to reset passwords. Don't forget to reverse your
> pg_hba.conf changes when you are done.
>
> http://www.postgresql.org/docs/8.0/interactive/client-
> authentication.html#AUTH-PG-HBA-CONF
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster



Re: Login problem

From
John DeSoi
Date:
On Dec 18, 2005, at 5:44 AM, Danjel Jungersen wrote:

> Below are instructions on how to login without a password, and a
> point in the direction on how to change it, but I keep getting:
> psql: FATAL: User "me" does not exist.
>
> After some tries I got pass the database does not exist ;-)
>
> Is there a user that is allways there, or can I see a list somewhere
> ?
> The "postmaster" is mentioned, so I have tried that "root" and
> "Postmaster", no luck...
>


The default superuser name is "postgres". There is generally a
default database called template1. In PostgreSQL 8.1 and later, I
believe the default database name has changed to "postgres" as well.
So try

psql -U postgres template1

or

psql -U postgres postgres

John



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


COPY command, linking foreign keys

From
Srinivas Iyyer
Date:
Hello group:

I am working on a huge datasets (every dataset will
have over 50K lines and I have 100 such datasets).
Previously I wrote SQL statements (inserts and insert
within Insert statements).

However, the process went on and on for days due to
many lines and large number of datasets.

I am about to try COPY command. Here is snippet of my
data.


Table Gene:

gene_no |  gene_name
--------------------
   1    |     abc
   2    |     def
   3    |     kir
   .    |     ...
   .    |     ...
   N    |     xxx
--------------------

Here is how my data looks in a tab delim format:


gene_name     expr_value    exp_value2
 def             100.9        300.3
 kir             200.4        334.4
 sir              39.9          4.9
 ..               ....        .....
(30K lines like these)

Now I want to use copy command and upload this whole
tab delim file into the following table:

Table: exp

exp_no  |  gene_no(FK)  | exp_one | exp_two
---------------------------------------


There are two questions :

1. In table 'exp' I have gene_no and not gene_name. In
the tab delim file I have gene_name.  So, what is the
ideal way to link these up - a fast one after
'COPY'ing this tab delim file in to exp_table.  should
I include gene_name also into the 'exp' table or ask
postgres to link up with 'Gene' table through
'gene_no' foreign key.
2. Can this be done by simple SQL statement or should
I have to write a pl/pgql script.

would any one please help me in teaching to get around
this problem.

Thank you.
Srini







__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: COPY command, linking foreign keys

From
Sean Davis
Date:


On 12/18/05 8:59 AM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote:

> Hello group:
>
> I am working on a huge datasets (every dataset will
> have over 50K lines and I have 100 such datasets).
> Previously I wrote SQL statements (inserts and insert
> within Insert statements).
>
> However, the process went on and on for days due to
> many lines and large number of datasets.
>
> I am about to try COPY command. Here is snippet of my
> data.
>
>
> Table Gene:
>
> gene_no |  gene_name
> --------------------
>    1    |     abc
>    2    |     def
>    3    |     kir
>    .    |     ...
>    .    |     ...
>    N    |     xxx
> --------------------
>
> Here is how my data looks in a tab delim format:
>
>
> gene_name     expr_value    exp_value2
>  def             100.9        300.3
>  kir             200.4        334.4
>  sir              39.9          4.9
>  ..               ....        .....
> (30K lines like these)
>
> Now I want to use copy command and upload this whole
> tab delim file into the following table:
>
> Table: exp
>
> exp_no  |  gene_no(FK)  | exp_one | exp_two
> ---------------------------------------
>
>
> There are two questions :
>
> 1. In table 'exp' I have gene_no and not gene_name. In
> the tab delim file I have gene_name.  So, what is the
> ideal way to link these up - a fast one after
> 'COPY'ing this tab delim file in to exp_table.  should
> I include gene_name also into the 'exp' table or ask
> postgres to link up with 'Gene' table through
> 'gene_no' foreign key.
> 2. Can this be done by simple SQL statement or should
> I have to write a pl/pgql script.
>
> would any one please help me in teaching to get around
> this problem.

Srini,

One way to do this that works well for me is to "copy" the data into a
temporary table and then use regular SQL to do the inserts into separate
tables.  This technique is quite fast.

Sean



Re: COPY command, linking foreign keys

From
Srinivas Iyyer
Date:
Table: gene_tab

 gene_id | gene_name
---------+-----------
       1 | AARS
       2 | AGC1
       3 | APOA5
       4 | APOB
       5 | ATP13A2
       6 | C9orf106
       7 | CCNI
       8 | CENTG3
       9 | CITED4
      10 | GPR24

Table: gene_exp

 gene_id | gene_exp
---------+----------
(0 rows)



Table : gene_exp_temp

 temp_name | temp_exp
-----------+----------
 AARS      |      100
 AGC1      |      200
 APOA5     |      201
 APOB      |      202


question : Inserting into gene_exp from gene_exp_temp:


insert into gene_exp (gene_id, gene_exp) values (
(select gene_id from gene_tab, gene_exp_temp where
gene_name = temp_name),
(selct temp_exp from gene_exp_temp, gene_tab where
temp_exp = gene_id));

Something is wrong somewhere. I know I am not still
matured enough in terms of linking data.  Could you
help me where the problem is?

Thanks Sean.

-Srini






> > There are two questions :
> >
> > 1. In table 'exp' I have gene_no and not
> gene_name. In
> > the tab delim file I have gene_name.  So, what is
> the
> > ideal way to link these up - a fast one after
> > 'COPY'ing this tab delim file in to exp_table.
> should
> > I include gene_name also into the 'exp' table or
> ask
> > postgres to link up with 'Gene' table through
> > 'gene_no' foreign key.
> > 2. Can this be done by simple SQL statement or
> should
> > I have to write a pl/pgql script.
> >
> > would any one please help me in teaching to get
> around
> > this problem.
>
> Srini,
>
> One way to do this that works well for me is to
> "copy" the data into a
> temporary table and then use regular SQL to do the
> inserts into separate
> tables.  This technique is quite fast.
>
> Sean
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: COPY command, linking foreign keys

From
Sean Davis
Date:


On 12/19/05 9:27 AM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote:

> Table: gene_tab
>
>  gene_id | gene_name
> ---------+-----------
>        1 | AARS
>        2 | AGC1
>        3 | APOA5
>        4 | APOB
>        5 | ATP13A2
>        6 | C9orf106
>        7 | CCNI
>        8 | CENTG3
>        9 | CITED4
>       10 | GPR24
>
> Table: gene_exp
>
>  gene_id | gene_exp
> ---------+----------
> (0 rows)
>
>
>
> Table : gene_exp_temp
>
>  temp_name | temp_exp
> -----------+----------
>  AARS      |      100
>  AGC1      |      200
>  APOA5     |      201
>  APOB      |      202
>
>
> question : Inserting into gene_exp from gene_exp_temp:
>
>

Srini,

I like the construct:

INSERT INTO table1 (col1,col2,...)
SELECT ....

This lets me see what I am inserting before I do so....

Something like:

INSERT INTO gene_exp (gene_id,gene_exp)
SELECT gt.gene_id,
    ge.temp_exp
FROM
    gene_temp gt join gene_exp_temp ge
        on gt.temp_name=ge.gene_name;

I haven't tested this, but this might make the thought process a little
easier.  One thing it looks like you need (you have probably already thought
of this) is an "experiment ID" somewhere in your gene_exp table....

Sean



Re: Login problem

From
"Danjel Jungersen"
Date:
On 18 Dec 2005 at 9:00, John DeSoi wrote:

>
> On Dec 18, 2005, at 5:44 AM, Danjel Jungersen wrote:
>
> > Below are instructions on how to login without a password, and a
> > point in the direction on how to change it, but I keep getting:
> > psql: FATAL: User "me" does not exist.
> >
> > After some tries I got pass the database does not exist ;-)
> >
> > Is there a user that is allways there, or can I see a list somewhere
> > ?
> > The "postmaster" is mentioned, so I have tried that "root" and
> > "Postmaster", no luck...
> >
>
>
> The default superuser name is "postgres". There is generally a
> default database called template1. In PostgreSQL 8.1 and later, I
> believe the default database name has changed to "postgres" as well.
> So try
>
> psql -U postgres template1
>
> or
>
> psql -U postgres postgres
With some testing I have found that I have the databasename correct,
but still:
"user does not exist" :-(

Hope I can get throught this ;-)

btw, I'm running 7.4.3...

best regards
Danjel

>
> John
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>



Re: Login problem

From
John DeSoi
Date:
On Dec 20, 2005, at 2:33 AM, Danjel Jungersen wrote:

> With some testing I have found that I have the databasename correct,
> but still:
> "user does not exist" :-(
>
> Hope I can get throught this ;-)
>
> btw, I'm running 7.4.3...


Do you know who created the database cluster with initdb? Perhaps
they used their own user name rather than postgres.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


Re: Login problem

From
"Danjel Jungersen"
Date:
On 20 Dec 2005 at 8:37, John DeSoi wrote:

>
> On Dec 20, 2005, at 2:33 AM, Danjel Jungersen wrote:
>
> > With some testing I have found that I have the databasename correct,
> > but still:
> > "user does not exist" :-(
> >
> > Hope I can get throught this ;-)
> >
> > btw, I'm running 7.4.3...
>
>
> Do you know who created the database cluster with initdb? Perhaps
> they used their own user name rather than postgres.
I did :-)
as root...

Danjel

>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>



Re: Login problem

From
Tom Lane
Date:
"Danjel Jungersen" <danjel@jungersen.dk> writes:
> On 20 Dec 2005 at 8:37, John DeSoi wrote:
>> Do you know who created the database cluster with initdb? Perhaps
>> they used their own user name rather than postgres.

> I did :-)
> as root...

I don't think so.  initdb refuses to run as root.  However, the default
PG superuser account will be named the same as the Unix account that you
used to run initdb.

            regards, tom lane

Re: Login problem

From
"Danjel Jungersen"
Date:
On 20 Dec 2005 at 12:05, Tom Lane wrote:

> "Danjel Jungersen" <danjel@jungersen.dk> writes:
> > On 20 Dec 2005 at 8:37, John DeSoi wrote:
> >> Do you know who created the database cluster with initdb? Perhaps
> >> they used their own user name rather than postgres.
>
> > I did :-)
> > as root...
>
> I don't think so.  initdb refuses to run as root.  However, the default
> PG superuser account will be named the same as the Unix account that you
> used to run initdb.
Well, I did run it as root, but I have just this moment figured it
out, the name was _postgresql (I have no idea where that name comes
from)...
I got the tip from the startupscript hint...

My next problem, is that it refuses to let me log in from phpPgAdmin,
but I will post again, if I cannot get it to work after a bit more
testing :-)

Thank you all for your help so far !!

Best regards
Danjel
>
>             regards, tom lane



Re: Login problem

From
Frank Bax
Date:
At 12:22 PM 12/20/05, Danjel Jungersen wrote:

>On 20 Dec 2005 at 12:05, Tom Lane wrote:
>
> > "Danjel Jungersen" <danjel@jungersen.dk> writes:
> > > On 20 Dec 2005 at 8:37, John DeSoi wrote:
> > >> Do you know who created the database cluster with initdb? Perhaps
> > >> they used their own user name rather than postgres.
> >
> > > I did :-)
> > > as root...
> >
> > I don't think so.  initdb refuses to run as root.  However, the default
> > PG superuser account will be named the same as the Unix account that you
> > used to run initdb.
>
>Well, I did run it as root, but I have just this moment figured it
>out, the name was _postgresql (I have no idea where that name comes
>from)...


The username "_postgresql" came from your own fingers; it's a naming
standard for OpenBSD services.  You also did not run initdb as root - the
OpenBSD installation instructions directed you to create a user called
_postgresql, then su to that user and run initdb.  Perhaps you should reread:
/usr/local/share/doc/postgresql/README.OpenBSD

Frank