Question 235606 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres – granting all permissions, but still denied on tables

I have a DB in Postgres that i administrate with the admin user and i want to create a schema and tables for an application user.

I am creating the schema and the app user with the admin user and then i grant:

GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA my_schema to my_user;

Then i create my tables inside my_schema, but when i try to access them with my_user i get permission denied:

ERROR permission denied for table my_table

Is there a way to solve this?
Thank you

Tags:

2

Answers


  1. 0

    If you want to have mentioned privileges for your ‘my_user’. You need to have USAGE privilege.

    What you need to do in order to successfully retrieve data from tables in schema ‘my_schema’. If I understand your question correctly. You should follow this:

    1. create schema my_schema; — create tables if not already exists
    2. create user my_user; — set password as per your security recommendation
    3. grant usage on schema my_schema to my_user;
    4. GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA my_schema to my_user;
    Login or Signup to reply.
  2. 0

    GRANT … ON ALL TABLES does not include future tables. You were granting permissions on all tables currently in the schema, then you were creating new tables. Those new tables won’t have any permissions granted to my_user.

    You can either

    • move the GRANT … ON ALL TABLES statement after the creation of the tables on which you want to grant the privileges, or

    • change the default privileges that will be applied to new objects (i.e. tables):

      ALTER DEFAULT PRIVILEGES FOR ROLE admin_user IN SCHEMA my_schema GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO my_user;

      This will automatically GRANT the specified privileges to my_user on any new table that admin_user will create.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search