Re: Inconsistent permission enforcement for schemas
=?UTF-8?Q?Marcin_Barczy=C5=84ski?= writes: > Why can a user access a table from a forbidden schema if the table is > explicitly specified in the definition of a view in an allowed schema? And > not if the table is accessed by a function used in the view. (1) Whether a view can access a table is determined by the ownership of the view, not of the user using the view. (2) It might be nice if the view's permissions were inherited by functions called by the view, but we've not made that happen. You might be able to approximate it by marking such functions SECURITY DEFINER; although that makes the function's permissions those of the function's owner not the view's owner. regards, tom lane
Inconsistent permission enforcement for schemas
Hello, Why can a user access a table from a forbidden schema if the table is explicitly specified in the definition of a view in an allowed schema? And not if the table is accessed by a function used in the view. Example: ``` DROP SCHEMA IF EXISTS forbidden_schema CASCADE; DROP SCHEMA IF EXISTS allowed_schema CASCADE; DROP FUNCTION IF EXISTS func CASCADE; DROP USER IF EXISTS pinky; CREATE SCHEMA forbidden_schema; CREATE TABLE forbidden_schema.demo AS SELECT 1; CREATE SCHEMA allowed_schema; CREATE OR REPLACE FUNCTION func() RETURNS BIGINT AS $$ DECLARE i BIGINT; BEGIN SELECT * INTO i FROM forbidden_schema.demo; RETURN i; END; $$ LANGUAGE plpgsql; CREATE VIEW allowed_schema.can_see AS SELECT * FROM forbidden_schema.demo; CREATE VIEW allowed_schema.cannot_see AS SELECT * FROM func(); CREATE ROLE pinky LOGIN NOSUPERUSER PASSWORD 'pinky'; GRANT ALL PRIVILEGES ON SCHEMA allowed_schema TO pinky; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA allowed_schema TO pinky; ``` And now, as user pinky: ``` q=> SELECT * FROM allowed_schema.can_see; ?column? -- 1 (1 row) q=> SELECT * FROM allowed_schema.cannot_see; ERROR: permission denied for schema forbidden_schema LINE 1: SELECT *FROM forbidden_schema.demo ^ QUERY: SELECT *FROM forbidden_schema.demo CONTEXT: PL/pgSQL function func() line 5 at SQL statement ``` Best regards, Marcin Barczyński
Re: "set autocommit on" in golang client query
Hi, Le lun. 1 août 2022 à 15:10, Rory Campbell-Lange a écrit : > Apologies for the rather naive question. > > I have a psql migration script to call a looping procedure which commits > batches of data along the following lines: > > set search_path = a, b c; > \set AUTOCOMMIT on > -- call procedure with nested transactions > CALL c.pr_my_procedure(debug=>true); > > I'm trying to migrate this to a programme using the golang pgx module to > exec > this code on a large number of databases in parallel. There doesn't seem an > obvious way of setting autocommit outside of psql, so the equivalent of > > conn.Exec(context.Background(), > "set search_path = a, b c; set AUTOCOMMIT on; CALL > c.pr_my_procedure(debug=>true);") > > fails with > > ERROR: syntax error at or near "ON" > > while > > conn.Exec(context.Background(), > "set search_path = a, b c; CALL > c.pr_my_procedure(debug=>true);") > > fails with > > ERROR: invalid transaction termination > > How should I set autocommit on? The "Create Procedure" docs at > https://www.postgresql.org/docs/current/sql-createprocedure.html don't > seem to > mention autocommit. > > That's because it doesn't exist on the PostgreSQL server. You can use it with psql but that's a psql feature, not the server's. -- Guillaume.
"set autocommit on" in golang client query
Apologies for the rather naive question. I have a psql migration script to call a looping procedure which commits batches of data along the following lines: set search_path = a, b c; \set AUTOCOMMIT on -- call procedure with nested transactions CALL c.pr_my_procedure(debug=>true); I'm trying to migrate this to a programme using the golang pgx module to exec this code on a large number of databases in parallel. There doesn't seem an obvious way of setting autocommit outside of psql, so the equivalent of conn.Exec(context.Background(), "set search_path = a, b c; set AUTOCOMMIT on; CALL c.pr_my_procedure(debug=>true);") fails with ERROR: syntax error at or near "ON" while conn.Exec(context.Background(), "set search_path = a, b c; CALL c.pr_my_procedure(debug=>true);") fails with ERROR: invalid transaction termination How should I set autocommit on? The "Create Procedure" docs at https://www.postgresql.org/docs/current/sql-createprocedure.html don't seem to mention autocommit. Rory
Re: Connecting to postgres on OSX from Swift using PostgresClientKit
On 2022-08-01 04:06, Adrian Klaver wrote: 2022-08-01T00:20:18.530Z Connection-1 finer] Created socket [2022-08-01T00:20:18.532Z Connection-1 fine] Opening connection to port 5432 on host localhost 2022-08-01 01:20:18.534308+0100 TestPostgres[10654:120173] dnssd_clientstub ConnectToServer: connect() failed A search on above error message found: https://github.com/firebase/firebase-ios-sdk/issues/3033 https://developer.apple.com/forums/thread/67792 https://github.com/emqx/CocoaMQTT/issues/202 all of which point to what Tom Lane mentioned, sandboxing. path:/var/run/mDNSResponder Socket:13 Err:-1 Errno:1 Operation not permitted 2022-08-01 01:20:18.535617+0100 TestPostgres[10654:120173] [si_destination_compare] socket(PF_SYSTEM, SOCK_DGRAM, SYSPROTO_CONTROL) failed: Operation not permitted 2022-08-01 01:20:18.535694+0100 TestPostgres[10654:120173] [si_destination_compare] socket(PF_SYSTEM, SOCK_DGRAM, SYSPROTO_CONTROL) failed: Operation not permitted [2022-08-01T00:20:18.537Z Connection-1 severe] Unable to connect socket: Error code: -9989(0x-2705), Operation not permitted [2022-08-01T00:20:18.537Z Connection-1 finer] Closing socket Hi Adrian, you & Tom were correct - it was sandbox settings (which was turned off??) in Xcode which caused the problems. In case anyone else has the problem, the steps I took to fix the problem in XCode 13.3 were as follows: 1. Go to the project explorer and select the project. 2. Select the Target under the list of Targets 3. Select the "Signing & Capabilities" Tab/Heading 4. Under the "App Sandbox" settings, select the "Outgoing connections (client)" checkbox. 5. Note - the "Enable App Sandbox" under Build Settings seems to have no effect! Apologies for not finding this myself - This was purely a MacOS / XCode problem - but many thanks to you all (Adrian, Tom, Rob) for proving yet again that Postgresql support is the best. Howard.
Re: a database can be created but not droped
Am Mon, Aug 01, 2022 at 11:22:33AM +0200 schrieb Matthias Apitz: > testdb=# DROP DATABASE IF EXISTS SRP-27097 WITH FORCE; > ERROR: syntax error at or near "-" > ZEILE 1: DROP DATABASE IF EXISTS SRP-27097 WITH FORCE; > > Why is this? Please try: DROP DATABASE IF EXISTS "SRP-27097" WITH FORCE; Best rehards Frank
Re: a database can be created but not droped
Hi, On Mon, Aug 01, 2022 at 11:22:33AM +0200, Matthias Apitz wrote: > > This is with 14.1 on Linux. I have created a new database with > > $ createdb -U sisis -T template0 SRP-27097 > > I can connect to it, created tables and fill them with SQL: > > but I can not drop the database: > > $ psql -Usisis -dtestdb > psql (14.1) > Geben Sie »help« für Hilfe ein. > > testdb=# DROP DATABASE IF EXISTS SRP-27097 WITH FORCE; > ERROR: syntax error at or near "-" > ZEILE 1: DROP DATABASE IF EXISTS SRP-27097 WITH FORCE; > > Why is this? It's because createdb a command that knows how to quote identifiers and will do automatically for you. At the SQL level you have to properly quote identifier, so this command will work: DROP DATABASE IF EXISTS "SRP-27097" WITH FORCE;
a database can be created but not droped
Hello, This is with 14.1 on Linux. I have created a new database with $ createdb -U sisis -T template0 SRP-27097 I can connect to it, created tables and fill them with SQL: $ psql -Usisis -dSRP-27097 psql (14.1) Geben Sie »help« für Hilfe ein. SRP-27097=# select count(*) from sisisinst; count --- 2 but I can not drop the database: $ psql -Usisis -dtestdb psql (14.1) Geben Sie »help« für Hilfe ein. testdb=# DROP DATABASE IF EXISTS SRP-27097 WITH FORCE; ERROR: syntax error at or near "-" ZEILE 1: DROP DATABASE IF EXISTS SRP-27097 WITH FORCE; Why is this? matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Is Client connections via ca.crt only possible?
Requirement is to use only ca.crt and connect to postgres Server.crt, Server.key and ca.crt are configured at the postgres server for tls connection. Connection successful while using psql ‘host=172.29.21.222 dbname=test user=postgres sslmode=verify-ca sslcert=/tmp/server.crt sslkey=/tmp/server.key sslrootcert=/tmp/ca.crt port=5432’ For clients to connect, can they use only ca.crt and connect to the DB. Tried and got the below error psql ‘host=172.29.21.222 dbname=test user=postgres sslmode=verify-ca sslrootcert=/tmp/ca.crt port=5432’ psql: error: connection to server at “172.29.21.222”, port 50001 failed: FATAL: connection requires a valid client certificate
Unable to start replica after failover
Hi, We're using asynchronous streaming replication together with WAL archiving and recovery (we've set these parameters: archive_command, restore_command, recovery_target_timeline=latest). To manage backup and recovery, we're using pg_probackup. We have a 3-node cluster managed by Patroni on Raft. While running our cluster, we encounter a recurring problem: after a failover, the former leader cannot recover neither via the tools provided by PostgreSQL nor with the Patroni tools (pg_rewind). We've seen a number of different WAL reading errors. For example: * "could not find previous WAL record at E6F/C2436F50: invalid resource manager ID 139 at E6F/C2436F50"; or * "could not find previous WAL record at 54E/FB348118: unexpected pageaddr 54E/7B34A000 in log segment 0005054E00FB, offset 3448832". There have been some other variations but all such errors have the same cause: 1. At startup, the former leader does not immediately get access to the backup. As a result, the recovery process uses the local WAL, which inevitably leads to a split brain since in most cases there is a lag in replication after a failover. 2. Next, the former leader finally manages to read the latest log from the backup, and that log has a higher priority than the local log, even though the recovery with the local log has already occurred. As a result, we see the following: "new timeline 3 forked off current database system timeline 2 before current recovery point 54E/FB348150". When pg_rewind is run, it also uses the log from the backup (the lagging log from the new leader) instead of the partial log with which the former leader has already been started. Next, pg_rewind returns errors while reading the log from the backup back, looking for the last checkpoint, which is quite reasonable because, once a new leader starts, the point of divergence normally ends up in the next timeline and the previous timeline's backup log does not have a block with the LSN of the divergence. Also, in this case, it is impossible to build a map of the pages that have been changed to roll back the changes using pg_rewind. To avoid the problem, we decided to stop using restore_command. Could you please let us know if there is a better solution to the problem we've described?
