Thread: PostgreSQL DBI DBD::Pg Access Problem

PostgreSQL DBI DBD::Pg Access Problem

From
Samizdatt
Date:
*************** Configuration Info ******************
I'm using:

SuSE PPC Linux v7.3 on an Apple iBook
PostgreSQL v7.1.3
Perl v5.6.1
DBI v1.20
DBD::Pg v1.01
Apache v1.3.20
Apache DBI v0.88

I created 2 users in addition to postgres with the createuser command. These users have actual corresponding accounts
onthe system. 

(1)postgres - can create users and databases
(2)root - can create databases
(3)wwwrun - is just the web server account that can neither create databases nor users

I modified the pg_hba.conf to temporarily allow connections from all users on the box by adding the following lines to
thefile: 

local    all    trust
host    all    127.0.0.1    255.255.255.255        trust
host    all    10.10.10.50    255.255.255.255        trust

Postmaster runs as "/usr/bin/postmaster -i -B 512 -D/var/lib/pgsql/data"
******************************************************


*************** Problem ******************************

I can connect to any of the PostgreSQL databases through any of the 3 user accounts using psql, but I can only connect
tothe databases with my web server cgi & command line Perl DBI/DBD::Pg applications by including "postgres" as the user
inmy DBI database handles. I'd like to be able to connect to the databases using the wwwrun user account that is
restrictedfrom creating both users and databases in my DBI based applications and cgi scripts. 

Since the pg_hba.conf is set to allow any user with an account in the PostgreSQL database to connect from my box, and I
canconnect to any of the databases through any of the 3 accounts using psql, shouldn't my DBI based cgi & command line
Perlapplications be able to connect to the same databases using any of the 3 postgres user accounts I created using
createuser?Now, only including "postgres" as the user in my DBI/DBD::Pg database handles allows my cgi & command line
programsto access my PostgreSQL databases.  
*****************************************************

Thank you for any assistance.

Re: PostgreSQL DBI DBD::Pg Access Problem

From
Tom Lane
Date:
Samizdatt <Samizdatt@earthlink.net> writes:
> I can connect to any of the PostgreSQL databases through any of the 3
> user accounts using psql, but I can only connect to the databases with
> my web server cgi & command line Perl DBI/DBD::Pg applications by
> including "postgres" as the user in my DBI database handles.

What happens when you try to connect as someone else?  What error
messages show up in the postmaster log?  (If you're not keeping the
postmaster's log output, now is a good time to start.)

            regards, tom lane

Re: PostgreSQL DBI DBD::Pg Access Problem

From
postgresql@finner.de
Date:
On 24-Dec-01 Samizdatt sat down, thought for a long time and then wrote:
>
> I created 2 users in addition to postgres with the createuser
> command. These users have actual corresponding accounts on the
> system.
>
> (1)postgres - can create users and databases
> (2)root - can create databases
> (3)wwwrun - is just the web server account that can neither create
> databases nor users

Did you grant some rights for using the databases created by anybody
else (root, postgres) to the user "wwwrun"? It is not enough just to
have that user, the owner of the database (usually the creator) or any
database masteruser must grant specific rights to any other user who
should work with the database. Especially wwwrun, who may not create
his own database, must be given at least some rights, "SELECT" for
example.

>
> I modified the pg_hba.conf to temporarily allow connections from all
> users on the box by adding the following lines to the file:
>
> local all     trust
> host  all     127.0.0.1       255.255.255.255         trust
> host  all     10.10.10.50     255.255.255.255         trust
>

This means that all postgres users (postgres, root, wwwrun) on that
host may connect to the database engine without further examination, but
not, that they can do anything else, using a database for example. ;-)

> I can connect to any of the PostgreSQL databases through any of the 3
> user accounts using psql, but I can only connect to the databases
> with my web server cgi & command line Perl DBI/DBD::Pg applications
> by including "postgres" as the user in my DBI database handles. I'd
> like to be able to connect to the databases using the wwwrun user
> account that is restricted from creating both users and databases in
> my DBI based applications and cgi scripts.
>
> Since the pg_hba.conf is set to allow any user with an account in the
> PostgreSQL database to connect from my box, and I can connect to any
> of the databases through any of the 3 accounts using psql, shouldn't
> my DBI based cgi & command line Perl applications be able to connect
> to the same databases using any of the 3 postgres user accounts I
> created using createuser?

No, the user just may connect to the engine, but without granted rights
they may do nothing, at least wwwrun.

> Now, only including "postgres" as the user
> in my DBI/DBD::Pg database handles allows my cgi & command line
> programs to access my PostgreSQL data
> bases.
> *****************************************************
>
> Thank you for any assistance.
>

Hope it helps.

Greetings,
--
Frank Finner

And now there is no turning back at all.
                              (M. Moorcock, "Elric Of Melnibone")"

Re: PostgreSQL DBI DBD::Pg Access Problem

From
"Rob Arnold"
Date:
Have you granted (in Postgres) rights to the accounts so that they can read
tables a such?  Everything else appears to be fine.  Yes, if you can connect
via psql then the only thing that should change in the DBI connect string is
the username parameter.

I'm assuming your DBI line looks something like this:

my $dbh =
DBI->connect('dbi:pg:dbname=YOURDBNAME;host=localhost','wwwrun','PASSWORD');

NOTE: You can probably leave off the ';host=localhost' part.  I think doing
so will make a native UNIX socket connection rather than an INET socket
connection.  You might try it both ways.

--rob

----- Original Message -----
From: "Samizdatt" <Samizdatt@earthlink.net>
To: <pgsql-general@postgresql.org>
Sent: Sunday, December 23, 2001 8:31 PM
Subject: PostgreSQL DBI DBD::Pg Access Problem


>
> *************** Configuration Info ******************
> I'm using:
>
> SuSE PPC Linux v7.3 on an Apple iBook
> PostgreSQL v7.1.3
> Perl v5.6.1
> DBI v1.20
> DBD::Pg v1.01
> Apache v1.3.20
> Apache DBI v0.88
>
> I created 2 users in addition to postgres with the createuser command.
These users have actual corresponding accounts on the system.
>
> (1)postgres - can create users and databases
> (2)root - can create databases
> (3)wwwrun - is just the web server account that can neither create
databases nor users
>
> I modified the pg_hba.conf to temporarily allow connections from all users
on the box by adding the following lines to the file:
>
> local all trust
> host all 127.0.0.1 255.255.255.255 trust
> host all 10.10.10.50 255.255.255.255 trust
>
> Postmaster runs as "/usr/bin/postmaster -i -B 512 -D/var/lib/pgsql/data"
> ******************************************************
>
>
> *************** Problem ******************************
>
> I can connect to any of the PostgreSQL databases through any of the 3 user
accounts using psql, but I can only connect to the databases with my web
server cgi & command line Perl DBI/DBD::Pg applications by including
"postgres" as the user in my DBI database handles. I'd like to be able to
connect to the databases using the wwwrun user account that is restricted
from creating both users and databases in my DBI based applications and cgi
scripts.
>
> Since the pg_hba.conf is set to allow any user with an account in the
PostgreSQL database to connect from my box, and I can connect to any of the
databases through any of the 3 accounts using psql, shouldn't my DBI based
cgi & command line Perl applications be able to connect to the same
databases using any of the 3 postgres user accounts I created using
createuser? Now, only including "postgres" as the user in my DBI/DBD::Pg
database handles allows my cgi & command line programs to access my
PostgreSQL databases.
> *****************************************************
>
> Thank you for any assistance.
>


Re: PostgreSQL DBI DBD::Pg Access Problem

From
Samizdatt
Date:
Thanks for the assistance; it made a nice Christmas gift ;-)

I should have spent a bit more time reading the documentation. I set up PostgreSQL / DBI over two years ago, and must
haveforgotten that I had to grant user access rights for tables...  
http://www.postgresql.org/idocs/index.php?privileges.html

Hope everyone had a nice holiday!