CODEWITHSUNDEEP
postgresqlvagrant
Musterknabe
Musterknabe

Reputation: 6091

Relation does not exist when trying to grant privileges

I'm currently writing a script to build my vagrant box. I got PHP 7 and some other tools installed, as well as PostgreSQL, but when creating the database and trying to grant privileges I'm getting the following error

ERROR: relation "projectname" does not exist

This is my script (the important stuff)

#!/usr/bin/env bash
projectname='projectname'
echo "Initializing database"
sudo apt-get install -y postgresql
sudo -u postgres psql -c "CREATE USER $projectname WITH PASSWORD 'xxxx';"
sudo -u postgres psql -c "CREATE DATABASE $projectname;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON $projectname TO $projectname;"

Everything works until the last step

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON $projectname TO $projectname;"

When trying this out, I'm getting the above error. I also tried to write it manually

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON projectname TO projectname;"

Same error.

I also tried to wrap it in quotation marks

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON 'feedparser' TO 'feedparser';"

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON \"feedparser\" TO \"feedparser\";"

What exactly am I doing wrong here?

Upvotes: 22

Views: 11742

Answers (1)

alexander.polomodov
alexander.polomodov

Reputation: 5534

Documentation about GRANT PRIVILEGES says that your version GRANT command is suitable for grant rights to table. If you want to grant rights to database you should use GRANT ALL PRIVILEGES ON DATABASE projectname TO projectname;. Below I show the results of this commands:

postgres=# CREATE USER projectname WITH PASSWORD 'projectname';
CREATE ROLE
postgres=# CREATE DATABASE projectname;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON projectname TO projectname;
ERROR:  relation "projectname" does not exist
postgres=# GRANT ALL PRIVILEGES ON DATABASE projectname TO projectname;
GRANT

Upvotes: 40

Related Questions