PostgreSQL vs. SQL Server: Security Model Differences | POSETTE: An Event for Postgres 2026

Key Takeaways

Hi, my name is Toyo Bali. I'll be talking about SQL Server and Postgres security model differences. This talk is for someone like me who is accidental Postgres DBA coming from SQL Server world and we'll be comparing and contrasting the security model differences between the two database platform. Okay, let's get it started with Postgres and SQL Server security model differences. I'm not going to read my bio here in the interest of time, but I definitely want you to take a note of my contact on the left side on the bottom left. So, you can reach out to me after this talk if you have any question, concerns and you're guaranteed to get an answer. Uh before I start get started into the technical stuff, as I mentioned at the beginning that this is for accidental Postgres DBAs. And so, here I have slide because of you know, compare and contrast slide of SQL Server and Postgres. I'll most likely be spending less time on the SQL Server slides compared to the Postgres slides, but I have those for references so we can talk about those. And if you're watching this uh live stream, I'll be in the Discord now. I'm there and I'll also be waiting there after the talk so you can ask me questions uh and if you have, you know, any kind of discussions, feedback, I'm willing to, you know, entertain there. So, let's make sure you're on the same page because some of you, as I said, you know, probably a accidental Postgres DBA, some of you are not using it. You're just probably looking at it because Postgres is one of the fastest growing databases now in the world in in in relational database. What is authentication? So, doesn't matter if it's in Postgres or SQL Server, authentication is who am I, right? Am I saying I'm saying I'm to you Am I really to you? All right, how do the people check it? I have an idea. I have a passport. So, in this case, we check with login passwords, managed identity, pass key. And it's interesting, you know, I was listening to a webinar of Microsoft and they said, you know, very soon we you all heard about agents, bots, and all that, you know, they will have their own identity in in Microsoft Entra. And we'll be asked to manage permissions for those within our database systems. So, that's called authentication. So, what's the authorization? So, I have authentication to come to your house or maybe go to a hotel. Like I can get into the front door. Can I go to every room of that hotel? Probably not, right? Because I only booked one or two rooms. I can only go to those rooms with my key. So, that's called authorization. Like I can come to the database. I can come to the server or cluster in terms of Postgres. But what can I do inside? What databases can I get into? And then within those databases, what can I do? That's defined by authorization. Now, login in SQL Server, you all know I just talked about what's the login. Now, I understand with the Azure SQL database uh and with the contained user, this concept get a little bit blurry, but let's just stay to the traditional one that you have a login. And then you also have a user. So, your login is at a server level. A user is at a database level. And [clears throat] each login is mapped to a user for every database. And then we give permission or you give authorization to the user when it comes to SQL Server. And I understand like as I said, you know, nowadays you have managed identity. Uh we have other Entra objects that, you know, some of these will get blurred. But, you know, let's just stay with this just so you can compare with with Postgres. Other thing is a schema. So, a schema is a logical boundary within the databases. Uh why do people use schema? There can be many different reason. It can be functional, Uh it can be you might have different clients, you might have different operations that you want to put it in the same database for different reason. So, you can separate those those things. But, it's also security boundary. So, we can we can latch on a schema and we can design our security based on a schema. And and I'll show you some of this stuff later and that we'll talk a little bit more on those. Super user. So, if you are used to SQL Server, we know something called SA or sysadmin. That means I have the master key. So, if in terms of I talked about a hotel, if you are the manager or supervisor in the hotel, you can probably get into every room in case of emergency, right? So, you have a master key. So, super user in SQL Server we call SA, in Linux there is a root user, in Postgres also there is a super user called Postgres. So, this super user can do anything. And when a super user comes other than if they can log in into a Postgres cluster, nothing else get checked. They automatically get every privilege then that you know, they have that they need within a Postgres cluster. SQL Server roles. So, in SQL Server a role is a collection of privileges. DB data reader or bulk admin, backup admin, security admin. It tells that if you are a member of this role, you can do ABC at a server or a database level. And we can also create our own roles like the user defined database roles and server roles. And we can then give make other people member of these roles. And there's something called application role. I'm not going to talk about this here. If you're interested, you can look up in the books online. It's at the database level. That's all about SQL Server. In Postgres, we're going to pretty details into roles. So, there are four things that I want you to really take note out of this talk because if you're going to be working in Postgres and if you're coming from SQL Server world, you will be struggle unless you make your understanding very clear about these four things. One is a role, which we briefly touched about SQL Server. We'll talk about this one. Other one is object ownership. So, a object owner is kind of a super user for that object. Uh this is totally different and you need to understand this object ownership, otherwise you're going to struggle when you're giving permission to other roles and within the Postgres. And there's also something called inheritance. And I know in SQL Server there is also inheritance because if I'm a member of a role in SQL Server, I automatically inherit all those permissions. Uh in Postgres, the inheritance is a little bit more granular. So, try to grasp that understanding. And other thing we'll be talking about is a default privilege and how to modify default privilege. You absolutely have to understand that if you're going to be working with a large Postgres cluster to manage permissions because it works totally different. And if you're not going to understand what is default privilege and how to edit default privilege, uh very soon it can get very messy. So, in SQL Server, whenever you talk about cluster, it is we're talking about Windows Server failover cluster or failover cluster instance. It means one one or more machines are bundled together to create a cluster. But in Postgres, it's totally different. In Postgres, whenever you talk about a cluster, you're talking about a single Postgres engine and a collection of database inside that engine. Inside that Postgres engine, they're all in a one data directory. So, I can have a post dress I can have a host that can have multiple post dress cluster same as in a sequel server host I can have a default instance and then I can have multiple named instance but in post dress we don't call it a named instance we just say there is three or four post dress cluster but you have to understand a post dress cluster is same as a sequel server instance not a bundle of one or more hosts together that we build during when you talk about a sequel server cluster. Post dress role. So in sequel server we just talked about server level role database level role in post dress role is totally different roles only exist at a cluster level every login every user every group that we know in sequel server everything is a role in post dress. A role is created at a cluster level. But the permissions the authorizations are at a database level. So I can have a role that can create table in database A but it cannot create table in database E because the permissions are different I have to give it separately for each database. Now in post dress you can also say create a user actually that's an alias for a creating a role same you can create group which is also alias of create roles. So users can login and groups cannot login. Now these roles what I'm saying as a group which is alias of role you can use those same way that we know server level role and database role in sequel server. So once you create these groups you assign bunch of permissions and then you give this role membership to the other roles that way they inherit this permission. And a very clever way uh Postgres implemented something that we use a loose term called like just-in-time access, right? I want to give someone this access for a smaller period of time. With any inheritance the way Postgres implemented it, it's it's a very clever trick that that you can use for the same purpose. So, in every role, if it's not a user, what kind of permission it has? It will get some get some permissions by default from the public. And in version 15, there was a major change. Before that, any role could create objects in public role, now it cannot do anymore after version 15. So, that public permission is already there, and you can modify those if you do not feel comfortable you or if you think those are too much for every role, you can always control those. And then the role is going to grant whatever you grant directly and whatever is going to inherit. So, combination of these three is the total privilege that a role is going to have within a database. SQL Server is a two-level. And I mentioned this before. Yes, there are exceptions and I'm totally aware of it. Like in Azure SQL database and contained users. But in general, you come in as a login like I said. You have the key of the door of the of the of the front door, and then you have uh users that manage your permissions per database. In Postgres, this concept doesn't exist. In that way, the way I explained in SQL Server. The way it exists in PostgreSQL is a you are a role. The same role is coming at a server level and see if you can come in. You came into the cluster, now your cluster has taken different databases, but you're not going to create separate user, you're going to give permission to that same role per database, and that's how you keep it the you know, what I said user in SQL Server, that's how you keep the permissions separate between the databases for a single role. This next two slides, this one and the next one, um I have not tested everything, I have done few, because most of the Postgres uh engines that I acquired as accidental DBA uh reside in Azure, uh in Microsoft cloud. Uh so, I do not do some of these stuff, but I did double-check with some of the experts, these are correct. So, if you are running in Windows, uh or if you're doing Windows authentication, uh depending on uh your Postgres is running on Windows or Linux, I have these two slides that you can uh definitely, you know, uh consult and you have some idea uh about authentication, what's easier, what's easy to implement, what will be difficult, and you have to do some custom stuff, and some are not supported at all that I posted here, as you can see on the second column, on the third row, it says that SQL Server uh on Linux doesn't support OS level authentication, uh but in Postgres, it's very easy setup. So, it will be a good to know if you are going to admin for Postgres engines or Postgres clusters. As I mentioned that you must understand role, and if you are admin of Postgres clusters, this is going to be in your wheelhouse all the time. Um if you look at the slide on the right at the bottom part, you'll see a full list. That's a URL that shows you the Postgres official documentation about role. It lists all the attributes. I only listed the top six that I feel like that you should know off your head uh without going to the documentation every time. So, the one that you see uh, is highlighted is the default. So, what does that mean? If I say create role to your if I do not say anything, I cannot log in because no login is the default attribute for every role you create in Postgres. If I want this role to your to login, I have to say with login. And I'm not going to read the whole slide. The other six other five are same. Highlighted means default. Not highlighted means not default. So, if you mean to role to do some of those, you have to mention those while creating, but you can always change it later on except the login and no login one. Role is scope. I talked about it briefly a few times. I'm going to mention that again. A role we always create at a cluster level, globally shared across the databases. But, when we want that role to connect to the databases, it has to be in the database context and whatever permission it has within the database context that that boundary doesn't change between the databases. It doesn't matter how many databases I have in the cluster. Public role we mentioned few times. Every login in SQL Server is a member of public role. You have permissions that are predefined and you can change the permissions of a public role in SQL Server. If you create a login, it doesn't have any other permission, it will inherit from public role within the server level. If you create a user without any permission, it will inherit whatever is in the database public role. And all these three bullet points applies to both server and database public role within the SQL Server. In Postgres, it is very similar to SQL Server in terms of every role is automatically member, no exception. And a public role exist in every database and you cannot drop it, right? And the permissions are by default as I said. And if you think whatever business you are in that those public permissions are too much for every role, you have the full control to tighten that and you should review that with your security team, with your app orders, whatever way you do it at your workplace, but you should definitely review it and make sure everybody is on the same boat. Postgres also have a public schema. I We talked about a schema uh before in the definition slides. And I talked about in general because I said it's pretty much same concept between Postgres and SQL Server. So, every database will come with a public schema and every role can do certain things on public schema. I have a list of those things. Version 15 removed create privilege because that was deemed too much and which is true. Uh even the one that is today by default in some industry, you'll probably want to control some of those. Um like there's one called Uh no, I have that slide later on, sorry. So, Postgres object ownership I mentioned before. Whoever creates object in Postgres that role becomes the super user. Only object creator can grant exclusive privileges to other roles. And unless you change the ownership uh nobody else can do anything. But one good one good thing is you can change ownership and uh uh I'll talk about it later on uh you know, when you go and see the demo code. Default privilege is this what I was talking about. So, this is a a list I copy-pasted pretty much from the documentation. One thing you want to notice, I mentioned it few times, are you comfortable with any role coming into your database and executing any functions or stored procedures? If you are not, you should pay attention because if you do not do anything, every role within every database in the public schema will be able to execute all functions and all stored procedures. So, if you don't like it, you should do something. You should tighten your security before you create the roles. I mentioned four things that you should pay extra attention, and one was default privilege. And if you do not use this feature, I can guarantee you in any commercial enterprise, you going to be struggle struggling with setting up a unified and scalable uh security model within your Postgres. You must use default privileges. So, this is what I talked about in SQL Server, where we have built-in server level and database role. This is exactly for that reason you're going to use default privilege to create those roles. Uh and if you want to create those roles for future objects, which is not same as SQL Server, you have to uh use uh a code called alter default privileges. So, let me show you that. This is a very simple code, and it can be more complex, but I kept it very simple just so we can walk through this and we can talk about it what I mean by this. What this saying is this, going forward, when admin role is going to create any object in public schema, another role named dev login will automatically have select. And this will be implemented for the objects that already exist and the objects that admin role is going to create in future. As I said, so this is just a simple one. Uh your your one you know, your roles are probably going to be a little bit more complex, but this is the way you decide how many roles you need, you create those and then later on you make other roles the member of this, so they automatically are going to get, you know, inherit all these permissions. So in the interest of time, uh because this is a short talk, I'm not going to run through all the demo code, uh but I'll probably show you a few things that I prepared. And uh we will be switching to a tool that you are seeing here right now called uh Visual Studio Code. This is by Microsoft. It's a open source. It's a multi platform. Uh it's extension-based. And uh what I'm using right now, I'm using extension called, let me show you this. This is a little bit zoomed out, so I will zoom back and then I'll come back just so I can show you uh Okay, now so I Okay, now let me show you these extensions here. So I'm using extension called Postgres Postgres or Postgres SQL. This is by Microsoft. Uh this is a newer one. If you have a older one, uh I disabled it. I can show you, but make sure you have this this current one. Um the and I I'm using this for this demo. So, let me Okay, I'm kind of a little bit long. I'm just going to minimize this. Okay, good. And what I have here I have a Docker container running. And that is running a Postgres instance. As you can see at the bottom right corner, I'm connected to a localhost. My database is Postgres and I my user name is Postgres, which is a superuser. And so I have a demo section one and a demo section two and three. I know I don't have time to run through all three sections, but I have put enough comment here, more than I'll put even in my production code, because I know that we don't have time here. So, once this conference is over wherever you're going to get our slide I will upload this and you just have to, you know, change the name of your cluster and wherever you're running it, maybe the port number. Everything else should work as is. And if you get it stuck, as I shared you my contact and I'll do it again at the end, you can always reach out to me if you run into an issue. So, let's try to walk through this pretty quickly. Um and give you some ideas that what I mean, you know, what I want to show you. If you're just creating a Let's start a base here. You know, just an empty database. Um let me go one more up, yeah, just to make sure you can see it. Good. And then I have some of this code might look complex, but they are here so I can run this over and over, right, if I need to for demo purpose. Just clearing out some stuff. So, basically I'm creating a role. But as you see here, I said with login, right? As I mentioned, because no login is the default, now you've created a dev login and I created a role called read login and both are login. They're not group. They're not They're users. So, I created one with dev login, one is with the read login. And by default, of course, these permissions are granted as we talked about. And now, if I go to create a table, I'm going to get an error message because I haven't given any permission to the dev login. And later I give permission to create table. And then the dev login can create a table. But now I ask the read login in to read from this table, but it cannot because I also didn't give anything to the read login. Now I give it grant select and it can read. Now, dev login created a table two, but read login cannot read because what I talked about the default privilege and it stops you here because when I gave the read login permission, uh read permission to the read login, table two did not exist. So, now it cannot read. It failed. As you can see, this is going to get very complex, right? Every time you create new object, are you going to come and give it permission again? It's not going to work. It's just not feasible, right? And you will have more and more developers, more and more people probably in QA and your business accounts or application accounts that want to read your data or modify your data. It can get very cumbersome. So, then if you go to the demo section, two, here I'm creating roles as you see with no login, right? I mentioned it just to be clear. I know it's default. Now I'm creating start creating groups as you know in SQL Server and I'm also creating uh you know, giving the group a permission and then making the dev login a member of dev group. I you know, we don't say here member, we say grant. And the same way I'm going to do it do the same for the for the read login, right? So, I'm going to create two groups. I'm going to make everybody member. That way when I have new developers, new folks in the QA team, and other teams, I can just keep making them member. And going forward, what I'm going to do, I'm going to use the default privilege. So, I do not even though I create this test table three, test table four, after I grant the permission, uh you know, things are I don't have to go and do it again. They're automatically going to have those permissions. So, you have to think about how many groups I need, and then, you know, create those, give them proper permission. Then you create your users, and make them member of those roles, or grant them permission, or make them member of the Yeah, those those groups. Uh I also have code for you that I'm going to go down and show you. Uh I created a second database now. And I'm trying to use the same roles that I created before. As you can see, I cannot This will fail because it says you gave me permission in the first database, not in the second database. So, the it doesn't work here. Now, if you are a SaaS provider, if you have 100 of these databases, are you going to do this every time? No, you do not. So, for that, I have put here five options. You know, in SQL Server, something called msdb. If you change your msdb, and you bring new database, you know, it will be get will get created from msdb. You can do something like this. I have five different options. And I have a demo three uh as a bonus talking about inheritance that I talked about. Uh so, again, you know, download this code. If you have any issues, please uh let me know. And uh we are almost you know, end of the talk, so let's wrap it up, go back to the slides. So, what did you learn here? Uh As a summary, you definitely want to think about unique sets of privileges. If I have to translate it into SQL Server language, how many database level groups I need, right? DB data reader, DB data writer, that kind of groups. How many I need? Create those. And then make other uh roles member of those groups. Use default privilege for everything I just talked about. And these are some further readings uh if you want to go a little bit more deeper on some of the topics I talked about. I have read all this. Uh so, consult those. And thank you again and for listening and for attending the conference. Hopefully, you enjoy other sessions. Reach out to me with any questions, comments, and you're guaranteed an answer. And take a note of my um contacts at the bottom left of the slide. Thank you again.