Create PostgreSQL ROLE (user) if it doesn't exist

sql

database-management

postgresql

role-management

byNikita Barsukov·Sep 11, 2024

Use the DO block in PostgreSQL to safely attempt creating a ROLE, ignoring potential errors:

DO $$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'your_new_role') THEN
      CREATE ROLE your_new_role;
   END IF;
END$$;

Simply replace 'your_new_role' with the desired ROLE name. This IF NOT EXISTS approach allows you to bypass the need to handle errors related to existing roles.

Action handling with PL/pgSQL

Sometimes, multiple operations could attempt to create the same role at the same time, a scenario known as a race condition. You can handle such exceptions using PL/pgSQL:

DO $$
BEGIN
   BEGIN
      CREATE ROLE your_new_role;
   EXCEPTION WHEN duplicate_object THEN
      RAISE NOTICE 'Role % already exists. Move along, nothing to see here.', 'your_new_role';
   END;
END$$;

Replace 'your_new_role' with the role you intend to create. The exception handling here will intercept the error and raise a notice rather than disrupt the process.

Advanced role management

For managing roles dynamically, PostgreSQL requires specific checks and error traps. Here's how to handle this by combining the format() function with EXECUTE in a PL/pgSQL block:

DO $$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = format('your_new_role')) THEN
      EXECUTE format('CREATE ROLE %I', 'your_new_role'); -- As dynamic as Spielberg's movie scenes
   END IF;
END$$;

Remember, it's crucial to verify permission levels for each role to prevent security inconsistencies or privilege escalations.

Shell scripting shortcut

If you're comfortable with shell scripting, use psql and grep in your bash scripts to manage existing roles:

if psql -tAc "SELECT 1 FROM pg_roles WHERE rolname='your_new_role'" | grep -q 1; then
  echo "Role exists, not creating. Someone got there first."
else
  psql -c "CREATE ROLE your_new_role" -- Be the first to the finish line!
fi

This approach integrates seamlessly with existing bash-based deployment processes and saves you the extra trips to the database.

Anticipating role creation pitfalls

Before you CREATE ROLE in PostgreSQL, be cognizant of potential hurdles and intricacies:

Permissions: Only users with the necessary privileges can execute a CREATE ROLE command. Check your user's permissions to avoid unwanted surprises.
Database Object Ownership: Ensure you're not dropping a role that currently owns database objects. This could cause a cascade of problems down the line.
Name Uniqueness: Keep the role names unique to ensure your database security structure remains neat and defensible.

Reusable function for role creation

Saving and reusing your role creation logic within a function proves handy for regular role management:

CREATE OR REPLACE FUNCTION create_role_if_not_exists(role_name TEXT) RETURNS void AS $$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = role_name) THEN
      EXECUTE format('CREATE ROLE %I', role_name); -- Instant replay at your service.
   END IF;
END;
$$ LANGUAGE plpgsql;