pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Boris P. Korzun <drtr0jan@yandex.ru>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
Date: Wed, 24 Mar 2021 23:38:50 +0300
Message-ID: <111621616618184@mail.yandex.ru> (raw )
In-Reply-To: <7161616616624@mail.yandex.ru >
------==--bound.1064580.iva5-4e96f110c519.qloud-c.yandex.net
Content-Transfer-Encoding: base64
Content-Type: text/html; charset=utf-8
PGRpdj5IaSw8L2Rpdj48ZGl2PjxkaXY+wqA8L2Rpdj48ZGl2PjxkaXY+UG9zdGdyZVNRTCB2ZXJz
aW9uOiAxMy4yPC9kaXY+PGRpdj5PcGVyYXRpbmcgc3lzdGVtOiDCoEZyZWVCU0QgMTMtU1RBQkxF
PC9kaXY+PGRpdj7CoDwvZGl2PjwvZGl2PjxkaXY+SSd2ZSBmb3VuZGVkIHRoYXQgcGdfZHVtcCAo
YW5kIHBnX2R1bXBhbGwpIGluY29ycmVjdGx0eSBleHBvcnRzIHN1YnNlcXVlbnQgZGVmYXVsdCBw
cml2aWxlZ2VzLjwvZGl2PjxkaXY+RXguOjwvZGl2PjxkaXY+Jmd0OyBBTFRFUiBERUZBVUxUIFBS
SVZJTEVHRVMgRk9SIFJPTEUgcG9zdGdyZXMgUkVWT0tFIEFMTCBPTiBGVU5DVElPTlMgRlJPTSBQ
VUJMSUM7PC9kaXY+PGRpdj4mZ3Q7IEFMVEVSIERFRkFVTFQgUFJJVklMRUdFUyBGT1IgUk9MRSBw
b3N0Z3JlcyBJTiBTQ0hFTUEgYmFyIEdSQU5UIEVYRUNVVEUgT04gRlVOQ1RJT05TIFRPIFBVQkxJ
Qzs8L2Rpdj48ZGl2PiZndDsgYnVnPSMgXGRkcDwvZGl2PjxkaXY+Jmd0OyDCoCDCoCDCoCDCoCDC
oCDCoCDCoERlZmF1bHQgYWNjZXNzIHByaXZpbGVnZXM8L2Rpdj48ZGl2PiZndDsgwqAgT3duZXIg
wqAgfCBTY2hlbWEgfCDCoCBUeXBlIMKgIHwgwqBBY2Nlc3MgcHJpdmlsZWdlczwvZGl2PjxkaXY+
Jmd0OyAtLS0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0t
PC9kaXY+PGRpdj4mZ3Q7IMKgcG9zdGdyZXMgfCBiYXIgwqAgwqB8IGZ1bmN0aW9uIHwgPVgvcG9z
dGdyZXM8L2Rpdj48ZGl2PiZndDsgwqBwb3N0Z3JlcyB8IMKgIMKgIMKgIMKgfCBmdW5jdGlvbiB8
IHBvc3RncmVzPVgvcG9zdGdyZXM8L2Rpdj48ZGl2PiZndDsgLS0gSSBjYW4gZXhlY3V0ZSBuZXds
eSBjcmVhdGVkIGZ1bmN0aW9ucyBvbiBzY2hlbWUgJ2JhcicgYWZ0ZXIgdGhhdC48L2Rpdj48ZGl2
PsKgPC9kaXY+PGRpdj53aWxsIGJlIGV4cG9ydGVkIGFzOjwvZGl2PjxkaXY+Jmd0OyBBTFRFUiBE
RUZBVUxUIFBSSVZJTEVHRVMgRk9SIFJPTEUgcG9zdGdyZXMgSU4gU0NIRU1BIGJhciBSRVZPS0Ug
QUxMIE9OIEZVTkNUSU9OUyDCoEZST00gcG9zdGdyZXM7PC9kaXY+PGRpdj4mZ3Q7IEFMVEVSIERF
RkFVTFQgUFJJVklMRUdFUyBGT1IgUk9MRSBwb3N0Z3JlcyBSRVZPS0UgQUxMIE9OIEZVTkNUSU9O
UyDCoEZST00gUFVCTElDOzwvZGl2PjxkaXY+Jmd0OyBidWc9IyBcZGRwPC9kaXY+PGRpdj4mZ3Q7
IMKgIMKgIMKgIMKgIMKgIMKgIMKgRGVmYXVsdCBhY2Nlc3MgcHJpdmlsZWdlczwvZGl2PjxkaXY+
Jmd0OyDCoCBPd25lciDCoCB8IFNjaGVtYSB8IMKgIFR5cGUgwqAgfCDCoEFjY2VzcyBwcml2aWxl
Z2VzIMKgPC9kaXY+PGRpdj4mZ3Q7IC0tLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0t
LS0tLS0tLS0tLS0tLS0tLS08L2Rpdj48ZGl2PiZndDsgwqBwb3N0Z3JlcyB8IMKgIMKgIMKgIMKg
fCBmdW5jdGlvbiB8IHBvc3RncmVzPVgvcG9zdGdyZXM8L2Rpdj48ZGl2PiZndDsgLS0gSSBjYW4g
Tk9UIGV4ZWN1dGUgbmV3bHkgY3JlYXRlZCBmdW5jdGlvbnMgb24gc2NoZW1lICdiYXInIGFmdGVy
IHRoYXQuPC9kaXY+PGRpdj7CoDwvZGl2PjxkaXY+QWZ0ZXIgcmVzdG9yaW5nIHRoZSBkdW1wIEkg
Y2Fubm90IGV4ZWN1dGUgbmV3bHkgY3JlYXRlZCBmdW5jdGlvbnMgb24gc2NoZW1lICdiYXInLjwv
ZGl2PjxkaXY+SSd2ZSBhdHRhY2hlZCB0aGUgc2hlbGwtc2NyaXB0IHRoYXQgY3JlYXRlIHRoZSBl
bnZpcm9ubWVudC48L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj5JIGNyZWF0ZSBhIGZ1bmN0aW9uIGFm
dGVyIGR1bXAgb3IgcmVzdG9yZSBwcm9jZWR1cmVzIChleC4gYmFja3VwIHB1cnBvc2VzKTo8L2Rp
dj48ZGl2PjxkaXY+Jmd0OyBDUkVBVEUgRlVOQ1RJT04gYmFyLm5ldygpIFJFVFVSTlMgYm9vbDwv
ZGl2PjxkaXY+Jmd0OyDCoCDCoCBJTU1VVEFCTEU8L2Rpdj48ZGl2PiZndDsgwqAgwqAgTEFOR1VB
R0Ugc3FsPC9kaXY+PGRpdj4mZ3Q7IEFTICdTRUxFQ1QgVFJVRTsnOzwvZGl2PjxkaXY+VGhlIGZ1
bmN0aW9uIGJhci5uZXcoKSBjYW5ub3QgYmUgZXhlY3V0ZWQgYnkgJ2J1ZycgdXNlciBpZiBjcmVh
dGVkIGFmdGVyIHRoZSByZXN0b3JlIHByb2NlZHVyZS48L2Rpdj48ZGl2PkkndmUgZ290IGFuIGVy
cm9yOiBFUlJPUjogwqBwZXJtaXNzaW9uIGRlbmllZCBmb3IgZnVuY3Rpb24gbmV3LjwvZGl2Pjxk
aXY+wqA8L2Rpdj48L2Rpdj48ZGl2PldCUiw8L2Rpdj48ZGl2PkJvcmlzPC9kaXY+PC9kaXY+
------==--bound.1064580.iva5-4e96f110c519.qloud-c.yandex.net
Content-Disposition: attachment;
filename="sql-def_priv_bug.sh"
Content-Transfer-Encoding: base64
Content-Type: text/x-shellscript;
name="sql-def_priv_bug.sh"
IyEvYmluL3NoCgpQU1FMPSJwc3FsIC12IE9OX0VSUk9SX1NUT1A9MSIKCmV4cG9ydCBQR1VTRVI9
InBvc3RncmVzIgpleHBvcnQgUEdIT1NUPSIxMjcuMC4wLjEiCmV4cG9ydCBQR1BPUlQ9IjU0MzIi
CmV4cG9ydCBQR1NTTE1PREU9ImRpc2FibGUiCgpleHBvcnQgUEdEQVRBQkFTRT0idGVtcGxhdGUx
IgpleHBvcnQgUEdVU0VSPSJwb3N0Z3JlcyIKCiR7UFNRTH0gPDwgRSpPKkYgfHwgZXhpdCAxClNF
TEVDVCBwZ190ZXJtaW5hdGVfYmFja2VuZChwaWQpCkZST00gcGdfc3RhdF9hY3Rpdml0eQpXSEVS
RSBkYXRuYW1lID0gJ2J1ZycKICBBTkQgcGlkIDw+IHBnX2JhY2tlbmRfcGlkKCk7CgpEUk9QIERB
VEFCQVNFIElGIEVYSVNUUyAiYnVnIjsKCkRST1AgUk9MRSBJRiBFWElTVFMgImJ1ZyI7CkNSRUFU
RSBST0xFICJidWciIE5PSU5IRVJJVCBMT0dJTiBCWVBBU1NSTFMgUEFTU1dPUkQgJzEyMzQ1Nic7
CgpDUkVBVEUgREFUQUJBU0UgImJ1ZyI7CkUqTypGCgpleHBvcnQgUEdEQVRBQkFTRT0iYnVnIgpl
eHBvcnQgUEdVU0VSPSJwb3N0Z3JlcyIKCiR7UFNRTH0gLXEgPDwgRSpPKkYgfHwgZXhpdCAxCkFM
VEVSIERFRkFVTFQgUFJJVklMRUdFUyBSRVZPS0UgQUxMIE9OIEZVTkNUSU9OUyBGUk9NIFBVQkxJ
QzsKCkNSRUFURSBTQ0hFTUEgZm9vOwpHUkFOVCBVU0FHRSBPTiBTQ0hFTUEgZm9vIFRPICJidWci
OwpDUkVBVEUgRlVOQ1RJT04gZm9vLmZvbygpIFJFVFVSTlMgYm9vbAogICAgSU1NVVRBQkxFCiAg
ICBMQU5HVUFHRSBzcWwKQVMKXCRfXCQKU0VMRUNUIFRSVUU7ClwkX1wkOwoKQ1JFQVRFIFNDSEVN
QSBiYXI7CkdSQU5UIFVTQUdFIE9OIFNDSEVNQSBiYXIgVE8gImJ1ZyI7CkFMVEVSIERFRkFVTFQg
UFJJVklMRUdFUyBJTiBTQ0hFTUEgYmFyIEdSQU5UIEVYRUNVVEUgT04gRlVOQ1RJT05TIFRPIFBV
QkxJQzsKQ1JFQVRFIEZVTkNUSU9OIGJhci5iYXIoKSBSRVRVUk5TIGJvb2wKICAgIElNTVVUQUJM
RQogICAgTEFOR1VBR0Ugc3FsCkFTClwkX1wkClNFTEVDVCBGQUxTRTsKXCRfXCQ7CkUqTypG
------==--bound.1064580.iva5-4e96f110c519.qloud-c.yandex.net--
Attachments:
sql-def_priv_bug.sh (text/x-shellscript, 1023B) download
#!/bin/sh
PSQL="psql -v ON_ERROR_STOP=1"
export PGUSER="postgres"
export PGHOST="127.0.0.1"
export PGPORT="5432"
export PGSSLMODE="disable"
export PGDATABASE="template1"
export PGUSER="postgres"
${PSQL} << E*O*F || exit 1
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'bug'
AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS "bug";
DROP ROLE IF EXISTS "bug";
CREATE ROLE "bug" NOINHERIT LOGIN BYPASSRLS PASSWORD '123456';
CREATE DATABASE "bug";
E*O*F
export PGDATABASE="bug"
export PGUSER="postgres"
${PSQL} -q << E*O*F || exit 1
ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM PUBLIC;
CREATE SCHEMA foo;
GRANT USAGE ON SCHEMA foo TO "bug";
CREATE FUNCTION foo.foo() RETURNS bool
IMMUTABLE
LANGUAGE sql
AS
\$_\$
SELECT TRUE;
\$_\$;
CREATE SCHEMA bar;
GRANT USAGE ON SCHEMA bar TO "bug";
ALTER DEFAULT PRIVILEGES IN SCHEMA bar GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
CREATE FUNCTION bar.bar() RETURNS bool
IMMUTABLE
LANGUAGE sql
AS
\$_\$
SELECT FALSE;
\$_\$;
E*O*F
view thread (26+ messages) latest in thread
Message-ID: <111621616618184@mail.yandex.ru>
Permalink: ../111621616618184@mail.yandex.ru/
Also on: postgresql.org/message-id/111621616618184@mail.yandex.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: drtr0jan@yandex.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
In-Reply-To: <111621616618184@mail.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox