Skip to content

Queries for db creation

To create the db, it recommended to use the scripts provision_new_admin.sh and postgres_create_db_user.sh. The script have to be run in that order. More description about how to use these scripts is explains in details in the README.md file. Script location: https://github.com/Attracs/attracs-linux-playbooks/tree/master/scripts/db-scripts/creating_bd

When the db is created, it will be owned by the postgres user. The reason for this, is to avoid issues during the import process, in case imports are necessary from another pre-existing db.

Once the db has been created and the import finished (if any), the following queries need to be run to change ownership and permissions.

To keep it as a standard, the name of the owner carries the same name as the db, e.g. if the db is called eazytms_eazytms_nightly the name of the owner also be eazytms_eazytms_nightly.

The script will generate the user, so afterwords all is needed is to run the queries.

List of queries run manually

Phase 1: Ownership & Database Security

This section fixes the "Member" error, hands over ownership to the app user, and locks out the public.

-- 1. Allow postgres to act as the app user (Fixes "must be able to SET ROLE" error)
GRANT db_new_owner TO postgres;

-- 2. Transfer Database Ownership to the App User
ALTER DATABASE db_name OWNER TO db_new_owner;

-- 3. Security Hardening (Lock the door)
REVOKE CONNECT ON DATABASE db_name FROM public;

-- 4. Grant Database-Level Access to DevOps
GRANT ALL PRIVILEGES ON DATABASE db_name TO devops_group;

-- 5. Set the App User Password
ALTER USER db_new_owner WITH PASSWORD 'password_from_1password';

Phase 2: Public Schema Permissions

This fixes access to the default schema where most standard tables live.

-- 1. Grant DevOps full control over CURRENT tables/sequences
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO devops_group;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO devops_group;

-- 2. FUTURE PROOFING (Crucial!)
-- Ensures NEW tables created by the app user are automatically shared with DevOps
ALTER DEFAULT PRIVILEGES FOR ROLE db_new_owner IN SCHEMA public 
GRANT ALL ON TABLES TO devops_group;

ALTER DEFAULT PRIVILEGES FOR ROLE db_new_owner IN SCHEMA public 
GRANT ALL ON SEQUENCES TO devops_group;

Phase 3: Custom Schemas Permissions

This fixes ownership and access for all your specific application modules.

-- PART 1: Dynamic Loop for EXISTING Schemas and Objects
DO $$ 
DECLARE 
    row record;
BEGIN 
    -- Loop through all schemas except system schemas (postgres internals)
    FOR row IN 
        SELECT nspname 
        FROM pg_namespace 
        WHERE nspname NOT LIKE 'pg_%' 
        AND nspname != 'information_schema'
    LOOP 
        -- 1. Set Schema Owner
        EXECUTE format('ALTER SCHEMA %I OWNER TO db_new_owner', row.nspname);

        -- 2. Grant Schema usage to DevOps
        EXECUTE format('GRANT ALL ON SCHEMA %I TO devops_group', row.nspname);

        -- 3. Grant Tables/Sequences to DevOps
        EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO devops_group', row.nspname);
        EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO devops_group', row.nspname);

        -- 4. Grant Tables to the Owner User (from your section 5)
        EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO db_new_owner', row.nspname);

        RAISE NOTICE 'Fixed permissions for schema: %', row.nspname;
    END LOOP; 
END $$;

-- PART 2: Future Proofing (Database-wide)
-- By removing "IN SCHEMA x", this applies to the whole database now and forever.

ALTER DEFAULT PRIVILEGES FOR ROLE db_new_owner 
GRANT ALL ON TABLES TO devops_group;

ALTER DEFAULT PRIVILEGES FOR ROLE db_new_owner 
GRANT ALL ON SEQUENCES TO devops_group;

What this script does differently

  1. Dynamic Loop (FOR row IN...): Instead of you typing ahola_digital, then ahola_transport, etc., this queries the database for any schema that isn't a system schema (pg_...) and applies your logic.
  2. Global Defaults: In your original script, you used ALTER DEFAULT PRIVILEGES ... IN SCHEMA X. By removing the IN SCHEMA part, PostgreSQL applies this rule to every schema in the current database, including ones you create next year.

Important Prerequisites

  1. Superuser: You must run this as a Superuser (like postgres) or a user with sufficient admin rights to ALTER SCHEMA and GRANT on objects they don't own.
  2. Connections: Ensure no one is locking the tables (though GRANT usually runs without exclusive locks, ALTER SCHEMA might require higher locks).

Verification

To prove that devops_group has full, permanent access to all current and future tables across all schemas, we queried the PostgreSQL system catalogs (pg_default_acl). Query Used: SQL

SELECT 
    pg_namespace.nspname AS schema_name,
    pg_roles.rolname AS creator_role,
    pg_default_acl.defaclacl AS permissions
FROM pg_default_acl
JOIN pg_roles ON pg_roles.oid = pg_default_acl.defaclrole
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_acl.defaclnamespace;

Result: The query confirmed that devops_group possesses arwdDxtm (Append, Read, Write, Delete, Truncate, References, Trigger, Maintain) permissions on all 5 schemas (public + the 4 custom ones).