Open mmrraju opened 6 months ago
cc @devrimyatar , @moabu
@yurem Which of these previleges are required for Gluu to operate without issue? https://www.postgresql.org/docs/current/ddl-priv.html
We need to change owner of all object in jansdb
to new user jans_admin
.
Setup before creating tables should add jans_admin
user and use it as owner of all tables. After this we can change privileges of jans
user:
ALTER DEFAULT PRIVILEGES
FOR USER jans
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO jans;
-- Need only if there are tables already
REVOKE ALL
ON ALL TABLES IN SCHEMA public
FROM jans;
-- Need only if there are tables already
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO jans;
Expected result after installation:
Also we need to modify jans
user DB privileges:
REVOKE ALL ON DATABASE jansdb FROM jans;
GRANT CONNECT ON DATABASE jansdb TO jans;
To change the owner you can simply use REASSIGN OWNED BY
.
What I really wanted to highlight here is that in my case the database and the roles will have been created by the cluster admin, so the installer shouldn't try to create those, or perhaps it only should as an option. For example I would run these:
SET ROLE postgres;
CREATE DATABASE jansdb;
CREATE USER jans;
CREATE USER jans_admin;
GRANT CREATE ON DATABASE jansdb TO jans_admin;
\password jans_admin
\password jans
\c jansdb
SET ROLE postgres;
REVOKE ALL ON SCHEMA public FROM public;
ALTER SCHEMA public OWNER TO jans_admin;
Then the installer can connect to the jansdb
database using the jans_admin
role and run
ALTER DEFAULT PRIVILEGES FOR USER jans IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO jans;
and then run all the object creation DDLs.
@iromli
I believe the ALTER DEFAULT PRIVILEGES should actually be
ALTER DEFAULT PRIVILEGES FOR USER jans_admin GRANT ALL PRIVILEGES ON TABLES TO jans;
ALTER DEFAULT PRIVILEGES FOR USER jans_admin GRANT ALL PRIVILEGES ON SEQUENCES TO jans;
ALTER DEFAULT PRIVILEGES FOR USER jans_admin GRANT EXECUTE ON ROUTINES TO jans;
ALTER DEFAULT PRIVILEGES FOR USER jans_admin GRANT USAGE ON TYPES TO jans;
ALTER DEFAULT PRIVILEGES FOR USER jans_admin GRANT USAGE ON SCHEMAS TO jans;
@yurem , @devrimyatar any update ?
Target ticket-11691
It needs to have superuser rights on its database. It's important to separate the user used for installation and upgrade from the user used for day-to-day operations