Introduction
PostgreSQL, or Postgres, is an open-source relational database management system. As with other relational databases, PostgreSQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL. PostgreSQL is a powerful tool that can be used to manage application and web data on a Virtual Private Server.
This guide will demonstrate how to properly manage privileges and grant user permissions. This will allow you to provide your applications the privileges necessary without affecting separate databases.
Prerequisites
To follow along with this tutorial, you will need:
One Ubuntu 22.04 server that has been configured by following our Initial Server Setup for Ubuntu 22.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.
To complete Step 1 of our How To Install and Use PostgreSQL on Ubuntu 22.04 tutorial to have Postgres installed on your server.
With your environment prepared and Postgres running on your server, you can begin learning about how Postgres handles permissions.
Viewing Roles and Permissions in PostgreSQL
Postgres manages permissions through the concept of roles. Roles are different from traditional Unix-style permissions in that there is no distinction between users and groups. Roles can be manipulated to resemble both of these conventions, but they are also more flexible. Upon installation, Postgres is set up to use peer authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.
The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account.
First, make sure your server is running by using the systemctl start command:
sudo systemctl start postgresql.service
Then, you can switch to the postgres account by typing:
sudo -i -u postgres
You can now access the PostgreSQL prompt immediately by typing:
psql
To list the roles in your Postgres instance, type the following command:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Currently, there is only one default role with many powerful privileges.
Creating Roles in PostgreSQL
There are a number of different ways to create roles for Postgres. It is possible to create roles from within Postgres, or from the command line.
Creating Roles From Within PostgreSQL
One way of creating a new role is from within the Postgres prompt interface. The following is the syntax for creating a new role within the Postgres prompt interface:
CREATE ROLE new_role_name;
To demonstrate this, create a new role called demo_role:
CREATE ROLE demo_role;
Check the defined users again:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Your output will reveal two users.
Creating Roles from the Command Line
An alternative method of creating roles is using the createuser command from the command line.
First, exit out of the PostgreSQL command prompt for a moment by typing:
q
Then, log into the postgres account:
sudo -i -u postgres
You can create new roles from the command line with the createuser command. Using the --interactive flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.
Logged in as the postgres account, you can create a new user by typing:
createuser --interactive
The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to your specifications:
Enter name of role to add: test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
By answering n for no to all of these prompts, you will create a user similar to the previous user.
Log back into your psql Postgres prompt:
psql
Then execute the du command to reveal the differences between the two new roles. This command starts with `` because it is a psql specific meta-command that is processed by psql itself and not by PostgreSQL:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
Notice that the user created from the command line does not have Cannot login listed as an attribute.
Deleting Roles In PostgreSQL
You can delete a role using the following syntax:
DROP ROLE role_name;
To demonstrate, delete the demo_role role by typing:
DROP ROLE demo_role;
If you issue the command on a non-existent user, you will receive an error message:
ERROR: role "demo_role" does not exist
To avoid this situation and make the drop command delete a user if present, and quietly do nothing if the user does not exist, use the following syntax:
DROP ROLE IF EXISTS role_name;
With this option specified, the command will complete successfully regardless of the validity of the role. Trying to remove the demo_role with the above commands will result in this:
DROP ROLE IF EXISTS demo_role;
NOTICE: role "demo_role" does not exist, skipping
DROP ROLE
The role is now deleted.
Defining Privileges Upon Role Creation
Now, you are ready to recreate the demo_role with altered permissions. You can do this by specifying the permissions you want after the main create clause like this:
CREATE ROLE role_name WITH assigned_permissions;
To see the full list of the options, type:
h CREATE ROLE
Command: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/14/sql-createrole.html
You can give the demo_role user the ability to log in by typing:
CREATE ROLE demo_role WITH LOGIN;
Checking the attributes with the du command, the two users now have identical privileges:
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
You can get to this state without specifying the LOGIN attribute with every role creation. By using the following CREATE USER command, it automatically gives the role login privileges:
CREATE USER role_name;
The role is created with privilege automatically granted.
Changing Privileges of Roles in PostgreSQL
To change the attributes of an already created role, use the ALTER ROLE command. The syntax for this command is:
ALTER ROLE role_name WITH attribute_options;
This command allows you to define privilege changes without having to delete and recreate users as demonstrated earlier. For instance, you can change demo_role back to its previous state of Cannot login by issuing this command:
ALTER ROLE demo_role WITH NOLOGIN;
You can confirm the change with the du command:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
To change it back to a role with login access, use the following command:
ALTER ROLE demo_role WITH LOGIN;
Now the role has been reverted.
Logging In as a Different User in PostgreSQL
By default, users are only allowed to login locally if the system username matches the PostgreSQL username. You can alter this by either changing the login type, or by specifying that PostgreSQL should use the loopback network interface. This changes the connection type to remote even though it is actually a local connection.
First, create a password for the user you want to connect with, so that it can authenticate. You can try this with the test_user you created earlier by giving it a password:
password test_user
You will be prompted to enter and confirm a password. Now, exit the PostgreSQL interface and exit back to your normal user with this command:
q
PostgreSQL assumes that when you log in, you will be using a username that matches your operating system username, and that you will be connecting to a database with the same name.
To explicitly specify the options you want to use, use the following syntax with your parameters:
psql -U user_name -d database_name -h 127.0.0.1 -W
Here’s a brief breakdown of each item in the command:
The user_name should be replaced with the username you want to connect with.
The database_name should be the name of an existing database that you have access to.
The -h 127.0.0.1 section is the part that specifies that you will be connecting to the local machine, but through a network interface, which allows you to authenticate even though your system username does not match.
The -W flag tells PostgreSQL that you will be entering a password.
To log in with your test_user, issue the following command:
sudo psql -U test_user -d postgres -h 127.0.0.1 -W
You will need to enter a password after this command.
In this example, you use the database postgres. This is the default database set up during the installation. If you attempt to perform some actions in this session, you will see that you don’t have the ability to do many things. This is because test_user has not been granted administrative permissions.
Exit the current session:
q
Then get back into the administrative postgres session:
sudo u - postgres psql
Next you’ll be granting permissions.
Granting Permissions in PostgreSQL
When a database or table is created, usually only the role that created it, not including the roles with superuser status, has permission to modify it. This behavior can be altered by granting permissions to other roles.
You can grant permissions using the GRANT command with this general syntax:
GRANT permission_type ON table_name TO role_name;
You can create a table to practice these concepts with the following commands:
CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
To view the table you created, enter this command:
d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
(2 rows)
Notice that there is one table type and one sequence type. The sequence is generated for you when you used the id serial command in your table creation. This generates an auto-incrementing integer.
You can now grant some privileges to the new demo table to the demo_role. To do so, give the demo_role user UPDATE privileges with the following command:
GRANT UPDATE ON demo TO demo_role;
You can grant full permissions to a user by substituting the permission type with the word ALL. Grant this permission to the test_user with this command:
GRANT ALL ON demo TO test_user;
If you want to specify permissions for every user on the system, you can use PUBLIC instead of a specific user:
GRANT INSERT ON demo TO PUBLIC;
To view the grant table, use the following command:
z
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+----------+----------------------------+-------------------+----------
public | demo | table | postgres=arwdDxt/postgres +| |
| | | demo_role=w/postgres +| |
| | | test_user=arwdDxt/postgres+| |
| | | =a/postgres | |
public | demo_id_seq | sequence | | |
(2 rows)
This reveals all the grant permissions that have been assigned.
Removing Permissions in PostgreSQL
You can remove permissions by using the REVOKE command. The REVOKE command uses almost the same syntax as grant:
REVOKE permission_type ON table_name FROM user_name;
You can use the same shorthand words, ALL and PUBLIC, in the command as well:
REVOKE INSERT ON demo FROM PUBLIC;
The permissions you set before have now been revoked.
Using Group Roles in PostgreSQL
Roles are flexible enough to allow grouping of other roles to allow for widespread permissions control. For instance, you can create a new role called temporary_users and then add demo_role and test_user to that group.
First create the new role that will be used as a group:
CREATE ROLE temporary_users;
Then assign the users to the newly created temporary_users group:
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;
Now these two users can have their permissions managed by manipulating the temporary_users group role instead of managing each member individually.
You can view the role membership information by typing:
du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------
demo_role | | {temporary_users}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
temporary_users | Cannot login | {}
test_user | | {temporary_users}
Any member of a group role can act as the group role they are a member of by using the SET ROLE command. Since the postgres user you are logged in as currently has superuser privileges, you can use SET ROLE command even though it’s not a member of the temporary_users group:
SET ROLE temporary_users;
Now, any tables that are created are owned by the temporary_users role:
CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);
Now, check the table ownership by issuing this command:
d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-----------------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | temporary_users
public | hello_id_seq | sequence | temporary_users
(4 rows)
The new table, and the sequence associated with the serial data type, is owned by the temporary_users role.
To get back to the original role permissions, enter the following command:
RESET ROLE;
If you give a user the INHERIT property with the ALTER ROLE command, that user will automatically have all the privileges of the roles they belong to without using the SET ROLE command:
ALTER ROLE test_user INHERIT;
Now test_user will have every permission of the roles it is a member of. You can remove a group role, or any role, with the DROP ROLE command. You can test this with the temporary_users group by typing the following command:
DROP ROLE temporary_users;
ERROR: role "temporary_users" cannot be dropped because some objects depend on it
DETAIL: owner of sequence hello_id_seq
owner of table hello
This outputs an error because the hello table is owned by temporary_users. You can solve this problem by transferring ownership to a different role:
ALTER TABLE hello OWNER TO demo_role;
You can check if temporary_users no longer owns any of the tables with the following:
d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+-----------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | demo_role
public | hello_id_seq | sequence | demo_role
(4 rows)
You can now drop the temporary_users role successfully by issuing this command:
DROP ROLE temporary_users;
This will destroy the temporary_users role. The former members of temporary_users are not removed.
Conclusion
You now have the basic skills necessary to administer your PostgreSQL database permissions. It is important to know how to manage permissions so that your applications can access the databases they need, while not disrupting data used by other applications.
If you’d like to learn more about Postgres and how to use it, we encourage you to check out the following guides:
SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
Practice running queries with SQL
