Create Postgres user with similar permissions to avnadmin

Aiven Community
1

I want to create a postrgres user similar to avnadmin, so that I can provide it to a customer instead of avnadmin. Both roles should be able to create database/alter any table etc.

2 Likes
2

Hi @chath

You can try this:

defaultdb=> CREATE ROLE new_role_name WITH LOGIN PASSWORD 'new_password';
CREATE ROLE
defaultdb=> GRANT avnadmin TO new_role_name;
GRANT ROLE
defaultdb=> SELECT rolname, 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 FROM pg_catalog.pg_roles r WHERE rolname = 'new_role_name';
    rolname    |  memberof  
---------------+------------
 new_role_name | {avnadmin}
(1 row)

I’ve tried it in a brand new Aiven for PostgreSQL database.

Of course, adapt your name / password to your use case.

Enjoy!

3 Likes