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.
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.
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
Output 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.
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.
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
Output 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.
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:
OutputEnter 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
Output 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.
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:
OutputERROR: 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;
OutputNOTICE: role "demo_role" does not exist, skipping
DROP ROLE
The role is now deleted.
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
OutputCommand: 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:
Output 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.
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
Output 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.
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:
user_name
should be replaced with the username you want to connect with.database_name
should be the name of an existing database that you have access to.-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.-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.
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
Output 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
Output 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.
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.
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
Output 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
Output 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;
OutputERROR: 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
Output 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.
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:
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Excellent, easy-to-follow intro to roles and permissions in PostgreSQL. Thanks Justin.
A small note. On Ubuntu 14.04, after logging in as ‘test_user’, then reverting to the admin session and typing sudo su - postgres from the command line, I found I was asked for the postgres password, which I didn’t set / didn’t know. Problem solved by opening a new terminal and entering the same command - then I was asked for my standard Ubuntu password.
great article. helped me a lot!
Thanks! Didn’t know a default user is created and its name is postgres
This article helped me a lot, Thanks
Hi, I’m trying to create a user with permissions to create a database. I’m logged in as the deploy user and I’m able to create the user ‘deploy’
However, when I try to alter the role and give it CREATEDB permissions with the command:
and then check on the changed permissions with \du, nothing has happened. The ‘deploy’ user is there, but it has no CREATEDB permissions.
I also tried this as a root user, with the same result.
I also tried to create the user from the commandline with:
createuser deploy
according to this tutorial I should get these questions:
but I didn’t, the user gets created and I’m back at the prompt.
at the prompt I’m also seeing:
Does that mean my version of Postgres is 9.4? That’s odd, because I installed version 9.3
thanks for your help,
Anthony