Then we connect this function to the event using an event trigger capturing all CREATE TABLE events: CREATE EVENT TRIGGER trg_create_set_ownerĮXECUTE PROCEDURE trg_create_set_owner() This function will loop over any objects create and issue an OWNER TO command for them, changing the owner from the default one to our hardcoded role common_role. We'll start by creating the function that will execute: CREATE OR REPLACE FUNCTION trg_create_set_owner()įOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOPĮXECUTE format('ALTER TABLE %s OWNER TO common_role', obj.object_identity) So how do we fix this? Let's use an EVENT TRIGGER. However, this isn't particularly user-friendly for the end users, and it's also quite easy to forget, which can then lead to issues down the road. Of course we can work around this by requiring that user1 issue a SET ROLE common_role before creating the table: You are now connected to database "postgres" as user "user1". This is because the owner of the table in this case is user1. You are now connected to database "postgres" as user "user2". The only issue with this is if user1 creates a table, user2 won't be able to drop it: You are now connected to database "postgres" as user "user1". With these commands, all users that are members of common_role (that is, user1, user2 and user3) will be able to access and modify all data in the tables once they are created. GRANT common_role TO user1, user2, user3 ĪLTER DEFAULT PRIVILEGES IN SCHEMA test GRANT ALL ON TABLES TO common_role In trying to set up this scenario, we can get past the initial hurdle of permissions fairly quickly by issuing: CREATE ROLE common_role Fortunately, this is something where we can once again use an event trigger to make the system do what we need. And this is a scenario that is not very well handled by the built-in role support, due to the ownership handling. That is, it's the main login role, or another role if the user has run the SET ROLE command before creating the table.Ī fairly common scenario that is not well handled here is when a number of end-users are expected to cooperate on the tables in a schema all the way, including being able to create and drop them. However, there isn't much we can do about the owner, which will get set to the role that is currently active. We can alter the default privileges using the very useful ALTER DEFAULT PRIVILEGES command (a PostgreSQL extension to the standard). When you create a table in PostgreSQL, it gets assigned default permissions and a default owner. ![]() ![]() Tags: postgresql, security, event trigger, trigger.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |