[SOLVED] Views Creation Permissions without table Creation Permissions in POSTGRESQL

Views Creation Permissions without table Creation Permissions in POSTGRESQL

I have a use case where I want to create a user which should have access on select for public schema and Views Creation On public schema but not table creation User can create a new view but not table

`--by admin user 
CREATE ROLE view_creator;
GRANT CREATE ON SCHEMA public TO view_creator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO view_creator;
CREATE USER test WITH PASSWORD 'test123';

GRANT view_creator TO test;
-- by user test 
create view test_2 as select * from public.users_test -- This should 
work
CREATE TABLE public.users (
id serial4 NOT NULL,
"name" varchar(50) NULL,
age int4 NULL,
CONSTRAINT users_pkey PRIMARY KEY (id)
);  --- This should not work`

Solution

There is no way to do that with permissions. If you have the CREATE privilege on a schema, you can create any object there.

Your only option is to write an event trigger that throws an error whenever the user creates something other than a view. See here for the CREATE EVENT TRIGGER syntax and here for information about writing event triggers in PL/pgSQL.