aispot.blogg.se

If you want to restrict them to a schema named after them, do the following after the CREATE USER: CREATE SCHEMA jsmith AUTHORIZATION jsmith This way your students will be able to create a schema for themselves (the name can be anything), and as they own that schema, they can also create objects in it. You can put the latter two statements into a stored function/procedure (which will include some dynamic SQL), or a script in your second favourite language. We will use it as the privilege collector for the actual students, which you will have to create like this: CREATE USER jsmith IN ROLE student - will be able to log in, REVOKE CREATE ON DATABASE class FROM public - disallows schema creation, see belowĪt this point, student is a role that cannot log in. every role (user, group) is a member of REVOKE CREATE ON SCHEMA public FROM public - the second public is a pseudorole

You can create an arbitrarily complex role hierarchy, but for our purposes a simple one will be enough: CREATE ROLE student Postgres is quite good at doing such things, this is one reason I like it. Of course, I'm available to answer questions for clarification - being concise while still offering comprehensive details is a challenge. I haven't thought this through thoroughly, but appreciate any feedback, concerns, and answers offered. The tricky bit is I would like any authenticated user to be part of that role, which I don't think you can add public to a role, so I don't know how that would work. I was also curious if I could create a roll and use the role to administer permissions. I'm not sure those are the proper permissions. can permissions be revoked from public? being able to create on database makes it seem like they can create in public GRANT CREATE ON DATABASE class TO PUBLIC - can it be CREATE SCHEMA ? Thought process: REVOKE ALL ON DATABASE class FROM PUBLIC to apply these settings to all all users or more specifically, not have to target specific user accounts when assigning permissions.students to not be able to create tables/views/objects in public.each student (any authenticated user) to be able to create their own schema and use that as the scope where they can create tables and views.The public space is where I place all my default course material (tables, views, etc), most of which can be accessed by anyone interested. Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth).