Creating role with full access to schema, and assign it to user

user_5172268 0
Viewed 374

I try to create role that will be used by developers account and will be granting access to all tables under one schema. Everything looks correct, but user still don't have access that it need. Can someone point me what i'm doing wrong ?

  1. Create new role DEVELOPER_R that will contain all right

CREATE ROLE DEVELOPER_R

  1. Add role full access privileges to database

GRANT ALL PRIVILEGES ON DATABASE postgres to DEVELOPER_R;

  1. Create new schema TEST_SCHEMA

CREATE SCHEMA TEST_SCHEMA;

  1. Grant all access to TEST_SCHEMA for DEVELOPER_R role

GRANT ALL ON SCHEMA TEST_SCHEMA TO DEVELOPER_R;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA TEST_SCHEMA TO DEVELOPER_R;

  1. Create user with role

CREATE USER testuser PASSWORD 'password' IN GROUP DEVELOPER_R LOGIN;

  1. Select from TEST_TABLE

SELECT * FROM TEST_SCHEMA.TEST_TABLE

As result I would expect to have all rows form table but only what I got is permission denied to table : enter image description here

When I look into TEST_SCHEMA properties I can see that DEVELOPER_R have UC access there: enter image description here

And testuser is in DEVELOPER_R group.

enter image description here

Can someone point me what i missing ?

roles privileges postgresql
1 Answers

Did you create the table after running the GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA TEST_SCHEMA TO DEVELOPER_R; command ? Because it would explain why you got the permission denied error message. You need to grant the privileges again on any new table you create in the test_schema like in this example :

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

postgres=# create database test_database;
CREATE DATABASE

postgres=# create role developer_r;
CREATE ROLE

postgres=# grant all privileges on database test_database to developer_r;
GRANT

postgres=# \c test_database
You are now connected to database "test_database" as user "postgres".

test_database=# create schema test_schema;
CREATE SCHEMA

test_database=# grant all on schema test_schema to developer_r;
GRANT

test_database=# grant all privileges on all tables in schema test_schema to developer_r;
GRANT

test_database=# create user test_user password 'password' in group developer_r login;
CREATE ROLE

test_database=# create table test_schema.test_table (col1 int);
CREATE TABLE

test_database=# insert into test_schema.test_table values (1);
INSERT 0 1

test_database=# select col1 from test_schema.test_table;
 col1
------
    1
(1 row)

Now validate what was created and granted so far :

test_database=# \dg+ (test_user|developer_r)
                      List of roles
  Role name  |  Attributes  |   Member of   | Description
-------------+--------------+---------------+-------------
 developer_r | Cannot login | {}            |
 test_user   |              | {developer_r} |


test_database=# \dn+ test_schema
                        List of schemas
    Name     |  Owner   |    Access privileges    | Description
-------------+----------+-------------------------+-------------
 test_schema | postgres | postgres=UC/postgres   +|
             |          | developer_r=UC/postgres |
(1 row)

test_database=# \dt+ test_schema.*
                                          List of relations
   Schema    |    Name    | Type  |  Owner   | Persistence | Access method |    Size    | Description
-------------+------------+-------+----------+-------------+---------------+------------+-------------
 test_schema | test_table | table | postgres | permanent   | heap          | 8192 bytes |
(1 row)

test_database=# \dp+ test_schema.test_table
                                  Access privileges
   Schema    |    Name    | Type  | Access privileges | Column privileges | Policies
-------------+------------+-------+-------------------+-------------------+----------
 test_schema | test_table | table |                   |                   |
(1 row)

Note that no privilege was given to test_schema.test_table yet because it was create after issuing the first GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA TEST_SCHEMA TO DEVELOPER_R; :

test_database=> \conninfo
You are connected to database "test_database" as user "test_user" on host "127.0.0.1" at port "5432".

test_database=> select col1 from test_schema.test_table;
ERROR:  permission denied for table test_table

Now run the GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA TEST_SCHEMA TO DEVELOPER_R; again to grant the desired privileges to the developer_r role :

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

test_database=# grant all privileges on all tables in schema test_schema to developer_r;
GRANT

test_database=# \dp+ test_schema.test_table
                                       Access privileges
   Schema    |    Name    | Type  |      Access privileges       | Column privileges | Policies
-------------+------------+-------+------------------------------+-------------------+----------
 test_schema | test_table | table | postgres=arwdDxt/postgres   +|                   |
             |            |       | developer_r=arwdDxt/postgres |                   |
(1 row)

And as we can see, the developer_r role has all privileges (arwdDxt) and now the test_user should have what it needs to at least SELECT from the test_schema.test_table table :

test_database=> \conninfo
You are connected to database "test_database" as user "test_user" on host "127.0.0.1" at port "5432".

test_database=> select col1 from test_schema.test_table;
 col1
------
    1
(1 row)
user_75020200
Related