KAHIBARO
Discord Login Register ☰
☰

11.5. Permissions

Table of Contents

Understanding Permissions in PostgreSQL

PostgreSQL has a powerful permission system that controls who can do what with which database objects. As a backend developer you must understand this so your application does not accidentally expose or destroy data.

This chapter focuses only on PostgreSQL permissions. General authentication and authorization concepts are covered in other chapters.

Roles, Users, and Groups

PostgreSQL uses roles to represent both individual users and groups.

You create and manage permissions for roles, not directly for connections.

Creating Roles

You can create a role with SQL:

sql 
CREATE ROLE app_user;
CREATE ROLE app_admin;

By default, these roles cannot log in. To make a role that can log in, you give it the LOGIN attribute:

sql 
CREATE ROLE backend_user WITH LOGIN PASSWORD 'very_secret';

You can also use CREATE USER which is just a shortcut for CREATE ROLE ... LOGIN:

sql 
CREATE USER api_user WITH PASSWORD 'another_secret';

Functionally, USER and ROLE are the same. USER implies LOGIN.

Role Attributes

Roles have special attributes that control high-level power:

AttributeDescriptionExample use
LOGINCan authenticate and connectApplication accounts, human users
SUPERUSERBypass all permission checksDatabase admin only
CREATEDBCan create databasesDev ops, database admin
CREATEROLECan create and manage rolesDatabase admin
INHERITInherit privileges from roles it is a member ofUsually left on (default)
REPLICATIONCan initiate replicationReplication users
BYPASSRLSBypass row level securityTrusted admin roles

Example:

sql 
CREATE ROLE app_admin
  WITH LOGIN
       CREATEDB
       CREATEROLE
       PASSWORD 'admin_password';

Rule: Never give SUPERUSER to an application role. Superusers can do anything, including dropping databases and reading all data.

GRANT and REVOKE Basics

Most permission management in PostgreSQL uses two commands:

You use them on specific objects such as databases, schemas, tables, sequences, and functions.

Basic pattern:

sql 
GRANT privilege_list
ON object_type object_name
TO role_name;
REVOKE privilege_list
ON object_type object_name
FROM role_name;

Common Privileges by Object Type

Object typeCommon privileges
DATABASECONNECT, CREATE, TEMP
SCHEMAUSAGE, CREATE
TABLESELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
SEQUENCEUSAGE, SELECT, UPDATE
FUNCTIONEXECUTE
TYPEUSAGE

Examples:

Give SELECT and INSERT on a table to a role:

sql 
GRANT SELECT, INSERT
ON TABLE public.users
TO app_user;

Remove INSERT:

sql 
REVOKE INSERT
ON TABLE public.users
FROM app_user;

Give a role permission to connect to a database:

sql 
GRANT CONNECT
ON DATABASE myapp
TO app_user;

Remove that permission:

sql 
REVOKE CONNECT
ON DATABASE myapp
FROM app_user;

Public Role

PostgreSQL has a special built-in role called PUBLIC.

Example:

sql 
GRANT CONNECT
ON DATABASE myapp
TO PUBLIC;

This means all users can connect to myapp.

Rule: Be careful with PUBLIC. If you grant SELECT on a table to PUBLIC, every user can read that table.

Database Level Permissions

Database-level permissions control whether a role can access a particular database.

The main database-level privileges are:

Example: restrict who can connect to a database.

sql 
-- Remove connect rights from everyone
REVOKE CONNECT ON DATABASE myapp FROM PUBLIC;
-- Allow only specific roles
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT CONNECT ON DATABASE myapp TO app_admin;

Try it from psql as a role with no permission:

bash 
psql -U random_user -d myapp

You will see a permission denied error if random_user does not have CONNECT.

Schema Level Permissions

Schemas are namespaces that group tables, functions, and other objects.

Schema privileges:

Example: create a schema and control access.

sql 
CREATE SCHEMA app AUTHORIZATION app_admin;

Give USAGE to normal application users so they can see and use tables in that schema:

sql 
GRANT USAGE
ON SCHEMA app
TO app_user;

Allow only admins to create new tables and functions there:

sql 
REVOKE CREATE
ON SCHEMA app
FROM PUBLIC;
GRANT CREATE
ON SCHEMA app
TO app_admin;

Without USAGE, a role cannot even see that a schema exists.

Table and Column Permissions

Most of the time, you manage permissions on tables.

Table privileges:

Basic Table Permissions

sql 
-- Allow reading from the users table
GRANT SELECT
ON TABLE app.users
TO app_user;
-- Allow full access to admins
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLE app.users
TO app_admin;

If a role does not have SELECT, tries to read:

sql 
SELECT * FROM app.users;

It will get:

text 
ERROR:  permission denied for table users

Column-Level Permissions

You can restrict permissions to specific columns:

sql 
-- app_analytics can see only id and created_at
GRANT SELECT (id, created_at)
ON app.users
TO app_analytics;

Now:

sql 
SET ROLE app_analytics;
SELECT id, created_at FROM app.users;      -- works
SELECT id, email FROM app.users;          -- permission denied
SELECT * FROM app.users;                  -- permission denied

Column-level permissions apply only to SELECT, INSERT, and UPDATE.

Sequence and Function Permissions

Sequence Permissions

Sequences generate auto-increment values, often for primary keys.

Privileges:

If you have a table like:

sql 
CREATE TABLE app.users (
  id bigserial PRIMARY KEY,
  email text NOT NULL
);

PostgreSQL creates a sequence, for example app.users_id_seq.

To let app_user insert rows, you must allow access to the sequence as well:

sql 
GRANT USAGE, SELECT
ON SEQUENCE app.users_id_seq
TO app_user;

If you forget this, inserting may fail with a permission error on the sequence.

Function Permissions

Functions can encapulate logic and access tables. They have an EXECUTE privilege.

Example function:

sql 
CREATE FUNCTION app.get_user_count()
RETURNS integer
LANGUAGE sql
AS $$
  SELECT COUNT(*) FROM app.users;
$$;

By default, only the function owner (often the role that created it) can execute it.

Allow app roles to use it:

sql 
GRANT EXECUTE
ON FUNCTION app.get_user_count()
TO app_user;

Now app_user can run:

sql 
SELECT app.get_user_count();

This is useful to give restricted or computed data access without exposing full table access.

Managing Permissions with Role Hierarchies

Instead of assigning permissions to each user separately, you usually:

  1. Define group roles that represent types of access.
  2. Grant privileges to these group roles.
  3. Add users or application roles as members of these group roles.

This keeps your permission setup clean and easier to maintain.

Example Hierarchy

sql 
-- Group roles (no login)
CREATE ROLE app_readonly;
CREATE ROLE app_writer;
CREATE ROLE app_admin;
-- Grant permissions to group roles
-- Read-only access to public data
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
-- Read-write access to main tables
GRANT app_readonly TO app_writer;
GRANT INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_writer;
-- Full access for admins
GRANT app_writer TO app_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT ALL ON TABLES TO app_admin;

Now create actual users and attach them to these group roles:

sql 
CREATE USER api_readonly WITH PASSWORD 'x';
CREATE USER api_writer WITH PASSWORD 'y';
CREATE USER db_admin WITH PASSWORD 'z';
GRANT app_readonly TO api_readonly;
GRANT app_writer   TO api_writer;
GRANT app_admin    TO db_admin;

The individual users inherit the privileges from group roles.

Viewing Role Membership

To see which roles are members of which:

sql 
SELECT
  rolname AS role_name,
  member.rolname AS member_name
FROM pg_roles role
JOIN pg_auth_members m
  ON role.oid = m.roleid
JOIN pg_roles member
  ON member.oid = m.member;

In psql, you can also run:

sql 
\du

to see a list of roles and some attributes.

Ownership and Default Privileges

Object Ownership

Every object in PostgreSQL has an owner:

Example:

sql 
-- Change owner of a table
ALTER TABLE app.users OWNER TO app_admin;
-- Change owner of a schema
ALTER SCHEMA app OWNER TO app_admin;

For applications, it is common to:

Default Privileges

When you create new tables, sequences, or functions, they inherit default privileges.

By default:

You can change this behavior with ALTER DEFAULT PRIVILEGES.

Example: ensure every new table in schema app is readable by app_readonly and writable by app_writer.

Execute this as the owner (for example app_admin):

sql 
-- For tables
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT INSERT, UPDATE, DELETE ON TABLES TO app_writer;
-- For sequences (for serial / bigserial)
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

Now, when you create a new table:

sql 
CREATE TABLE app.orders (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  total_cents integer NOT NULL
);

app_readonly and app_writer will already have the correct permissions.

Without this, every time you add a new table you would need to run GRANT manually.

Practical Patterns for Backend Apps

Here are a few patterns you can apply in real projects.

Pattern 1: Strict separation of owner and app role

  1. Create an owner role that is not used by the application:
sql 
   CREATE ROLE app_owner NOLOGIN;
   CREATE ROLE app_readonly NOLOGIN;
   CREATE ROLE app_writer NOLOGIN;
  1. Create login-only application roles that inherit group permissions:
sql 
   CREATE USER app_backend WITH PASSWORD 'secret';
   GRANT app_writer TO app_backend;
  1. Make app_owner own all objects:
sql 
   CREATE SCHEMA app AUTHORIZATION app_owner;
  1. As app_owner, set default privileges:
sql 
   ALTER DEFAULT PRIVILEGES IN SCHEMA app
     GRANT SELECT ON TABLES TO app_readonly;
   ALTER DEFAULT PRIVILEGES IN SCHEMA app
     GRANT INSERT, UPDATE, DELETE ON TABLES TO app_writer;
  1. In your application, use app_backend as the connection user.

Now, even if your application code is compromised, the attacker cannot change table definitions or drop the schema, since the app user is not the owner.

Pattern 2: Read-only analytics role

You often need a role for reporting tools or manual analyses.

sql 
CREATE ROLE analytics NOLOGIN;
GRANT USAGE ON SCHEMA app TO analytics;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO analytics;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO analytics;
CREATE USER analytics_user WITH PASSWORD 'analytics_pw';
GRANT analytics TO analytics_user;

Use analytics_user in BI tools. It cannot modify data, only read it.

Common Permission Problems and How to Fix Them

Problem: Application cannot insert into table

Error:

text 
ERROR:  permission denied for table users

Check:

sql 
\dp app.users           -- in psql, shows privileges on the table

Fix:

sql 
GRANT INSERT, SELECT
ON TABLE app.users
TO app_writer;          -- or your app role

Also check the sequence:

sql 
\ds app.users_id_seq
GRANT USAGE, SELECT
ON SEQUENCE app.users_id_seq
TO app_writer;

Problem: New tables not accessible to app

You recently created a new table, but the application gets permission errors only on that table.

Cause: ALTER DEFAULT PRIVILEGES was not set, so the new table did not receive the same grants.

Fix:

  1. Grant permissions on existing table:
sql 
   GRANT SELECT, INSERT, UPDATE, DELETE
   ON TABLE app.new_table
   TO app_writer;
  1. Set default privileges for future tables (if not already):
sql 
   ALTER DEFAULT PRIVILEGES IN SCHEMA app
     GRANT SELECT, INSERT, UPDATE, DELETE
     ON TABLES
     TO app_writer;

Problem: Role can see schema, but cannot access table

Error:

text 
ERROR:  permission denied for schema app

or

text 
ERROR:  permission denied for table users

You need:

Fix:

sql 
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON TABLE app.users TO app_user;

Summary

With these tools, you can design database permissions that keep your application secure and maintainable.

Views: 17

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3540 / 24h
Impressum / Imprint
Datenschutz / Data Policy