Postgres Open 2016 - Permissions in Postgres

I think urine for coming today you see I'm going to talk about permissions in Postgres also known as privileges I'll probably use both words interchangeably I'm gonna talk first just about how to look at and set permissions I'll talk about a couple things that aren't permissions that maybe you'd think they were then I'm gonna go over each of the objects in Postgres that has permissions and go over them you can get all this of course of the grant command and reading the documentation for that but the grant command documentation talks about each permission like it'll say here's what select I'll do and all the things that select will affect rather I'm going to talk about here's all the things you can do to a table or schema and so forth I'll talk a little bit about setting up a read-only user very briefly on how roles can inherit permissions from other roles and we'll talk about security definer permissions and then defaults so getting right into it in psql basically to look at permissions this is the back /dp command and here we have an example which is right out of the documentation for looking at permissions on a table called my table the interesting column here is the third one and that's where you can see we have access privileges and the first line there is Miriam equals there's some alphabet soup and then slash Miriam and the way to decode that is that shows the piece before the equals the Miriam is what user these permissions apply to so if you're connected to the database as the user or role Miriam these are the permissions that you're gonna have on this table and then the alphabet is each letter encodes one possible permission and the slash Miriam shows who granted those permissions to the user Miriam and in this case Miriam has granted the permissions to herself and that is essentially intrinsically what happens whenever an object in Postgres is created the user is considered to have itself all of the permissions available for the object and in this case the a RW d xtr all of the permissions on a table the next line the equals / r equals r / miriam that is because there's no username those are the permissions which have been granted to public so if there's no user name that means public and public is sort of i mean it's a user but it's a pseudo user that has special things it always exists certain rights that public has are implicitly available to every user on the system and then in the third line we can see where miriam has granted three permissions to a user named admin and that just shows again what it would look like where you're granting permissions to a role that isn't you as it were the last column column access privileges I'm not going to talk a whole lot here about column privileges but in general for tables and so forth all tables and things that are like tables views and foreign tables you can grant permissions not to the entire table but only to specific columns within the table so if you have a user that perhaps you want to be able to see say you have an employee's table you might want a user that logs in to maybe see their address but not their salary for instance and you could use column level grants of access privilege to achieve that as well and that fourth column shows the privileges that have been granted specifically to column one and I'm sure why that keeps cutting out on us all right here are just for immediate reference and this is in the documentation for the grant command as well well you can see how each possible privilege that exists within Postgres has a letter and you'll note say with delete and truncate that those are case-sensitive that shows then in when you look at the privileges you can see that for instance miriam has granted select the are read is the mnemonic permissions to public and insert read select and update permissions to admin not all objects have all privileges execute for instance is only applies to functions no other objects why is flaky isn't it there we go all right granting permissions in order to grant permissions you use surprisingly enough the grant command here I have an example of the syntax for granting permissions to a table and the syntax is pretty much grant and then just a comma separated list of whatever privileges you want to be allowing or you can also say all or all privileges privileges is an optional noise word and that just grants all of the available permissions in this case say on a table to someone so just here full access here you go in addition to specifying individual table name Postgres and I think this is nine five nine four Postgres that's relatively new for some of these that instead of saying on say table foo you can just say give me I want to grant this permission to all tables that exist within a schema and this can work out being really handy if you're needing you know you have a new user or you're doing some kind of blanket thing rather than find I mean I've easily seen you know things of hundreds or thousands of tables but they're usually collected up in a relatively small number of schema and it's a lot faster to write the commands out to just say okay I need this for everything in you know the accounts receivable schema or whatever you happen to have and you have to specify here you can see to role specification you have to say who you're giving the privileges to and the option there is either a role name and roles roles groups users really all mean the same thing in Postgres they're they're the same kind of thing what the difference between say a user in a group is really just how it's been set up typically creating a user you think of that as something that can log in and do stuff a group there are things is is the type of thing that you know typically well it's not gonna log in directly but rather the individual users will be members of the group but from within Postgres there's no actual distinction between users and groups in addition to specifying a particular role you can also say public and we saw that earlier where we had the permissions to public I forgot to start my timer and we saw that earlier additionally you can specify current user or session user and I'm not going to get into those in detail but Postgres keeps track of the notion of the current user and the session user and that could be it's really out of scope for this I don't think I've ever actually even seen anyone do that but it it's kind of grant permissions to myself sir go ahead you can do we'll get I'll get I'll cover the functions in a little bit that's correct you would you can also get largely effective that by altering what are called the default permissions that everything gets and you can do that by schema I'll cover that a little bit later effectively is you'll notice at the end here there's also something called with grant option and what that does is it allows the holder of that privilege that has say you've done grants select on table food to Bob with grant option and in that case the Bob user would be able to then further grant that permission on to other users so what that effectively does is says here you have this permission but if you think other people needed as well you can go ahead and just make that happen on your own rather than say having to go back to the DBA for that and if you were you know I can see you might have for instance I'm just going to grant say select permission on these tables to the manager of the developer group and then he can worry about which of his developers need further access with grant option can't be given to public it has to be given to an actual role rather than just everybody for those of you who are UNIX users public kind of amounts of a world or other rights revoking privileges it's pretty much just the reverse of the grant revoke whatever privileges you want or all from whomever you're you're pulling them from or public apparently according the syntax you can't do a revoke from current user I'm not sure why the discrepancy is there and maybe it's not but at least as far as the the syntax and documentation doesn't show it you can also revoke only the grant option but not the privilege itself so next time talk about a couple things that aren't privileges yeah although you would have to do that with two different grants because otherwise yeah creating an object is not a privilege of the object itself which kind of makes sense we can't grant privilege doesn't on an object until it's created so to create objects you do need to create permission but you need create permission on sort of the container for tables functions all of those things live inside of a schema and so what you need to create them tables and whatnot are actually create permission on the schema that they're gonna live in so they're just gonna live on the public schema only someone that has create permissions on the public schema could create objects in public and the right to create schemas themselves is controlled by the create permission on the database and I will go ahead and and cover that in a little bit dropping and altering and this is one perhaps more surprising there is no way to like grant drop or grant alter on something being able to drop or alter an object that is intrinsic to the owner of the object and it cannot be granted cannot be revoked so the owner of an object can always drop it or alter it and of course super users database super user none of this applies database super user can do anything and everything this is why you don't want to be logging in a super user even if you are the DBA generally similarly took in a UNIX system even if you're the admin you normally login as a non privilege non room count or I hope you do and that way you don't accidentally opes I did something horrible because I had the permissions and I made a mistake even though I didn't want to be in that situation all right now I'm going to move on I'm gonna talk about each of the objects in Postgres individually and what permissions apply to them first we're gonna look at tables and we just saw this as it's what I used for the example earlier one thing to note is that if you say all tables in a schema that also means views and foreign tables views and foreign tables in Postgres are really kind of just like funny sorts of tables but for many purposes they are equivalent to tables and this is one of those so views in foreign tables then we use this exact same syntax so you can do grant I think you can even say grants a select on table and then a view name and I think it actually works because Postgres really doesn't draw much of a distinction there so let's look at each of those individual privileges on a table there select privilege and this allows if you have a select privilege on the table as a whole this allows select on any single column or you can again only grant an I didn't show the syntax for that but you can grant select only on specific columns and in that case you could only do select and you'd have to specify the column names and you'd only be able to select from those copy two which is I know for those of you have used it what copy to is it says I want to copy a bunch of stuff from somewhere like maybe a CSV file or something into a table and copy the data from the CSV to the table no other way around I'm copying it from the table to the CSV and that since you're effectively reading those columns you also need to select privilege to do the copy to something to note say you want a user to be able to update a table um normally if you just do update table set you know salary equals you know 100 million you're not normally gonna want to do that on the whole table you're only gonna want to set one employees salary to that presumably your own but in order to update one row in a table you're gonna need a where clause so you're gonna have to do something like you know update employees set salary equal to 100 million where employee ID equals and in order to do that where employee ID that query is gonna have to pull the value of the employee ID out as though it were a select and so in order to use that employee ID column in the where Clause of an update or delete you need select privileged on it so if you're giving update and delete permissions on a table you're almost always gonna have to give at least some select privilege as well just so that they can select which rows they want to affect of those so whenever you're doing update and delete also think about select when you're granting those permissions let's talk about insert insert amazingly allows you to insert a new row into the table you can also grant insert only on specific columns those of you if you recall reviews the insert command presumably it's you do insert you know into table and you can specify column lists and then the values to assign to each of those columns all other columns in the table will just get the default values so if you allow insert only on specific columns and they couldn't specify values for the other columns let's say you had an invoice table you probably don't want the person inserting creating a new invoice to say specify the invoice number you want the invoice number to maybe be assigned by a sequence or something and so in that case you would or could control that by saying oh they have insert but not on that particular column an insert also allows copy from which is again when you're copying data from say an external CSV or something into the table again you're creating new rows that needs insert permission update is similar just allows you to update rows or again this can be restricted to specific columns to allow updates to their and as I noted under select any kind of update where you're not just updating blanket the whole table you'll need select privileges as well select for update select for share also require this privilege to some extent as well because again if you're doing select for update that tells it you're likely to be doing some updates delete delete allows you to delete rows from a table and again since you probably don't want to delete them all you're gonna select be needing to select permission just to fill out the where Clause truncate allows you to truncate the whole table truncate and delete are two separate permissions so even if user doesn't have truncate permission but they do have the delete privilege they could delete all the rows by just doing delete from table and that gives you an inefficient equivalent of truncate but they wouldn't need truncate permission to do it similarly if they have truncate permission they could just truncate the table and effectively delete all rows even without trunk even without delete privileges so those two are also kind of go hand at hand and you want to think about both whenever you're allowing either the references privilege on a table is what you need to create foreign key constraints so if you have one table that references another table there by setting up a foreign key you'll need to have references privilege on both tables at minimum and this is another one that can be assigned to columns at minimum you need each references privilege on the columns that refer to each other that are linked together and finally on tables there is trigger permission and what trigger permission allows it allows a user to create trigger functions laterz will see also need of course permission well they may need permission to create the function if they're not using an existing function for the trigger the references and trigger are typically set up at table create time so I mean in practice one rarely effects or adjusts these but but they are there and they are needed so next to talk about schemas schemas have two permissions create and usage an example the syntax up there all the syntax is for everything is largely the same obviously though there's no all schemas in schema because it doesn't make any sense I dunno though there's no all schemas in database so so you'll have to do each scheme individually great privilege on schemas allows you to create new things inside this scheme and I talked about that briefly earlier you also need to create privilege on the schema to rename something so if you have a table foo and you wanted to rename it to foo - you'll need create privilege on the schema and that makes sense because you're creating a new name in the schema so you need that privilege you also need to own the table in order to to do the alter usage on schemas is a privilege which I think of as again in the UNIX world equivalent to execute permission on a directory it essentially allows you to see and this is all in italics because this is a direct quote out of the docs it allows you to effectively use the name and the schema to find an object so you can do select star from say public dot foo to get things out of the food table in the public schema you need usage permission on that schema alter alter schema and in general anything to do with altering you have to own the object in order to use alter and so altering an ultra schema isn't really a privileges and other it's altering things it's not really a privilege it's just something that the owner can do and nobody else same with dropping talked about sequences sequences have three privileges and a sequences for those of you who may not know a sequence just assigns another numeric value in some sort of a numeric sequence just each time you call the next Val function and get the next number in the sequence and you can also curve there's a curve Val function that allows you to say what's the current value or the most recent value that the sequence has handed out and then there's something called set Val which allows you to just set the sequence to an arbitrary number wherever you may happen to want it to be you'll see set sequences a lot if you look at the output of post grows a PG dump scripts because in order to restore the sequence they created and then it calls set Val to put it into the place that's supposed to be so I thought I updated that ignore the first part there it's select select what select does it allows you to use the curve val function to find out where the sequence is at update allows the use of both the next Val and set val function because those both change the value of the sequence and usage allows you to use the sequence by finding out the current value and using the next value which winds up being three privilege or privileges that are overlap you can use Kerrville if you have either the select or the usage privilege and you can use next val if you have either the update or the usage privilege and set value must have the update privilege so there's two ways to get at Kerrville and next val in terms of granting a sequence or granting permissions which also means if you do need to control that you need to you can't just say oh this one isn't there so they can't do this you'll need to make sure you've got them both covered databases my favorite Bugaboo there are three privileges on databases create connect and temporary but manifestly what all privileges on databases does not mean is they can do anything you can't do grant all on database to user foo when you just want the user food to be able to log into the database and do it every once the three privileges on the databases actually don't do a whole lot I mean they're necessary but the create privilege on the database only thing it does is allows you the user to create new scheme ism the connect permission on the database just allows the user to connect this is checked at the connection startup and the restrictions in the PGH PA dot com must also be met in order for the user to log in I was happy to discover this and I was putting together my notes for this talk because in the past what I had always done if I wanted to lock a user out temporarily I edit the PGH be a.com to remove the access and then I'd do a PG reload to reload the file well turns out you don't actually need to do that you could just revoke inside the database connect and then you're not editing a file on the file system doing your reload and so forth and then remembering to turn it back obviously need to remember to regrab the connect but you can lock users out just by revoking their connect and then killing their sessions rather doing the edit the temporary permission only allows temporary tables to be created so the three of these are certainly all necessary to normal usage of the database but they're not a blanket catch-all to set up a user to kind of have full access I bring that up because I've seen two people do that at places I've been working in or attempt to and then being confused and coming to me asking why that didn't work types and domains for most purposes and Postgres types and domains are the same thing and so I'm going to talk about them together there's only one privilege which is use and what using a type or domain means it just means that you can't or can create objects that use the type so if you have a custom type in post or any type really in Postgres and you want to create a table that has a column of that type you have to have usage privilege on the type or domain you could still construct literal values of that type if you wanted to even without usage I mean you could do like select you know cast a value to that type and get that type back that doesn't prevent that type of use it just prevents you from creating things that if you tried to drop the type would break foreign data wrappers now I can talk a whole lot about these foreign data wrappers being entire talk or probably even half-day tutorial on those so but the use of privilege and for our data wrappers just allows you to create new servers that are with that using that format in a wrapper so you know if you have the Postgres foreign data wrapper to make a connection out to another Postgres thing - even just to create that server definition you're gonna have to have the use of privilege and similarly for foreign servers once you've created the foreign server in the foreign data wrapper the use of privilege there is what you need in order to create those foreign tables that reference that external data source user privilege on that also allows a user to muck about with his own user mappings again I'm gonna talk about that any great detail it's far too long functions functions have again only one privilege which is execute and that allows the use of the specified function but the other thing that executes privilege on a function does is it allows you to use the operators that may be implement of that function in Postgres every single operator is implemented by some function most of the ones you know if you do select 2 + 3 to get 5 that's built into Postgres it comes with but that + underneath is implemented by function that knows how to add two integers and so when you call 2 + 3 you're really calling that function with the two arguments and you need execute permission on the underlying function it would probably be daft to revoke that permission on something that that's that built-in from somebody but I mean in principle you could and you need that thing that privilege and like most of these you see for instance you can do you know grant execute on all functions in a schema languages but what we mean here is the procedural languages PL PG SQL pill Perl and so forth an order free user to be able to do a create function language SQL PL PG SQL PL perl in order to do that they need a usage permission on the language itself just to create the function in the first place now obviously in order to create a function you need to create on the schema and so forth to create the new object tablespaces for those who may not be familiar with tablespaces tablespaces are just a way of naming normally Postgres stores all of its actual underlying data files on the file system the operating system in one place in one directory what table spaces are as a way to give a name to some other place on the disks and in order to put things in those other places on the disk tables indexes temporary files and so forth you need create permission on that table space itself great permission on a tablespace also allows you to set up a database that just says hey by default I want everything to live over in that part of the disk instead of the default part that postgres normally uses so that allows both if you revoke this privilege that does not move things from the tablespace that they're already in if they've been put there nor does it make the money inaccessible it's just merely the ability to put them there in the first place if you do want to move them that's an alter alter table set tablespace and so forth and like any other alter you would have to own the table to do that you would also need to create on whatever tablespace you were moving them to because you're putting something there large objects large objects are sort of their own little thing in Postgres but where you just have a big chunk of data and you don't want to store it within a column as a byte a but treat it as a separate thing I still have it within the database you can use a large object and there are two permissions related to large objects which are select and update and much like a table select as you to select allows you to read from a large object and update allows you to either write to or truncate I assume the docs weren't clear on this but I'm pretty sure that in order to drop a large object entirely you would have to be the owner much like dropping a table or any other object that is all that I have about and those are all of the privileges on all of the tables or rather all of the different objects in Postgres and approximately what they mean the next thing I'd like to talk about are setting up and creating read-only users these permissions a lot of times you know when I'm out somewhere I frequently get the requests too we need to let the developers on to the production database after I get done panicking and hopefully I can talk them into let's just give them read-only access to the production database so at the very least they can only bring it to its knees by issuing bad queries not actually wipe out production data so we say a read-only user what do we mean there well basically we don't mind so much if they create little rights temporary tables that kind of stuff what we really don't want them to do is modify any of the data or any of the the DDL that you know we don't want them to like drop a table create an add a column to a table create a new index or knowing that kinda stuff functions though functions of course can effectively do anything I mean they're called with a select but inside the function I mean they can make whatever modifications the code calls for but turns out if they're not security definer functions that's okay because whatever the function tries to do will execute run with only the permissions of whatever user has called the function so it's okay to let them run functions as long as they're not security definer functions and I'll talk about those in a minute so we also noted that non-privileged is one of the non privileges the owner of a table can always drop it alter it and so forth so in order to be read-only that read-only user can't own anything in the database there must not be the owner of any object because the owner can largely do what they want so to create a read-only user we need to give them select permission on every table and that is made easier by doing the grant select on all tables in schema and you'll have to issue that for each schema let me select on all views again those are largely like tables I'd also need to select on all foreign tables which I don't have listed here they'll need to select on all large objects that may exist so that they can get at the contents of those they will need to select on all the sequences but as we noted make sure it's not update or just a usage because usage will give them next file as well let me execute on all the functions they'll need usage on all the schemas just so that they can find things within the schema and finally they'll only connect on database of course because well they have to be able to get in to do anything at all and that's pretty much it the hardest part of doing that really is just to make sure you get a good schema list and there's ways you can query the system catalogs to get those and automate your scripts but but the biggest thing is just make sure they have select on everything execute on functions usage on schema connect on the database go ahead that's I'm sorry say again mm-hmm not unless they are security to finer functions because a function normally just runs as with whatever privileges of whatever users running it regardless of what the function says I'll talk about security to find our functions in a minute and I'll cover that in a little bit more detail I want to talk about inheritance as I noted earlier post-crisis doesn't draw a whole lot of distinction between a user and a group and so here we have some syntax and you'll see we have grant role name - role name so we can grant Alice - admins for instance and that and both admins and Alice or would-be roles so you do like create role Alice create role admins and then later you can do grant Alice - admins and what that means is that when alice is logged in Alice will in addition to having the permissions that have been granted to Alice we'll also have all of the permissions that are granted to admins yes yes thank you yes so grant admins - Alice and that makes a lot more sense means that Alice will run with in addition to anything that say you did a grant select - Alice on table foo so then grant yeah let's can do select foo from table if you do grant update insert delete on foo to admin then if you've later done grant admin to Alice Alice will be able to do inserts updates deletes and anything else the advantage of doing this the primary advantage of doing this is you can centralize your management a little bit so if you have five or six DBAs you don't need to grant each of them whatever permissions they need instant well DBA it probably needs everything but instead you can just grant all those permissions to admin and then later if you have Alice and Bob as both of your DBA s you can just grant admins to Alice grant hadrons to Bob later Alice resigns then you just revoke admin from Alice and you're done because they will no longer have those permissions or no longer remember as we call it of that role with admin option on this is similar to the with grant option earlier if I grant admin to Alice with admin option then Alice can later go grant admin to Bob and it basically allows Alice to Ari grant those permit or we grant that role membership to somebody else again this might be useful to do say for your developer lead so earlier I had setting up a read-only role and I would recommend actually not granting all of that process to whatever users need but instead do create role read-only and then do all of those grants to that read-only role then later you have you know developer Joe needs access and you just go grant read-only to Joe and you don't need to worry about Joe specifically because he'll just pick up all of those roles by inheriting them from the read-only role what will what all the rules have been granted Joe show me play Joe there is and I don't recall what that easy way is Stevie remember do you - so that as well as something I want to note that these sort of Cascade so you could if you had read only and read write you could it's kind of a contrived example but you could grant only the right permissions to the read/write role and then grant read only to read write and then grant read right to admins and then grant admins to Alice and Alice would wind up getting all the stuff - admins which might even be nothing all the stuff from read right and all of the stuff from read only because it would inherit right on down the chain and users can be members of more than one role you could also grant read write or read only and read rights to admins you could do it that way as well and have a forking tree instead of a cascading yeah it would probably be bad yeah would it prevent it okay will prevent it you can prevent that though if you Alice would have do this deliberately but if you have like multiple role memberships you can do a set role and then the table will be owned by that so Alice could do set role admins and then do the issue the table create and then it would be owned alternatively Alice could create the table and the new alter table owner two admins so it could either set the role and get it by default or they could expel us with that membership set could do the issue that alter table largely yes yes you can yes yeah when you create when you when you create the roll you can create that with the no inherit and that basically will make it so whatever permissions they have don't automatically roll down the line so that yes then you would have to explicitly do a set roll and again this you might even want for instance if you're granting admin to to over to roll to have the admin not inherit so when they log in they don't automatically just have everything rather they have to explicitly do a set roll to you know kind of let the database know hey I'm gonna want to start to be modifying some stuff now let me talk about security definer functions which will get I believe to your point or a question earlier about executing functions and whether that's dangerous or not you'll see down the middle of the huge syntax for creating a function where it says security definer or security invoker just above cost and just below called on null input so you create a function a function can either be a security invoker which is the default or a security definer and a function that is a security invoker function runs with the user that calls it so if say our developer Joe who only has read-only access calls the you know update and blow away all the old invoices function you know for maintenance it'll just fail because it'll get through to where it tries to run its updates or deletes but those will still run as the user Joe because the user Joe is the one who called that function so those will fail in other words a function doesn't get you any more privileges than you already had except if the function is called or rather created as a security definer function and a security definer function or security to finer functions those execute with whatever privileges of the user that owns it so if our DBA Alice creates a function that again let's say that we have a table with our developers and has their phone number contact information in there and along with a bunch of other stuff and we want the developers for instance maybe to update their own phone contact info or maybe on call info Alice could create a function that took say a new phone number and updated that table create that as a security definer function and then that function would then run as Alice and be allowed to do the updates and that's safe because Alice wrote it she knows the only thing that this does is this one update that we want to allow the developers to do but that doesn't you know since it's a controlled set of code and obviously security to find a function you have to audit and be very very careful because it's not gonna run in this case as Joe it's gonna run as Alice with all of her permissions inherited down from read rights admins and you know whatever you whatever else you happen to have so security definer functions are essentially a way to encapsulate or at least their main purpose is to encapsulate a very small piece of privilege that would be difficult to do with the privileges themselves because it might need to oh I need to select from these two things you know I'm one could come up with you know obviously the examples are going to depend on your business case but but the security definer functions allow you to create those functions that run separately the default is the security invoker the default is no extra privileges each or rather just uses the privilege of who executes the function so if by default if we create a function we haven't said anything it's security invoker and then when Bob runs the function it'll run with Bob's privileges when Alice runs it it'll run with Alice's privileges when Joe runs it it uses Joe's privileges security definer is the way to get around that so that it always runs with a defined set of privileges which are the table owner if you go through the docs and we'll probably get this fixed soon it says that they run is whatever user created the function it's actually whatever user owns the function Postgres doesn't even keep track of who created it and like other alters the owner of the function could be changed by super user with alter function if so you can create it and set it later okay I think the owner could change another owner if they were a member of that they'd have to be a member of both the owning role and the target role and I think that's all they would need so yes I might even have set that earlier but so in this case for instance you know like with our example if we had granted granted admin to Alice Alice could create a function and then change the owner to admin if want to again provided there's also create permission there's a set of default permissions that everything is the general rule on when soon you create a new object it has a certain amount of permissions to begin with and by default those are the owner has every permission public has none nobody else has any with the following exceptions public automatically or by default gets connect permission and temporary permission on a database so other users can log in and connect on when you create a new function public gets executed create new function by default public can execute it as we've sort of been discussing that's okay because they don't otherwise have any other permissions that you haven't given them and functions don't get you extra privileges as such when you create a language or a new language by default has usage privilege so if you have a language you install PL pearl PL Python whatever by default users will be able to create functions of the language but of course in order to create the function they need create permission on the schema so again by default that isn't really getting them a whole lot and types and domains allow their usage which again is just the ability to say create a table that uses a type I'm sorry no not by default there's no well the owner of the table space has the that create permission but public does not so in order to allow if you create a new table space if you want a user to be able to put stuff there you'll have to explicitly grant that permission either to the user or to public we talked briefly about changing default permissions because we have the defaults here which are all the owner none of the public but you can change that for either a role and possibly only for objects in a particular schema so for instance the main use I think of this is you may wish to do something like ultra deep privileges for role read-only in schema public grants select on tables so that way if you later to create a new table read-only will already will get the Select without having to read up the commands in schema grants like oh sure okay okay yep oh yeah for okay so for whomever then creating then grant to public can only alter the default privileges for tables views foreign table sequences functions types and domains it also when you alter the defaults doesn't change permissions and anything that's already been created it's only future stuff you can look at them if they've ever been modified with the backslash DDP which is similar to the backslash 2dp command that's what all I have I might have time for one or two more questions although we've taken some during yeah good right now you would have to basically it's sort of the merging of also if you've granted select for instance on the table and granted select on some columns they're gonna be able to read them all but I think you could revoke from just a column you might have to no no I don't think that yeah I think you would you would have to not grant select you'd effectively have to grant you don't grant select on the whole table grant select only on the columns that you want them to use because otherwise you'll get you'll get the the union of all effectively whenever there's sets of permissions the user has the union of all the permissions that have been granted that that's when I write a script to troll through the system catalogs any other questions all right well oh sorry there is one apparent all right everyone thank you for coming I hope you all got some

Channel: Postgres Open

Views: 6,946

Rating: 4.8709679 out of 5

Keywords:

Id: 8NQYOX6lvgo

Length: 52min 39sec (3159 seconds)

Published: Wed Oct 12 2016