CODEWITHSUNDEEP
databasepostgresql
8888B8888
8888B8888

Reputation: 21

Why database and role can't be found when granting privilege or changing ownership in postgresql

I have created database and role in postgresql (version 9.2.7) which can be listed with \l and \du command, but when I am trying to grant privilege or change ownership of the database, both database and role are unable to be found.

Below are the list

Databases

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg
es
-----------+----------+----------+-------------+-------------+------------------
-----
 PIJS01    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
           |          |          |             |             | postgres=CTc/post
gres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
           |          |          |             |             | postgres=CTc/post
gres
(5 rows)

Roles

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 JS01_USER |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

Following are the commands with object does not exist error message I've tried

Grant privilege

postgres=# grant all privileges on database PIJS01 to JS01_USER;
ERROR:  database "pijs01" does not exist

Change ownership

postgres=# alter database PIJS01 owner to JS01_USER;
ERROR:  role "js01_user" does not exist

I have no idea why they are inaccessible, please help, thanks.

Upvotes: 2

Views: 94

Answers (1)

Filipe Roxo
Filipe Roxo

Reputation: 722

If you're using Windows it is typically case insensitive so you need to use double quotes. If that is the case try:

GRANT ALL PRIVILEGES ON DATABASE "PIJS01" TO "JS01_USER";
ALTER DATABASE "PIJS01" OWNER TO "JS01_USER";

Edit:

Ye, I'm pretty sure that is your problem. As you can see the database and username in the error message are lowercased.

Upvotes: 1

Related Questions