I'm trying to create a database with a few roles for my app but I'm struggling to get things, work.
I want to have:
- an owner user that run the migrations and who can run actions in the database bypassing RLS
- an application user that is not able to create/drop tables and is limited by RLS
- a few roles that I'll use in the app switching base on business logic
The user is created using the postgres user, after that I run one migration using the postgres user where I create my users/roles
-- Create user with login
create role ${owner.user} with login password '${owner.password}' bypassrls;
create role ${app.user} with login password '${app.password}' noinherit;
grant connect on database ${database} to ${owner.user};
grant connect on database ${database} to ${app.user};
grant ${owner.user} to postgres;
grant ${app.user} to postgres;
-- Revoke default permissions
revoke all on schema public from public;
alter default privileges revoke all on sequences from public;
alter default privileges revoke all on functions from public;
-- Assign db to owner
alter database ${database} owner to ${owner.user};
-- grant permissions
grant all on database ${database} to ${owner.user};
grant all on schema public to ${owner.user};
-- Create roles used by the app
create role anonymous;
create role logged_user;
create role admin;
grant anonymous TO ${app.user};
grant logged_user TO ${app.user};
grant admin TO ${app.user};
grant pg_read_all_data to ${owner.user};
grant pg_write_all_data to ${owner.user};
grant usage on schema public to ${owner.user};
alter default privileges in schema public grant execute on functions to ${owner.user};
alter default privileges in schema public grant usage, select on sequences to ${owner.user};
-- app
grant usage on schema public to anonymous;
grant usage on schema public to logged_user;
grant usage on schema public to admin;
alter default privileges in schema public grant usage, select on sequences to anonymous;
alter default privileges in schema public grant usage, select on sequences to logged_user;
alter default privileges in schema public grant usage, select on sequences to admin;
Then I run this migration with the owner user
-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
-- Functions
GRANT EXECUTE ON FUNCTION uuid_generate_v4() TO anonymous, logged_user, admin;
but the migration thrown this error:
Error migrating the database: error: permission denied for function uuid_generate_v4
what am I doing wrong? Does this permissions make a sense? I want to give as less permissions as possible
Asked by francesco.venica
(121 rep)
Apr 10, 2025, 10:12 PM
Last activity: Apr 13, 2025, 12:03 PM
Last activity: Apr 13, 2025, 12:03 PM