PostgreSQL Database Setup

This guide explains how to set up a PostgreSQL database user, role, and permissions for your application.

Setup Script

The following script creates a dedicated PostgreSQL role and user with appropriate privileges on the application database:

DO $$
    DECLARE
        username VARCHAR := '<app_username>';
        role_name VARCHAR := '<app_role>';
        db_name VARCHAR := '<app_database>';
        user_password VARCHAR := '<secure_password>';
    BEGIN
        -- Create role
        EXECUTE 'CREATE ROLE ' || role_name;
 
        -- Create user with password
        EXECUTE 'CREATE USER ' || username || ' WITH PASSWORD ''' || user_password || ''' IN ROLE ' || role_name;
 
        -- Grant privileges
        EXECUTE 'GRANT ALL PRIVILEGES ON DATABASE ' || db_name || ' TO ' || role_name;
    END $$;

Detailed Permissions Script

After creating the role and user, you may want to grant more specific permissions on the schema level. The following script grants detailed permissions to a role, ensuring it has full access to all objects in the public schema, including future objects:

DO $$
    DECLARE
        role_name VARCHAR := '<app_role>';
    BEGIN
        -- Grant schema-level permissions
        EXECUTE 'GRANT CREATE, USAGE ON SCHEMA public to ' || role_name;
        EXECUTE 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ' || role_name;
        EXECUTE 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ' || role_name;
        EXECUTE 'GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO ' || role_name;
 
        -- Ensure future tables automatically have the same permissions
        EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO ' || role_name;
        EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO ' || role_name;
        EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO ' || role_name;
 
        -- Grant usage on public schema
        EXECUTE 'GRANT CREATE ON SCHEMA public TO ' || role_name;
        EXECUTE 'GRANT USAGE ON SCHEMA public TO ' || role_name;
    END $$;

Components Explained

Variables

username: The PostgreSQL username (e.g., app_user) that will be used by the application to connect to the database
role_name: The PostgreSQL role name (e.g., app_role) that defines the permissions
db_name: The target database name (e.g., app_database) for the application
user_password: The password for the PostgreSQL user

Operations in Setup Script

Create Role: Creates a new PostgreSQL role with the name specified in role_name
Create User: Creates a new PostgreSQL user with the name specified in username, assigns the password from user_password, and adds it to the role specified in role_name
Grant Privileges: Grants all privileges on the database specified in db_name to the role specified in role_name

Operations in Detailed Permissions Script

Schema-Level Permissions: Grants specific permissions on the public schema:
- CREATE, USAGE: Allows the role to create objects and use the schema
- ALL PRIVILEGES ON ALL TABLES: Grants full access to all existing tables
- ALL PRIVILEGES ON ALL SEQUENCES: Grants full access to all existing sequences
- ALL PRIVILEGES ON ALL FUNCTIONS: Grants full access to all existing functions
Default Privileges for Future Objects: Sets up permissions that will automatically apply to objects created in the future:
- ALTER DEFAULT PRIVILEGES ... ON TABLES: Ensures the role has full access to any new tables
- ALTER DEFAULT PRIVILEGES ... ON SEQUENCES: Ensures the role has full access to any new sequences
- ALTER DEFAULT PRIVILEGES ... ON FUNCTIONS: Ensures the role has full access to any new functions
Additional Schema Permissions: Reinforces schema-level permissions:
- GRANT CREATE ON SCHEMA public: Allows the role to create objects in the public schema
- GRANT USAGE ON SCHEMA public: Allows the role to use the public schema

Prerequisites

PostgreSQL must be installed and running
The script must be executed by a PostgreSQL user with administrative privileges
The database specified in db_name must already exist before running this script

Security Considerations

The password is hardcoded in this script. For production environments, consider:
- Using environment variables
- Using PostgreSQL's password file
- Implementing a secrets management solution
Review the granted privileges to ensure they follow the principle of least privilege

Usage

Execute this script using the PostgreSQL client:

psql -U postgres -d postgres -f setup_database.sql

Or copy and paste the script directly into a PostgreSQL client session.

Post-Setup Verification

After running the script, verify the setup with:

-- Verify role creation
SELECT rolname FROM pg_roles WHERE rolname = '<app_role>';
 
-- Verify user creation
SELECT usename FROM pg_user WHERE usename = '<app_username>';
 
-- Verify database privileges (run as superuser)
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_catalog = '<app_database>';
 
-- Verify schema privileges
SELECT grantee, privilege_type, table_schema
FROM information_schema.role_usage_grants
WHERE table_schema = 'public' AND grantee = '<app_role>';
 
-- Verify table privileges in public schema
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_schema = 'public' AND grantee = '<app_role>';
 
-- Verify default privileges
SELECT defaclrole, defaclnamespace, defaclobjtype, defaclacl
FROM pg_default_acl
WHERE defaclnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

Postgresql Dump Snippets Tuning Postgresql