Hi there, so hopefully everybody's in the right talk here. This talk's gonna be about role-level security as implemented in and committed to Postgres for inclusion in 9.5. It's not a guarantee, of course, but having been committed and now has been through a number of improvements and whatnot, feeling pretty comfortable about it, and very much looking forward to when it rules out. 9.5 is scheduled to be coming out kind of in the fall, the hope is to get back on the same kind of track that we used to be on where we had fall releases. 9.4 slipped a little bit for JSON-B and a few other things, but looking forward to moving on. So as mentioned, I'm the Chief Technology Officer at Crunchy Data Solutions. I'm also one of the committers. I'm a major contributor way back when I implemented the role system. So who here is familiar with the role system? Okay, good, and almost everybody. It's important, it's an important piece of Postgres. So hopefully everybody's familiar with it to some extent. I also did column-level privileges, so I've been kind of on this security thing for a while. Column-level privileges were added in 8.4. They were added principally because that other open-source database that has these weird different storage engines already had it, and I was like, well, that's not cool. Whenever I go to talk to people about, well, you don't want to run that, they're like, well, it's got column-level privileges. I'm like, oh, all right, let's go fix that, so we did. I've also made contributions to PLPGSQL, to PostGIS. I've got code in the Linux kernel. I have code in Sendmail. I've got code in a lot of different pieces of the open-source community. I've been working in open-source for, I don't know, 15 years or something like that. So lots of code out there. Kind of scary and frightening when you think about it. So the first thing I wanted to hit on a little bit was what is ROS when it comes to the Postgres ROS, right? What is the Postgres kind of consideration for it, right? Different products have different things that they call ROS or label-based security or whatnot. When it comes to Postgres, it's simply a way to provide an individual view of the data in the system, right? I say for each user, it's not necessarily that it's gonna be different for every different user. It could be, right? The whole point here is that we wanna filter out records based on some criteria about the user or the query or potentially anything, right? It's just this general filter records feature, right? It's a way of saying, I wanna add a where clause to the queries that are being run against this table. I mean, at the very base level, that's what it is. We also wanna be able to limit what records get added back into a table. So I think that's an important piece of it. I know I think about it a lot when I'm talking about it from a perspective of what is ROS. I think about the filtering aspect of it, but it's filtering in both directions, right? It's both filtering data that can be selected out by the individual user or the individual connection or what have you, and it's also about what is that connection allowed to add back into the system, right? What are they allowed to insert or update or potentially delete in the table? One of the big things that I'm really glad that I was pushed to work on for ROS is the ability to have them be specified for a particular role or a particular user, right? Originally, my thinking was that all policies would be generic, right? They would apply to all users in the system, but where that becomes an issue is that the policies end up being, the way that they get combined means that it becomes inefficient for us to be able to just kind of throw all policies at a given query, or at least it can be inefficient. It depends on exactly what the policies are doing, but I'll talk a little bit more about that in a minute. One of the other things that's important was how we require that policies are going to be applied. It's a security associated feature, and therefore we have to make it very clear when it's going to be applied, when it's not going to be applied, and I'll talk to that a bit more as we move forward, but the point is that it's got to always be there. So from the standpoint of right from the get-go, we have this default-deny approach when it comes to role-level security. As soon as you enable role-level security, there's a de facto policy that's implemented, right? Specifically to ensure that there's always something happening, right? There's always a policy being enforced there, and so I think that's a really key aspect of how role-level security has been implemented in Postgres. So that's kind of a real high level of ROS, right? I want to take a few minutes to kind of walk people through the way that that can be done even in 9.4, right? So there's a few different ways of implementing this filtering concept, right? One of the ways is using what are called security barrier views, okay? ROS feature is built on top of the same code paths that security barrier views are built on top of, right? The issues that I find with using security barrier views, because somebody may ask, why use ROS? Why not use security barrier views? The issue I find with it is that means you have to build a view, right? And that view has got its own permission set, right? Permissions that are independent of the table underneath. And now you've suddenly got two different objects that you're managing all of those grant-based permissions on top of, right? And that gets complex and hairy and can lead to security problems if you do it wrong. So if you end up granting the same rights to the view and the same as you do to the table, then the user, whoever's getting those rights, doesn't actually have to go through the view, right? If you say grant select on the view and grant select on the table, well, if they query the table directly, they no longer are going, the filtering's no longer being applied that you wanna be using with, which is the point of having this view. So another thing is that you have to make sure you remember the security barrier option. It's not a huge deal, but people coming from other databases may not realize it. One of the things that's really important about the way Postgres is able to optimize queries that are given to it by the user is our ability to do selective pushdown of particular qualifications. So say, for example, I want all of the rows greater than 1,000, right? I can push that qualification very far down and potentially use an index scan to make it a very efficient way of getting at the data. However, with the way Postgres is extremely extensible, right, there's a challenge with that when it comes to security of the system. And what I mean by that is that someone can create a function. Anybody can create a function in Postgres, and they can make that function, they can put that function in the where clause of the query that they're writing. So they have a query that's going against a view, and then they have this where clause with a conditional says my function, and it takes some column argument, or maybe it takes no arguments, it doesn't really matter. The user's also able to specify what is the cost of that function, and it can make it very inexpensive, right? It can make it so that that cost is so inexpensive. And what Postgres will naturally do is wanna push that all the way down, right? We'll push that as close to the data as we can, and we might push it past what are the qualifications in the view definition. Where that becomes a problem is that the function is running as the user who called it, but now it's getting fed records from the table prior to those records being checked through the conditional that was in the where clause. So the security barrier option is specifically implemented to address all of that, right? It essentially says that only those conditionals that we consider leak-proof, only those operators that we consider or operator families, but we consider leak-proof are allowed to be pushed through a security barrier view. The same is true for O-level security. Again, they're built on the same code paths and the database, and so when you mark a function as leak-proof, that means that that function is then able to be, or operator is able to be pushed down past other qualifications, and therefore you have to be really careful and make sure that it's actually not gonna end up leaking any of your data. So to create, and this is just a quick example of how one might create such a table in a view. I didn't include all the grant commands and whatnot, but this is essentially what you would do. You create your table and then you create your view with security barrier, and then you'd have this conditional where my user equal current user, right? When the user executes a query against my view, this conditional gets added because security barrier is on, none of the conditionals that the user specifies on their half of the query are pushed down unless they're using operators that are marked as leak-proof. Coming back in to the data, right? Having the data come back from the outside into the database, you can do that with the security barrier view using what's called a with check option. So the with check option basically says that if you insert to a view, so one of the things to remember right is that Postgres now supports what are called simple views as automatically updateable. So what that means is that if your view doesn't have any joins, if it doesn't have any aggregate, if it doesn't have all these other special bells and whistles that SQL supports, then we can say, okay, that view is so simple that we know how to take and transform a particular record or tuple that's being inserted against that view and transform it to operate against the table underneath. Right? What the, one of the issues with that is that someone could potentially insert records that are not visible through the view. Right? If they're not visible through the view, maybe you didn't want that user to be able to actually do that, right? Actually insert that data. So with the example that we have here, so this is more to the last one where my user equal current user, I've added this check option. Okay, what the check option does is it says, okay, if my user equal current user isn't accurate, right, if that fails on an insert, that conditional check fails on an insert, we will throw an error saying sorry, we're not gonna accept that. It's a little bit more complex than that based on the way the SQL standard implement, requires us to implement it, which it actually says after all other things are done, all other check constraints, index constraints, uniqueness, anything like that, after all that other stuff is done, then we check and see is that tuple visible? Right? That includes any other triggers or anything else that get fired, right? Basically it's the very last thing we do is say, okay, I've done all this work. Is this tuple visible to this user who's running this insert? And that's why it's called a with check. So we're adding that check onto the very end of the overall process. Okay, again, real level security is built on top of this with check option for security barrier views as well. And so it shares some of the same attributes. Right now as committed to nine five, it shares the with check timing as well. We're looking at, and I think it's pretty much agreed by everybody that it would be better for roll level security to not operate in quite the same way as the with check option. And the SQL standard doesn't specify anything about roll level security, and therefore we're free to do what changes we want there. And so the plan at the moment in probably gonna be committed here in the next couple of weeks is to move that check up in front of all the other check constraints and other things that are firing against that particular tuple for the view. The other thing about cascaded versus local that I'll mention, if you have views that are built on top of other views, you can say whether the with check option is applicable to just that particular view or whether that tuple has to be visible through all of the views that may be underneath. And to make sure that it comes back through all of them. That's what the distinction there between cascaded versus local is. That isn't really applicable for roll level security because roll level security operates at a table level. Those are implemented on relations and therefore there aren't things underneath them. All right, another way of achieving this filtering is this multi schema approach that I have seen in a lot of places. And it's neat as a capability, I did it myself once upon a time but it's got some serious drawbacks, right? One of the big issues that you run into with this idea is where you create a schema and that for every single users and all the tables and all the schemas are the exact same the first day. And then only the rows that that user gets visible supposed to see get inserted into their tables, right? The problem is that it's difficult to maintain consistency across all those sets of tables. You have to maintain this huge nightmare set of objects all throughout the system and it doesn't end up scaling very well, right? It becomes a real big problem for applications that are then using this approach to deal with, well, we're doing this rolling change over from this table definition to this table definition and the application has to deal with both or maybe three different versions of what the table definitions are inside of the system. Otherwise, you have to do everything wholesale all at once and that could lead to downtime or outages or inconvenience for the users, which is pretty painful. Another thing is that you may have the right dynamic SQL which it's not hard but it's just adding more complexity, it's not fun, right? Partitioning then also has to be matched up with the requirement for what's visible, right? So if you're gonna build a partitioning system which makes sense as tables get larger, well, you've already essentially partitioned your data, right? You've partitioned it by user with this approach and you only have the option to sub partition it beyond that, you can't partition it in a different way besides partitioning by user and that can be a problem as well. So let's talk about the actual, you know, row-level security that we have coming in 9.5. So the first thing is that every table that is going to be performing row-level security must be set to enable row-level security. It's a very simple alter table enable row-level security and now row-level security is enabled for that table, right? And therefore all access to that table from that point forward has to go through a policy of some kind. A couple of caveats on that. Caveat number one is that the super user and the owner of the table, both of whom can change and enable and disable this row-level security on the table, do not by default have their rows filtered. They still see all the data inside the table. So when you're running and using row-level security, you need to actually consider that while you're doing your testing, right? However, there is a default deny policy for all other users. If you're not the owner or a member of a role that is an ownership role on the table and you're not a super user, then if there are no policies on the table, you will not see any records in the table, right? Because of the default deny policy being applied. This also means that you can, so the other option on this is that you can disable ROS. But because it's enabling and disabling of row-level security on the table is independent from creating the actual policies on the table, that means that you could disable row-level security and still have policies on the table. Those policies will not be applied, right? Once the row-level security has been disabled for the table, policies can still exist, but they won't be used for anything. And then if you go and re-enable it, then suddenly any policies that exist on the table will be used, right? So one of the things that gets kind of interesting is how do you test this, right? If you're a table owner and you've just created a table and you're like, okay, so I've created this table, I've set this policy up, but I don't know whether it's actually doing anything or not. So the way we address that is that there's an option called setRowSecurity that was also added, right? It's a standard guck that you can have. And by default, it's on, right? Normal mode, policies are applied as appropriate. They're only applied if you are not the super user and you're not an owner of the table. Off means don't ever apply real-level security. This is useful for things like backups, right? In some instances, you may want to say, okay, I need to be able to backup the database, and that means I need to be able to see all the records in the data. So that's where the off option comes from. And in fact, off is actually set by default by PGDump, right? Because we're kind of thinking, if you're doing a PGDump, you probably want everything. There's an option to have row security set to on instead with PGDump. So if you actually want policies applied when you do your PGDump, you can get that functionality as well. Note that you may not always have privileges to disable real-level security. So that's what the error bit here is, right? If you set row security to off and you don't actually have the right to access that table without going through a policy because you're not a super user, you're not the table owner, and you don't have what's called the bypass RLS option that I'll get to in a minute, then it's gonna throw an error back at you instead. It's gonna say, sorry, I'm gonna error out instead of actually either just returning zero records or doing something else, because the expectation is that there's something in error in that case. There's a mistake that was made. The last thing is the force option, right? And this is where you can use it for testing. You can say set row security equals force, and then that row-level security gets applied even for the table owner and for super users. Now of course, remember, the table owner and the super user, you just go disable RLS if they wanted. So this is not intended to be used for a security measure. It's just intended to be used for testing, right? If you wanted to do testing, or maybe you wanna do a PG dump with all the data in your database as visible to that particular user doing the PG dump, you could set it to force even if you're running PG dump as a super user, for example. So that may be useful. So here's the create policy kind of command syntax, and I realize it's pretty complicated. I'm gonna hit a bunch of really kind of simple things and build it up as we go. Policy through table specific. They're applied for particular commands, right? So you can have a different policy for insert than you do for update or a different policy for delete. All right? They can also be for specific roles or for all roles. We use the notion of public to mean all roles, right? So if it's set to public, then the expectation, or the implementation is we apply it for everybody accessing the table. Again, excluding the table one of the super user, and unless raw security is at the force. I mentioned the default to deny policy already. The way we deal with the records going in to the database versus the records coming out of the database is with these two different clauses, right? There's a using clause, which is for rows that are visible, and then there's the with check clause for rows getting added. And that with check clause applies for both inserts and updates, right? It doesn't actually apply for deletes. Reason for that is because deleting is never adding records, right? It's not adding it or even changing a record. So if you have a policy on a table that says, okay, this is a delete policy, I use the using clause to say which rows are available to you to delete. I think that's one of the key things about one of the really good reasons why we have different policies based on command is that you can then allow them to view records that they are not allowed to delete out of the table, right? That one thing that can be very handy in certain environments, maybe there's a subset that they're allowed to delete, just not all of them. If no with check clause is specified, and in some instances it doesn't make sense to specify a with check. However, if one is not specified and it does make sense to have one for that particular type of policy, what we'll do is we'll simply use the using policy, right? So the policy that you said for using, which as I showed earlier on is current, my user equal current user, that will get set for both using and with check. So here's an example of that policy creation, and this is a very, very simple example, and the point here is to illustrate that, you know, we have a lot of defaults in here to keep it simple, right? It doesn't have to be super complex for users to work with policies if they wanna do relatively simple things and they can use a relatively simple command. So this particular policy applies for all types of commands, all roles, and it uses the same conditional set, my current user, for both any records that are being reported out from the table as well as any records that are going into the table. So I'm gonna keep that top thing there as it's the exact same, right? As it was on the prior slide, but what I'm gonna do here is I'm gonna start adding in the different options, right? These are all gonna mean exactly the same thing. I just wanna show that what the defaults end up being. So for this part, I'm gonna show that using and with check end up with the same conditional, right? Of course, you can specify them independently if you want, just like I'm doing here, but you could also have a different check. So moving on, we're now gonna add kind of that next level which is for all, right? This is the sub-command or the command type, right? Where we can say this is for select or for insert or for update. Now we're gonna add in to public. So it's for command type to the roles, right? Either public or a specific role or a set of roles, right? All of these mean the exact same thing. So if you wanted to create a policy that was specific to select queries, then this is what you would do, right? Create policy and then you say for select. Now what this does if there are no other policies on the table means that this user is only able to select records out where my user equal current user, right? They're not allowed to insert any records or update any records, right? Or delete any records because there are no policies for those different command types, therefore the default deny policy applies. So here's an example for insert. So for insert, only with check makes sense and so only with check is allowed, right? Because there's no using clause here because we're not looking, we're not worried about the visibility of records in the table when we're doing an insert, we're only worried about the contents of the tuple being added and therefore it only accepts a with check. This is update, update's one of the fun ones where it's actually the only case where we care about both sides in a single command type, right? So this is which rows are visible to you for update and then what are the results of that update, what are the contents of the results of that update allowed to go back in? So in this particular case, it's kind of interesting because we're saying for update only, you can only see rows that are yours for update. However, you can change that to be somebody else, right? And when you do that, that tuple will no longer be visible to you for update. You've essentially given it away, you've given it to another user in this particular scenario. Now you may want that, you may not want that, but the neat thing about this is that it's a capability that Postgres supports with this approach to real-level security. I talked a little bit about delete again, so I forgot to update that. So that should say for delete, sorry. So delete only, it only has the using policy makes sense for it, right, the using clause. Because again, delete is all about visibility of data. All right, altering the policy is pretty straightforward, while I was changing the policy definitions. So you can change pieces of the policy if you want. You don't have to change and redefine the whole thing from the get-go when you're altering a policy. You can say, okay, I wanna just change what roles are applied for this particular policy. I don't have to restate what the using or where the check clauses are when I'm doing that. I can just alter policy name on the table and then two and then list a set of roles. One thing you can't change is the command, right? The reason that you can't change the command is that then we're not, or you can't change the command by itself, I should say. And the reason for that is, you have to drop it and recreate it instead, is because the with check and the using clauses only apply sometimes. So it wouldn't really make sense to change a policy that was for select into a policy for insert, right? Because the select only the using clause applies and the insert only the with check clause applies and we don't know if that's the same thing or not. And so that's why we don't allow changing of the command for the policy. You can just drop it and recreate it. Policies do have names. So all policies have names and you can change the name. The policy name is specific to the table though, right? So you could have the same name of a policy across the entire system. And as long as you are only ever using that policy once per table, you won't run into any naming conflicts or anything like that, right? Policy names are specific to the table that they're applied to. So here's a couple of very simple examples. We can change the using clause for a policy. We can change the with check clause for a particular policy. Note that we don't have to specify the command here because again, you don't get to change the command and therefore we don't need to have that specified in the ultra policy statement. It would simply be redundant. One of the things you can do is you can change multiple things at once if you wanted to. You can change a set of roles and change the using and change the with check clause all in one statement if you want. Very similar to how altered table handles that. Of course, policies can also be dropped. But as noted and I've kind of hit on a couple of times, if you drop a policy and there ends up being no policies being applied for a particular user, then the default deny policy will kick in and the user will not see any records. As long as role level security is enabled on the relation, that default deny policy will always apply. So I wanted to show or talk a little bit about the distinction between the policy versus the grant system, right? So the policy system's all about rows, right? Whereas the grant system isn't really tied to rows so much, right? It's tied to maybe columns, definitely tables, right? And for a particular column, it's all or nothing for the values in that column, right? You either have select on that column or you don't have select on that column, right? Or maybe you have insert on the column, but you don't have delete or sorry not delete but update, right? Delete is one of those interesting ones that doesn't apply for column level privileges because delete only happens at a complete row level, right? So what ends up happening is a lot of times you use views to limit data or you use stored procedures, things like that to deal with those more complex cases and of course those are still available. But what the policy system does is it allows you to have that row level control without having to resort to using stored procedures or more complex methods to be able to get that result. So I have a number of examples here that I can talk through. I did want to kind of take a pause for a minute since I've been talking for half an hour straight. Anybody have any questions about kind of anything that we've gone over or things you're thinking about at the moment? All right, I can, okay, yeah. That's okay, you can't. The question was what happens if you name a row public? Yes, my recollection is that it is actually globally disallowed. Yeah, I believe it's globally disallowed. I'd have to go look. If you quote it, you put double quotes around it as an identifier, I think it's still disallowed. But if it's not, then you would have to use it that way whenever you reference it. But I'm pretty sure it's actually just globally disallowed. I went through the grammar not that long ago and my recollection is that it is just, you can't do that. There's a few very specific things like that. We try to avoid it, but there are a few cases where it makes sense because it has a special meaning. Any other questions before I kind of start going through the examples? Yeah, so the question was if you created a, if you had a roll R and you did a grant select or a grant update on a given table to R, and then you've also created a policy for R, right? Now, I wasn't quite sure if your question was what if you create a policy for a command that they don't have access to? So if you say grant, so here's a good example, right? So if you say grant delete on the table, however, in the create policy statement, you either don't create a policy for delete or you create a delete policy, then while they have that right on the table, the only records they can delete is either nothing because there's no policy and therefore there are no records visible to the delete to be deleted or they are only able to delete the records that the delete policy allows, right? Now, if you create a delete policy and you don't grant delete on the table, then it doesn't matter, right? Because the grant system will essentially be checked first, right? So the overall structure of what you're trying to do is checked through the grant system initially and it's only after it's gone through that that the visibility question comes up about the records or the with check option comes up about the tuples that are being inserted, right? If you don't have insert rights on the table, we're gonna blast you back with a permission denied error before we even get into thinking about policy questions. Okay, yes. So the interesting thing about this, the question was why isn't all of this stuff done in the application instead, right? And there are a lot of people out there that write web applications that run as super user and handle all the permissioning and everything at the application level and all the controls. And that's fine and dandy when you have one application touching the database. Soon as you have more than one application touching the database. Now, this second application also has to implement all of that and do it in the same way, right? In order to have consistency, right? By pushing all of that complexity and permissioning closer into the database, you basically avoid that problem completely, right? Because all of it's being handled at the database level instead. So there's obvious trade-offs there, right? Some of these things, Postgres does not scale well to millions of roles in the system. Absolutely true, right? There's no way to partition the role tables, for example, which you would tend to need to do if you wanted to create millions of them. However, with the role-level security approach, you don't always have to base it off of role, right? You could just have some custom guck that you set that is like the application user, right? And then have that guck be referenced in the policy definition against some column that has the user name or the user ID or what have you, right? So you don't have to use roles with policies to get role-level security coming out for your application users, right? And again, having all of that pushed down into the database makes it a lot simpler for the application and a lot less risky to have multiple applications accessing the same database. Does that answer the question? Okay, happy to discuss further, of course. So I just wanted to show a couple of examples here. So here's what you get when you do a backslash D in PSQL, PSQL is my favorite thing ever. Like, I love PSQL, so it's a fantastic interface. So this is what things look like when you do a backslash D. This is the policy that you'll get back from that. And then I always like to show off this table command. I don't know if many people are familiar with it, but it's essentially a select star from table, right? But you don't have to do all of that select star business. You just say table and then the table name and it just returns all the records. It's very cool. I like it. And it also accepts order by and limit too if you wanted to use those with table, which is actually an extension over and above the SQL standard where it's just table. But table is actually an SQL standard command. Kind of neat. Did I move forward? There we go. So this, you know, moving on from that example, what we do here, if we do a set role, you know, we did table before as a super user and we do a set role to a particular role that has a policy applied. And then we do table again. Together it's just a select star and now we see only the records that are applicable to that user. Again, if no with check clause is used, then we're gonna use the using clause. And here I'm trying to insert data into this table for some other user that I am not, right? And because of that, I'm gonna get this with check option failure. The wording in this error message that I think is actually gonna change here pretty quickly. Again, this isn't in a released version. So we don't have any concerns about changing those kinds of minutia at this point in the release cycle. Once nine fives released, we won't be changing that for the nine fives cycle at least, the nine fives set of releases, nine five dot zero dot one dot two. So if you're interested in playing with this or looking at it, you'll have to go to the Postgres master or git head or whatever you wanna call it, repo to get all of these commands. So here's just another example of where, you know, if I change that policy now to say with check is true, meaning I can do anything, now I can insert data for another user even though I won't be able to see it after it's been inserted. This can get even more interesting when you consider what I'll call external policy systems, right? Because essentially these expressions in the using and with check clauses can be anything that can go into a where clause. That includes functions, right? An interesting function might be a call out to SE Linux to say I wanna check whether the SE Linux policy system which is being managed by the Linux kernel allows this particular piece of data to be visible to the user, right? Now a lot of times what you'll do there is you'll pass in some kind of tag column, right? If you have a, they call them context, right? In SE Linux. So what you'll have is a context column that'll define what the context of that particular row is or was when it was inserted and using something like this check SE Linux call we could pass in a column called the context column and then have that checked back out, right? By SE Linux, whenever we're trying to select data out and what SE Linux will do or could do in any case is say take that piece of data, that context on the row, take the context that it knows from its connection, right, cause SE Linux actually on a per connection basis maintains a context and say, okay, do these two match up, right? Or is this user allowed to see that context, right? And based off of that, it'll either return records or not return records. The point is that it's very, very expressive, right? You can do a lot of different things with this capability by using functions. So another quick example is a fun one I like to do which is the Unix password file, right? So these are all the standard columns that you'll get inside of a password file and I'm gonna have three different roles. I like the Alice Bob example from Kerberos, it's been around forever and it's good. So we have an administrator role, note that that's not a super user, which is important. And then we have two normal roles, all right? Now we're gonna say, okay, we're gonna populate the data, the table with a few bits of data, right? We're gonna populate it with three records, one record for each one of the users that we're talking about and then we're gonna make sure we enable role-level security on it, okay? Nothing too special here, it's just a Unix password file. Now what we're gonna do is create a few policies that go on this, okay? We're gonna say an admin can see all the rows, right? And it can also add any rows into the table. So it's all just true, right? That create policy command could actually be simplified to just say using true. This particular case I like to say both because it kind of makes it more explicit in my mind when I'm crafting it. In Postgres it doesn't matter. And I say, okay, normal users can see all the rows, right? But I don't want them to be able to update all of the rows. This is the exact same way as the Unix, regular Unix password file. You can cat at cpasswd and see everything that's in there because there's nothing particularly private inside of that file. But you can't modify things except your own. So I'm gonna create a policy that says, okay, for update, I say using, right? So I'm defining what records are visible to the update command that the user's running against this table. And that's where I say current user equal uname, right? Uname being the field inside of the table. And then I'm gonna say with check, okay? So now I'm gonna say when that record comes back from that update statement, I'm gonna check a few things. First, I'm gonna make sure they didn't try to change that username to somebody else's username. Because that could be a problem, right? Because then we could end up with duplicates and other things and that'd be bad. And I'm further gonna say, okay, if they do change their shell, they have to change it to one of these values. Now note that I'm specifying this list of values inside of an in clause. It doesn't have to do that. You could have it in a separate table that has a query against it as a sub select. That'll work just fine. Just like it doesn't wear clauses. Further, I'm only applying this policy for users, right? And so I'm saying, okay, users can only change it to this set of shells, but like an administrator could change it to any shell that they want, right? They could change it to dev zero or dev null, for example, if they wanted to prevent that user from being able to log in. That's something people do sometimes. So the point here is that it's not the same as a check constraint, right? A check constraint, yes, you can limit a check constraint and require that all columns or all entries in the table completely are in that set, but that would then preclude the administrator from being able to make a modification to this table to set it to dev null. It was kind of an interesting play out here with that. I don't know if we're ever gonna kind of get a more granular notion of check constraints that are at the table level that are applied based on roles or something like that. Might be an interesting idea, but at least if you wanna do this, you can do this with policies today, and that may be sufficient, yes. So policies can't conflict with each other, right? Pretty simply. The reason for that is that policies are always adding information, okay? So policy says what records are visible. So if there's two different policies on the table and policy A says that records one to 100 are visible and policy B says records 101 to 200 are visible, if both of those policies are applicable to your connection or your role, you'll be able to see records from one to 200, right? Because you're granting access to rows, right? And grants, in general, in the Postgres system, as well as with policies, are always about adding more privileges. They're not about taking it away. So policies, I mentioned that earlier, but whenever policies come into play, they are always orred together. So all the conditionals get orred in this big or tree, and that's where the performance problem can happen, is that big or trees can end up being not performant. So if you have lots of policies that apply for a particular user, it can end up being, may preclude us from being able to use indexes and do other things in a performant way, with maybe a reason why you wanna create policies on a per-role basis. So in this particular case, I'm just showing how we can grant privileges on this table to do, like, you know, the admin can basically do anything, select, interrupt, delete. However, users can only select, and there are only a certain set of fields that they're allowed to update. Right, so this is just showing how we can combine policies and the grant system to work together. So to show an example here, I can set the role to admin, and I can, boom, I see all records, right? Next, I'm gonna set the role to Alice. Alice is gonna say, well, I wanna see everything, and you get permission to night error, because Alice doesn't have select rights on all of the columns. Okay, fine, so Alice selects out a subset of columns. Great, fine, she gets to see those, no problem. All right, now we're gonna try to update and change the username. Oh, can't do that, right? Doesn't have update privileges on the username column. Right, so I had the with check clause in there as well to make sure that the record coming back in is set, but as long as Alice is not able to change that column, it ends up being a little bit redundant. Personally, I think it's a good thing to have anyway, because security is like an onion, right? You wanna have lots of layers. Now Alice is allowed to update the past file and set the real name to something else. Now this is interesting, right? Update one, we know there's more than one table or more than one row, but there's no conditional on this particular query. Well, that's because the policy is being applied, right? So the using policy of the update policy or the using expression of the update policy says that Alice can only see her record to update. Therefore, even if she wanted to add some kind of condition or where your name equals admin on that, all that's gonna do is further filter the records. And what that means is that you'll get this update zero and say, well, there weren't any records visible to be updated, so it's not relevant, right? We then have update past WD with set shell and we say, well, that doesn't pass the set of shells that we said you were allowed to update to and then you don't have permission to delete anything from the password file. You don't have to delete privileges at a grant level for the file at all, so you can't delete any records. And further, if you wanted to do an insert of a specific column, you're not allowed to insert new records into that table either again, that's a grant level privilege. However, Alice can change her own password using update past WD. I mentioned this before, but I wanna hit it on again. Leakproof functions is where a lot of this comes into play when it comes to performance, right? Only leakproof functions are allowed to be pushed down and that means that they are allowed to be executed prior to other qualifications either in the policy or on the, you know, or that the view, if there's a view over top of this table as well, or that the user's specified, all right? What that takes them to allow you to do is make things faster because it allows us to use indexes. Note that only super users can mark functions as leakproof and generally I would recommend that you not. It's very dangerous to mark things as leakproof because of their ability to have that be pushed down security barrier views and pushed down through world-level security policies because we took a lot of time to think about whether something is really leakproof or not. Is it ever gonna throw an error that comes back and says what the value in that column was, right? That's not leakproof, right? Even if it's in an error mode, it doesn't matter because that call can get pushed down to a level that you're not supposed to see any data had and therefore if it throws an error, it can be throwing an error about a record that you're not supposed to be seeing. That's a very important thing to think about and generally I would really recommend don't mess with it. A couple of quick things. So here's a quick explain, right? Just a very simple case, explain table. You can see how the filter gets added here. Further, when there's an index available, we can use that index for that. And then that tends to work out just fine for that particular query. So you can see that because we rewrote the query prior to actually running it, we're able to, prior to planning it or optimizing it, we're able to do this index usage. And then I just have this last slide which just is a more complex case where we have two different, two different conditionals that are being applied and you can see that in this particular case, we're able to do the index scan using the MyUserEqualCurrentUserIndexCondition and then we have the filter applied over top with the regular expression which is that little delay symbol. So I may have time for like maybe one question but I know we have lunch coming up so I wouldn't push your lock. You can always find me at lunch. Anybody have a last question or two? Fantastic, thank you very much.


gh, but that's my initial response. Okay, I'll send you more questions afterwards if I feel like it. You know how to reach me. All right. Oh, we do still have one member of the public. Any public comment questions? All right, thank you all. Great. Well, thank you very much. So we will move on to item H, Vermont Community Foundation Spark Connecting Community for Skate Park Improvements Grant. Yeah, that is a mouthful of a title for a grant program. But this is a $3,000 grant that we had support from our commission member Bruce Wilson and some of the community members who folks have seen come to our commission meetings in recent months to talk about the skate park. So this is a $3,000 grant to help start some repair work on the skate park moving towards the goals that were included in the parks and open space master plan. And there's no match requirement. And the funds here would be again, focused on just really safety repairs and some surface repairs. Thanks. Any questions from council? Any questions from the public? All right, do I hear a motion to approve the grant for skate park improvements? So moved. Second. Motion by Mike, second by Hal. All those in favor, please say aye. Aye. Aye. Motion carries, thank you. Ray, next we have Vermont After School for All Recreation Programs grant. Yeah, so this was a sort of surprising second way of a funding in this grant program for this year. As you saw in the cover sheet, pretty quick turnaround, but we were able to pull together an application. I wanna give Jenny Hill, who I think mercifully departed before the impact fee conversation, but she put a ton of work into getting that application together and it would be for funding elementary school age camp programs, STEM programs for February, April and then two weeks of June. That's great. Any questions from council? Do I hear a motion to approve? So moved. Second. Motion by Mike, second by Amy. All those in favor, please say aye. Aye. Aye. Motion carries, thank you. And finally the childcare workforce stabilization program for Thrive grants. So we'll end on a really good note here. Kate Anderson again continues to keep a near to the street and got this funding secured. And essentially this represents a one time bonus payment to all childcare workers whose organization is applied. So it would be a $938 bonus payment one time that would come through payroll, but it's for anybody that worked at Thrive between September and December. And it's sort of an acknowledgement from the state of the critical nature of childcare workers during the pandemic in particular. So it feels really good to be able to provide that benefit for our staff who have been working really hard. Awesome. Any questions? I can just add to this. I think this is a really real kudos to Kate for going after this. And as we see school closing for a few days, those there, the Thrive staff are hourly staff so they don't get paid when they don't work. So we're hoping that this at least mitigates some of that impact for them on a human level, obviously. Do I hear a motion to approve the grant for Thrive? So I'll move. I second. Motion by Mike, second by Jim. All those in favor, please say aye. Motion carries. Thank you everyone and thank your team for looking out for this stuff. This brings us to the end. Do I hear a motion to adjourn? So I'll move. I second. Motion by Mike, second by Hal. All those in favor, please say aye. Aye. Aye. Thank you all for your time this evening. Happy New Year. Happy holidays, everybody. Happy holidays.


Good afternoon folks.  Hopefully about static mock-ups about the absurdity of using images to design websites. So my name is Mark Conroy. I'm a lead front-end developer with a company called AnarTech where the largest Drupal agency in Ireland, and at the moment we have the accolade of WebAgency of the Year. At Drupal Conalash we won WebAgency of the Year and the best website in Ireland and most beautiful website in Ireland and lots of lots of nice awards. So we think we're kind of good at what we do. I'm also the chairperson of Drupal Ireland. So if you came to Drupal Conalash year and had a good time you might come along to Drupal camp next month. I'm an admin on the Drupal twig.slack.com. The Drupal twig Slack is the kind of the front end Drupal, the front end Drupal Slack. If you're looking for front-end knowledge, it's a great great great resource. It's a, you know, you ask a question and usually within five minutes somebody will at least tease out the question a bit with you if you can't get an answer. One problem we do have with being Slack is it's the free plan and we lose lots of knowledge as well. So if you do join it and you ask a question and you get an answer, please write a blog post so we can kind of archive the knowledge somewhere. I'm also working on the out of the box initiative. So I'm part of the team implementing the team for the new team for Drupal core as part of out of the box and I'm Mark Conroy on Twitter. So today here's what we're going to look at. A quick kind of overview of you know the problem, what goes wrong when we use static mock-ups and why should we not use Photoshop and why should we not use InVision and why should we not use Sketch and things like that? Then we look at what is the solution. So, you know, I'm happy to rant and complain a lot about Photoshop, but we might have some sort of a solution or some sort of thoughts about how we can solve the problem. And after that then we will look kind of a bit more detail at Atomic Design and specifically at Pattern Lab and how Pattern Lab integrates with Drupal. So this is me. This is the day we won a nice contract and I'm fairly happy. My dog is, dogs are dogs I guess, but I do the developer thing then. We win the contract, we're going to build a new website and I become a code monkey and I start writing my code. So this is a me writing the code and being productive and thinking that yeah, we're doing it right. But the contracts often come with the kind of contracts we work on on fairly big contracts in two stages. Sort of could be a tender for some company to design a website and a tender for some company to build a website. So often we win the contract to build a website. We don't have a large design team. So we often don't win the design aspect of it. So this happens. We meet the client and they tell us they've got designers and it's a great design agency and often it is and they've won lots of awards and they tell us yeah, we'll deliver the PSDs, the Photoshop documents.  And I get frustrated, but eventually after some thinking about that I realized hang on, hang on, hang on, we can fix this. So I had a Eureka moment. So we're going to try to solve it. Here's a quick little exercise. I'm going to guess this sounds familiar to certainly the front-end developers here. The back-enders might not realize the pain we go through. So this sounds familiar, let me guess. You get some designs. Is this showing up here? And the client signs off designs and says yep, this is exactly what we want. The spacing is right. The line height is right. Imagery is correct. Everything is on brand. And then you build a website based on these designs. And the website that you build, it looks quite like the designs. It's not exactly like the designs, but it's quite like them. I mean from the developer point of view, it's exactly like the designs except the designer used five words in every single blog post title. And yours doesn't look exactly like the designs because you have one blog post that has six words in the title and one that has four. And the client can't understand why different words look different. And these are real problems. We can laugh, but these things happen. So that's not the client's fault. That this isn't exactly as it should be. And it's not your fault that it's not exactly as it should be. You've built what you were asked to build in the manner that you're asked to build it. But you know, it's I suppose in terms of diversity and inclusion, I shouldn't say it's someone's fault. It's something's fault. I'm gonna say it's someone's fault. So someone's someone has made a cock up here and I think that is the person who used Photoshop. So here's the problem we have. So websites in the real world use real content. An example of this becoming an actual real issue for a client of ours recently, we're working with, the designers used five words in the title of every single news view. Lorm, Ibsen, Dollar, Sit, Amit. Okay, every news post, every press release, every person's name was these five words. And I said what happens if there's more than five words in a news post? We will look at their present content. See this news post here. This has 26 words in the title. This one here has 17. So, you know, they're long kind of government things where you have to mention all the ministers and the counselors and that in the titles. So the designer said we're gonna train the client how to write shorter titles. And I said okay, here's the client's name. Vienna, city and county council. Okay, the client's name alone has five words. Now, the data website goes live. You're gonna tell me, you're gonna train them through right titles that are less than five words when the first news post they're going to write is Vienna, city and county council launches new website. So we've got a problem when we start working with real content, we've got a problem and Photoshop can't really solve that unless the designers are willing to copy and paste in all the different titles and lens and things like that. The next problem we've got then is that these are these are static images. They can't be tested on proper devices. They can't be like you send them mobile design to your client. You can be sure to open up that in a desktop and have a look. Oh, this is going to look great on the phone on my desktop. You send them desktop designs and they're traveling at the time. So they open it up on an iPad and say oh, yeah, my desktop design is going to look great on an iPad. On top of that then, if they design them for 1200 pixels wide, they're only seeing just one representation. They're seeing exactly what the website will look like when it's 1200 pixels wide, not when you're on a 22 inch monitor, not when you're on a 50 inch monitor, not when you're on your watch. You know, not when you're opening your fridge and whatever incident of things kind of are connected. So it's basically, you know, to make it short, what you're doing in effect is you're showing your client that this is the most effective way that I can make sure that you will never have the website that you think you're having. I actually don't know who Steven Hay is. I found a file with some notes for this presentation and I don't know where I got that quote from. So thank you, Steven, if you're here or wherever you got it from. So here's two examples of Photoshop layers. This was from a blog post we wrote called Design Wars. If you see the one on the bottom left, it's called Dock One. It's a Microsoft Outputs.DOC for Dock files with lowercase letters. So somebody actually had to go to that and edit that and decided I would prefer to call this Dock One with capital letters. We've got subheading two and we've got layer 29. Now how I'm supposed to find the correct things I want to find in a timely manner, I don't know. So we end up going over time and we end up going over budget. This one here is from one of the best design agencies in Ireland. We do a lot of work with them and they are absolutely fantastic and we really like working with them. But their layers are called or their groups are called group and inside group we've got group. And below that we have group and below that we have group and below that we have group. You can see where this is going. So this doesn't lead to a nice workflow. Now we've had some meetings with them and we're talking about how do we make handover better and how can we make things more enjoyable. And that's great. So you can massage your designers somewhat. So basically when we're talking Photoshop, we're saying the function of Photoshop is in the name, photo. It's an image manipulation tool. You know it's not a design tool. It's for adding lens flare to buttons when you click them that you can't implement when you're a front-end developer. It's for cropping out the sunshine or cropping out electrical poles on a nice view. It's not for designing websites. It just happens to be used for designing websites. So we need to move away from that. One very commenting that happens with me is I'm working on a Mac and my Mac system renders fonts in a certain way. Photoshop renders fonts in a certain way. Chrome renders fonts in a certain way. Safari renders fonts in a certain way. They all look fairly similar but there are slight differences. Photoshop renders fonts in, I don't know, some other mental way that I can't work out. So again, you get the designs and they look great because you're looking at a photograph. Then you implement it and your client says, hang on, this is supposed to be open sands, size 72, font weight, bold, line height, 34 pixels. And I opened my inspector and I showed him this is exactly what is in the designs and this is exactly what I've implemented. I'm not a very good developer so I have to make sure I implement what I'm given so you can't complain about it. And this is what happens. So the fonts get rendered differently. Again, you're not handing over to the client what they think they're getting. Your pixel-perfect design can't be pixel-perfect for these kind of reasons. So here's three reasons that we don't use Photoshop or we don't like Photoshop. Number one is that it's not responsive. So what you're going to end up getting is lots of designs for desktop. Again, with the client we had the recent, as mentioned earlier on, we got 46 desktop designs. 52% of their traffic comes on mobile. We got six mobile designs. We got zero tablet designs. So as a front-end developer, your job then is to, okay, I can implement a mobile. I can implement a desktop version. And I don't know what happens on tablet. Do we just stretch the images more and more and more? And that doesn't break layout, but it doesn't look great. So you get the exact same on tablet as you get on mobile. Or do we do something else? Do we design ourselves and say, okay, well, if there's three items in a grid on desktop and one on mobile, how about we put in two on tablet? And that's great, but there's three, because they want to show nine results in their view. So then when we put in two on a row on tablets, we get to end up with one extra one at the bottom. So we're not getting tablet designs. They're designed then for a specific browser size. As I mentioned a minute ago, if they're designed at 1200 pixels, you'll only see them at 1200 pixels. And often not see the big bleed of what happens to the hero image when the monitor is larger. So designers are kind of designing these great experiences. They're about user experience and user interaction and being creative, I guess. The user is just one functionality. You know, the design of a drop-down, and it looks great, and it's got subtle hover effects. But most of our work is with government. We've got to make sure we pass WCAG 2.0. So the government signs off a design, but the drop-downs aren't accessible anymore. So we've got those innovations. So you don't see them popping up in Photoshop. They're not a problem. Number two, I think, is that designs are zoomed out. So you send your design to a client, and it's maybe, we'll say, 4000 pixels high. What the client ends up doing is zooming out, because they want to get a look at the whole overall web page. Now, the point of this, I don't know, because this is one of our own designs in Photoshop. And they zoom out completely. Their font is rendering at about one pixel. They can't read it. No one's going to actually look at a website like this. But for whatever reason, they want to zoom out. And the same with the mobile designs. You can see this tiny, tiny little mobile screen, and it's going way down the page. So what you should be able to see here is only the header and that hero image, because that's about what you're going to be able to see on a desktop or on a laptop. And my favorite reason for not using something like Photoshop is that it's too easy to create crazy ideas. So this is three examples. The client is anonymized because we like winning contracts. But these are three actual requests we got from clients, not necessarily when I was working with Anarchek, when I was working self-employed. So the first one was that we don't want to maintain an about us page. Instead, we want that when you click on the about us page, it will open up Wikipedia page in a color box. But Wikipedia has to be styled like our website, our color scheme, because we can't have Wikipedia breaking our brand guidelines. So if that was Drupal, when you click on the Drupal about us page, this is the Wikipedia Drupal article, and we played around with it yesterday. That's very easy to do in Photoshop. You can do that no problem, and you can hand it to a designer, to the developers. But no matter how much you do that in Photoshop, I still cannot change the CSS of Wikipedia on the fly. Another reason, another example, a client came to me and said, there's a website in Ireland called Dundeele. It's basically like eBay. It's an Irish version of that. And he said, I don't like the look of my website. He came to me first time. He has an e-commerce website. And I said, no, you don't. I built your website. There's no e-commerce. Yesterday, I have an e-commerce website, and he showed me basically eBay, where he sells his products. That's kind of clever. Okay, you've got an e-commerce website. Yes, Mark, I do, but I don't like the look of my products on it. All of the things on Dundeele, their branding is red and brown, but my color scheme is blue and yellow. So can you change the look of Dundeele for me? So if we're selling Drupals again, yeah, no problem at all. Give me Photoshop, and we can do that. But since we're, we can't, again, same as Wikipedia, we can't edit the HTML to see as that's the JavaScript. And no amount of explaining to this client could get them to understand that I cannot change Dundeele. I cannot change eBay just because your designer gave you a Photoshop document. We get the lens flare issue. When you click on this button, it gets lens flare. And that's fine if you want to put in, say, a transparent PNG, you can say that, okay, on clicker, on hover, on focus, that there's an after pseudo element. But we did have a client who says we have this hero image, and here's what the hero image has to look like.  And they have to have alpha transparency layer on top. It must have a gradient on top. It must have a desaturation filter. It then must recolorize the image with a kind of a blue hue to fit our brand guidelines. And we said, why can't you just upload the image? That's in the Photoshop document. And they thought, we might want to change it sometime. So we said, okay, well, for the first instance, until you do want to change it, can we upload that? No, no, we've got to get this done properly. We've got to get this done with CSS. So we went back to the designers and said, do you have any idea how this might be done in CSS or how we might be able to implement this? This is looking a bit tricky. And they said, no, that's not possible with CSS. Okay. Where do we go from here? So it got to the stage that the client had no choice but to upload the image. So again, if you want to add some lens there when you click on Try Drupal, this is what you might get. And again, this is simple in Photoshop, but this is very difficult in code. So what's better? Well, SketchApp is better. I'm not going to go through SketchApp because it's a fantastic tool. It's great for quick mock-ups. It's great for UI, creating interfaces and things like that. We use this internally for our designs, but then they get transferred to me to pattern lab and we can send the URL to our clients. But ultimately, it fails for the same reasons. You're still sending static mock-ups. You're still just really sending an image to your client. There's a man called Clark. Clark Valberg, I think his name is. Does anyone happen to know who he is? Okay, I'm kind of... Is he here? He's going to turn up some of my presentations sometime. Clark doesn't like InVision. Anyone not like InVision? Brilliant. Thank you. Thank you. Thank you. Welcome to my party. So Clark is the CEO of InVision.  And Clark replied to me, unfortunately, you're correct, sir. I'm all over it. Expect significant improvements soon. So fair play. At least the CEO of InVision knows that InVision is not a good tool. And then they contacted me for a couple of weeks to see if they could schedule an interview or a chat to discuss my pain points with InVision, which I declined because I've seen no reason to speak to them unless they have a big fat delete button and feel like doing that to the master repo of InVision. So then a couple of weeks ago, they launched some new tool that does some inspect thing in InVision which is basically sketch on line or something like that. I don't really see the point of it, but I had to sign up for it because another design agency asked us, will you try this tool with us? See what it's like. So I signed up and I got an email from InVision. I thought, okay, you're developing what you say is the best design tool in the world. You can't make a mobile email. So what's the solution? That's enough of the complaints. What's the solution? Well, basically, the web was founded on HTML, CSS, and JavaScript. And at some stage, we're going to have to write HTML and CSS and JavaScript. Whether that's in Drupal and the renderer pops it out for us or we write it using something like pattern lab or similar cases, no of whatever style generator we're going to use. But at some stage, you have to write CSS, JavaScript, and HTML. So why not just do that at the beginning? Take the pain out of it. Instead of sending a PSD to our clients, send them a URL. Every time you want to change and you want something different color or whatever, they can just refresh the URL and they have it straight away. And they have that there then as a living document for themselves. So Brad Frost, I'm sure you've heard of Brad Frost. He's the founder of the kind of idea of atomic design. He had a great blog post a while back about building a website for some politician in America. And he got the whole thing done in one week. And I thought this was a very good point from it, that designing in the browser allows you to get closer to the final product which faster. So we're not sending our clients this kind of approximation of what the website will look like. We think it will be something along these lines. We're actually saying to them, this is what the website will look like. Here it is with long titles and short titles. Here it is when you're logged in, here it is when you're not logged in. Here's the different views we have for different content types. And they can see these then in real time and on real devices.  And if he happens to open it in a laptop, he's going to see what it looks like on a desktop. So we've got five stages really to go through. Number one, discovery. This is finding out what the client wants. This is great for the client that's all the different types of unicorns that exist and what they want. And then we go to number two, research, which is where the real magic happens. When we actually talk to some users and see what you actually need, what problems are we trying to solve? And there's quite a difference between what the client wants and what you need. So again, a lot of our work with the government, we need a big picture of the minister on the front page of the website. Nobody needs to see a picture of the minister on the front page of the website, but the minister wants to get elected next election. And that's why he has to go on the front page, not because he's adding any value to the front page.  Then we can do some rapid prototyping. It depends. The papers posted notes, pencils, whatever you need, and gets your information architecture and things worked out. And then there's more rapid prototyping and something like maybe Balsamic or Sketch or Photoshop if you must. And that can get the design going. And I don't mind designers using these tools. My problem is when we send that on to the client. So you get the design going and you get a component finished. So you know what the header is going to look like. But when you know that, tell me and I'll write the HTML, CSS and JavaScript and we'll have a header designed and the client can sign off the header from a browser, not from an image that we're going to send to them. So we agree on the different components then and we can team them up. And we use an atomic design approach. I'm going to guess that at this stage people have a fair idea so I'll kind of skip through this but it's basically that we go through what we call atoms, molecules, organisms, templates and pages. So an atom is something that's the smallest unit of HTML like an input field or a button. And a molecule is when you get more than one atom and put those together. So you get the input field and you get the button you put those together and that becomes a search block. An organism then is when you get a couple of molecules and put those together. So you get the search block and you get the branding block and you get a menu and you put those together that makes a header. Then you get a template.  And then the page is the actual colors and the fonts and the real content going into it. So here's what we do then. We have pattern lab. The version we use is the version created by phase two. Simply because that was the first version. I think that was released. I'm going to start looking at emulsify and some of the other versions as well to see what we can learn from them. So we create all the components in pattern lab. We then map these one to one from the Drupal team so we could say that the node hyphen hyphen teaser.html.twig maps to the teaser.twig inside pattern lab. And then the client will see these designs eventually and when they see them and they sign off them and say these look great, they're actually signing off then on the front end. We've got the front end built. So the design process is actually the Drupal teaming process. So rather than going through discovery, back end, site building, design, then front end, we get the QA for the front end done up front. And when we're finished, they've got two things. Number one, the team is signed off. So we're working quicker than we would be working with Photoshop. And number two, they've got themselves a style guide. So we don't even ask them, do you want a style guide? How much would a style guide cost? Well, maybe 10,000 euros for three weeks of work or something like that.  Because when you use pattern lab, we can't not give them a style guide. That's just, that's what pattern lab does. It automatically builds it for us. Okay, so here's an example then. Each of these examples I'll go through the things on the top. This pattern lab, the things on the bottom is Drupal. So this is just a very simple one of a menu and I put in five menu items, home, about us, our work, blog, and contact, and gave one of the menu items a class of, is active or something like that. And then the bottom one is the Drupal implementation. And you can see that we have six menu items instead of five and the design doesn't break and we can add in more in short client list what it would be like with seven items. But the design is still the exact same. Pixel for pixel, color for color, padding, margin, everything is 100% identical. So if the client was to sign off on the menu in the top one, we know that we can't not give them the exact same design in the bottom one. And if you were to make something like a header, again, you can see on the left-hand side of the top image it says ENGA, that's for English and Guelga. Again, a lot of our websites in Ireland for the government would have to be multilingual in Irish and English. So we got that language which are there, we got the logo then in the middle and we've got the menu on the right-hand side. And on the Drupal implementation, the client decided that we don't want EN and GA, we want the words English and Guelga for accessibility reasons. So they can change that, no problem and the design doesn't break. You don't get that kind of flexibility when you're working with something like Drupal. So we go a bit further then towards looking at a node. Again, this is a node page from this winter-ready website. So on the left-hand side, we've got our default data that we pull in via JSON and we got an image, which doesn't matter what the image is, all we were worried about here is that we have the image dimensions and the client can see any image will be going there. And you can see the Drupal version of them, it's the exact same. The style is the same, the size is the same. So we can't, again, we can't not give the client the design that they're signing off on. So to do this, as I said earlier, I'm not a very good developer and I'm a lazy developer and I don't know enough clever things to be able to do things cleverly and I don't know how I would write the Drupal menu system in PatternLab. So what I did instead was I took menu.html.twig and copy and pasted it into PatternLab. So I thought, I can't go around. I have the exact same markup now that Drupal has. And when I did that then in my Drupal templates, so I've got two directories, one called components or called PatternLab and one called templates and the templates is the Drupal templates. I then go to my templates folder and I get menu.html.twig and you can see there on the top right and I say that you will find the HTML for this inside the directory called atoms slash menus slash horizontal menu slash horizontal menu.twig. And that's as simple as that. You're really just swapping one set of templates for another. So if you look at then at the branding block, again, what we've done here is actually this is the default code that comes with the Phase 2 PatternLab team. So what Phase 2 did here was just wrote the HTML that they're going to get back from Drupal into their branding block.twig file. And then for that I go to my branding block.twig file in my templates folder and I've said what you're going to do here is extend rather than include the block.html.twig file. And inside that file you'll see a block called content as in a twig block. And there where we have a variable called URL which links back to the home page of the PatternLab installation. You use that for that variable where you use angle brackets front. So it'll link to the front page of the website instead of the front page of the style guide. So again, we're extending from one template to another and then swap a variable. Okay, if we've got slightly more complex then we can look at the header. So the header is an organism. So again, what we're doing here is we put in the header HTML that we're going to expect to get back from Drupal and then we just include three different blocks that we need for the header. So in this case we've included the molecule called branding, that's the branding block. The molecule called language switcher and the molecule called superfishing menu. And when we have those three in there and we've seen just a moment ago from here what the HTML is going to be like. So that means then inside our block PL block we get the exact same HTML that Drupal was going to give us. What I've done then is on the left hand side I've got a block called block PL. This is where I say I'm not very clever and that's what you see in PatternLab. And then under that I've got a block called block Drupal and this is what you're going to see in Drupal. So the block Drupal in PatternLab is empty and on the right hand side in the Drupal header.twig file I make the block PatternLab empty and I print the content. So whatever blocks get placed in the header in Drupal will get rendered in the header in the team. Then we had some issues with attributes and in terms of accessibility and things like that to get all the area attributes and to know if things are open or closed and know what it is in classes. So to make sure that would work I just added the attributes item into my PatternLab so that when I extend from Drupal to PatternLab and it reads that HTML attribute is already there. So if you're looking for attributes at class or attributes then know what title and things like that. In my JSON I've just created a default item in that. This one here is slightly more complex. This is looking at a paragraph bundle. Specifically a paragraph bundle that would create a card. So the card has an image field, a title field, a small text field and a link field. On the left hand side in PatternLab we put our if statements data. If there's a title, print a title. If there's an image, print the image. So this will allow the editors to not have an image if they want or to not have a title if they so choose. Then in Drupal where it gets a little bit more complex is that what I've said is the four code blocks at the top. I'm checking for a value first one. So I'm saying that check if the image field has a value and if it does have a value create a new variable and the variable will be called a card title or card image. And then the second block there then I've said is that you will find the HTML for this paragraph hyphen hyphen card.html.twig. You will find that in the organisms folder, building blocks slash cards as card.twig. And then swap the variable that we call title in card for card title which is the variable we created just a second ago in the top block and swap image for card image and tease it out. So that then allows the editors to not use an image if they wish to not use an image or not use title if they wish to not use the title but we don't end up getting empty divs rendered on the page. And then if we were to do this with a node it's pretty much the same again we pop out. Pretty much the HTML we're going to be expecting to get back from Drupal. And then we tell the node template so node hyphen hyphen teaser.html.twig that you'll find the code for this up in the organisms content type guide guide that twig file and then swap what we call the body in that field for content that body swap image for content that field image. And again that renders back then the HTML that we've already been expecting from pattern lab. So why is this good? I mean it's easy to do. It does take a bit of conceptualizing how you're doing it and you're kind of if you're used to Drupal and the modular approach you should get the theoretical framework fairly quickly but it does take a bit of work time to work out what your general pattern is. But why is this still good? First thing I think it's sustainable. What we have on the left here is the Photoshop way of doing things. So a client doesn't like the background colors. The designer needs to open up every single Photoshop file and go and change the background color of all the different buttons. Now I know you can do some clever ish things that might make that a bit less painful but that's the general thing. And the designer charges by the hour so he can retire young. Here's what we do in component-based style guide driven development. The client doesn't like the background color on buttons. The developer changes the button class to be red instead of blue. And the URL then automatically gets updated and the client can see the new work. That takes 10 seconds, 15 seconds. So there's a minimal cost and the developer needs to get a second job to pay the mortgage. Why is this so good? Part two, it's so good because we get QA done quickly. I'm going to guess like us, we talk to our clients and we say we want things tested continuously throughout the project and we're working in an agile manner and as we finish each component and you test that and you sign that off and they always say yes and then no testing gets done until the project's finished. And they tell us we don't want to test individual components. We want to test whole pages or we want to test whole site sections. So the designer designs the website. The developers build a website. It's not exactly what the client wants or they realize there are some issues that they didn't factor in because of the designs. They do some QA, they report some bugs. The bugs get fixed. The client tests again. More bugs reported. We eventually get it fixed and everyone's happy and we get a sign off and that process takes time. If we take a style guide approach to this the designer will design the website in the browser. The client will look at this on real devices and as many clients as they want can look at them as many devices as they want to look at them and then the QA becomes part of the design process. So as they sign off the design and say yeah, this is what we want you to develop they're signing off the front end QA. So you can still do all your QA in terms of the back end has to be done. The Drupal website needs to be built and things like that but we're signing off basically the front end QA. So the finished design then is the finished front end. And then we get to the idea of a living style guide. So like I said we don't ask clients do you want a living style guide? We just give them a living style guide. There's probably going to be a question in a minute when we open up the questions along the lines of how do you sell this to your clients? How do you sell pattern lab to your clients? How do you convince your boss that you want to use pattern lab? And the basic answer is you don't care what's your boss. Thinks about your desire to use pattern lab or not because your boss doesn't know you're using pattern lab. They're not the front end developers. No client will ever come to you and say yeah we want a website developed and you must use Bartik as your base team. The client doesn't care what base team we're using and they're trusting us to use the correct one. So again I don't tell my boss I'm going to start using pattern lab and it's going to be great and I'm going to spend 16 weeks convincing you because to be honest I do talk to her. She's still not convinced that we should use pattern lab. That can be hard so I would say don't. But what you're doing then is just use what you want to use and if pattern lab is the correct choice for you use that or KSS node is or if Fractal is or whichever you want to use and then you're going to give a living style guide to your clients. So if they want to design a new page they can look at your components and you can say these are the components you have there's no more. What do you want the page to look like based on these components and you can drop four or five bits of HTML into a pattern lab file and show them instantly this is what your new page is going to look like. So this happened to us a while ago. We got like I say 46 PSDs one of them was called style guide dot PSD then we got some final design dot PSD hyphen updated dot final dot PSD which had some changes to colors and we are implemented the style guide version because we presume the style guide is a canonical document and then the client told us now we need to change these to these colors and we said hang on the style guide says this this says this and what you're asking for now is actually something different which is the canonical source and the client didn't know. So you're under pressure when the client doesn't know what they actually do want and you don't have that problem with with something like pattern lab because there can only be one source and every time the code gets updated gets updated the style guide automatically gets updated so again I'll skip three days what you're doing down really is that when it's just a URL any updates are instantaneous so it's good for regression testing it means we can use what we call real content I'm going to get some questions about this in a minute so I'll have a few answers what we call real content in terms of say 10 blog posts and we can put in a body field of different lengths in each of those and different length in titles and different images but we don't use live content so we don't put in a JSON feed from the actual website we could do that but we don't and the reason we don't is for regression testing so if we want to test the views, listing page for news and we're using live content every time we try to do a regression test it's going to fail because there's new news posts on the page so when we use real but dummy content let's say actual content from the website that won't be changing so that our regression tests will pass because we're testing against the exact same news page all the time or if there's an events page and the dates are changing well if we use static content this doesn't happen oh yeah I had a joke during after skipping it oh well so the COS had a heart attack because he's designed a new component and the new component breaks something else he realizes after three weeks when we use regression testing and we use something like pattern lab and pattern lab is a great I can't think of what is called inheritance or something like that that when you look at any component it tells you that this component here is made up of these other components and this is automatically generated for you so if you want to change a component you know what other components need to go back and regression test and then it tells you every component you have it then tells you that this component is used in a higher up one so it tells you that the branding block is used in the header and it tells you that the header is made up of the branding block plus the search block so when a new component gets added we can test against this we can make sure that the website doesn't break and then three weeks later the CEO is in good health probably retiring early with the designers so that's the basic discussion around what we're trying to and what I'm trying to promote in terms of not using static generators not using static design tools and instead using things like pattern lab using things like fractal Drupalcamp Dublin is on next month the 21st of October it would be great if some of you could come along it's only 20 euros for a ticket we're only charging this year for the first time because we find if we don't charge money lots of people sign up and don't come if you can't afford 20 euros talk to me and I'm sure we can get you a ticket there's contributions, sprints on we're obliged to put this up so please have a read of it and go to sprints if you can and help out whatever you can and if you'd like to review this session click like or heart or faith or stature or whatever this you do you'll find a comment form on the link for this page so is everybody happy? Do you have any questions? No, I explained it too well or not well enough great presentation thank you agree with everything but I'm still having trouble understanding like how do we convince designers to use these tools because it seems to me that you became the designer but how do you know what to design what to make? I'm a front-end developer so I get Photoshop designs all the crap with that but the problem I'm having is that I can't convince designers to like switch tools like learn new tools to make it for websites is this the solution for that problem what I used to say was fire your designers that designers are clever people you know if they can work out how to use something like Photoshop they can work out how to write HTML and CSS and at least rudimentary JavaScript in terms of show hide type stuff or slide toggle I stopped saying that one of our friends company is another Drupal company I had a quite discussion with him about his wife telling him that his wife is not a web designer his wife is a graphic designer that happens to be designing websites so I don't go that far anymore I don't want to insult people and I don't think you can convince designers to not use what they want to use so instead now what we say is if you want to use Photoshop to design that's fine just don't send those to the client don't send the client to PST don't send the PDF so use Photoshop if you want to design or use Sketch whatever gets you designing the best website as quickly as possible at some stage I have to write the HTML and CSS and JavaScript so before you send out the client give it to me I'll do that part of it and then we'll send a link to the client so we let the designer design with the tools that they want to use and we get the front-end written as quickly as possible good thank you what did you do when the client want to completely change the front page after the first design it says oh no I don't want that maybe you can do some something different do you redo all the stuff that's what you do yeah it's no different if you use Photoshop you've got to get your big juicy delete button and start using it if the client doesn't like it you design again one thing that we do hope will work out in our favour in the coming years we'll have different design patterns from different websites and a client might say that we like the header style of your Oxfam website and we like the slider that you have on the Irish Cancer Society website and we have those components from the pattern that have been dropped into their new site and changed the colours then to match their branding and that would mean that we could design very very quickly and it's already regression tested and things like that but yeah if the client doesn't like it you've got to go back to designing again I don't see a solution for that I arrived to the solution just a few minutes later forgive me if I asking you something that you already replied but I think besides the problem of the mock-up itself it raises potential problems on the sales process if you know what I mean that you show your work to the client for example you can have clients that say what's all those for the website that's actually finished why do you need three more weeks to finish that understand the gap between the mock-up and the finished website for example or signing a deal because if you do that sophisticated mock-up you're essentially working for free unless you have a deal signed in advance and to get that deal signed in advance you need something to sell and I don't know how do you deal with those problems in the sales process it's been in our contracts when we write a tender and the section on design we have a browser and we'll be using a a website generator and that you'll be at the URL so we tell them specifically you will not be getting any Photoshop documents you will not be getting sketch files you'll get a URL you can try some kind of education process on the client to understand the we have the onboarding session so we go through to the client this is an example of pattern lab here's some design components for a listing page here's some design components I'll get you through the process with that different example it does take a bit of they're not used to this kind of approach so it takes some not necessarily selling their approach to them because they've already won the contract and they've agreed that this is what we want to do but just some education to let them know how it actually works in the real world thank you how do you deal with typography beyond headings like if you had a field label and a tag that looked just the same would you create a component for that maybe mixing just not reusing sorry can you repeat the question how do you deal with typography like different textiles not just for headings but maybe if you had a field label and a tag that looked just the same yeah you have to just create classes we have base files which would allow us to do things like spacings and margins and text float left text float right and things like that you can pop those classes into whatever components you're using so they wouldn't get styled at the individual component level if you're going to need to reuse them so they get styled in a utilities directory at the root level another quick question what do you use as your base team the pattern lab the pattern lab phase 2 version we use as far as I know doesn't have a base team it's all runs on itself let's say when I'm working myself for a base team we're progressing towards let's say a pattern lab so we have another base team that we use we call the weather team because in Ireland the weather changes a lot and that uses as a base team hi I'm a developer so you were talking about testing what do you use for testing designs backstop backstop it runs on phantom it's a headless I don't know a huge amount about that part okay thank you