PostgreSQL - Administrator Role and SUPERUSER

 Step 1: Creating an Administrator Role with SUPERUSER Privileges (Standard PostgreSQL)

 In a self-managed PostgreSQL environment (on-premises or cloud VM), you can create an administrator role with full SUPERUSER privileges using:

 CREATE ROLE administrator SUPERUSER LOGIN PASSWORD 'StrongSecurePassword';

 This role will have:

• Full access to all databases

• Ability to create, alter, and drop any database objects

• Manage roles and permissions

• Access system catalogs and perform backups

• Enable or disable extensions

• Perform maintenance and configuration tasks

 Behavior in Amazon RDS for PostgreSQL

 In Amazon RDS, even though the master user is granted the rds_superuser role, it is NOT a true PostgreSQL superuser.

 AWS restricts certain superuser capabilities for security and managed service operations.

 Important Limitation:

Creating another user with the SUPERUSER attribute is NOT permitted in Amazon RDS, even if you are using the rds_superuser role.

 The SUPERUSER attribute is one of the restricted operations in RDS.

 Workaround / Alternatives in Amazon RDS

 

Although you cannot create a true SUPERUSER role in RDS, you can create a role with almost all administrative privileges by granting the rds_superuser role.

 Example:

 CREATE ROLE administrator NOLOGIN;

GRANT rds_superuser TO administrator;

 You can then grant this role to specific users as needed.

 

Key Takeaways

 

• True SUPERUSER roles are only available in self-managed PostgreSQL.

• Amazon RDS uses rds_superuser as a controlled alternative.

• SUPERUSER creation is blocked in RDS environments.

• Administrative access can still be delegated safely using role grants.

PostgreSQL - Read Write Role

 This guide explains how to create a PostgreSQL role that allows users to:

• Read data and schema
• View query execution plans
• Modify data (INSERT / UPDATE / DELETE) without schema changes
• Create and manage objects (CRUD) within their own schema only

Step 1: Create a Base Role (No Login)

CREATE ROLE read_write NOLOGIN;

Step 2: Grant Read Access on Shared Schema

GRANT USAGE ON SCHEMA public TO read_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_write;

Step 3: Allow Data Modification (Without Schema Changes)

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

Step 4: Ensure Future Tables Inherit Permissions

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

Step 5: Enable Query Performance Insights

ALTER ROLE read_write SET track_io_timing TO on;

Step 6: Create User with Personal Schema

CREATE ROLE rw_user LOGIN PASSWORD 'securepassword';
GRANT read_write TO rw_user;

CREATE SCHEMA AUTHORIZATION rw_user;

Conclusion

The user now has:
• Read and write access to shared schema tables
• No permission to alter shared schema structure
• Full CREATE / ALTER / DROP privileges within their own schema

This approach follows PostgreSQL security best practices by separating:
• Permission roles (NOLOGIN)
• User login roles
• Shared schema access
• Personal schema ownership

PostgreSQL - Read-Only Role

 

PostgreSQL Read-Only Role Setup 

Overview

This guide explains how to create a reusable READ_ONLY role in PostgreSQL that allows users to:

• Read data from tables

• Access schema objects

• Use temporary tables

 The approach uses a NOLOGIN role for permissions and a LOGIN role for end users. All database, schema, and user names are generic so the steps can be reused across environments.

Step 1: Create the Read-Only Role (No Login)

CREATE ROLE read_only NOLOGIN;

Explanation

A NOLOGIN role acts as a permission group. Users inherit privileges by being granted membership in this role.

Step 2: Grant Schema Usage

GRANT USAGE ON SCHEMA schema_name TO read_only;

GRANT USAGE ON SCHEMA public TO read_only;

Explanation

USAGE allows the role to see objects inside the schema.

Step 3: Grant SELECT on Existing Tables

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Explanation

This grants read access to all current tables in the schemas.

Step 4: Grant SELECT on Future Tables

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name

GRANT SELECT ON TABLES TO read_only;

Explanation

This ensures new tables created later automatically grant SELECT permission to the read_only role.

Step 5: Allow Temporary Tables (Database Level)

GRANT TEMP ON DATABASE database_name TO read_only;

Explanation

This allows users to create temporary tables, which is often required for reporting or analytics queries.

Step 6: Create a User and Assign Read-Only Role

CREATE ROLE user_name LOGIN PASSWORD 'secure_password';

GRANT read_only TO user_name;

Explanation

This creates a login user and grants membership in the read_only role.

Example: Applying to Another Schema

CREATE ROLE read_only NOLOGIN;

 

GRANT USAGE ON SCHEMA another_schema TO read_only;

 

GRANT SELECT ON ALL TABLES IN SCHEMA another_schema TO read_only;

 

ALTER DEFAULT PRIVILEGES IN SCHEMA another_schema

GRANT SELECT ON TABLES TO read_only;

 

CREATE ROLE another_user LOGIN PASSWORD 'secure_password';

GRANT read_only TO another_user;

Best Practices

• Use group roles for permission management

• Avoid granting privileges directly to users

• Periodically review role memberships

• Restrict write access unless required

• Use strong passwords or IAM/SSO authentication

Conclusion

Using a READ_ONLY role simplifies access management and improves security by centralizing permissions. This approach is scalable and recommended for production environments.

PostgreSQL - Manual VACUUM Execution Using psql

 

Overview

This guide explains how to connect to a PostgreSQL database using psql, configure session-level memory parameters, and run VACUUM ANALYZE on selected tables. All hostnames, database names, schema names, and table names are kept generic for reuse across environments.

1. Connect to the Database

psql -h <db-host> -U <username> -d <database_name>

Example Output

psql (client_version, server_version)

WARNING: psql major version mismatch may show this message.

SSL connection established.

Type "help" for help.

2. Execute a SQL Script (Optional)

\i /path/to/script.sql

Explanation

This command executes SQL commands stored in a file.

3. Enable Timing

\timing on

Explanation

This shows execution time for each command.

4. Set Session Memory Parameters

SET work_mem = '64MB';

SET maintenance_work_mem = '10GB';

Explanation

work_mem is used for query operations like sorting and hashing.

maintenance_work_mem is used for maintenance operations such as VACUUM and CREATE INDEX.

Increasing maintenance_work_mem can significantly improve VACUUM performance for large tables.

5. Validate Session Time

SELECT now();

6. Run VACUUM ANALYZE on Tables

VACUUM ANALYZE schema_name.table1;

VACUUM ANALYZE schema_name.table2;

VACUUM ANALYZE schema_name.table3;

VACUUM ANALYZE schema_name.table4;

VACUUM ANALYZE schema_name.table5;

VACUUM ANALYZE schema_name.table6;

VACUUM ANALYZE schema_name.table7;

Explanation

VACUUM removes dead tuples and frees space, while ANALYZE updates planner statistics.

Running both together ensures optimal query performance.

Best Practices

• Run during maintenance windows for large tables

• Increase maintenance_work_mem temporarily for large operations

• Monitor disk I/O and CPU during execution

• Avoid running many VACUUM operations concurrently

• Use autovacuum for routine maintenance

Conclusion

Manual VACUUM ANALYZE is useful for large tables or after heavy data modifications. Proper memory tuning and monitoring help reduce execution time and improve performance.

PostgreSQL - Autovacuum Tuning and Troubleshooting

 

Introduction

This document explains important PostgreSQL autovacuum parameters, how to monitor autovacuum activity, memory tuning strategies, and steps for performing manual VACUUM FREEZE operations. All examples use generic database, schema, and table names so the guide can be reused in any environment.

1. Important Autovacuum Parameters

SELECT short_desc, name, setting, unit

FROM pg_settings

WHERE name IN (

'autovacuum_max_workers',

'autovacuum_analyze_threshold',

'autovacuum_analyze_scale_factor',

'autovacuum_vacuum_threshold',

'autovacuum_vacuum_scale_factor',

'maintenance_work_mem',

'autovacuum_naptime',

'vacuum_cost_limit',

'autovacuum_freeze_max_age',

'vacuum_freeze_min_age');

2. Check If Autovacuum Is Running (Performance or I/O Issues)

SELECT datname,

       usename,

       pid,

       state,

       current_timestamp - xact_start AS xact_runtime,

       query

FROM pg_stat_activity

WHERE upper(query) LIKE '%VACUUM%'

ORDER BY xact_start;

Terminate Long Running Autovacuum (If Required)

SELECT pg_terminate_backend(<PID>);

3. Why Autovacuum Runs for a Long Time

One common reason is insufficient memory allocation, particularly a very small maintenance_work_mem setting.

4. What Is maintenance_work_mem

Memory allocated for maintenance operations such as VACUUM and CREATE INDEX. It stores row identifiers that are candidates for cleanup during vacuum.

5. maintenance_work_mem Details

• Default value: 64MB

• Higher values improve vacuum and index build performance

• Usually safe to set higher than work_mem

6. autovacuum_work_mem

When autovacuum runs, memory may be allocated up to autovacuum_max_workers times this value. VACUUM can use up to 1GB memory for dead tuple identifiers.

7. Memory Allocation Recommendations

• Large servers: maintenance_work_mem up to 2GB

• Very large workloads: 2GB–4GB

• Tune based on workload

8. Verify Databases Requiring Vacuum (Wraparound Risk)

SELECT datname,

       age(datfrozenxid)

FROM pg_database

ORDER BY age(datfrozenxid) DESC;

Check Current Setting

SHOW autovacuum_freeze_max_age;

9. Performing Manual VACUUM FREEZE

In addition to autovacuum, manual VACUUM FREEZE should be performed periodically on high-churn tables during maintenance windows.

Identify Running Autovacuum

SELECT datname,

       usename,

       pid,

       current_timestamp - xact_start AS xact_runtime,

       query

FROM pg_stat_activity

WHERE upper(query) LIKE '%VACUUM%'

ORDER BY xact_start;

10. Increase Memory for Large Tables (Session Level)

SET maintenance_work_mem = '2GB';

SET maintenance_work_mem = '128MB';

Run Manual Vacuum

\timing on

VACUUM FREEZE VERBOSE schema_name.table_name;

Terminate Autovacuum If Needed

SELECT pg_terminate_backend(<PID>);

Conclusion

Proper tuning of autovacuum memory and monitoring processes is essential for PostgreSQL performance and stability.

PostgreSQL - Aggressive Vacuuming

 

Aggressive Vacuuming

Overview

This guide explains how to monitor dead tuples in PostgreSQL tables and tune autovacuum settings to improve performance. All schema names, table names, and database references are kept generic for reuse across environments.

1. Identify Tables with High Dead Tuples

SELECT schemaname,

       relname,

       last_vacuum,

       last_analyze,

       vacuum_count,

       analyze_count,

       last_autoanalyze,

       last_autovacuum,

       autovacuum_count,

       autoanalyze_count,

       n_dead_tup

FROM pg_stat_all_tables

WHERE n_dead_tup > 10000

ORDER BY n_dead_tup DESC

LIMIT 50;

Explanation

This query identifies the top tables with significant dead tuples, which may require manual vacuuming or autovacuum tuning.

2. Tune Autovacuum Settings for a Specific Table

ALTER TABLE schema_name.table_name

SET (autovacuum_vacuum_scale_factor = 0.0);

 

ALTER TABLE schema_name.table_name

SET (autovacuum_vacuum_threshold = 10000);

 

ALTER TABLE schema_name.table_name

SET (autovacuum_analyze_scale_factor = 0.0);

 

ALTER TABLE schema_name.table_name

SET (autovacuum_analyze_threshold = 10000);

Explanation

These settings ensure autovacuum triggers based on a fixed threshold instead of percentage growth, which is useful for large or frequently updated tables.

3. Generate Vacuum Commands Dynamically

SELECT 'VACUUM VERBOSE ANALYZE ' || schemaname || '.' || relname || ';'

FROM pg_stat_all_tables

WHERE n_dead_tup > 0

ORDER BY n_dead_tup DESC

LIMIT 50;

Explanation

This query generates VACUUM commands for tables with dead tuples, allowing administrators to execute maintenance efficiently.

4. Example: Applying Autovacuum Settings to Another Table

ALTER TABLE schema_name.another_table

SET (autovacuum_vacuum_scale_factor = 0.0);

 

ALTER TABLE schema_name.another_table

SET (autovacuum_vacuum_threshold = 10000);

 

ALTER TABLE schema_name.another_table

SET (autovacuum_analyze_scale_factor = 0.0);

 

ALTER TABLE schema_name.another_table

SET (autovacuum_analyze_threshold = 10000);

Best Practices

• Monitor pg_stat_all_tables regularly

• Tune autovacuum per table for high-write workloads

• Use VACUUM VERBOSE to observe maintenance behavior

• Avoid aggressive settings on small tables

• Ensure autovacuum workers and memory settings are adequate

Conclusion

Proper monitoring and tuning of autovacuum significantly improves PostgreSQL performance and prevents table bloat. This guide provides a reusable approach for maintaining healthy database tables.

PostgreSQL - Users and Roles Queries - \du+ in DBever

 

PostgreSQL Users and Roles Queries

Introduction

In PostgreSQL, there is no strict distinction between users and roles. Everything is a role; a user is simply a role that has the LOGIN privilege (rolcanlogin = true). This document provides practical SQL queries to audit roles, role memberships, and privileges in a PostgreSQL database environment. All names are kept generic for reuse.

Key Concept

Users: Roles with LOGIN privilege (rolcanlogin = true)

Groups/Roles: Roles without LOGIN privilege (rolcanlogin = false)

A role can be a member of other roles, inheriting permissions.

1.      List All Roles with Attributes and Membership

 

SELECT r.rolname,

       r.rolsuper,

       r.rolinherit,

       r.rolcreaterole,

       r.rolcreatedb,

       r.rolcanlogin,

       r.rolconnlimit,

       r.rolvaliduntil,

       ARRAY(

           SELECT b.rolname

           FROM pg_catalog.pg_auth_members m

           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)

           WHERE m.member = r.oid

       ) AS memberof,

       r.rolreplication,

       r.rolbypassrls

FROM pg_catalog.pg_roles r

WHERE r.rolname !~ '^pg_'

ORDER BY 1;

Explanation

This query lists all non-system roles and shows:

• Superuser privileges

• Database creation privileges

• Login capability

• Connection limits

• Membership in other roles

• Replication privileges

• Row-level security bypass capability

2. List Users and Their Granted Roles

SELECT

    u.rolname AS username,

    r.rolname AS granted_role

FROM pg_auth_members m

JOIN pg_roles u ON m.member = u.oid

JOIN pg_roles r ON m.roleid = r.oid

WHERE u.rolcanlogin = true

ORDER BY u.rolname, r.rolname;

Explanation

This query shows which roles are granted to login users only, making it useful for access audits.

3. Get Roles Assigned to a Specific User

SELECT roleid::regrole,

       member::regrole

FROM pg_auth_members

WHERE member = 'username'::regrole;

Explanation

Replace 'username' with the target user. This query returns all roles granted to that specific user.

4. Alternative Query — User Membership View

SELECT

    r.oid AS role_id,

    r.rolname,

    r.rolcanlogin,

    COALESCE(b.rolname, '') AS memberof

FROM pg_catalog.pg_roles r

LEFT JOIN pg_catalog.pg_auth_members m ON r.oid = m.member

LEFT JOIN pg_catalog.pg_roles b ON m.roleid = b.oid

WHERE r.rolcanlogin = true

  AND r.rolname !~ '^pg_';

Explanation

This query focuses only on login roles (users) and shows which group roles they belong to.

Common Audit Use Cases

• Security audits

• Access reviews

• Compliance checks

• Migration planning

• Privilege troubleshooting

Best Practices

• Avoid granting superuser unless absolutely required

• Use group roles for permission management

• Periodically audit role memberships

• Monitor replication and bypass RLS privileges

• Remove unused roles

Conclusion

Understanding PostgreSQL roles and memberships is essential for secure database administration. The queries provided in this guide help administrators audit and manage access efficiently.