Re: problem
Check in your %temp% directory, there should be some bitrock* or bitrock_installer* file, these are setup logs and can point you to the problem. If initdb failed, you can try running something like this initdb -D "C:\My\Postgres\Setup\Path" --encoding=UTF8 --locale="Italian, Italy" -W –n and see if you get some error (if you are installing an old veresion (<9.3 IIRC) you can have to add -U postgres to have the user Postgres -- that's been removed in last versions -- to be the owner of data directory) If you point us to the os/postgres versions we could try to be more accurate :-) Cheers Moreno.- Il 31/01/2019 14:10, Mirco Gallazzi ha scritto: GoodMornig, I can’t install postgres on my pc because i have Always this error: “ Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.” I looked for a guide that can help me to solve this problem but it was all a big fail. Itried to create a new user on my pc to create postgres server but nothing. I tried to follow some guides on web/YouTube but nothing. Can you please help me to solve this problem? I must need to solve this because in my university they use postgres and i must need this to complete the exam and the project on database. Thank for patience. Have a good day, mirco Inviato da Posta per Windows 10
FK Constraint with ON DELETE SET DEFAULT cascading as table owner
Hello, We are using RLS on Postgres 11 to implement multi tenancy in our application. We have a tenant table whose id matches the tenant’s user role. Each table has a tenant_id FKA that defaults to “current_user”. All of our foreign key constraints are multipart (tenant_id + row_id). So far this works great except when we try to clean up FKA references on deletion. Here’s a script that demonstrates the issue in an empty database: CREATE ROLE tenant1; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO tenant1; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO tenant1; CREATE TABLE tenant ( id TEXT NOT NULL DEFAULT user PRIMARY KEY ); CREATE TABLE foo ( tenant TEXT REFERENCES tenant NOT NULL DEFAULT user, id SERIAL NOT NULL, default_bar INT, PRIMARY KEY (tenant, id) ); CREATE TABLE bar ( tenant TEXT REFERENCES tenant NOT NULL DEFAULT user, id SERIAL NOT NULL, foo_id INT, PRIMARY KEY (tenant, id), constraint foo FOREIGN KEY (tenant, foo_id) REFERENCES foo (tenant, id) ON DELETE CASCADE ); ALTER TABLE foo ADD CONSTRAINT default_bar FOREIGN KEY (tenant, default_bar) REFERENCES bar (tenant, id) ON DELETE SET DEFAULT; ALTER TABLE foo ENABLE ROW LEVEL SECURITY; ALTER TABLE bar ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_tenant on tenant USING (id = current_user) WITH CHECK (id = current_user); CREATE POLICY foo_tenant on foo USING (tenant = current_user) WITH CHECK (tenant = current_user); CREATE POLICY bar_tenant on bar USING (tenant = current_user) WITH CHECK (tenant = current_user); SET ROLE tenant1; INSERT INTO tenant DEFAULT VALUES; INSERT INTO foo DEFAULT VALUES; INSERT INTO bar ( foo_id ) (SELECT id FROM foo ); UPDATE foo SET default_bar = ( SELECT id FROM bar ); DELETE FROM bar; This script winds up failing because the “user” default value on foo.tenant evaluates to the table owner, not the active user role of “tenant1”. Is this the expected behavior? The desired outcome, after deleting from bar, would be for foo’s tenant to remain “tenant1” and its default_bar value be set to null. Is there another, cleaner way to achieve this? Thank you! Brad
Re: pgexpress 4.60 vita voom
Thank you everybody, for all the suggestions. I'm using postgres 8.2 and with with that driver I can upgrade to 9.2 basically without changes on the software, this is the only reason why I'm looking for that driver. I've understand from your answers the postgresql 9.2 is also an old unsupported release, so I think it's better to upgrade to some new driver. I'm working on Delphi 7, what driver you suggest to use ? Kind Regards -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: FK Constraint with ON DELETE SET DEFAULT cascading as table owner
Hello, On Fri, 2019-02-01 at 14:01 +, Brad Leupen wrote: > Hello, > > We are using RLS on Postgres 11 to implement multi tenancy in our > application. We have a tenant table whose id matches the tenant’s > user role. Each table has a tenant_id FKA that defaults to > “current_user”. All of our foreign key constraints are multipart > (tenant_id + row_id). So far this works great except when we try to > clean up FKA references on deletion. Here’s a script that > demonstrates the issue in an empty database: > > > CREATE ROLE tenant1; > > ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON > TABLES TO tenant1; > ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON > SEQUENCES TO tenant1; > > CREATE TABLE tenant ( > id TEXT NOT NULL DEFAULT user PRIMARY KEY > ); > > CREATE TABLE foo ( > tenant TEXT REFERENCES tenant NOT NULL DEFAULT user, > id SERIAL NOT NULL, > default_bar INT, > PRIMARY KEY (tenant, id) > ); > > CREATE TABLE bar ( > tenant TEXT REFERENCES tenant NOT NULL DEFAULT user, > id SERIAL NOT NULL, > foo_id INT, > PRIMARY KEY (tenant, id), > constraint foo FOREIGN KEY (tenant, foo_id) REFERENCES foo > (tenant, id) ON DELETE CASCADE > ); > > ALTER TABLE foo ADD CONSTRAINT default_bar FOREIGN KEY (tenant, > default_bar) REFERENCES bar (tenant, id) ON DELETE SET DEFAULT; > > ALTER TABLE foo ENABLE ROW LEVEL SECURITY; > ALTER TABLE bar ENABLE ROW LEVEL SECURITY; > > CREATE POLICY tenant_tenant on tenant USING (id = current_user) WITH > CHECK (id = current_user); > CREATE POLICY foo_tenant on foo USING (tenant = current_user) WITH > CHECK (tenant = current_user); > CREATE POLICY bar_tenant on bar USING (tenant = current_user) WITH > CHECK (tenant = current_user); > > SET ROLE tenant1; > > INSERT INTO tenant DEFAULT VALUES; > INSERT INTO foo DEFAULT VALUES; > INSERT INTO bar ( foo_id ) (SELECT id FROM foo ); > UPDATE foo SET default_bar = ( SELECT id FROM bar ); > DELETE FROM bar; > > This script winds up failing because the “user” default value on > foo.tenant evaluates to the table owner, not the active user role of > “tenant1”. Is this the expected behavior? The desired outcome, after > deleting from bar, would be for foo’s tenant to remain “tenant1” and > its default_bar value be set to null. Is there another, cleaner way > to achieve this? > > Thank you! > Brad I ran your script. Output:- postgres 11.1 => select * from tenant; id - tenant1 (1 row) postgres 11.1 => select * from foo; tenant | id | default_bar -++- tenant1 | 1 | 1 (1 row) postgres 11.1 => select * from bar; tenant | id | foo_id -++ tenant1 | 1 | 1 (1 row) postgres 11.1 => delete from bar; ERROR: insert or update on table "foo" violates foreign key constraint "foo_tenant_fkey" DETAIL: Key is not present in table "tenant". postgres 11.1 => Your foreign key constraint is defined as:- default_bar ==> FOREIGN KEY (tenant, default_bar) REFERENCES bar(tenant, id) ON DELETE SET DEFAULT If you don't specify a "default" it uses NULL. There is no tenant.id that is NULL. So, the foreign key validation fails. AFAICT, it is working as intended. Cheers, Robert
FK Constraint with ON DELETE SET DEFAULT cascading as table owner
On Friday, February 1, 2019, rob stone wrote: > Hello, > > On Fri, 2019-02-01 at 14:01 +, Brad Leupen wrote: > > CREATE TABLE foo ( > > tenant TEXT REFERENCES tenant NOT NULL DEFAULT user, > > > This script winds up failing because the “user” default value on > > foo.tenant evaluates to the table owner, not the active user role of > > “tenant1”. Is this the expected behavior? > If you don't specify a "default" it uses NULL. > There is no tenant.id that is NULL. > So, the foreign key validation fails. > > AFAICT, it is working as intended. > It working as intended but your analysis is wrong (for one, there is a default). Cascading triggers use the role of the table owner instead of the statement executing role to handle various authorization configurations. You may not be able leverage on delete/update here given your requirement. David J.
