1. Creating a User Role

To establish a new user, you can utilize the following SQL command. Ensure you specify a strong password:

CREATE USER admin_user WITH PASSWORD 'secure_password';

Alternatively, you can use the CREATE ROLE syntax, but it is crucial to include the LOGIN attribute to allow database connections:

CREATE ROLE admin_role WITH PASSWORD 'secure_password' LOGIN;

If you create a role without the LOGIN privilege, you can rectify this later by executing:

ALTER ROLE admin_role LOGIN;

2. Initializing the Database

When creating a new database, its best practice to explicitly define the owner, encoding, and locale settings. The following script demonstrates how to create a database named app_db with specific configurations:

CREATE DATABASE app_db
    WITH OWNER = admin_user
         ENCODING = 'UTF8'
         TABLESPACE = pg_default
         LC_COLLATE = 'en_US.UTF-8'
         LC_CTYPE = 'en_US.UTF-8'
         CONNECTION LIMIT = -1
         TEMPLATE template0;

-- Grant basic permissions to public users
GRANT CONNECT, TEMPORARY ON DATABASE app_db TO public;

-- Grant full privileges to the specific owner
GRANT ALL ON DATABASE app_db TO admin_user;

-- Grant full privileges to the postgres superuser
GRANT ALL ON DATABASE app_db TO postgres;

-- Add a description to the database
COMMENT ON DATABASE app_db IS 'Primary application database';

Note: If you encounter a collation mismatch error, such as new collation (zh_CN.UTF-8) is incompatible with the collation of the template database, ensure you include TEMPLATE template0 in your creation statement. This bypasses the default template's locale settings.

3. Recursively Updating Schema Ownership

After creating objects or migrating data, you may need to transfer ownership of all tables, sequences, views, and functions within a schema to a specific user. The following annoymous PL/pgSQL block automates this process for the public schema:

DO $$
DECLARE
    obj_record record;
    schema_index INTEGER;
    target_schemas TEXT[] := '{public}';
    new_owner VARCHAR := 'admin_user';
BEGIN
    -- Update ownership for tables, sequences, views, and functions
    FOR obj_record IN
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || new_owner || ';' AS stmt
        FROM information_schema.tables
        WHERE table_schema = ANY(target_schemas)

        UNION ALL

        SELECT 'ALTER SEQUENCE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || new_owner || ';' AS stmt
        FROM information_schema.sequences
        WHERE sequence_schema = ANY(target_schemas)

        UNION ALL

        SELECT 'ALTER VIEW "' || table_schema || '"."' || table_name || '" OWNER TO ' || new_owner || ';' AS stmt
        FROM information_schema.views
        WHERE table_schema = ANY(target_schemas)

        UNION ALL

        SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || new_owner || ';' AS stmt
        FROM pg_proc p
        JOIN pg_namespace nsp ON p.pronamespace = nsp.oid
        WHERE nsp.nspname = ANY(target_schemas)
    LOOP
        EXECUTE obj_record.stmt;
    END LOOP;

    -- Update ownership for the schemas themselves
    FOR schema_index IN array_lower(target_schemas, 1)..array_upper(target_schemas, 1)
    LOOP
        EXECUTE 'ALTER SCHEMA "' || target_schemas[schema_index] || '" OWNER TO ' || new_owner;
    END LOOP;
END $$;