Role and Permission Management

Beginner10 min

Create and manage PostgreSQL roles with granular permissions for secure multi-user database access control.

Prerequisites

-PostgreSQL installed
-Superuser or admin access to the database

Steps

Create a new role with login

Create a database user role with a password and login capability.

$ psql -U postgres -c "CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password' VALID UNTIL '2027-01-01';"

Always set VALID UNTIL for service accounts to enforce credential rotation.

Grant database-level permissions

Allow the role to connect to a specific database.

$ psql -U postgres -c "GRANT CONNECT ON DATABASE mydb TO app_user;"

Grant schema and table permissions

Give the role read/write access to tables in a schema.

$ psql -U postgres -d mydb -c "GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;"

Set default privileges for future tables

Ensure the role automatically gets permissions on tables created in the future.

$ psql -U postgres -d mydb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;"

Create a read-only role

Create a restricted role that can only read data.

$ psql -U postgres -d mydb -c "CREATE ROLE readonly_user WITH LOGIN PASSWORD 'readonly_pass'; GRANT CONNECT ON DATABASE mydb TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;"

List all roles and their attributes

View existing roles with their permissions and settings.

$ psql -U postgres -c "\du+"

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL User and Role Management
set -euo pipefail

DB="mydb"
USER="postgres"

echo "=== Creating application roles ==="
psql -U "$USER" -c "
-- Read-write application role
CREATE ROLE app_readwrite WITH LOGIN PASSWORD 'rw_secure_pass';
-- Read-only reporting role
CREATE ROLE app_readonly WITH LOGIN PASSWORD 'ro_secure_pass';
-- Admin role (no login, used as group)
CREATE ROLE app_admin NOLOGIN;
"

echo ""
echo "=== Granting database access ==="
psql -U "$USER" -c "
GRANT CONNECT ON DATABASE $DB TO app_readwrite;
GRANT CONNECT ON DATABASE $DB TO app_readonly;
GRANT CONNECT ON DATABASE $DB TO app_admin;
"

echo ""
echo "=== Granting schema permissions ==="
psql -U "$USER" -d "$DB" -c "
-- Read-write: full CRUD
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- Read-only: SELECT only
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

-- Admin inherits readwrite + can create/drop
GRANT app_readwrite TO app_admin;
"

echo ""
echo "=== Current roles ==="
psql -U "$USER" -c "\du+"

echo ""
echo "=== Table permissions ==="
psql -U "$USER" -d "$DB" -c "
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
ORDER BY grantee, table_name;
"

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL User and Role Management set -euo pipefail DB="mydb" USER="postgres" echo "=== Creating application roles ===" psql -U "$USER" -c " -- Read-write application role CREATE ROLE app_readwrite WITH LOGIN PASSWORD 'rw_secure_pass'; -- Read-only reporting role CREATE ROLE app_readonly WITH LOGIN PASSWORD 'ro_secure_pass'; -- Admin role (no login, used as group) CREATE ROLE app_admin NOLOGIN; " echo "" echo "=== Granting database access ===" psql -U "$USER" -c " GRANT CONNECT ON DATABASE $DB TO app_readwrite; GRANT CONNECT ON DATABASE $DB TO app_readonly; GRANT CONNECT ON DATABASE $DB TO app_admin; " echo "" echo "=== Granting schema permissions ===" psql -U "$USER" -d "$DB" -c " -- Read-write: full CRUD GRANT USAGE ON SCHEMA public TO app_readwrite; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite; -- Read-only: SELECT only GRANT USAGE ON SCHEMA public TO app_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly; -- Admin inherits readwrite + can create/drop GRANT app_readwrite TO app_admin; " echo "" echo "=== Current roles ===" psql -U "$USER" -c "\du+" echo "" echo "=== Table permissions ===" psql -U "$USER" -d "$DB" -c " SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges WHERE table_schema = 'public' ORDER BY grantee, table_name; "