Use Roles and Permissions in PostgreSQL on a VPS

PostgreSQL, also known as Postgres, is an open-source relational database management system that stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language (SQL). When running PostgreSQL on a Virtual Private Server (VPS), managing roles and permissions is essential for security and proper access control.

This guide demonstrates how to manage privileges and grant user permissions in PostgreSQL. You’ll learn how to create roles, assign privileges at different levels (database, schema, and table), use role inheritance, and secure your PostgreSQL installation on a VPS.

By the end of this tutorial, you’ll understand how PostgreSQL handles access control and how to safely manage users and privileges on a VPS setup.

• Understand PostgreSQL’s role system: Learn how roles act as both users and groups, making access management simpler and more flexible.
• Create and organize roles effectively: Use LOGIN roles for database users and NOLOGIN roles as permission groups that other roles can inherit from.
• Grant permissions with confidence: Use the GRANT command to assign access at the database, schema, or table level so users only get what they truly need.
• Leverage role inheritance: Streamline permission management by letting roles inherit privileges from parent or group roles automatically.
• Secure your VPS deployment: Configure pg_hba.conf for password-based authentication, enforce strong passwords, and restrict database access through firewalls.
• Audit access and verify roles: Use PostgreSQL meta-commands like \du to view existing roles and \z to check who has access to what.
• Follow best practices: Regularly review privileges and apply the principle of least privilege to reduce potential security risks.

To follow along with this tutorial, you will need:

• Ubuntu server configured by following our Initial Server Setup for Ubuntu guide. Your server should have a non-root user with sudo permissions and a basic firewall.

• PostgreSQL installed on your server. Complete Step 1 of our How To Install and Use PostgreSQL on Ubuntu tutorial.

With your environment prepared and Postgres running, you can begin learning about how PostgreSQL handles permissions for databases.

PostgreSQL manages permissions through roles. A role can represent a database user, a group of users, or both. Unlike traditional Unix-style permissions, PostgreSQL doesn’t distinguish between users and groups—roles can function as either. When you install PostgreSQL, it creates a default role called postgres with superuser privileges. The installation also sets up peer authentication, which associates PostgreSQL roles with matching Unix/Linux system accounts. If a role exists in PostgreSQL, a Unix/Linux username with the same name can sign in as that role.

Differences Between Roles & Users

While PostgreSQL uses “role” as the primary term, there’s a practical distinction:

• Roles without LOGIN privilege: These roles are typically used to group together sets of permissions that can then be granted to various users, making it easier to manage access control at scale.

• Roles with LOGIN privilege: These roles are essentially the actual database users who can authenticate and initiate a connection to PostgreSQL, allowing them to perform actions based on their granted permissions.

• Superuser roles: Superusers possess unrestricted privileges within the PostgreSQL instance. They can override any permission checks and perform administrative tasks that regular roles cannot.

The default postgres role is a superuser with full database access. For production environments, create specific roles with only the privileges needed for each application or user.

Before we start managing roles, make sure that PostgreSQL is running and that you have access to its administrative interface before proceeding with role management.

Starting PostgreSQL

Start PostgreSQL with:

sudo systemctl start postgresql.service

Verify it’s running:

sudo systemctl status postgresql.service

Look for “active (running)” in the output. If the service isn’t running, you’ll see “inactive” and need to troubleshoot the startup logs.

Accessing the PostgreSQL Interface

PostgreSQL uses peer authentication by default, which means your system username must match your PostgreSQL role name. Switch to the postgres system account:

sudo -i -u postgres

Then connect to PostgreSQL:

psql

You’ll see the postgres=# prompt, indicating you’re connected as the postgres superuser role. From here, you can execute SQL commands and PostgreSQL meta-commands (those starting with \).

Viewing Existing Roles

List all roles with the \du meta-command:

\du

Meta-commands (starting with \) are processed by psql, not sent to PostgreSQL. The output shows role names, attributes, and group memberships:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

A fresh installation includes only the postgres superuser role. The “Member of” column shows group role memberships, which we’ll cover later.

Create roles using CREATE ROLE for group roles or CREATE USER for login-capable roles. Choose based on whether the role needs to connect to the database.

Creating Roles from Within PostgreSQL

Use CREATE ROLE to create a role that cannot log in (suitable for group roles):

CREATE ROLE role_name;

By default, roles created this way have NOLOGIN, meaning they can’t connect to the database. They’re ideal for organizing permissions that multiple roles will inherit.

Create a group role:

CREATE ROLE demo_role;

Verify it was created:

\du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

The “Cannot login” attribute confirms demo_role is a group role, not a user role.

Creating Roles from the Command Line

Use the command createuser to create roles from the command line without entering psql. This is useful for automation scripts or when you prefer command-line workflows.

Exit psql:

\q

Switch to the postgres system account:

sudo -i -u postgres

Create a role interactively:

createuser --interactive

The --interactive flag prompts for role attributes. Answer the prompts:

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

Answering “n” to all creates a basic role with login privileges but no special permissions—perfect for application users.

Return to psql to verify:

psql

Compare the roles:

\du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {}

test_user has no attributes listed, which means it can log in (unlike demo_role) but has no special privileges. This is the default for roles created with createuser.

Creating Roles with Specific Privileges

Specify privileges at creation time using the WITH clause. This is more secure than creating a role and modifying it later, as it ensures the role starts with the correct permissions.

To set specific privileges as you create a role, use the following pattern:

CREATE ROLE role_name WITH privilege_options;

The WITH clause allows you to specify multiple attributes when creating the role, such as LOGIN, SUPERUSER, CREATEDB, or PASSWORD. You can combine multiple options separated by spaces.

View all available options:

\h CREATE ROLE

The \h meta-command displays help information for SQL commands. This shows you all available options for CREATE ROLE, including privilege settings and their descriptions.

Create a role with login privileges:

CREATE ROLE demo_role WITH LOGIN;

This creates a role that can connect to the database. The LOGIN attribute is required for roles that need to authenticate and establish database sessions.

Alternatively, you can use CREATE USER, which automatically grants LOGIN:

CREATE USER role_name;

The CREATE USER command is a convenience alias that automatically includes the LOGIN privilege, making it faster to create roles that can connect to the database.

This is equivalent to CREATE ROLE role_name WITH LOGIN;.

Modifying Role Privileges

Modify existing roles with ALTER ROLE without dropping and recreating them. This is essential when security requirements change or you need to adjust permissions.

Change role attributes:

ALTER ROLE role_name WITH attribute_options;

You can change any attribute: LOGIN, NOLOGIN, CREATEDB, CREATEROLE, PASSWORD, etc.

Example: Convert a user role to a group role by removing login privileges:

ALTER ROLE demo_role WITH NOLOGIN;

Verify the change:

\du demo_role

You’ll see “Cannot login” in the attributes. To restore login capability:

ALTER ROLE demo_role WITH LOGIN;

Common use cases:

• Disable a compromised account: ALTER ROLE username WITH NOLOGIN;
• Enable database creation: ALTER ROLE developer WITH CREATEDB;
• Change password: ALTER ROLE username WITH PASSWORD 'new_password';
• Revoke dangerous privileges: ALTER ROLE app_user WITH NOCREATEROLE;

Deleting Roles

Remove roles with DROP ROLE, but only after transferring ownership of any objects they own.

Drop a role:

DROP ROLE role_name;

PostgreSQL prevents dropping roles that own database objects. If you see “cannot be dropped because some objects depend on it”, transfer ownership first:

ALTER TABLE table_name OWNER TO new_owner;
ALTER SEQUENCE sequence_name OWNER TO new_owner;
-- Repeat for all owned objects

Then drop the role. For scripts, use IF EXISTS to avoid errors if the role doesn’t exist:

DROP ROLE IF EXISTS role_name;

Privileges control what actions roles can perform on database objects. By default, only the object owner and superusers can access newly created objects. Grant privileges to other roles to allow access.

Understanding Privilege Types

PostgreSQL privileges control specific operations on database objects. Choose the minimum privileges needed for each role’s purpose.

Table-level privileges:

• SELECT: Read data from tables. Required for queries, reporting, and read-only access.
• INSERT: Add new rows. Required for applications that create records.
• UPDATE: Modify existing rows. Required for applications that edit data.
• DELETE: Remove rows. Use carefully—this allows permanent data removal.
• TRUNCATE: Remove all rows instantly. More dangerous than DELETE—cannot be rolled back.
• REFERENCES: Create foreign key constraints pointing to the table. Needed when creating relationships.
• TRIGGER: Create triggers on the table. Usually only needed for advanced database logic.
• ALL PRIVILEGES: Grants all applicable privileges. Convenient but less secure—grant specific privileges when possible.

Schema-level privileges:

• USAGE: Access objects in the schema. Required before granting table privileges.
• CREATE: Create new objects (tables, sequences, etc.) in the schema. Grant carefully—allows creating arbitrary objects.

Database-level privileges:

• CONNECT: Connect to the database. Required for any database access.
• CREATE: Create schemas in the database. Usually only needed for setup scripts.
• TEMPORARY: Create temporary tables. Rarely needed for application roles.

Granting Table Privileges

Grant privileges on specific tables when you need fine-grained control or when roles only need access to certain tables.

Grant a specific privilege:

GRANT privilege_type ON table_name TO role_name;

Create a sample table:

CREATE TABLE demo (
    name varchar(25),
    id serial,
    start_date date
);

Grant UPDATE only to demo_role:

GRANT UPDATE ON demo TO demo_role;

This allows demo_role to modify existing rows but not read, insert, or delete. This demonstrates granular permissions, which are useful for audit roles or restricted access scenarios.

Grant all privileges to test_user:

GRANT ALL ON demo TO test_user;

ALL grants SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER. Use this sparingly—it’s convenient but less secure than specific privileges.

GRANT INSERT ON demo TO PUBLIC;  -- Avoid in production

REVOKE INSERT ON demo FROM PUBLIC;

Viewing Granted Privileges

Audit privileges with \z to see exactly who has access to what. This is essential for troubleshooting permission errors and verifying security.

View privileges on a table:

\z table_name

For more detail (including column-level privileges and row security policies):

\z+

Example output for the demo table:

\z demo

                                      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)

Privilege code reference:

• a = INSERT
• r = SELECT (read)
• w = UPDATE (write)
• d = DELETE
• D = TRUNCATE
• x = REFERENCES
• t = TRIGGER

In the example above:

• postgres=arwdDxt means the postgres role has all privileges (a, r, w, d, D, x, t)
• demo_role=w means demo_role only has UPDATE (w)
• test_user=arwdDxt means test_user has all privileges
• =a means PUBLIC has INSERT (a) privilege

Granting Schema Privileges

Use schema-level grants when you need to grant access to multiple tables at once or allow roles to create objects in a schema.

Grant schema privileges:

GRANT privilege_type ON SCHEMA schema_name TO role_name;

Schema privileges affect all objects within that schema. Common use cases:

• Granting access to all tables in a schema (more efficient than granting on each table)
• Allowing roles to create new objects (useful for development or migration scripts)

Example: Grant USAGE and CREATE on the public schema:

GRANT USAGE, CREATE ON SCHEMA public TO demo_role;

• USAGE: Required to access objects in the schema. You must grant this before granting table privileges.
• CREATE: Allows creating new tables, sequences, and other objects. Grant carefully—this allows creating arbitrary objects.

When to use schema vs table grants:

• Schema grants: When a role needs access to many tables, or you want to grant access to future tables automatically (combined with ALTER DEFAULT PRIVILEGES).
• Table grants: When a role only needs access to specific tables, or you want fine-grained control over each table’s permissions.

Granting Database Privileges

Use database-level grants to control connection access and schema creation. These are the broadest privileges and should be granted carefully.

Grant database privileges:

GRANT privilege_type ON DATABASE database_name TO role_name;

Database privileges:

• CONNECT: Required to connect to the database. Without this, the role cannot establish a connection, even if it has table privileges.
• CREATE: Allows creating new schemas. Usually only needed for setup scripts or developers.
• TEMPORARY: Allows creating temporary tables. Rarely needed for application roles.

Example: Allow a role to connect and create schemas:

GRANT CONNECT, CREATE ON DATABASE postgres TO demo_role;

Important: CONNECT is required for any database access. If a role can’t connect, check this first:

\du role_name  -- Check if role exists
\z database_name  -- Check CONNECT privilege (not directly viewable with \z)

To verify CONNECT privilege, query the system catalog:

SELECT has_database_privilege('role_name', 'database_name', 'CONNECT');

Example: Setting Up a Read-Only Role for Reporting

Create a read-only role when you need to grant access for reporting, monitoring, or analytics without allowing data modification. This follows the principle of least privilege and prevents accidental data changes.

Complete workflow for a read-only application role:

-- 1) Create a login role with a strong password
CREATE ROLE app_ro WITH LOGIN PASSWORD 'strong_password';

-- 2) Grant permission to connect to the desired database
GRANT CONNECT ON DATABASE postgres TO app_ro;

-- 3) Allow usage of the target schema
GRANT USAGE ON SCHEMA public TO app_ro;

-- 4) Grant read (SELECT) rights on all current tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

-- 5) Ensure read (SELECT) rights are automatically granted on new tables in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_ro;

Why each step matters:

• Step 1: Creates a role that can connect (required for any access)
• Step 2: CONNECT privilege is required to establish a connection to the database
• Step 3: USAGE on the schema is required before you can grant table privileges
• Step 4: Grants SELECT on all existing tables
• Step 5: Ensures new tables automatically grant SELECT to this role (critical for production)

Verify the setup:

\du app_ro  -- Check role exists and has LOGIN
\z public.your_table_name  -- Verify SELECT privilege on a specific table

Example: Setting Up a Web Application Role

Create a role for a web application that needs to read and write data, but shouldn’t be able to drop tables or modify schema structure.

Complete workflow for a typical web application:

-- 1) Create the application role with login and password
CREATE ROLE webapp WITH LOGIN PASSWORD 'secure_password_here';

-- 2) Grant database connection
GRANT CONNECT ON DATABASE your_database TO webapp;

-- 3) Grant schema usage (required before table privileges)
GRANT USAGE ON SCHEMA public TO webapp;

-- 4) Grant read/write privileges on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp;

-- 5) Grant usage on all existing sequences (needed for serial/identity columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO webapp;

-- 6) Set default privileges for future tables and sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO webapp;

What this role can do:

• Connect to the database
• Read from all tables (SELECT)
• Insert new rows (INSERT)
• Update existing rows (UPDATE)
• Delete rows (DELETE)

What this role cannot do:

• Drop tables (DROP TABLE requires ownership or DROP privilege)
• Create tables (no CREATE on schema)
• Modify table structure (ALTER TABLE requires ownership)
• Truncate tables (no TRUNCATE privilege)

This is the standard setup for most web applications (Django, Rails, Laravel, etc.).

Revoking Privileges

Remove privileges with REVOKE when security requirements change or roles no longer need access. This is the inverse of GRANT and follows the same syntax.

Revoke a specific privilege:

REVOKE privilege_type ON object_name FROM role_name;

Example: Remove INSERT from PUBLIC (if you mistakenly granted it):

REVOKE INSERT ON demo FROM PUBLIC;

After this, only roles with explicitly granted INSERT privileges can add rows.

Revoke all privileges:

REVOKE ALL ON demo FROM role_name;

This removes all privileges the role had on the object. Use this when completely removing a role’s access.

Role inheritance allows roles to automatically inherit privileges from the group roles to which they belong. This simplifies permission management by organizing roles into groups and granting privileges to the group rather than individual roles.

Understanding Role Inheritance

By default, roles inherit privileges from the group roles to which they belong. When you add a role to a group, it automatically gains the group’s privileges (if INHERIT is enabled, which is the default).

Use group roles when:

• Multiple roles need the same privileges (e.g., all developers need CREATE on a schema)
• You want to manage permissions centrally (grant to the group, not each role)
• Roles have overlapping access needs (e.g., all application users need read access to reference tables)

Avoid group roles when:

• Each role needs unique privileges (individual grants are clearer)
• Security requirements are very strict (inheritance can make privilege auditing more complex)
• You need to track exactly which role performed an action (inherited privileges may obscure the actual role)

Creating Group Roles

Create group roles with CREATE ROLE (which defaults to NOLOGIN). Group roles organize permissions that multiple roles will share.

Create a group role:

CREATE ROLE temporary_users;

Since CREATE ROLE defaults to NOLOGIN, this role can’t connect to the database, which is perfect for a group role.

Add members to the group:

GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

The syntax GRANT group_role TO member_role adds roles as members. Both demo_role and test_user now inherit any privileges granted to temporary_users.

Verify memberships:

\du

The “Member of” column shows group memberships. You should see {temporary_users} for both demo_role and test_user.

                                          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}

Using SET ROLE

Use SET ROLE to temporarily act as a group role when creating objects that should be owned by the group. This is useful for shared resources or when you want objects owned by a group rather than an individual role.

Switch to a group role:

SET ROLE temporary_users;

Any objects created while this role is active are owned by temporary_users, not your original role:

CREATE TABLE hello (
    name varchar(25),
    id serial,
    start_date date
);

Verify ownership:

\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 hello table is owned by temporary_users, not your original role. Return to your original role:

RESET ROLE;

Enabling Automatic Inheritance

Inheritance is enabled by default. Roles automatically use privileges from group roles they belong to without needing to SET ROLE.

Verify inheritance is enabled:

SELECT rolname, rolinherit FROM pg_roles WHERE rolname = 'test_user';

If rolinherit is t (true), inheritance is enabled. To explicitly enable it:

ALTER ROLE test_user INHERIT;

With inheritance enabled, test_user can use the privileges granted to temporary_users automatically. For example, if you grant SELECT on a table to temporary_users, test_user can query that table without any additional grants.

Removing Group Roles

Transfer ownership of all objects before dropping a group role. PostgreSQL prevents dropping roles that own database objects.

Transfer ownership:

ALTER TABLE hello OWNER TO demo_role;

For multiple objects, use REASSIGN OWNED to transfer all at once:

REASSIGN OWNED BY temporary_users TO demo_role;

This transfers ownership of all tables, sequences, functions, and other objects owned by temporary_users to demo_role.

Verify the role no longer owns objects:

\d

Once all objects are transferred, drop the group role:

DROP ROLE temporary_users;

Securing PostgreSQL on a VPS requires configuring authentication, network access, and firewall rules. These steps protect your database from unauthorized access.

Configuring Authentication

Configure authentication in pg_hba.conf to control who can connect and how they authenticate. This file determines connection rules for your PostgreSQL instance.

View the current configuration:

sudo -u postgres cat /etc/postgresql/*/main/pg_hba.conf

The file contains rules in this format: TYPE DATABASE USER ADDRESS METHOD. Rules are processed from top to bottom, so order matters.

Authentication methods:

• peer: Uses the operating system username (default for local connections). Requires system username to match PostgreSQL role name.
• md5: Password authentication using MD5 hashing. Legacy method, still supported but less secure.
• scram-sha-256: Modern password authentication (recommended). More secure than md5.
• trust: No authentication required. Never use in production—only for local development.

Edit the configuration:

sudo nano /etc/postgresql/*/main/pg_hba.conf

For production, configure password authentication for local connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

These lines require password authentication (scram-sha-256) for:

• IPv4 connections from localhost (127.0.0.1/32)
• IPv6 connections from localhost (::1/128)

The /32 and /128 are CIDR notations specifying single IP addresses.

Reload PostgreSQL to apply changes:

sudo systemctl reload postgresql

reload applies changes without stopping the service, preserving active connections. Use restart only if reload doesn’t work.

Setting Role Passwords

Set passwords for roles that connect remotely or use password authentication. Passwords are stored encrypted in PostgreSQL’s system catalogs.

Set a password:

ALTER ROLE role_name WITH PASSWORD 'secure_password';

For better security, use the interactive \password command to avoid exposing the password in shell history:

\password role_name

If you omit role_name, it sets the password for the currently connected role. The command prompts twice for confirmation and doesn’t display the password on screen.

Configuring Firewall Rules

Restrict network access to PostgreSQL using firewall rules. By default, PostgreSQL listens only on 127.0.0.1 (localhost), which prevents remote connections.

Check which interfaces PostgreSQL is listening on:

sudo netstat -plnt | grep postgres

Look for 127.0.0.1:5432 in the output, which confirms PostgreSQL is only accepting local connections. If you see 0.0.0.0:5432, PostgreSQL is listening on all interfaces.

If you need remote access (only enable if necessary):

1. Configure PostgreSQL to listen on specific interfaces:

   sudo nano /etc/postgresql/*/main/postgresql.conf
   

   Find and modify:

   listen_addresses = 'localhost'  # Change to '*' or specific IP
   

   Options:

   • 'localhost': Only local connections (default, most secure)
   • '*': All interfaces (use with extreme caution)
   • '192.168.1.100': Specific IP address (more secure than *)

2. Add firewall rule to allow connections only from trusted IPs:

   sudo ufw allow from trusted_ip_address to any port 5432
   

   Replace trusted_ip_address with the actual client IP. This restricts access at the firewall level before PostgreSQL even sees the connection attempt.

Limiting Connection Attempts

Prevent brute-force attacks by limiting connection attempts. Configure pg_hba.conf with connection limits or use a firewall tool like fail2ban.

PostgreSQL uses peer authentication by default for local connections, requiring your system username to match your PostgreSQL role name. To connect as a different role, use password authentication through the network interface.

Setting Up Password Authentication

Set a password for the role:

\password test_user

The command prompts twice for confirmation and doesn’t display the password. Exit psql:

\q

Connecting with Specific Credentials

Connect using explicit parameters to override peer authentication:

psql -U user_name -d database_name -h 127.0.0.1 -W

Parameters:

• -U: Username to connect as
• -d: Database name
• -h 127.0.0.1: Connect through the loopback interface (enables password authentication)
• -W: Prompt for password

Example: Connect as test_user:

psql -U test_user -d postgres -h 127.0.0.1 -W

Permission errors in PostgreSQL typically arise from missing privileges, incorrect authentication configurations, or incorrect role attributes. This section covers the most common issues and their solutions.

Role Exists but Login Fails

Problem: You see FATAL: role "username" does not exist or password authentication failed when trying to connect.

Solution: Check three things in order:

1. Verify the role exists and has LOGIN privilege:

   \du username
   

   If the role doesn’t exist, create it:

   CREATE ROLE username WITH LOGIN PASSWORD 'secure_password';
   

   If it exists but shows “Cannot login”, enable login:

   ALTER ROLE username WITH LOGIN;
   

2. Check pg_hba.conf allows the connection:

   sudo -u postgres cat /etc/postgresql/*/main/pg_hba.conf | grep -v "^#"
   

   Ensure there’s a matching rule for your connection type. For password authentication:

   host all username 127.0.0.1/32 scram-sha-256
   

3. Reload PostgreSQL after configuration changes:

   sudo systemctl reload postgresql
   

Application Connects but Fails with permission denied for table

Problem: Your application connects successfully but queries fail with permission denied for table table_name.

Root cause: The role lacks explicit privileges on the table or schema. PostgreSQL defaults to restrictive permissions—roles can’t read or write unless explicitly granted.

Solution: Grant the required privileges:

-- Grant schema usage (required first)
GRANT USAGE ON SCHEMA public TO username;

-- Grant table privileges
GRANT SELECT, INSERT, UPDATE ON table_name TO username;

For multiple tables, grant on all tables in the schema:

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

For future tables, set default privileges so new tables automatically grant access:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO username;

3. Role Cannot Create Tables or Databases

Error: ERROR: permission denied to create database or ERROR: permission denied to create table

Diagnosis: The role lacks the CREATEDB attribute or CREATE privilege on the schema.

Fix:

ALTER ROLE username WITH CREATEDB;
GRANT CREATE ON SCHEMA public TO username;

To verify:

\du username

4. User Can’t Connect Remotely (Timeout or Connection Refused)

Why this happens: PostgreSQL, by default, listens only on the local interface (127.0.0.1). If remote connections fail, it’s almost always due to configuration in postgresql.conf, firewall rules, or missing host entries in pg_hba.conf.

Solution:

1. Edit PostgreSQL’s main configuration:

   sudo nano /etc/postgresql/*/main/postgresql.conf
   

   Ensure this line is uncommented:

   listen_addresses = '*'
   

2. In pg_hba.conf, add a secure host entry:

   host    all    all    trusted_ip/32    scram-sha-256
   

3. Allow access in the firewall:

   sudo ufw allow from trusted_ip to any port 5432
   

4. Reload PostgreSQL:

   sudo systemctl reload postgresql
   

5. Application Errors After Migration or Restore

Symptom: Roles appear valid, but queries fail with “permission denied” or missing ownership.

Cause: Object ownership isn’t automatically preserved during dump/restore unless roles are recreated first.

Resolution:

1. Before restoring, recreate all roles:

   CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
   

2. Restore the database dump using a role with superuser privileges:

   psql -U postgres -d target_db -f backup.sql
   

3. Reassign ownership:

   REASSIGN OWNED BY old_owner TO app_user;
   

6. Read-Only Roles Still Failing to Access New Tables

Root cause: The ALTER DEFAULT PRIVILEGES command wasn’t executed by the object owner.

Solution: Re-run the command while logged in as the schema owner:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_ro;

Then verify privileges:

\z public.*

7. Connection Works Locally but Fails for Non-Superusers

Typical causes:

• The database’s CONNECT privilege hasn’t been granted.
• Row-Level Security (RLS) is active and blocking queries.

Fix:

GRANT CONNECT ON DATABASE db_name TO role_name;
ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

8. Slow or Hanging Connections After Adding SSL

Why: SSL negotiation or incorrect certificate paths in postgresql.conf.

How to verify: Check PostgreSQL logs:

sudo journalctl -u postgresql | grep SSL

Ensure configuration points to valid certs:

ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert.key'

Practical Debugging Checklist

• Use \du to audit role attributes and memberships.

• Use \z to inspect privileges on objects.

• Review pg_hba.conf for missing or misordered rules.

• Check the PostgreSQL logs (/var/log/postgresql/postgresql-*.log) for precise authentication reasons.

• After any configuration change, reload PostgreSQL using:

  sudo systemctl reload postgresql
  

These scenarios represent the most common, high-impact permission and connectivity challenges developers face in PostgreSQL on VPS or cloud setups. By diagnosing issues through privilege inspection and configuration hierarchy, you can maintain secure, stable, and predictable access control across environments.

What is the difference between a role and a user in PostgreSQL?

In PostgreSQL, “role” and “user” are essentially the same concept. The CREATE USER command is an alias for CREATE ROLE WITH LOGIN. The primary distinction is practical: roles with LOGIN privilege can connect to the database (function as users), while roles without LOGIN function as groups for organizing permissions.

How do I grant a user read-only access to a PostgreSQL database?

Grant SELECT privilege on specific tables:

GRANT SELECT ON table_name TO role_name;

For all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

For future tables in a schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO role_name;

How do I revoke privileges from a role in PostgreSQL?

Use the REVOKE command:

REVOKE privilege_type ON object_name FROM role_name;

Revoke all privileges:

REVOKE ALL ON object_name FROM role_name;

What are the best practices for managing PostgreSQL roles on a VPS?

1. Use the principle of least privilege: Grant only the minimum privileges necessary for each role.

2. Organize with group roles: Create group roles for common permission sets and add individual roles as members.

3. Use strong passwords: Set secure passwords for all roles that can log in, especially in production.

4. Configure authentication properly: Use scram-sha-256 or md5 authentication in pg_hba.conf instead of trust for production.

5. Restrict network access: Use firewall rules to limit PostgreSQL connections to trusted IP addresses.

6. Regular audits: Periodically review role privileges using \du and \z to ensure they match your security requirements.

How can I allow a remote user to connect securely to PostgreSQL?

1. Configure postgresql.conf: Set listen_addresses to include your server’s IP or * (use with caution).

2. Update pg_hba.conf: Add a host entry with secure authentication:

   host    database_name    role_name    client_ip/32    scram-sha-256
   

3. Set a strong password: Use ALTER ROLE to set a secure password.

4. Configure firewall: Allow connections only from trusted IP addresses:

   sudo ufw allow from trusted_ip to any port 5432
   

5. Reload PostgreSQL: Apply configuration changes:

   sudo systemctl reload postgresql
   

How do role inheritance and group roles work in PostgreSQL?

Role inheritance allows a role to automatically receive privileges from group roles it’s a member of. When you add a role to a group using GRANT group_role TO member_role, the member role inherits the group’s privileges (if INHERIT is enabled, which is the default).

Example:

CREATE ROLE developers;
GRANT CREATE ON SCHEMA public TO developers;
GRANT developers TO app_user;

The app_user role can now create objects in the public schema because it inherits privileges from developers.

How do I view all roles and privileges in PostgreSQL?

View all roles:

\du

View privileges on a specific object:

\z object_name

View detailed role information:

SELECT * FROM pg_roles WHERE rolname = 'role_name';

View privileges granted to a role:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE grantee = 'role_name';

You now understand how to manage roles and permissions in PostgreSQL on a VPS. These skills enable you to provide applications with the necessary database access while maintaining security and separation between different services.

Key concepts covered include creating and modifying roles, granting and revoking privileges at different levels, using role inheritance for efficient permission management, and securing your PostgreSQL installation on a VPS.

For more information about PostgreSQL and related topics, consider these resources:

• How To Secure PostgreSQL on an Ubuntu VPS
• How To Install PostgreSQL on Ubuntu 22.04 Quickstart
• SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
• DigitalOcean Managed Databases for PostgreSQL