why pgAdmin4 Tablespace Security privileges does not count for pg_default if no tablespace is selected when creating new database? how to solve this issue?
Hy to all
This question is somehow indrectly related to Ignition, but maybe soeone cand give a few useful advices.
I use PostGreSQL for history DB in ignition
-I have a server dedicated to PostGreSQL and with two additional HDD's to associate with two distinct users and their databases
(I'll use generic names gere)
-I sucessfully created logins for additional two users: and besides the user with Privileges enabled only for CanLogin, CreateSatabases and CanInitiate backups
-I sucessfully created two Tablespaces <db_a> <db_b> pointing to locations on the two distinct few TB HDD's (Ubuntu)
and set security to grant privileges <db_a> only for and and <db_b> only for and
-I modified Tablespace pg_default's Security to grant privileges only for user
-The owner for all Tablespaces is postgres
-I set these up because I want to limit and to not to be able to create new databases on other Tablespaces than the one allocated to their users
-I somewhat succeded, and if tryes to create a new database using a different Tablespace than <db_b> (the one it is assigned to him), it gets "permission denied for tablespace db_a
it even gets "permission denied for tablespace pg_default" if it chooses the <pg_default> Tablespace
BUT here is my problem:
If the same user does not specify(choose) a Tablespace, the database gets created without any error or notification and the Tablespace gets set to <pg_default> that resides on the install folder of the small OS HDD and not one of the additional large HDD's
I'm not sure if it's ok to delete the pg_default Tablespace trying to find a workaround for this.
Since you are using Linux, I recommend using LVM to make mountable disk volumes for each user, and give them their own complete postgresql instance. Make the mount points /var/lib/postgresql/16/custa and /var/lib/postgresql/16/custb for instances 16-custa and 16-custb. For each instance's SystemD configuration, include RequiresMountsFor=/.... the mount point to ensure that is present before postgresql starts.
Then each user can have the entire space in their instance to themselves.
Consider leaving some space unallocated in the LVM group so you can live-extend either volume on the run, if a storage crisis ensues. (It always does, eventually.)
Thanks a lot for the idea.
I had in my mind from the beginning and was looking for a solution for the idea with two instances from a while, (ecpecially with a lot of MSSQL background knowledge where I had multiple instances) but found no reliable documentation on how to achieve that on pgSQL and on linux.
I already have two distinct MountPoints and can have as many as I need since the whole machine is in a virtual environment.
I put away the multiInstance idea because if I use two instances I have to deal with resource balancing and proper configuration between each other.
So since everything is virtualized I'm now thinking of would it be better creating two distinct Virtual machines each with it's own postgreSQL and everything dedicated to their coresponding IO_ignition?
The planned arhitecture is one FrontEnd_Ignition server with it's redundant ; two IO-Ignition servers with their reduntants
and one PostGreSQL_server with two distinct databases for history for each IO_ignition on distinct mappings.
or two distinct servers with their own PostGreSQL for each IO_ignition.
The project mandatory requires two distinct IO_ignition servers because they will have each their own separate role and each quite a few thousands of tags that will require history.
Probably off the topic this whole reply, but I'm even thinking of Tag History Splitter if it would help in this big project for load balancing between recent and old history readouts. but in this case I will need 4 distinct database connections preferably with independent resources. since using the same resources for a tagHistprySplit I think it's useless.
You have to tweak the configuration of PostgreSQL anyways, as its out-of-the-box settings are a bit on the weak side. PostgreSQL won't use more RAM than what is dictated by postgresql.conf.
To have two clusters coexist, they simply need separate ports. Most distros have multi-instance management scripts as pg_*cluster that presume data folders of the form /var/lib/data/$majorversion/$instancename, where the default instance name is "main".