CODEWITHSUNDEEP
postgresqlpostgresql-15
Kifsif
Kifsif

Reputation: 3843

Necessity to grant privileges on schema public + not working after the priveleges have been granted

Under postgres user:

postgres=# GRANT ALL ON schema public TO trololo;
GRANT


postgres=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | trololo=UC/pg_database_owner           | 
(1 row)


postgres=# \l
                                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges    
-----------+----------+----------+-------------+-------------+------------+-----------------+------------------------
 marketing | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres +
           |          |          |             |             |            |                 | marketing=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres           +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres           +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 trololo   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres +
           |          |          |             |             |            |                 | trololo=CTc/postgres

Under trololo user:

michael@michael:~$ psql -U trololo
Password for user trololo: 
psql (15.2 (Ubuntu 15.2-1.pgdg20.04+1))
Type "help" for help.

trololo=> create table trololo_entity (id int8 not null, trololo varchar(255), primary key (id));
ERROR:  permission denied for schema public
LINE 1: create table trololo_entity (id int8 not null, trololo varch...
                     ^
trololo=>

I want to find out the following:

  1. Why do I still get the error message if I have granted the privileges?
  2. I faced this problem after upgrading PostgreSQL from 12 to 15. If I'm not mistaken, for 12th there was no need to grant privileges on schema public. I just granted privileges on the table and that's all. Why should I grant permissions on this schema as well as on the table?

Upvotes: 1

Views: 1615

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

You changed the permissions in database postgres, but you are trying to create the table in database trololo (look at the psql prompt). Since you didn't grant the permissions in database trololo the attempt to create the table fails.

You are probably misled by psql's defaults: if you don't specify the database explicitly, for example with the -d option, psql will connect to the database with the same name as the database user.

Upvotes: 2

Related Questions