CODEWITHSUNDEEP
postgresqllaravel-9
Fil
Fil

Reputation: 8873

Permission denied for schema public at character x

I am using postgresql 15 and I tried running these:

grant all privileges on database my_database to my_database_user;
grant all privileges on all tables in schema public to my_database_user;
grant all privileges on all sequences in schema public to my_database_user;
grant all privileges on all functions in schema public to my_database_user;

but when I run:

php artisan migrate --seed

I got:

SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for schema public at character 14 (SQL: create table "migrations" ("id" serial primary key not null, "migration" varchar(255) not null, "batch" integer not null))

What I am missing?

I do make sure .env has correct credentials:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=my_database
DB_USERNAME=my_database_user
DB_PASSWORD=password

Checking that I did:

postgres=# \du my_database_user
            List of roles
  Role name  | Attributes | Member of 
-------------+------------+-----------
 my_database_user |            | {}

and:

postgres=# SELECT * FROM pg_roles;
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 my_database_user               | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16389

Upvotes: 18

Views: 29969

Answers (4)

michalxo
michalxo

Reputation: 101

For psql scripting newbies like myself, here's oneliner:

PGPASSWORD=<password> psql -h localhost -d <dbname> -U postgres -c "GRANT ALL ON SCHEMA public TO <username>";

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246838

You are missing permissions on the schema:

GRANT CREATE ON SCHEMA public TO my_database_user;

You are probably using PostgreSQL v15 or higher. The default permissions on the public schema have changed in v15. Before, the insecure default was to allow everyone (PUBLIC) to create objects in schema public. Now only the database owner can do that, unless you grant extra privileges.

Make sure that you are connected to the correct database when you grant the permissions, as each database has its own schemas.

Upvotes: 32

SyntaxGoonoo
SyntaxGoonoo

Reputation: 1070

Do this:

  • Login to psql using a user with superuser priv.
  • Change to the target database
  • Grant CREATE on 'public' schema to the target user

For example:

$ sudo -u postgres psql
postgres=# \c dbname 
postgres=# GRANT CREATE ON SCHEMA public TO username;

Upvotes: 14

Strato80
Strato80

Reputation: 51

To supplement Laurenz's answer, this is for those who might be executing this from a script, as I was.

Since specifying the database in such a query is not possible (as a prefix, which I initially thought would be logical: <db_name>.<schema_name>), you first need to connect to the target database (with \c <db_name>), and then execute the query. If not, it will silently grant creation permission to whichever database context is connected (in my case, it was Postgres).

Upvotes: 5

Related Questions