Views Creation Permissions without table Creation Permissions in POSTGRESQL

Question

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`

please show us yoour attempts, by the way your text is hard to read — nbk
– nbk, Commented Feb 29, 2024 at 19:02
please always edit your question with new information, code in comments is also bad to read — nbk
– nbk, Commented Feb 29, 2024 at 19:25
Thank you for your suggestions, I will keep them in mind for next time. — Vishal
– Vishal, Commented Feb 29, 2024 at 19:32
CREATE: Create objects (including tables) within this schema as you give him such rights, as far as i know you can't not select what he can create — nbk
– nbk, Commented Feb 29, 2024 at 19:34

Laurenz Albe · Accepted Answer · 2024-03-01 09:41:52Z

0

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.

edited Mar 1, 2024 at 9:41

answered Mar 1, 2024 at 7:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vishal Over a year ago

Do you have any resources on how to create event triggers? I'm not familiar with the process of creating events.

Laurenz Albe Over a year ago

I have added links to the documentation. But perhaps it would be easier to change your requirements or to solve the underlying problem in a different fashion.

Collectives™ on Stack Overflow

Views Creation Permissions without table Creation Permissions in POSTGRESQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related