Обсуждение: Problem database access

Hello,

I am trying to install a postgresql 8.1 shared server. I want each user
to access only its own database through phpPgAdmin. For example, user
“toto” access a database named “toto”, etc.

I used these lines to create the user and his database :
bash-3.2$ createuser toto -P -D -A
Enter password FOR new role:
Enter it again:
Shall the new role be allowed TO CREATE more new roles? (y/n) n
CREATE ROLE
-bash-3.2$ createdb -O toto -E LATIN1 toto
CREATE DATABASE

This is the contents of the file pg_hba.conf :
# TYPE DATABASE USER CIDR-ADDRESS METHOD
LOCAL sameuser ALL md5
LOCAL ALL @admins md5

The “postgres” user (so the administrator of the database) can log in
phpPgAdmin (without any problem). This user can see all databases. When
the user”toto” logs in phpPgAdmin, the connection failed and I get the
following error message in the postgres logs :

FATAL: no pg_hba.conf entry FOR host "[local]", user "toto", DATABASE
"template1", SSL off

Do you have any idea about why it doesn’t work and how to correct it?

Thanks in advance for your help

Regards

Yann

--
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex

Hello,,<br /><br /> Thank you for your help but it doesn't work with the line :<br /> host    toto     toto 0.0.0.0
0.0.0.0md5<br /><br /> I've got the same error message :<br /> FATAL:  no pg_hba.conf entry for host "[local]", user
"toto",database "template1", SSL off<br /><br /> Yann<br /><br /> Tommy Cheng wrote: <blockquote
cite="mid:2267dd9e0903120226t66d02558pabc83cec2232a253@mail.gmail.com"type="cite"> Try this:<br /> host    toto    
toto0.0.0.0 0.0.0.0 md5<br /><br /><div>On Wed, Mar 11, 2009 at 9:02 PM, Yann Collavoli <span><<a
href="mailto:cyann@cict.fr"moz-do-not-send="true">cyann@cict.fr</a>></span> wrote:<br /><blockquote>Hello,<br /><br
/>I am trying to install a postgresql 8.1 shared server. I want each user to access only its own database through
phpPgAdmin.For example, user “toto” access a database named “toto”, etc.<br /><br /> I used these lines to create the
userand his database :<br /> bash-3.2$ createuser toto -P -D -A<br /> Enter password FOR new role:<br /> Enter it
again:<br/> Shall the new role be allowed TO CREATE more new roles? (y/n) n<br /> CREATE ROLE<br /> -bash-3.2$ createdb
-Ototo -E LATIN1 toto<br /> CREATE DATABASE<br /><br /> This is the contents of the file pg_hba.conf :<br /> # TYPE
DATABASEUSER CIDR-ADDRESS METHOD<br /> LOCAL sameuser ALL md5<br /> LOCAL ALL @admins md5<br /><br /> The “postgres”
user(so the administrator of the database) can log in phpPgAdmin (without any problem). This user can see all
databases.When the user”toto” logs in phpPgAdmin, the connection failed and I get the following error message in the
postgreslogs :<br /><br /> FATAL: no pg_hba.conf entry FOR host "[local]", user "toto", DATABASE "template1", SSL
off<br/><br /> Do you have any idea about why it doesn’t work and how to correct it?<br /><br /> Thanks in advance for
yourhelp<br /><br /> Regards<br /><br /> Yann<br /><br /> -- <br /> Yann Collavoli<br /><br /> Tél : 05 61 36 60 20<br
/><br/> Université Paul Sabatier<br /> CICT<br /> 118, route de Narbonne<br /> 31062 Toulouse Cedex<br /><br /><br />
--<br /> Sent via pgsql-admin mailing list (<a href="mailto:pgsql-admin@postgresql.org"
moz-do-not-send="true">pgsql-admin@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-admin"
moz-do-not-send="true">http://www.postgresql.org/mailpref/pgsql-admin</a><br/></blockquote></div><br /></blockquote><br
/><br/><pre class="moz-signature" cols="72">--  
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex</pre>

Hi, <br /><br /> If i add this line  "local   all         all                               trust", the user "toto"
canlog in phpPgAdmin but he can see the others databases. Or, I want each user to access only its own database through
phpPgAdmin(for example, user “toto” access only at the database named “toto”). I think it's possible to do that but i
don'tunderstand how...<br /><br /> Regards<br /><br /> Yann<br /><br /> BRAHMA PRAKASH TIWARI wrote: <blockquote
cite="mid:2acc2c820903120310w48f01252r1d00e4374293902c@mail.gmail.com"type="cite"><div>Hi you need to add following
enty100% it will resolve the problem</div><div> </div><div> </div><div># "local" is for Unix domain socket connections
only<br/>  local   all         all                               trust<br /><br /><br /></div><div>On Thu, Mar 12, 2009
at3:21 PM, Yann Collavoli <span><<a href="mailto:cyann@cict.fr" moz-do-not-send="true">cyann@cict.fr</a>></span>
wrote:<br/><blockquote><div>Hello,,<br /><br /> Thank you for your help but it doesn't work with the line :<br />
host   toto     toto 0.0.0.0 0.0.0.0 md5<br /><br /> I've got the same error message :<br /> FATAL:  no pg_hba.conf
entryfor host "[local]", user "toto", database "template1", SSL off<br /><br /> Yann<br /><br /> Tommy Cheng wrote:
<blockquotetype="cite">Try this:<br /> host    toto     toto 0.0.0.0 0.0.0.0 md5<br /><br /><div>On Wed, Mar 11, 2009
at9:02 PM, Yann Collavoli <span><<a href="mailto:cyann@cict.fr" moz-do-not-send="true">cyann@cict.fr</a>></span>
wrote:<br/><blockquote>Hello,<br /><br /> I am trying to install a postgresql 8.1 shared server. I want each user to
accessonly its own database through phpPgAdmin. For example, user “toto” access a database named “toto”, etc.<br /><br
/>I used these lines to create the user and his database :<br /> bash-3.2$ createuser toto -P -D -A<br /> Enter
passwordFOR new role:<br /> Enter it again:<br /> Shall the new role be allowed TO CREATE more new roles? (y/n) n<br />
CREATEROLE<br /> -bash-3.2$ createdb -O toto -E LATIN1 toto<br /> CREATE DATABASE<br /><br /> This is the contents of
thefile pg_hba.conf :<br /> # TYPE DATABASE USER CIDR-ADDRESS METHOD<br /> LOCAL sameuser ALL md5<br /> LOCAL ALL
@adminsmd5<br /><br /> The “postgres” user (so the administrator of the database) can log in phpPgAdmin (without any
problem).This user can see all databases. When the user”toto” logs in phpPgAdmin, the connection failed and I get the
followingerror message in the postgres logs :<br /><br /> FATAL: no pg_hba.conf entry FOR host "[local]", user "toto",
DATABASE"template1", SSL off<br /><br /> Do you have any idea about why it doesn’t work and how to correct it?<br /><br
/>Thanks in advance for your help<br /><br /> Regards<br /><br /> Yann<br /><br /> -- <br /> Yann Collavoli<br /><br />
Tél: 05 61 36 60 20<br /><br /> Université Paul Sabatier<br /> CICT<br /> 118, route de Narbonne<br /> 31062 Toulouse
Cedex<br/><br /><br /> -- <br /> Sent via pgsql-admin mailing list (<a href="mailto:pgsql-admin@postgresql.org"
moz-do-not-send="true">pgsql-admin@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-admin"
moz-do-not-send="true">http://www.postgresql.org/mailpref/pgsql-admin</a><br/></blockquote></div><br /></blockquote><br
/><br/><pre>--  
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex</pre></div></blockquote></div><br /><br /><br /> -- <br /> Brahma Prakash Tiwari<br /> Database
Administrator<br/> iBoss Tech Solution Noida<br /></blockquote><br /><br /><pre class="moz-signature" cols="72">--  
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex</pre>

Yann Collavoli wrote:
> Hi,
>
> If i add this line "local all all trust", the user "toto" can log in
> phpPgAdmin but he can see the others databases. Or, I want each user
> to access only its own database through phpPgAdmin (for example, user
> “toto” access only at the database named “toto”). I think it's
> possible to do that but i don't understand how...
>
> Regards
>
> Yann
>
> BRAHMA PRAKASH TIWARI wrote:
>> Hi you need to add following enty 100% it will resolve the problem
>> # "local" is for Unix domain socket connections only
>> local all all trust
>>
>>
>> On Thu, Mar 12, 2009 at 3:21 PM, Yann Collavoli <cyann@cict.fr
>> <mailto:cyann@cict.fr>> wrote:
>>
>>     Hello,,
>>
>>     Thank you for your help but it doesn't work with the line :
>>     host toto toto 0.0.0.0 0.0.0.0 md5
>>
>>     I've got the same error message :
>>     FATAL: no pg_hba.conf entry for host "[local]", user "toto",
>>     database "template1", SSL off
>>
>>     Yann
>>
>>     Tommy Cheng wrote:
>>>     Try this:
>>>     host toto toto 0.0.0.0 0.0.0.0 md5
>>>
>>>     On Wed, Mar 11, 2009 at 9:02 PM, Yann Collavoli <cyann@cict.fr
>>>     <mailto:cyann@cict.fr>> wrote:
>>>
>>>         Hello,
>>>
>>>         I am trying to install a postgresql 8.1 shared server. I
>>>         want each user to access only its own database through
>>>         phpPgAdmin. For example, user “toto” access a database named
>>>         “toto”, etc.
>>>
>>>         I used these lines to create the user and his database :
>>>         bash-3.2$ createuser toto -P -D -A
>>>         Enter password FOR new role:
>>>         Enter it again:
>>>         Shall the new role be allowed TO CREATE more new roles? (y/n) n
>>>         CREATE ROLE
>>>         -bash-3.2$ createdb -O toto -E LATIN1 toto
>>>         CREATE DATABASE
>>>
>>>         This is the contents of the file pg_hba.conf :
>>>         # TYPE DATABASE USER CIDR-ADDRESS METHOD
>>>         LOCAL sameuser ALL md5
>>>         LOCAL ALL @admins md5
>>>
>>>         The “postgres” user (so the administrator of the database)
>>>         can log in phpPgAdmin (without any problem). This user can
>>>         see all databases. When the user”toto” logs in phpPgAdmin,
>>>         the connection failed and I get the following error message
>>>         in the postgres logs :
>>>
>>>         FATAL: no pg_hba.conf entry FOR host "[local]", user "toto",
>>>         DATABASE "template1", SSL off
>>>
>>>         Do you have any idea about why it doesn’t work and how to
>>>         correct it?
>>>
>>>         Thanks in advance for your help
>>>
>>>         Regards
>>>
>>>         Yann
>>>
>>>         --
>>>         Yann Collavoli
>>>
>>>         Tél : 05 61 36 60 20
>>>
>>>         Université Paul Sabatier
>>>         CICT
>>>         118, route de Narbonne
>>>         31062 Toulouse Cedex
>>>
>>>
>>>         --
>>>         Sent via pgsql-admin mailing list
>>>         (pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>)
>>>         To make changes to your subscription:
>>>         http://www.postgresql.org/mailpref/pgsql-admin
>>>
>>>
>>
>>
>>     --
>>     Yann Collavoli
>>
>>     Tél : 05 61 36 60 20
>>
>>     Université Paul Sabatier
>>     CICT
>>     118, route de Narbonne
>>     31062 Toulouse Cedex
>>
>>
>>
>>
>> --
>> Brahma Prakash Tiwari
>> Database Administrator
>> iBoss Tech Solution Noida
>
>
> --
> Yann Collavoli
>
> Tél : 05 61 36 60 20
>
> Université Paul Sabatier
> CICT
> 118, route de Narbonne
> 31062 Toulouse Cedex

Look at pg_hba what it says.

This will help you

local      /database/  /user/  /auth-method

so

local    toto toto trust
local     toto1 toto1 trust
local     toto2    toto2 trust


PS. Don't top post/
/
/

Hi,<br /><br /> Thank you very much, with this parameter it works like i want !!<br /><br /> Yann<br /><br /><br />
CarolWalter wrote: <blockquote cite="mid:E2CD6292-4B68-4D7B-BA10-CD99E9AABBAE@indiana.edu" type="cite"> If your users
arethe database owners, you can set the "Only show owned databases?" option in the config.inc.php file for your
PhpPgAdmin.<div><br /></div><div>Carol</div><div><br /><div><div>On Mar 12, 2009, at 7:31 AM, BRAHMA PRAKASH TIWARI
wrote:</div><br/><blockquote type="cite"><div>Hi Yann</div><div>For below given purpose you need to create roles  and
assignthem separately to each database user or revoke the global permission from database and givepermission to  
concernuser only and also You need to change the password of global user.this is the best way by which You can achive
yourrequirement.none can see others database<br /><br /></div><div>On Thu, Mar 12, 2009 at 4:52 PM, Yann Collavoli
<span><<ahref="mailto:cyann@cict.fr" moz-do-not-send="true">cyann@cict.fr</a>></span> wrote:<br
/><blockquote><div>Hi,<br /><br /> If i add this line  "local   all         all                               trust",
theuser "toto" can log in phpPgAdmin but he can see the others databases. Or, I want each user to access only its own
databasethrough phpPgAdmin (for example, user “toto” access only at the database named “toto”). I think it's possible
todo that but i don't understand how...<br /><br /> Regards<br /><br /> Yann <div><div><br /><br /> BRAHMA PRAKASH
TIWARIwrote: <blockquote type="cite"><div>Hi you need to add following enty 100% it will resolve the
problem</div><div> </div><div> </div><div>#"local" is for Unix domain socket connections only<br />  local  
all        all                               trust<br /><br /><br /></div><div>On Thu, Mar 12, 2009 at 3:21 PM, Yann
Collavoli<span><<a href="mailto:cyann@cict.fr" moz-do-not-send="true">cyann@cict.fr</a>></span> wrote:<br
/><blockquote><div>Hello,,<br/><br /> Thank you for your help but it doesn't work with the line :<br /> host    toto
   toto 0.0.0.0 0.0.0.0 md5<br /><br /> I've got the same error message :<br /> FATAL:  no pg_hba.conf entry for host
"[local]",user "toto", database "template1", SSL off<br /><br /> Yann<br /><br /> Tommy Cheng wrote: <blockquote
type="cite">Trythis:<br /> host    toto     toto 0.0.0.0 0.0.0.0 md5<br /><br /><div>On Wed, Mar 11, 2009 at 9:02 PM,
YannCollavoli <span><<a href="mailto:cyann@cict.fr" moz-do-not-send="true">cyann@cict.fr</a>></span> wrote:<br
/><blockquote>Hello,<br/><br /> I am trying to install a postgresql 8.1 shared server. I want each user to access only
itsown database through phpPgAdmin. For example, user “toto” access a database named “toto”, etc.<br /><br /> I used
theselines to create the user and his database :<br /> bash-3.2$ createuser toto -P -D -A<br /> Enter password FOR new
role:<br/> Enter it again:<br /> Shall the new role be allowed TO CREATE more new roles? (y/n) n<br /> CREATE ROLE<br
/>-bash-3.2$ createdb -O toto -E LATIN1 toto<br /> CREATE DATABASE<br /><br /> This is the contents of the file
pg_hba.conf:<br /> # TYPE DATABASE USER CIDR-ADDRESS METHOD<br /> LOCAL sameuser ALL md5<br /> LOCAL ALL @admins md5<br
/><br/> The “postgres” user (so the administrator of the database) can log in phpPgAdmin (without any problem). This
usercan see all databases. When the user”toto” logs in phpPgAdmin, the connection failed and I get the following error
messagein the postgres logs :<br /><br /> FATAL: no pg_hba.conf entry FOR host "[local]", user "toto", DATABASE
"template1",SSL off<br /><br /> Do you have any idea about why it doesn’t work and how to correct it?<br /><br />
Thanksin advance for your help<br /><br /> Regards<br /><br /> Yann<br /><br /> -- <br /> Yann Collavoli<br /><br />
Tél: 05 61 36 60 20<br /><br /> Université Paul Sabatier<br /> CICT<br /> 118, route de Narbonne<br /> 31062 Toulouse
Cedex<br/><br /><br /> -- <br /> Sent via pgsql-admin mailing list (<a href="mailto:pgsql-admin@postgresql.org"
moz-do-not-send="true">pgsql-admin@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-admin"
moz-do-not-send="true">http://www.postgresql.org/mailpref/pgsql-admin</a><br/></blockquote></div><br /></blockquote><br
/><br/><pre>--  
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex</pre></div></blockquote></div><br /><br /><br /> -- <br /> Brahma Prakash Tiwari<br /> Database
Administrator<br/> iBoss Tech Solution Noida<br /></blockquote><br /><br /><pre>--  
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex</pre></div></div></div></blockquote></div><br /><br /><br /> -- <br /> Brahma Prakash Tiwari<br />
DatabaseAdministrator<br /> iBoss Tech Solution Noida<br /></blockquote></div><br /></div></blockquote><br /><br /><pre
class="moz-signature"cols="72">--  
Yann Collavoli

Tél : 05 61 36 60 20

Université Paul Sabatier
CICT
118, route de Narbonne
31062 Toulouse Cedex</pre>