Postgresql granting a username to specific database

Question

I'm trying to create a user that only has access to one database. It's telling me database doesn't exist but clearly it does by following.

postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 GarsDB    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
(4 rows)
postgres=# grant all privileges on database GarsDB to gars;
ERROR:  database "garsdb" does not exist
postgres=# grant all privileges on database garsdb to gars;
ERROR:  database "garsdb" does not exist
postgres=#

Any ideas what could be going on? I've only been using Postgres recently.

score 1 · Accepted Answer · answered Apr 12 '23 at 07:56

You have a case-sensitive database name because you used the dreaded double quotes when you created it. This means you now have to use the double quotes always:

grant all privileges on database "GarsDB" to gars;

I highly recommend to not use case sensitive identifiers. I would rename the database to be all lowercase:

alter database "GarsDB" rename to garsdb;

Postgresql granting a username to specific database

1 Answers1