Обсуждение: can't "grant all on database..."

I've created a database called Inflight as user fc
I've created a user named luser

I want to:

    grant all on database Inflight to luser;

and PostgreSQL responds:

    ERROR:  database "inflight" not found

Which is strange because I had just connected to database Inflight as
user fc.
Bah Humbug!

On Wed, 18 Jun 2003, Travis Hume wrote:

> I've created a database called Inflight as user fc
> I've created a user named luser
>
> I want to:
>
>     grant all on database Inflight to luser;
>
> and PostgreSQL responds:
>
>     ERROR:  database "inflight" not found

Is the database inflight or "Inflight"?  You're probably being
bitten by the case folding of unquoted names.

The dbname is "Inflight", and I did think it was strange the the error
msg called it "inflight".
What is this "case folding of unquoted names" you speak of?  Do I need
to quote my dbname?  Anything else?

Thanks for quick reply.

Stephan Szabo wrote:

>On Wed, 18 Jun 2003, Travis Hume wrote:
>
>
>
>>I've created a database called Inflight as user fc
>>I've created a user named luser
>>
>>I want to:
>>
>>    grant all on database Inflight to luser;
>>
>>and PostgreSQL responds:
>>
>>    ERROR:  database "inflight" not found
>>
>>
>
>Is the database inflight or "Inflight"?  You're probably being
>bitten by the case folding of unquoted names.
>
>
>

Ok, using "Inflight" instead of Inflight sems to work, except the user
luser still doesn't have any privileges.

grant all on database "Inflight" to luser;

returns:

    GRANT

But if I then "psql Inflight luser", I can't do anything including
simple selects against the Inflight database.


Travis Hume wrote:

> The dbname is "Inflight", and I did think it was strange the the error
> msg called it "inflight".
> What is this "case folding of unquoted names" you speak of?  Do I need
> to quote my dbname?  Anything else?
>
> Thanks for quick reply.
>
> Stephan Szabo wrote:
>
>> On Wed, 18 Jun 2003, Travis Hume wrote:
>>
>>
>>
>>> I've created a database called Inflight as user fc
>>> I've created a user named luser
>>>
>>> I want to:
>>>
>>>    grant all on database Inflight to luser;
>>>
>>> and PostgreSQL responds:
>>>
>>>    ERROR:  database "inflight" not found
>>>
>>
>>
>> Is the database inflight or "Inflight"?  You're probably being
>> bitten by the case folding of unquoted names.
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

On Wed, 18 Jun 2003, Travis Hume wrote:

> Ok, using "Inflight" instead of Inflight sems to work, except the user
> luser still doesn't have any privileges.
>
> grant all on database "Inflight" to luser;
>
> returns:
>
>     GRANT
>
> But if I then "psql Inflight luser", I can't do anything including
> simple selects against the Inflight database.

The database permissions control whether or not you can create schemas and
temporary objects I think looking at the grant page.  That doesn't give
permission on other objects in the database, you need to grant permissions
to those separately.

> Travis Hume wrote:
>
> > The dbname is "Inflight", and I did think it was strange the the error
> > msg called it "inflight".
> > What is this "case folding of unquoted names" you speak of?  Do I need
> > to quote my dbname?  Anything else?

For objects made with double quotes and mixed case (I believe this applies
to createdb) you need to always refer to them with double quotes.

Ok, I give up.

I've created a database called "Inflight" using user "fc".  I want to
create a second user called "johndoe" and grant him all priviledges on
the "Inflight" database and then restrict him access to certain
IPAddress using the pg_hba.conf file.

Anyone know the magic words?


Stephan Szabo wrote:

>On Wed, 18 Jun 2003, Travis Hume wrote:
>
>
>
>>Ok, using "Inflight" instead of Inflight sems to work, except the user
>>luser still doesn't have any privileges.
>>
>>grant all on database "Inflight" to luser;
>>
>>returns:
>>
>>    GRANT
>>
>>But if I then "psql Inflight luser", I can't do anything including
>>simple selects against the Inflight database.
>>
>>
>
>The database permissions control whether or not you can create schemas and
>temporary objects I think looking at the grant page.  That doesn't give
>permission on other objects in the database, you need to grant permissions
>to those separately.
>
>
>
>>Travis Hume wrote:
>>
>>
>>
>>>The dbname is "Inflight", and I did think it was strange the the error
>>>msg called it "inflight".
>>>What is this "case folding of unquoted names" you speak of?  Do I need
>>>to quote my dbname?  Anything else?
>>>
>>>
>
>For objects made with double quotes and mixed case (I believe this applies
>to createdb) you need to always refer to them with double quotes.
>
>
>

On Wed, 18 Jun 2003, Travis Hume wrote:

> Ok, I give up.
>
> I've created a database called "Inflight" using user "fc".  I want to
> create a second user called "johndoe" and grant him all priviledges on
> the "Inflight" database and then restrict him access to certain
> IPAddress using the pg_hba.conf file.
>
> Anyone know the magic words?

I think you'll need to grant all privs to each of the schemas, tables, etc
the user should have access to.  There have been scripts that should do
the tables at least in the archives (I haven't paid enough attention to
know if they deal with other objects).

So I ended up doing this:

    create user ruser createuser;

and then limiting where he can connect from through pg_hba.conf file.

Thanks for the help

Stephan Szabo wrote:

>On Wed, 18 Jun 2003, Travis Hume wrote:
>
>
>
>>Ok, I give up.
>>
>>I've created a database called "Inflight" using user "fc".  I want to
>>create a second user called "johndoe" and grant him all priviledges on
>>the "Inflight" database and then restrict him access to certain
>>IPAddress using the pg_hba.conf file.
>>
>>Anyone know the magic words?
>>
>>
>
>I think you'll need to grant all privs to each of the schemas, tables, etc
>the user should have access to.  There have been scripts that should do
>the tables at least in the archives (I haven't paid enough attention to
>know if they deal with other objects).
>
>
>
>

On Wed, 18 Jun 2003, Travis Hume wrote:

> So I ended up doing this:
>
>     create user ruser createuser;
>
> and then limiting where he can connect from through pg_hba.conf file.

I think createuser implies superuser access. If you do that, you're
probably also going to want to control what databases he can connect to
not just from where (if you haven't already).  And, you need to be
careful with untrusted pl languages, but it might be easiest to just not
build any.

Yeah, I did limit access to a single database.  All this hassle is to
support our testing efforts.  The deployed database runs in a very
restricted environment.

Thanks for the help.

Stephan Szabo wrote:

>On Wed, 18 Jun 2003, Travis Hume wrote:
>
>
>
>>So I ended up doing this:
>>
>>    create user ruser createuser;
>>
>>and then limiting where he can connect from through pg_hba.conf file.
>>
>>
>
>I think createuser implies superuser access. If you do that, you're
>probably also going to want to control what databases he can connect to
>not just from where (if you haven't already).  And, you need to be
>careful with untrusted pl languages, but it might be easiest to just not
>build any.
>
>
>