Restrict create table privilege to newly created users in RDS PostgreSQL 9.6

Question

I am facing an issue while creating a readonly users in RDS PostgreSQL 9.6. I am executing the following SQL commands:

---- ###### CREATE ROLE ################
CREATE ROLE readonlyrole_dev;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readonlyrole_dev;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyrole_dev;

-- set the privileges that will be applied to objects created in the future.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonlyrole_dev;



CREATE USER readonly_dev WITH PASSWORD 'welcome1';
GRANT readonlyrole_dev TO readonly_dev;

When I login with the readonly_dev user, it has privilege to create the new tables by default but I don't want to do that. I want to keep readonly_dev only a read only user.

Note: To revoke the access from the user I am executing

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

which revokes create objects privilege to all old users as well. I only want to revoke create privilege from newly created user.

How can I do that?

Laurenz Albe · Accepted Answer · 2018-12-28T09:14:20.863

1

You cannot do that, and it is not necessary either.

Just deny the user the CREATE permission on all schemas. You should use user groups for that - put all users who should have the privilege to create tables in a group that has the required privilege on the schema and revoke CREATE from PUBLIC.

If you insist that you must have this, try creating an event trigger that throws an exception whenever a certain user tries to create a table.

edited Dec 28 '18 at 09:14

answered Dec 28 '18 at 09:08

Laurenz Albe

209,280
17
206
263

Thanks for your reply. can you please specify how can I move all existing users from PUBLIC to another group? How will it impact on the existing users privileges if new objects in the database are created? – Hamid Mahmood Dec 28 '18 at 11:28
You'll have to run a lot of `GRANT` statements. New objects in the database always have default privileges unless you change that with `ALTER DEFAULT PRIVILEGES`. – Laurenz Albe Dec 31 '18 at 08:05
what about the event trigger. Can I develop such a trigger that will be triggered only when a specific user is logged in? – Hamid Mahmood Dec 31 '18 at 09:06
Thanks. Event trigger is working fine for me. CREATE OR REPLACE FUNCTION public.abort_any_command() RETURNS event_trigger LANGUAGE plpgsql AS $function$ BEGIN if (select current_user) = 'readonly_dev' then RAISE EXCEPTION 'command % is disabled', tg_tag; end if; END; $function$ CREATE EVENT TRIGGER abort_ddl ON ddl_command_start EXECUTE PROCEDURE abort_any_command(); – Hamid Mahmood Dec 31 '18 at 11:16

Restrict create table privilege to newly created users in RDS PostgreSQL 9.6

1 Answers1