{"id":95378,"date":"2023-01-19T21:21:24","date_gmt":"2023-01-19T21:21:24","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95378"},"modified":"2024-03-07T11:56:22","modified_gmt":"2024-03-07T11:56:22","slug":"postgresql-basics-roles-and-privileges","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/postgresql-basics-roles-and-privileges\/","title":{"rendered":"PostgreSQL Basics: Roles and Privileges"},"content":{"rendered":"<table style=\"border-collapse: collapse; width: 90.3516%;\">\n<tbody>\n<tr>\n<td style=\"width: 38.6214%;\">\n<p><strong>Authentication<\/strong><\/p>\n<\/td>\n<td style=\"width: 45.148%;\"><strong>Authorization<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 38.6214%;\">Verify that the user is who they claim to be. This can be through password, trust, or some other federated login (like Kerberos)<\/td>\n<td style=\"width: 45.148%;\">As an authenticated user, what am I permitted to do within the system?<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Before we get started, lets establish a few terms:<\/p>\n<ul>\n<li><strong>Roles:<\/strong> There is only one type of authentication principal in PostgreSQL, a <code>ROLE<\/code>, which exists at the cluster level. By convention, a <code>ROLE<\/code> that allows login is considered a <strong>user<\/strong>, while a role that is not allowed to login is a <strong>group<\/strong>. Please note, while the <code>CREATE USER<\/code> and <code>CREATE GROUP<\/code> commands still exist, they are simply aliases for <code>CREATE ROLE<\/code>.<\/li>\n<li><strong>Database Objects:<\/strong> Anything that can be created or accessed in the PostgreSQL cluster is referred to as an object. Databases, schema, tables, views, procedures, functions, and more can each have different privileges applied to them for any role.<\/li>\n<li><strong>Privileges:<\/strong> The types of access that can be granted to a role for a database object. Often these will be applied at a database or schema level, but the ability to apply specific access to individual objects is always available. Privileges assigned to one role can be granted to other roles. This is typically done by granting group roles (those that cannot login) to user roles (those that can login) to assist with privilege management.<\/li>\n<\/ul>\n<p>Depending on your previous experience with information security (within a database, operating system, or application), the way that these components work together in PostgreSQL may be different than you would expect.<\/p>\n<p>Throughout this article you will see references to a PostgreSQL cluster. If you are new to PostgreSQL, this term may really confuse you. This is the way that PostgreSQL refers to the individual server\/instance that\u2019s running and hosting (a cluster of) databases. It does not mean that multiple servers are setup in a multi-node environment.<\/p>\n<h2>Principle of Least Privilege<\/h2>\n<p>One other key idea to discuss is the Principle of Least Privilege (PoLP), an information security methodology that states users should only be granted access to the least amount of information needed to do their job or task. Any access beyond files or data <strong>that they own<\/strong> must be specifically granted to them.<\/p>\n<p>Although not stated specifically in documentation, many of the nuances of PostgreSQL security and how roles and privileges work together, feel like they are implemented with PoLP in mind.<\/p>\n<p>For instance, <em>only the owner of a database object<\/em> (schema, table, function, etc.) can use or alter it unless specific access has been granted to other roles. For example, most users would probably expect that two roles with the same database or schema privileges (eg. <code>SELECT<\/code>) should be able to select from any table by default. That\u2019s not how it works in PostgreSQL without some intervention.<\/p>\n<p>Instead, objects are always owned by a role (and only one role) which must set privileges for other roles in most cases. This behavior <strong><em>can<\/em><\/strong> be modified so that new objects a role creates automatically grants specific privileges to other roles, but out of the box, object ownership is a key concept to understand when creating roles and other objects in a PostgreSQL database.<\/p>\n<p>We\u2019ll look at this more in a follow-up article on object ownership and privileges.<\/p>\n<h2>Superusers<\/h2>\n<p>One last thing to discuss before diving into role creation and granting privileges is the concept of a superuser. In PostgreSQL, a superuser is a role that is allowed to do anything in the system and is analogous to the <code>root<\/code> user in Linux or the <code>sa<\/code> account in SQL Server.<\/p>\n<p>When a user has been granted superuser, no permission checks are performed when they execute SQL code (DDL\/DML) or administer the cluster. The only check that is performed is whether they are allowed to login and connect to the cluster. Being designated as a superuser bypasses all other checks, including things like Row Level Security (RLS). This behavior is different than some other database systems.<\/p>\n<p>To function properly, every PostgreSQL cluster needs to have at least one superuser to perform some administration tasks. Initially these tasks might be creating databases and additional roles, but there are a few tasks as of PostgreSQL 15 (installing some extensions, modifying some server parameters) that still require a superuser.<\/p>\n<p>In PostgreSQL this initial superuser account is called <code>postgres<\/code> by default, which typically matches the system username that is running the PostgreSQL cluster process. This initial superuser can be changed using the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-initdb.html\"><code>initdb<\/code> command<\/a> to instantiate your own PostgreSQL cluster. That discussion is beyond the scope of this article but is generally not recommended without more experience.<\/p>\n<p>One final note. If your PostgreSQL database is hosted with a service such as AWS RDS or Azure Postgres, you will probably not have access to a superuser role. Instead, the initial role that is created for you has most privileges needed to administer users and create databases and objects. Even if you host your own PostgreSQL cluster and have access to a superuser role, the recommendation (and documented best practice) is to create at least one role that has <code>CREATE USER<\/code> and <code>CREATE DATABASE<\/code> permissions but<strong> is not a superuser<\/strong>. With these role-specific attributes, a role can create new users (or another database), but <strong>not<\/strong> bypass other security checks like selecting data from tables that they don\u2019t have permission to. This will allow you to do almost all administrative tasks without the ability to bypass all privilege checks while working in the database or cluster.<\/p>\n<h2>PostgreSQL Roles<\/h2>\n<p>Recall that in PostgreSQL both users and groups are technically roles. These are always created at the cluster level and granted privileges to databases and other objects therein. Depending on your database background it may surprise you that roles aren\u2019t created as a principal inside of each database. For now, just remember that roles (users and groups) are created as a cluster principal that (may) own objects in a database, and owning an object provides additional privileges, something we\u2019ll explore later in the article.<\/p>\n<p>For the purposes of this article, all example user roles will be created with password authentication. Other authentication methods are available, including GSSPI, SSPI, Kerberos, Certificate, and others. However, setting up these alternative methods is beyond what we need to discuss object ownership and privileges.<\/p>\n<h3>Create a User Role<\/h3>\n<p>To create a user role in PostgreSQL, execute the following DDL as a user that has the `CREATEROLE` privilege. As discussed above, this is typically the <code>postgresql<\/code> superuser or an administrative user provided by your hosting solution.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE ROLE dev1 WITH LOGIN PASSWORD \u2018supersecretpw\u2019;<\/pre>\n<p>Alternatively, PostgreSQL still supports the older <code>CREATE USER<\/code> command, but it\u2019s just an alias for <code>CREATE ROLE<\/code>. In theory it will be deprecated at some point, so users should tend towards <code>CREATE ROLE.<\/code><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- This still works as of PostgreSQL 15. Same as above, \r\n-- but implicitly adds LOGIN\r\nCREATE USER dev1 WITH PASSWORD \u2018supersecretpw\u2019;<\/pre>\n<p>At the most basic level, this is all that\u2019s required to create a role that can login to the cluster. What they can do after authenticating depends on the privileges that you grant to them (which we\u2019ll discuss in later in the article.)<\/p>\n<p>Maintaining unique privileges among many user roles can be cumbersome and error prone. Therefore, it\u2019s desirable to create roles that function as groups so that users can inherit group privileges as needed. For example, consider the following diagram:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1036\" height=\"260\" class=\"wp-image-95379\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/graphical-user-interface-description-automaticall.png\" alt=\"Graphical user interface\n\nDescription automatically generated with low confidence\" \/><\/p>\n<p>If a role is a member of the <code>reader<\/code> role, they have <code>SELECT<\/code> privileges on the <code>public.table_name<\/code> object. If they are in the <code>creator<\/code> role, then <code>INSERT<\/code> privileges, too, because each of those roles was explicitly granted privileges to a resource. Role <code>reader_and_creator<\/code>, which has not explicitly been granted any privileges outside of the <code>public<\/code> role, does have <code>SELECT<\/code> and <code>INSERT<\/code> privileges on <code>public.table_name<\/code> because it has been granted membership in both <code>reader<\/code> and <code>creator<\/code>. By crafting roles in a very deliberate manner, you can manage user roles through inheritance rather than granting individual privileges to each user. This is very important because user roles will change in different environments (people who can modify DEV may not even have access to login to the PROD cluster, for example.)<\/p>\n<h3>Create a Group Role<\/h3>\n<p>To create a group role in PostgreSQL, create a role that is not allowed to login. As mentioned earlier, this is simply a convention that denotes the role as a group.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE ROLE devgrp WITH NOLOGIN;<\/pre>\n<p>Like user roles, PostgreSQL still supports the older <code>CREATE GROUP<\/code> command, although it is a direct alias for <code>CREATE ROLE<\/code> because all roles are created with <code>NOLOGIN<\/code> by default, which as we\u2019ve discussed, means the role is used as a group. There is no advantage of using <code>CREATE GROUP<\/code> and it may be deprecated at some point.<\/p>\n<p>There are numerous other role attributes that can be applied at the time of creation or through <code>ALTER ROLE<\/code>. Let me highlight a few additional role attributes and their default values.<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p>Role Attribute Options (Default <strong>Bold<\/strong>)<\/p>\n<\/td>\n<td>\n<p>Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>SUPERUSER\/<strong>NOSUPERUSER<\/strong><\/p>\n<\/td>\n<td>\n<p>Is this role also a superuser? Superuser roles are required to effectively run a PostgreSQL cluster but should be rare and limited in use. All other privileges and role attributes checks are bypassed for a superuser. (ie. Superusers can do anything in the system!)<\/p>\n<p><em>Only a superuser can create another superuser<\/em>.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>CREATEDB\/<strong>NOCREATEDB<\/strong><\/p>\n<\/td>\n<td>\n<p>Can this role create databases on the given PostgreSQL cluster?<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>CREATEROLE\/<strong>NOCREATEROLE<\/strong><\/p>\n<\/td>\n<td>\n<p>Is this role allowed to create additional roles (users or groups) on the PostgreSQL cluster?<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><strong>INHERIT<\/strong>\/NOINHERIT<\/p>\n<\/td>\n<td>\n<p>Roles can be granted membership into other roles. If a role can inherit from another role, then they can automatically use privileges without having to switch roles, similar to how privileges work in operating and file systems.<\/p>\n<p>If the user does not <code>INHERIT<\/code> privileges of the \u201cparent\u201d group, then they must switch to that role (<code>SET ROLE<\/code>) in order to utilize specific privileges of the group.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>LOGIN\/<strong>NOLOGIN<\/strong><\/p>\n<\/td>\n<td>\n<p>Can this role authenticate and login to the cluster? <code>LOGIN<\/code> must be specifically specified to allow a new role to authenticate.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>See <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-createrole.html\">the documentation<\/a> for all possible attributes and more detailed more information. Also, any of these role attributes (including superuser) can be modified at any time with the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-alterrole.html\"><code>ALTER ROLE<\/code> command<\/a>.<\/p>\n<p>Now that we have roles, one of which can login (our user) and one that can\u2019t (our group), we need to dive into how privileges are assigned and applied.<\/p>\n<h3>The PUBLIC Role<\/h3>\n<p>Every PostgreSQL cluster has another implicit role called <code>PUBLIC<\/code> which cannot be deleted. All other roles are always granted membership in <code>PUBLIC<\/code> by default and inherit whatever privileges are currently assigned to it. Unless otherwise modified, the privileges granted to the <code>PUBLIC<\/code> role are as follows.<\/p>\n<table>\n<tbody>\n<tr>\n<td>\n<p>PostgreSQL 14 and below<\/p>\n<\/td>\n<td>\n<p>PostgreSQL 15 and above<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<ul>\n<li><code>CONNECT<\/code><\/li>\n<li><code>CREATE<\/code><\/li>\n<li><code>TEMPORARY<\/code><\/li>\n<li><code>EXECUTE<\/code> (functions and procedures)<\/li>\n<li><code>USAGE<\/code> (domains, languages, and types)<\/li>\n<\/ul>\n<\/td>\n<td>\n<ul>\n<li><code>CONNECT<\/code><\/li>\n<li><code>TEMPORARY<\/code><\/li>\n<li><code>EXECUTE<\/code> (functions and procedures)<\/li>\n<li><code>USAGE<\/code> (domains, languages, and types)<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The main thing to notice here is that the <code>PUBLIC<\/code> role always has the <code>CONNECT<\/code> privilege granted by default, which conveniently allows all roles to connect to a newly created database. Without the privilege to connect to a database, none of our newly created roles would be able to do much.<\/p>\n<p>Connections to a PostgreSQL database cluster <strong>are always opened to a specific database<\/strong>, <em>not just the cluster<\/em>. Remember that we started this article talking about authentication and authorization. To open a connection to a PostgreSQL cluster, a user first needs to authenticate (provide credentials for a role <code>WITH LOGIN<\/code>) and then have authorization to connect to a database. Because every role is granted membership in the <code>PUBLIC<\/code> role and that role has the privilege to <code>CONNECT<\/code> by default, all roles that can authenticate are allowed to <code>CONNECT<\/code> to the database as well.<\/p>\n<p>Many of these defaults can be modified. As a database administrator you could <code>REVOKE<\/code> the ability for the <code>PUBLIC<\/code> role to <code>CONNECT<\/code> and then grant it to each role individually, but the complexity of managing that is rarely worth the effort.<\/p>\n<p>To be clear, this privilege only allows the user to connect and see high-level objects in the <code>public<\/code> schema (tables, for instance). However, they cannot select or modify anything from tables without further permission. We\u2019ll talk about this in a bit.<\/p>\n<p>There is one last thing I\u2019d like to point out in the table above that lists default permissions for the <code>PUBLIC<\/code> role.<\/p>\n<p>Notice that default privileges changed slightly in PostgreSQL 15 and above. Previously, the <code>PUBLIC<\/code> role was also allowed to <code>CREATE<\/code> objects in the <code>public<\/code> schema of a database, and every database has a <code>public <\/code>schema by default. This permission was granted to the public role many years ago for backwards compatibility with very old versions of PostgreSQL, but also presented a security vulnerability. With the rise of DBaaS services, the potential for a new role to create tables, triggers, and functions in the <code>public <\/code>schema meant that they could potentially execute code that would later result in the elevation of their privilege (or worse).<\/p>\n<p>Therefore, starting with PostgreSQL 15, the public role can no longer create anything by default, regardless of the schema. While it means a bit more work for administrators, revoking the <code>CREATE<\/code> privilege has been a recommended best practice for a long time.<\/p>\n<h2>Testing The New Role<\/h2>\n<p>With the new user role we created, <code>dev1<\/code>, we can login to the PostgreSQL cluster with a tool like <code>psql<\/code> using the following command. In the following examples the PostgreSQL cluster is hosted locally on my laptop using Docker with the default port of 5432. Your connection details, including the database name, may be different and should be adjusted as necessary.<\/p>\n<pre class=\"lang:ps theme:powershell-ise\">-- Using flags, you will be prompted for a password as \r\n-- there is no specific \u201cpassword\u201d flag\r\npsql -h localhost -U dev1 -d postgres\r\n\r\n-- Alternate using PostgreSQL connection URI\r\npsql postgres:\/\/dev1:supersecretpw@localhost:5432\/postgres<\/pre>\n<p>This will cause a prompt for a password (if you didn\u2019t provide it through the URI) and will connect the user to the <code>postgres<\/code> database. It works because the dev1 role is automatically granted membership in the <code>PUBLIC<\/code> role, which in turn has permission to connect to the <code>postgres<\/code> database.<\/p>\n<p>With password authentication and one DDL statement, we created a role and logged into the PostgreSQL instance. Not too difficult, right?<\/p>\n<p>So now, let\u2019s get to work developing a new feature as the newly created <code>dev1<\/code> account!<\/p>\n<h3>Creating Objects With the New User<\/h3>\n<p>As a developer user my first order of business is to get to work creating the table required to support the new feature I\u2019m working on. The feature requires a table to store social accounts for each user. I\u2019m not sure how many social account handles we plan to support, so I\u2019ll just start with a basic table that stores each handle in a separate column.<\/p>\n<p>Depending on which version of PostgreSQL you\u2019re connected to, this may or may not be as easy. Remember, PostgreSQL 14 and below allowed the <code>PUBLIC<\/code> role (which `dev1` is a member of) to <code>CREATE<\/code> in the <code>public<\/code> schema by default. PostgreSQL 15 and above, however, does not.<\/p>\n<p>After logging in as `dev1`, we attempt to create the following table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE user_social (\r\n   user_id INT NOT NULL,\r\n   twitter_handle TEXT NULL,\r\n   facebook_handle TEXT NULL );<\/pre>\n<p>In PostgreSQL 14 and below, this probably succeeds on most installations given the setup we\u2019ve done so far. In PostgreSQL 15 and above, however, our dev1 user most likely receives the following error.<\/p>\n<p><code>ERROR:  permission denied for schema public<br \/>\nLINE 1: CREATE TABLE user_social (<\/code><\/p>\n<p>Let me reiterate, this only works in PostgreSQL 14 and below because the PUBLIC role is granted the ability to <code>CREATE<\/code> objects in the <code>public<\/code> schema <em>by default<\/em>. We would receive a similar error if we tried to do something like create a new schema in the database because <code>PUBLIC<\/code> has not been granted the <code>CREATE<\/code> privileges for the entire database, only the <code>public<\/code> schema.<\/p>\n<p>For reference, the <code>CREATE<\/code> privilege can be applied to three areas in PostgreSQL (database, schema, and tablespaces) and allow creation of different objects depending on where you have permission to <code>CREATE<\/code>, as <a href=\"https:\/\/www.postgresql.org\/docs\/current\/ddl-priv.html\">outlined on the privileges page<\/a> in the documentation.<\/p>\n<p>Whether we need to create a table or a new schema, how do we ensure that new developer roles can do that?<\/p>\n<p>We must <code>GRANT<\/code> them privileges.<\/p>\n<h2>Granting privileges to Roles<\/h2>\n<p>PostgreSQL has a robust set of privileges built in, all of which are outlined in the documentation. For the following examples we\u2019re going to focus on granting the <code>CREATE<\/code> privilege to the <code>dev1<\/code> user in PostgreSQL 15.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- As a superuser or role that can grant this \r\n-- privilege to others\r\nGRANT CREATE ON SCHEMA public TO dev1;<\/pre>\n<p>Now that we\u2019ve granted <code>CREATE<\/code> to <code>dev1<\/code>, we can attempt to create our table again in PostgreSQL 15. We could keep multiple sessions open (either in <code>psql<\/code> or your IDE of choice), but another option is to use <code>SET ROLE<\/code>. You can \u201cswitch\u201d to another role if you are logged in as a superuser or that you are a member of. This is helpful for things like setting the correct object ownership at creation time and for testing permissions like we\u2019re doing here.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- temporarily set role in the current session to a different \r\n--role. Only superusers or members of the role can do this.\r\nSET ROLE dev1;\r\n\r\n-- Create the table as dev1 given the new permissions\r\nCREATE TABLE user_social (\r\n   user_id INT NOT NULL,\r\n   twitter_handle TEXT NULL,\r\n   facebook_handle TEXT NULL );\r\n\r\n-- set the role back to the session initiated role\r\nSET ROLE NONE;<\/pre>\n<p>Success! We\u2019re a step closer to adding this new feature to our application.<\/p>\n<p>Unfortunately, we quickly run into the another problem. To develop this feature, our developer needs to select data in the `user` table, but attempting to do so results in another error:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- set our role back again if connected through \r\n-- another user session\r\nSET ROLE dev1;\r\n\r\n-- execute a select query against another table\r\nSELECT * FROM \u201cuser\u201d u\r\nINNER JOIN user_social usoc USING(user_id);<\/pre>\n<p>This returns:<\/p>\n<p><code>ERROR: permission denied for table user<\/code><\/p>\n<p>As you probably guessed, the solution is to grant another privilege to the <code>dev1<\/code> role. As a <code>superuser<\/code> or role that has the ability to grant these privileges:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- In this example, grant SELECT on ALL tables in the schema. \r\n-- We could be more selective if needed\r\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO dev1;<\/pre>\n<p>If we know all the privileges that our developers need to do their work, we can add multiple privileges at one time. For example, if a developer needs to <code>SELECT<\/code>, <code>INSERT<\/code>, <code>UPDATE<\/code>, and <code>DELETE<\/code> data in tables of the <code>public<\/code> schema, we could do this in one statement.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">GRANT SELECT, INSERT, UPDATE, DELETE \r\n     ON ALL TABLES IN SCHEMA public TO dev1;<\/pre>\n<p>Let\u2019s be honest, though. Managing privileges one at a time for each role from database to database is going to be a maintenance headache. As soon as we create an account for the next developer on our team, <code>dev2<\/code>, we\u2019ll have to start this entire process over.<\/p>\n<p>Surely there\u2019s a better way to manage this.<\/p>\n<h2>Granting Privileges Using Groups<\/h2>\n<p>PostgreSQL provides the ability to grant the privileges of one role to another role. Specifically, group roles (the ones that cannot login) are the perfect mechanism for applying sets of privileges to many users at once.<\/p>\n<p>Earlier we created a group role called <code>devgrp<\/code>. It\u2019s not allowed to log in and we haven\u2019t granted it any privileges yet. We could instead grant this role the privileges we want to provide to all developers and then grant the <code>devgrp<\/code> role to each of them. Then any time we need to tweak developer privileges we only have to modify (<code>GRANT<\/code> or <code>REVOKE<\/code>) privileges from the devgrp role.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- As a superuser or role that has the ability to grant \r\n-- this privilege to others\r\nGRANT CREATE ON SCHEMA public TO devgrp;\r\n\r\nGRANT SELECT, INSERT, UPDATE, DELETE \r\n            ON ALL TABLES IN SCHEMA public TO devgrp;<\/pre>\n<p>And now, any role that we grant membership to will inherit these permissions by default.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">GRANT devgrp TO dev1;<\/pre>\n<p>At this point <code>dev1<\/code> inherits all privileges that <code>devgrp<\/code> has. Any time a new developer joins the team and requires access to the database, we can create a role and grant them membership in the <code>devgrp<\/code> role. This can be done at any time using the <code>GRANT<\/code> statement above or during role creation time if the group role already exists.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">-- This will create the role and automatically add it as \r\n-- a member of the devgrp role\r\nCREATE ROLE dev2 WITH LOGIN PASSWORD \u2018supersecretpw2\u2019 \r\n                                            IN ROLE devgrp;<\/pre>\n<h2>Conclusion<\/h2>\n<p>PostgreSQL has a comprehensive role and privilege system that allows a lot of flexibility for managing access to server objects and resources. In this article we learned about roles, how to use them effectively to manage users and groups, and how to grant some initial privileges to the new roles that we created.<\/p>\n<p>We also discussed the importance of superuser roles and that most DBaaS services will not provide you with a superuser because it bypasses all privilege checks. Instead, the best practice is to create one or more administrative users with <code>CREATEROLE<\/code> and <code>CREATEDB<\/code> privileges for day-to-day administration. If you use a service like AWS or Azure, the administrative user they provide likely has these privileges rather than being a full superuser.<\/p>\n<p>Once we had created new roles, we briefly discussed how to <code>GRANT<\/code> privileges to new roles individually and through inheritance so that our new roles could do the work needed to develop a new application feature.<\/p>\n<p>In a follow-up article, we\u2019ll dig deeper into object ownership and how it plays an important role (pun intended) in how you set up and maintain object ownership in your applications. Understanding how object ownership impacts the way that scripts are run, and backups are managed, will help you design secure applications that are easier to manage.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Information security, whether in databases, applications, or larger IT systems, is based on the principles of Authentication and Authorization, often referred to as AuthN and AuthZ respectively. <\/p>\n<p>Likewise, PostgreSQL security involves creating roles that allow access to server resources (AuthN) and then granting privileges to database objects appropriately (AuthZ). Understanding how each of these components work together is essential to effectively manage security in a PostgreSQL cluster.&hellip;<\/p>\n","protected":false},"author":341037,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[158982,159066],"coauthors":[158981],"class_list":["post-95378","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-planetpostgresqlryanbooz","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95378","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341037"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95378"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95378\/revisions"}],"predecessor-version":[{"id":99018,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95378\/revisions\/99018"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95378"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95378"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95378"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}