Replies: 1 comment
-
Here's how I have only created a user that did not already exist: -- Deploy roles
BEGIN;
SET search_path TO public;
DO $$
BEGIN
PERFORM true FROM pg_roles WHERE rolname = 'editor';
IF NOT FOUND THEN
CREATE ROLE editor WITH NOLOGIN;
END IF;
END;
$$;
COMMIT; The revert script looks like this: -- Revert roles
-- Do nothing; leave the roles in place. Yep, just comments. Note I tend to create roles with psql -d stories -c "CREATE USER fred PASSWORD '****' IN ROLE editor” Using variables is harder, because psql CREATE OR REPLACE TEMPORARY FUNCTION coruser(
username text
password text
) language plpsql as $f$
BEGIN
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = username) THEN
EXECUTE format($$
CREATE USER %1I WITH ENCRYPTED PASSWORD %2L;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA my_schema TO %1I;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema TO %1I;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA my_schema TO %1I;
GRANT USAGE ON SCHEMA my_schema TO %1I;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO %1I;
$$, username, password);
END IF;
END
$f$;
SELECT coruser(:'user', :'password'); HTH! |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Me and my team just started to use sqitch to manage our database project, and we are trying to use PL/pgSQL in the scripts with variables, but we can't make it work. The current script we are trying to run is actually something not so common, because we use the FORMAT function to generate a PL/pgSQL command and run it using
\gexec
. Like the example below:In the example above we are trying to verify if a created role (user) has usage permissions in
my_schema
. The role was created using a variable as well, but using regular PG SQL statements. The next thing we would like to try is something like:The major goal is to create idempotent scripts, which the
CREATE USER
statement doesn't support, that's why we would like to run PL/pgSQL instead, but in this case we also would like to use variables, so for example an user and password is not hardcoded in our scripts codebase.I'm sorry if I missed something, but I couldn't find anything in the docs related to this kind of usage.
Thanks in advance! 😄
Beta Was this translation helpful? Give feedback.
All reactions