Map the normal user permissions vs the root on Postgresql

[mmrraju]

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

[mmrraju]

cc @devrimyatar , @moabu

[devrimyatar]

@yurem Which of these previleges are required for Gluu to operate without issue?
https://www.postgresql.org/docs/current/ddl-priv.html

[yurem]

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;

[pstef]

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.

[yurem]

@iromli

[pstef]

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;

[mmrraju]

@yurem , @devrimyatar any update ?