Обсуждение: Problem with 7.4.5 and webmin 1.8 in grant function
I newly installed the postgresql 7.4.5 and FC 3 in my server and transfer the data from 7.3.2 with just a few problems. After I use the webmin 1.8 to config the grant previllage to the users ,I found that there is an error in the grant previlege . postgresql --> Grant Previlege --> error select relname, relacl from pg_class where (relkind = 'r' OR relkind = 'S') and relname !~ '^pg_' order by relname : Unknown DBI error What is the cause of this error and how could I handle this order? Please make any comment, Thanks. Amrit , Thailand
amrit@health2.moph.go.th wrote: > I newly installed the postgresql 7.4.5 and FC 3 in my server and transfer the > data from 7.3.2 with just a few problems. After I use the webmin 1.8 to config > the grant previllage to the users ,I found that there is an error in the grant > previlege . > postgresql --> Grant Previlege --> error > > select relname, relacl from pg_class where (relkind = 'r' OR relkind = 'S') and > relname !~ '^pg_' order by relname : Unknown DBI error > > What is the cause of this error and how could I handle this order? > Please make any comment, Thanks. > I would suspect a DBI/DBD installation issue, either perl DBI cannot find DBD-Pg (not installed ?) or DBD-Pg cannot find your Pg 7.4.5. I note that FC3 comes with Pg 7.4.6 - did you installed 7.4.5 from source? If so this could be why the perl database modules cannot find it (you may need to rebuild DBD-Pg, telling it where your Pg install is). regards Mark
> I would suspect a DBI/DBD installation issue, either perl DBI cannot > find DBD-Pg (not installed ?) or DBD-Pg cannot find your Pg 7.4.5. > > I note that FC3 comes with Pg 7.4.6 - did you installed 7.4.5 from > source? If so this could be why the perl database modules cannot find it > (you may need to rebuild DBD-Pg, telling it where your Pg install is). > > regards > > Mark > I installed FC3 from rpm kernel 2.6.9 which already included postgresql 7.4.5 . Suppose that there were some missing component , what should be the missing rpm component which I forgot to install ? Amrit , Thailand
amrit@health2.moph.go.th wrote: >>I would suspect a DBI/DBD installation issue, either perl DBI cannot >>find DBD-Pg (not installed ?) or DBD-Pg cannot find your Pg 7.4.5. >> >>I note that FC3 comes with Pg 7.4.6 - did you installed 7.4.5 from >>source? If so this could be why the perl database modules cannot find it >>(you may need to rebuild DBD-Pg, telling it where your Pg install is). > > I installed FC3 from rpm kernel 2.6.9 which already included postgresql 7.4.5 . > Suppose that there were some missing component , what should be the missing rpm > component which I forgot to install ? > Ok - I must be looking at the *updated* FC3 distribution... I may have 'jumped the gun' a little - the situation I describe above will prevent *any* access at all to Pg from webmin. If this is the case then check you have (perl) DBI and (perl) DBD-Pg components installed. If on the other hand you can do *some* Pg admin from webmin, and you are only having problems with the grants then there is something it does not like about the *particular* statement. The way to debug this is to do a tiny perl DBI program that tries to execute the statement : select relname, relacl from pg_class where (relkind = 'r' OR relkind = 'S') and relname !~ '^pg_' order by relname So - sorry to confuse, but let us know which situation you have there :-) best wishes Mark
Mark Kirkwood wrote:
> If on the other hand you can do *some* Pg admin from webmin, and you are
> only having problems with the grants then there is something it does not
> like about the *particular* statement. The way to debug this is to do a
> tiny perl DBI program that tries to execute the statement :
>
> select relname, relacl from pg_class where (relkind = 'r' OR relkind =
> 'S') and relname !~ '^pg_' order by relname
>
I did a quick check of this case... seems to be no problem running this
statement using perl 5.8.5, DBI-1.42 and DBD-Pg-1.22. You might like to
try out the attached test program that does this (You may have to add a
password in order to connect, depending on your security settings).
Mark
#!/usr/bin/perl -w
#
# relacl.pl : testbed for
#
use DBI;
use strict;
my $db = "dbname=template1;port=5432";
my $user = "postgres";
my $pwd = "";
my $dsn = "DBI:Pg:$db";
my $con;
my $sql = "select relname, relacl from pg_class where " .
"(relkind = 'r' OR relkind = 'S') and relname !~ '^pg_' " .
"order by relname";
my $sth;
my @row;
$con = DBI->connect($dsn,$user,$pwd)
or die "Error in connect to $dsn: $!\n";
$sth = $con->prepare($sql)
or die "Error in prepare : $!";
$sth->execute()
or die "Error in execute : $!";
print "Relname\t\tRelacl\n";
while ( @row = $sth->fetchrow_array() ) {
print $row[0] . "\t" . $row[1] . "\n";
}
$sth->finish();
$con->disconnect();
> Ok - I must be looking at the *updated* FC3 distribution...
>
> I may have 'jumped the gun' a little - the situation I describe above
> will prevent *any* access at all to Pg from webmin. If this is the case
> then check you have (perl) DBI and (perl) DBD-Pg components installed.
>
> If on the other hand you can do *some* Pg admin from webmin, and you are
> only having problems with the grants then there is something it does not
> like about the *particular* statement. The way to debug this is to do a
> tiny perl DBI program that tries to execute the statement :
>
> select relname, relacl from pg_class where (relkind = 'r' OR relkind =
> 'S') and relname !~ '^pg_' order by relname
>
> So - sorry to confuse, but let us know which situation you have there :-)
>
> best wishes
>
> Mark
>
I used you perl script and found the error =>
[root@samba tmp]# perl relacl.pl
DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL: IDENT
authentication failed for user "postgres" at relacl.pl line 21
Error in connect to DBI:Pg:dbname=template1;port=5432:
And my pg_hba.conf is
# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host all all 192.168.0.0 255.255.0.0 trust
trusted for every user.
Would you give me an idea what's wrong?
Thanks .
Amrit,Thailand
amrit@health2.moph.go.th wrote:
>
> I used you perl script and found the error =>
> [root@samba tmp]# perl relacl.pl
> DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL: IDENT
> authentication failed for user "postgres" at relacl.pl line 21
> Error in connect to DBI:Pg:dbname=template1;port=5432:
>
>
Excellent - we know what is going on now!
> And my pg_hba.conf is
>
> # IPv4-style local connections:
> host all all 127.0.0.1 255.255.255.255 trust
> host all all 192.168.0.0 255.255.0.0 trust
>
> trusted for every user.
Ok, what I think has happened is that there is another Pg installation
(or another initdb'ed cluster) on this machine that you are accidentally
talking to. Try
$ rpm -qa|grep -i postgres
which will spot another software installation, you may just have to
search for files called pg_hba.conf to find another initdb'ed cluster....
This other installation should have a pg_hba.conf that looks something
like :
local all all ident
host all all 127.0.0.1 255.255.255.255 ident
So a bit of detective work is in order :-)
Mark
> > I used you perl script and found the error =>
> > [root@samba tmp]# perl relacl.pl
> > DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL:
> IDENT
> > authentication failed for user "postgres" at relacl.pl line 21
> > Error in connect to DBI:Pg:dbname=template1;port=5432:
> >
> >
> Excellent - we know what is going on now!
>
>
> > And my pg_hba.conf is
> >
> > # IPv4-style local connections:
> > host all all 127.0.0.1 255.255.255.255 trust
> > host all all 192.168.0.0 255.255.0.0 trust
> >
> > trusted for every user.
>
> Ok, what I think has happened is that there is another Pg installation
> (or another initdb'ed cluster) on this machine that you are accidentally
> talking to. Try
>
> $ rpm -qa|grep -i postgres
>
> which will spot another software installation, you may just have to
> search for files called pg_hba.conf to find another initdb'ed cluster....
>
> This other installation should have a pg_hba.conf that looks something
> like :
>
> local all all ident
> host all all 127.0.0.1 255.255.255.255 ident
>
> So a bit of detective work is in order :-)
>
> Mark
After being a detector I found that
[root@samba ~]# rpm -qa|grep -i postgres
postgresql-7.4.5-3.1.tlc
postgresql-python-7.4.5-3.1.tlc
postgresql-jdbc-7.4.5-3.1.tlc
postgresql-tcl-7.4.5-3.1.tlc
postgresql-server-7.4.5-3.1.tlc
postgresql-libs-7.4.5-3.1.tlc
postgresql-docs-7.4.5-3.1.tlc
postgresql-odbc-7.3-8.1.tlc
postgresql-pl-7.4.5-3.1.tlc
postgresql-test-7.4.5-3.1.tlc
postgresql-contrib-7.4.5-3.1.tlc
[root@samba ~]#
no other pg installation except the pgsql for windows in samba folder which I
think it isn't matter ,is it?
No other pg being run.
[root@samba ~]# ps ax|grep postmaster
2228 ? S 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
3308 pts/0 S+ 0:00 grep postmaster
[root@samba ~]#
Is it possible that it is related to pg_ident.conf ?
Any comment please.
Amrit,Thailand
amrit@health2.moph.go.th wrote: > After being a detector I found that > [root@samba ~]# rpm -qa|grep -i postgres > postgresql-7.4.5-3.1.tlc > postgresql-python-7.4.5-3.1.tlc > postgresql-jdbc-7.4.5-3.1.tlc > postgresql-tcl-7.4.5-3.1.tlc > postgresql-server-7.4.5-3.1.tlc > postgresql-libs-7.4.5-3.1.tlc > postgresql-docs-7.4.5-3.1.tlc > postgresql-odbc-7.3-8.1.tlc > postgresql-pl-7.4.5-3.1.tlc > postgresql-test-7.4.5-3.1.tlc > postgresql-contrib-7.4.5-3.1.tlc > [root@samba ~]# > > no other pg installation except the pgsql for windows in samba folder which I > think it isn't matter ,is it? > No other pg being run. > [root@samba ~]# ps ax|grep postmaster > 2228 ? S 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data > 3308 pts/0 S+ 0:00 grep postmaster > [root@samba ~]# > Well, sure looks like you only have one running. Your data directory is /var/lib/pgsql/data so lets see the files: /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_ident.conf /var/lib/pgsql/data/postmaster.opts Might also be useful to know any nondefault settings in postgresql.conf too. As I understand it, these vendor shipped rpms have ident *enabled*. I will download FC3 Pg and check this out... I'm a compile it from source guy :-) Mark
> Well, sure looks like you only have one running. Your data directory is > /var/lib/pgsql/data so lets see the files: > > /var/lib/pgsql/data/pg_hba.conf > /var/lib/pgsql/data/pg_ident.conf > /var/lib/pgsql/data/postmaster.opts > > Might also be useful to know any nondefault settings in postgresql.conf too. > > As I understand it, these vendor shipped rpms have ident *enabled*. > I will download FC3 Pg and check this out... I'm a compile it from > source guy :-) > > Mark I got the answer that is in module config of postgresl-webmin , there is a check box for Use DBI to connect if available? yes no the default is yes , but if I choosed no everything went fine. I also test it in the desktop mechine and get the same error and the same solution. Could you explain what happen to the FC3 + postgresql and webmin 1.8? Thanks Amrit ,Thailand
Sorry, just a fool tip, cause I haven't seen that you already done the pg_ctl stop && pg_ctl start ...
(I mean, did you reload your conf settings?)
Regards,
Guido
> > > I used you perl script and found the error =>
> > > [root@samba tmp]# perl relacl.pl
> > > DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL:
> > IDENT
> > > authentication failed for user "postgres" at relacl.pl line 21
> > > Error in connect to DBI:Pg:dbname=template1;port=5432:
> > >
> > >
> > Excellent - we know what is going on now!
> >
> >
> > > And my pg_hba.conf is
> > >
> > > # IPv4-style local connections:
> > > host all all 127.0.0.1 255.255.255.255 trust
> > > host all all 192.168.0.0 255.255.0.0 trust
> > >
> > > trusted for every user.
> >
> > Ok, what I think has happened is that there is another Pg installation
> > (or another initdb'ed cluster) on this machine that you are accidentally
> > talking to. Try
> >
> > $ rpm -qa|grep -i postgres
> >
> > which will spot another software installation, you may just have to
> > search for files called pg_hba.conf to find another initdb'ed cluster....
> >
> > This other installation should have a pg_hba.conf that looks something
> > like :
> >
> > local all all ident
> > host all all 127.0.0.1 255.255.255.255 ident
> >
> > So a bit of detective work is in order :-)
> >
> > Mark
> After being a detector I found that
> [root@samba ~]# rpm -qa|grep -i postgres
> postgresql-7.4.5-3.1.tlc
> postgresql-python-7.4.5-3.1.tlc
> postgresql-jdbc-7.4.5-3.1.tlc
> postgresql-tcl-7.4.5-3.1.tlc
> postgresql-server-7.4.5-3.1.tlc
> postgresql-libs-7.4.5-3.1.tlc
> postgresql-docs-7.4.5-3.1.tlc
> postgresql-odbc-7.3-8.1.tlc
> postgresql-pl-7.4.5-3.1.tlc
> postgresql-test-7.4.5-3.1.tlc
> postgresql-contrib-7.4.5-3.1.tlc
> [root@samba ~]#
>
> no other pg installation except the pgsql for windows in samba folder which I
> think it isn't matter ,is it?
> No other pg being run.
> [root@samba ~]# ps ax|grep postmaster
> 2228 ? S 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
> 3308 pts/0 S+ 0:00 grep postmaster
> [root@samba ~]#
>
> Is it possible that it is related to pg_ident.conf ?
>
> Any comment please.
> Amrit,Thailand
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
amrit@health2.moph.go.th wrote: > > I got the answer that is in module config of postgresl-webmin , there is a check > box for > > Use DBI to connect if available? yes no the default is > yes , but if I choosed no everything went fine. > > I also test it in the desktop mechine and get the same error and the same > solution. Could you explain what happen to the FC3 + postgresql and webmin 1.8? Well, given the error was coming from the postmaster, I don't believe that DBI or webmin have anything to do with it. What I can believe is that DBI=yes and DBI=no are using different parameters for connecting, therefore hitting different parts of your old (see below) pg_hba.conf settings. I concur with the other poster, and suspect that the files *were* using some form of ident identification, but have been subsequently edited to use trust - but the postmaster has not been restarted to know this! Try $ pg_ctl reload to get running with 'trust'.