PostgreSQL Basics: Roles and Privileges

Authentication	Authorization
Verify that the user is who they claim to be. This can be through password, trust, or some other federated login (like Kerberos)	As an authenticated user, what am I permitted to do within the system?

Before we get started, lets establish a few terms:

• Roles: There is only one type of authentication principal in PostgreSQL, a ROLE, which exists at the cluster level. By convention, a ROLE that allows login is considered a user, while a role that is not allowed to login is a group. Please note, while the CREATE USER and CREATE GROUP commands still exist, they are simply aliases for CREATE ROLE.
• Database Objects: 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.
• Privileges: 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.

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.

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’s running and hosting (a cluster of) databases. It does not mean that multiple servers are setup in a multi-node environment.

Principle of Least Privilege

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 that they own must be specifically granted to them.

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.

For instance, only the owner of a database object (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. SELECT) should be able to select from any table by default. That’s not how it works in PostgreSQL without some intervention.

Instead, objects are always owned by a role (and only one role) which must set privileges for other roles in most cases. This behavior can 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.

We’ll look at this more in a follow-up article on object ownership and privileges.

Superusers

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 root user in Linux or the sa account in SQL Server.

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.

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.

In PostgreSQL this initial superuser account is called postgres by default, which typically matches the system username that is running the PostgreSQL cluster process. This initial superuser can be changed using the initdb command to instantiate your own PostgreSQL cluster. That discussion is beyond the scope of this article but is generally not recommended without more experience.

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 CREATE USER and CREATE DATABASE permissions but is not a superuser. With these role-specific attributes, a role can create new users (or another database), but not bypass other security checks like selecting data from tables that they don’t 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.

PostgreSQL Roles

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’t 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’ll explore later in the article.

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.

Create a User Role

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 postgresql superuser or an administrative user provided by your hosting solution.

CREATE ROLE dev1 WITH LOGIN PASSWORD ‘supersecretpw’;

Alternatively, PostgreSQL still supports the older CREATE USER command, but it’s just an alias for CREATE ROLE. In theory it will be deprecated at some point, so users should tend towards CREATE ROLE.

-- This still works as of PostgreSQL 15. Same as above, 
-- but implicitly adds LOGIN
CREATE USER dev1 WITH PASSWORD ‘supersecretpw’;

At the most basic level, this is all that’s 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’ll discuss in later in the article.)

Maintaining unique privileges among many user roles can be cumbersome and error prone. Therefore, it’s desirable to create roles that function as groups so that users can inherit group privileges as needed. For example, consider the following diagram:

If a role is a member of the reader role, they have SELECT privileges on the public.table_name object. If they are in the creator role, then INSERT privileges, too, because each of those roles was explicitly granted privileges to a resource. Role reader_and_creator, which has not explicitly been granted any privileges outside of the public role, does have SELECT and INSERT privileges on public.table_name because it has been granted membership in both reader and creator. 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.)Create a Group Role<

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.

CREATE ROLE devgrp WITH NOLOGIN;

Like user roles, PostgreSQL still supports the older CREATE GROUP command, although it is a direct alias for CREATE ROLE because all roles are created with NOLOGIN by default, which as we’ve discussed, means the role is used as a group. There is no advantage of using CREATE GROUP and it may be deprecated at some point.

There are numerous other role attributes that can be applied at the time of creation or through ALTER ROLE. Let me highlight a few additional role attributes and their default values.

Role Attribute Options (Default Bold)	Description
SUPERUSER/NOSUPERUSER	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!) Only a superuser can create another superuser.
CREATEDB/NOCREATEDB	Can this role create databases on the given PostgreSQL cluster?
CREATEROLE/NOCREATEROLE	Is this role allowed to create additional roles (users or groups) on the PostgreSQL cluster?
INHERIT/NOINHERIT	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. If the user does not INHERIT privileges of the “parent” group, then they must switch to that role (SET ROLE) in order to utilize specific privileges of the group.
LOGIN/NOLOGIN	Can this role authenticate and login to the cluster? LOGIN must be specifically specified to allow a new role to authenticate.

See the documentation 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 ALTER ROLE command.

Now that we have roles, one of which can login (our user) and one that can’t (our group), we need to dive into how privileges are assigned and applied.

The PUBLIC Role

Every PostgreSQL cluster has another implicit role called PUBLIC which cannot be deleted. All other roles are always granted membership in PUBLIC by default and inherit whatever privileges are currently assigned to it. Unless otherwise modified, the privileges granted to the PUBLIC role are as follows.

PostgreSQL 14 and below	PostgreSQL 15 and above
CONNECT CREATE TEMPORARY EXECUTE (functions and procedures) USAGE (domains, languages, and types)	CONNECT TEMPORARY EXECUTE (functions and procedures) USAGE (domains, languages, and types)

The main thing to notice here is that the PUBLIC role always has the CONNECT 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.

Connections to a PostgreSQL database cluster are always opened to a specific database, not just the cluster. 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 WITH LOGIN) and then have authorization to connect to a database. Because every role is granted membership in the PUBLIC role and that role has the privilege to CONNECT by default, all roles that can authenticate are allowed to CONNECT to the database as well.

Many of these defaults can be modified. As a database administrator you could REVOKE the ability for the PUBLIC role to CONNECT and then grant it to each role individually, but the complexity of managing that is rarely worth the effort.

To be clear, this privilege only allows the user to connect and see high-level objects in the public schema (tables, for instance). However, they cannot select or modify anything from tables without further permission. We’ll talk about this in a bit.

There is one last thing I’d like to point out in the table above that lists default permissions for the PUBLIC role.

Notice that default privileges changed slightly in PostgreSQL 15 and above. Previously, the PUBLIC role was also allowed to CREATE objects in the public schema of a database, and every database has a public 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 public schema meant that they could potentially execute code that would later result in the elevation of their privilege (or worse).

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 CREATE privilege has been a recommended best practice for a long time.

Testing The New Role

With the new user role we created, dev1, we can login to the PostgreSQL cluster with a tool like psql 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.

-- Using flags, you will be prompted for a password as 
-- there is no specific “password” flag
psql -h localhost -U dev1 -d postgres

-- Alternate using PostgreSQL connection URI
psql postgres://dev1:supersecretpw@localhost:5432/postgres

This will cause a prompt for a password (if you didn’t provide it through the URI) and will connect the user to the postgres database. It works because the dev1 role is automatically granted membership in the PUBLIC role, which in turn has permission to connect to the postgres database.

With password authentication and one DDL statement, we created a role and logged into the PostgreSQL instance. Not too difficult, right?

So now, let’s get to work developing a new feature as the newly created dev1 account!

Creating Objects With the New User

As a developer user my first order of business is to get to work creating the table required to support the new feature I’m working on. The feature requires a table to store social accounts for each user. I’m not sure how many social account handles we plan to support, so I’ll just start with a basic table that stores each handle in a separate column.

Depending on which version of PostgreSQL you’re connected to, this may or may not be as easy. Remember, PostgreSQL 14 and below allowed the PUBLIC role (which `dev1` is a member of) to CREATE in the public schema by default. PostgreSQL 15 and above, however, does not.

After logging in as `dev1`, we attempt to create the following table.

CREATE TABLE user_social (
   user_id INT NOT NULL,
   twitter_handle TEXT NULL,
   facebook_handle TEXT NULL );

In PostgreSQL 14 and below, this probably succeeds on most installations given the setup we’ve done so far. In PostgreSQL 15 and above, however, our dev1 user most likely receives the following error.

ERROR: permission denied for schema public
LINE 1: CREATE TABLE user_social (

Let me reiterate, this only works in PostgreSQL 14 and below because the PUBLIC role is granted the ability to CREATE objects in the public schema by default. We would receive a similar error if we tried to do something like create a new schema in the database because PUBLIC has not been granted the CREATE privileges for the entire database, only the public schema.

For reference, the CREATE 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 CREATE, as outlined on the privileges page in the documentation.

Whether we need to create a table or a new schema, how do we ensure that new developer roles can do that?

We must GRANT them privileges.

Granting privileges to Roles

PostgreSQL has a robust set of privileges built in, all of which are outlined in the documentation. For the following examples we’re going to focus on granting the CREATE privilege to the dev1 user in PostgreSQL 15.

-- As a superuser or role that can grant this 
-- privilege to others
GRANT CREATE ON SCHEMA public TO dev1;

Now that we’ve granted CREATE to dev1, we can attempt to create our table again in PostgreSQL 15. We could keep multiple sessions open (either in psql or your IDE of choice), but another option is to use SET ROLE. You can “switch” 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’re doing here.

-- temporarily set role in the current session to a different 
--role. Only superusers or members of the role can do this.
SET ROLE dev1;

-- Create the table as dev1 given the new permissions
CREATE TABLE user_social (
   user_id INT NOT NULL,
   twitter_handle TEXT NULL,
   facebook_handle TEXT NULL );

-- set the role back to the session initiated role
SET ROLE NONE;

Success! We’re a step closer to adding this new feature to our application.

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:

-- set our role back again if connected through 
-- another user session
SET ROLE dev1;

-- execute a select query against another table
SELECT * FROM “user” u
INNER JOIN user_social usoc USING(user_id);

This returns:

ERROR: permission denied for table user

As you probably guessed, the solution is to grant another privilege to the dev1 role. As a superuser or role that has the ability to grant these privileges:

-- In this example, grant SELECT on ALL tables in the schema. 
-- We could be more selective if needed
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dev1;

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 SELECT, INSERT, UPDATE, and DELETE data in tables of the public schema, we could do this in one statement.

GRANT SELECT, INSERT, UPDATE, DELETE 
     ON ALL TABLES IN SCHEMA public TO dev1;

Let’s 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, dev2, we’ll have to start this entire process over.

Surely there’s a better way to manage this.

Granting Privileges Using Groups

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.

Earlier we created a group role called devgrp. It’s not allowed to log in and we haven’t granted it any privileges yet. We could instead grant this role the privileges we want to provide to all developers and then grant the devgrp role to each of them. Then any time we need to tweak developer privileges we only have to modify (GRANT or REVOKE) privileges from the devgrp role.

-- As a superuser or role that has the ability to grant 
-- this privilege to others
GRANT CREATE ON SCHEMA public TO devgrp;

GRANT SELECT, INSERT, UPDATE, DELETE 
            ON ALL TABLES IN SCHEMA public TO devgrp;

And now, any role that we grant membership to will inherit these permissions by default.

GRANT devgrp TO dev1;

At this point dev1 inherits all privileges that devgrp 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 devgrp role. This can be done at any time using the GRANT statement above or during role creation time if the group role already exists.

-- This will create the role and automatically add it as 
-- a member of the devgrp role
CREATE ROLE dev2 WITH LOGIN PASSWORD ‘supersecretpw2’ 
                                            IN ROLE devgrp;

Conclusion

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.

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 CREATEROLE and CREATEDB 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.

Once we had created new roles, we briefly discussed how to GRANT privileges to new roles individually and through inheritance so that our new roles could do the work needed to develop a new application feature.

In a follow-up article, we’ll 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.

The post PostgreSQL Basics: Roles and Privileges appeared first on Simple Talk.

from Simple Talk https://ift.tt/BhKyRnM
via