agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/2] Make ALTER SEQUENCE, including RESTART, fully transactional. 3+ messages / 3 participants [nested] [flat]
* [PATCH 1/2] Make ALTER SEQUENCE, including RESTART, fully transactional. @ 2017-05-31 23:39 Andres Freund <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Andres Freund @ 2017-05-31 23:39 UTC (permalink / raw) Previously the changes to the "data" part of the sequence, i.e. the one containing the current value, were not transactional, whereas the definition, including minimum and maximum value were. That leads to odd behaviour if a schema change is rolled back, with the potential that out-of-bound sequence values can be returned. To avoid the issue create a new relfilenode fork whenever ALTER SEQUENCE is executed, similar to how TRUNCATE ... RESTART IDENTITY already is already handled. This also makes ALTER SEQUENCE RESTART transactional, as it seems to be too confusing to have some forms of ALTER SEQUENCE behave transactionally, some forms not. This way setval() and nextval() are not transactional, but DDL is, which seems to make sense. This commit also rolls back parts of the changes made in 3d092fe540 and f8dc1985f as they're now not needed anymore. Author: Andres Freund Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/ref/alter_sequence.sgml | 15 ++-- src/backend/commands/sequence.c | 123 +++++++-------------------- src/test/isolation/expected/sequence-ddl.out | 92 ++++++++++---------- src/test/isolation/specs/sequence-ddl.spec | 19 +++-- 4 files changed, 96 insertions(+), 153 deletions(-) diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml index 30e5316b8c..3a04d07ecc 100644 --- a/doc/src/sgml/ref/alter_sequence.sgml +++ b/doc/src/sgml/ref/alter_sequence.sgml @@ -171,7 +171,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S <para> The optional clause <literal>RESTART [ WITH <replaceable class="parameter">restart</replaceable> ]</literal> changes the - current value of the sequence. This is equivalent to calling the + current value of the sequence. This is similar to calling the <function>setval</> function with <literal>is_called</literal> = <literal>false</>: the specified value will be returned by the <emphasis>next</> call of <function>nextval</>. @@ -182,11 +182,11 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S </para> <para> - Like a <function>setval</function> call, a <literal>RESTART</literal> - operation on a sequence is never rolled back, to avoid blocking of - concurrent transactions that obtain numbers from the same sequence. - (The other clauses cause ordinary catalog updates that can be rolled - back.) + In contrast to a <function>setval</function> call, + a <literal>RESTART</literal> operation on a sequence is transactional + and blocks concurrent transactions from obtaining numbers from the + same sequence. If that's not the desired mode of + operation, <function>setval</> should be used. </para> </listitem> </varlistentry> @@ -307,8 +307,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S <para> <command>ALTER SEQUENCE</command> blocks concurrent <function>nextval</function>, <function>currval</function>, - <function>lastval</function>, and <command>setval</command> calls, except - if only the <literal>RESTART</literal> clause is used. + <function>lastval</function>, and <command>setval</command> calls. </para> <para> diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 568b3022f2..4a56f03e8b 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -98,11 +98,9 @@ static void create_seq_hashtable(void); static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel); static Form_pg_sequence_data read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple); -static LOCKMODE alter_sequence_get_lock_level(List *options); static void init_params(ParseState *pstate, List *options, bool for_identity, bool isInit, Form_pg_sequence seqform, - bool *changed_seqform, Form_pg_sequence_data seqdataform, List **owned_by); static void do_setval(Oid relid, int64 next, bool iscalled); static void process_owned_by(Relation seqrel, List *owned_by, bool for_identity); @@ -117,7 +115,6 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) { FormData_pg_sequence seqform; FormData_pg_sequence_data seqdataform; - bool changed_seqform = false; /* not used here */ List *owned_by; CreateStmt *stmt = makeNode(CreateStmt); Oid seqoid; @@ -156,7 +153,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) } /* Check and set all option values */ - init_params(pstate, seq->options, seq->for_identity, true, &seqform, &changed_seqform, &seqdataform, &owned_by); + init_params(pstate, seq->options, seq->for_identity, true, &seqform, &seqdataform, &owned_by); /* * Create relation (and fill value[] and null[] for the tuple) @@ -417,19 +414,18 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) SeqTable elm; Relation seqrel; Buffer buf; - HeapTupleData seqdatatuple; + HeapTupleData datatuple; Form_pg_sequence seqform; - Form_pg_sequence_data seqdata; - FormData_pg_sequence_data newseqdata; - bool changed_seqform = false; + Form_pg_sequence_data newdataform; List *owned_by; ObjectAddress address; Relation rel; - HeapTuple tuple; + HeapTuple seqtuple; + HeapTuple newdatatuple; /* Open and lock sequence. */ relid = RangeVarGetRelid(stmt->sequence, - alter_sequence_get_lock_level(stmt->options), + ShareRowExclusiveLock, stmt->missing_ok); if (relid == InvalidOid) { @@ -447,22 +443,26 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) stmt->sequence->relname); rel = heap_open(SequenceRelationId, RowExclusiveLock); - tuple = SearchSysCacheCopy1(SEQRELID, - ObjectIdGetDatum(relid)); - if (!HeapTupleIsValid(tuple)) + seqtuple = SearchSysCacheCopy1(SEQRELID, + ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(seqtuple)) elog(ERROR, "cache lookup failed for sequence %u", relid); - seqform = (Form_pg_sequence) GETSTRUCT(tuple); + seqform = (Form_pg_sequence) GETSTRUCT(seqtuple); /* lock page's buffer and read tuple into new sequence structure */ - seqdata = read_seq_tuple(seqrel, &buf, &seqdatatuple); + (void) read_seq_tuple(seqrel, &buf, &datatuple); - /* Copy old sequence data into workspace */ - memcpy(&newseqdata, seqdata, sizeof(FormData_pg_sequence_data)); + /* copy the existing sequence data tuple, so it can be modified localy */ + newdatatuple = heap_copytuple(&datatuple); + newdataform = (Form_pg_sequence_data) GETSTRUCT(newdatatuple); + + UnlockReleaseBuffer(buf); /* Check and set new values */ - init_params(pstate, stmt->options, stmt->for_identity, false, seqform, &changed_seqform, &newseqdata, &owned_by); + init_params(pstate, stmt->options, stmt->for_identity, false, seqform, + newdataform, &owned_by); /* Clear local cache so that we don't think we have cached numbers */ /* Note that we do not change the currval() state */ @@ -472,36 +472,19 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) if (RelationNeedsWAL(seqrel)) GetTopTransactionId(); - /* Now okay to update the on-disk tuple */ - START_CRIT_SECTION(); + /* + * Create a new storage file for the sequence, making the state changes + * transactional. We want to keep the sequence's relfrozenxid at 0, since + * it won't contain any unfrozen XIDs. Same with relminmxid, since a + * sequence will never contain multixacts. + */ + RelationSetNewRelfilenode(seqrel, seqrel->rd_rel->relpersistence, + InvalidTransactionId, InvalidMultiXactId); - memcpy(seqdata, &newseqdata, sizeof(FormData_pg_sequence_data)); - - MarkBufferDirty(buf); - - /* XLOG stuff */ - if (RelationNeedsWAL(seqrel)) - { - xl_seq_rec xlrec; - XLogRecPtr recptr; - Page page = BufferGetPage(buf); - - XLogBeginInsert(); - XLogRegisterBuffer(0, buf, REGBUF_WILL_INIT); - - xlrec.node = seqrel->rd_node; - XLogRegisterData((char *) &xlrec, sizeof(xl_seq_rec)); - - XLogRegisterData((char *) seqdatatuple.t_data, seqdatatuple.t_len); - - recptr = XLogInsert(RM_SEQ_ID, XLOG_SEQ_LOG); - - PageSetLSN(page, recptr); - } - - END_CRIT_SECTION(); - - UnlockReleaseBuffer(buf); + /* + * Insert the modified tuple into the new storage file. + */ + fill_seq_with_data(seqrel, newdatatuple); /* process OWNED BY if given */ if (owned_by) @@ -511,10 +494,9 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) ObjectAddressSet(address, RelationRelationId, relid); - if (changed_seqform) - CatalogTupleUpdate(rel, &tuple->t_self, tuple); - heap_close(rel, RowExclusiveLock); + CatalogTupleUpdate(rel, &seqtuple->t_self, seqtuple); + heap_close(rel, RowExclusiveLock); relation_close(seqrel, NoLock); return address; @@ -1220,30 +1202,6 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple) } /* - * Check the sequence options list and return the appropriate lock level for - * ALTER SEQUENCE. - * - * Most sequence option changes require a self-exclusive lock and should block - * concurrent nextval() et al. But RESTART does not, because it's not - * transactional. Also take a lower lock if no option at all is present. - */ -static LOCKMODE -alter_sequence_get_lock_level(List *options) -{ - ListCell *option; - - foreach(option, options) - { - DefElem *defel = (DefElem *) lfirst(option); - - if (strcmp(defel->defname, "restart") != 0) - return ShareRowExclusiveLock; - } - - return RowExclusiveLock; -} - -/* * init_params: process the options list of CREATE or ALTER SEQUENCE, and * store the values into appropriate fields of seqform, for changes that go * into the pg_sequence catalog, and seqdataform for changes to the sequence @@ -1258,7 +1216,6 @@ static void init_params(ParseState *pstate, List *options, bool for_identity, bool isInit, Form_pg_sequence seqform, - bool *changed_seqform, Form_pg_sequence_data seqdataform, List **owned_by) { @@ -1378,8 +1335,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, defel->defname); } - *changed_seqform = false; - /* * We must reset log_cnt when isInit or when changing any parameters that * would affect future nextval allocations. @@ -1420,19 +1375,16 @@ init_params(ParseState *pstate, List *options, bool for_identity, } seqform->seqtypid = newtypid; - *changed_seqform = true; } else if (isInit) { seqform->seqtypid = INT8OID; - *changed_seqform = true; } /* INCREMENT BY */ if (increment_by != NULL) { seqform->seqincrement = defGetInt64(increment_by); - *changed_seqform = true; if (seqform->seqincrement == 0) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), @@ -1442,28 +1394,24 @@ init_params(ParseState *pstate, List *options, bool for_identity, else if (isInit) { seqform->seqincrement = 1; - *changed_seqform = true; } /* CYCLE */ if (is_cycled != NULL) { seqform->seqcycle = intVal(is_cycled->arg); - *changed_seqform = true; Assert(BoolIsValid(seqform->seqcycle)); seqdataform->log_cnt = 0; } else if (isInit) { seqform->seqcycle = false; - *changed_seqform = true; } /* MAXVALUE (null arg means NO MAXVALUE) */ if (max_value != NULL && max_value->arg) { seqform->seqmax = defGetInt64(max_value); - *changed_seqform = true; seqdataform->log_cnt = 0; } else if (isInit || max_value != NULL || reset_max_value) @@ -1480,7 +1428,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, } else seqform->seqmax = -1; /* descending seq */ - *changed_seqform = true; seqdataform->log_cnt = 0; } @@ -1502,7 +1449,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, if (min_value != NULL && min_value->arg) { seqform->seqmin = defGetInt64(min_value); - *changed_seqform = true; seqdataform->log_cnt = 0; } else if (isInit || min_value != NULL || reset_min_value) @@ -1519,7 +1465,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, } else seqform->seqmin = 1; /* ascending seq */ - *changed_seqform = true; seqdataform->log_cnt = 0; } @@ -1555,7 +1500,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, if (start_value != NULL) { seqform->seqstart = defGetInt64(start_value); - *changed_seqform = true; } else if (isInit) { @@ -1563,7 +1507,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, seqform->seqstart = seqform->seqmin; /* ascending seq */ else seqform->seqstart = seqform->seqmax; /* descending seq */ - *changed_seqform = true; } /* crosscheck START */ @@ -1638,7 +1581,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, if (cache_value != NULL) { seqform->seqcache = defGetInt64(cache_value); - *changed_seqform = true; if (seqform->seqcache <= 0) { char buf[100]; @@ -1654,7 +1596,6 @@ init_params(ParseState *pstate, List *options, bool for_identity, else if (isInit) { seqform->seqcache = 1; - *changed_seqform = true; } } diff --git a/src/test/isolation/expected/sequence-ddl.out b/src/test/isolation/expected/sequence-ddl.out index 6b7119738f..6766c0aff6 100644 --- a/src/test/isolation/expected/sequence-ddl.out +++ b/src/test/isolation/expected/sequence-ddl.out @@ -13,6 +13,52 @@ step s1commit: COMMIT; step s2nv: <... completed> error in steps s1commit s2nv: ERROR: nextval: reached maximum value of sequence "seq1" (10) +starting permutation: s1restart s2nv s1commit +step s1restart: ALTER SEQUENCE seq1 RESTART WITH 5; +step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15); <waiting ...> +step s1commit: COMMIT; +step s2nv: <... completed> +nextval + +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 + +starting permutation: s1restart s2nv s1commit +step s1restart: ALTER SEQUENCE seq1 RESTART WITH 5; +step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15); <waiting ...> +step s1commit: COMMIT; +step s2nv: <... completed> +nextval + +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 + starting permutation: s2begin s2nv s1alter2 s2commit s1commit step s2begin: BEGIN; step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15); @@ -37,49 +83,3 @@ step s1alter2: ALTER SEQUENCE seq1 MAXVALUE 20; <waiting ...> step s2commit: COMMIT; step s1alter2: <... completed> step s1commit: COMMIT; - -starting permutation: s1restart s2nv s1commit -step s1restart: ALTER SEQUENCE seq1 RESTART WITH 5; -step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15); -nextval - -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -16 -17 -18 -19 -step s1commit: COMMIT; - -starting permutation: s2begin s2nv s1restart s2commit s1commit -step s2begin: BEGIN; -step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15); -nextval - -1 -2 -3 -4 -5 -6 -7 -8 -9 -10 -11 -12 -13 -14 -15 -step s1restart: ALTER SEQUENCE seq1 RESTART WITH 5; -step s2commit: COMMIT; -step s1commit: COMMIT; diff --git a/src/test/isolation/specs/sequence-ddl.spec b/src/test/isolation/specs/sequence-ddl.spec index 42ee3b0615..5c51fcdae6 100644 --- a/src/test/isolation/specs/sequence-ddl.spec +++ b/src/test/isolation/specs/sequence-ddl.spec @@ -15,6 +15,7 @@ setup { BEGIN; } step "s1alter" { ALTER SEQUENCE seq1 MAXVALUE 10; } step "s1alter2" { ALTER SEQUENCE seq1 MAXVALUE 20; } step "s1restart" { ALTER SEQUENCE seq1 RESTART WITH 5; } +step "s1setval" { SELECT setval('seq1', 5); } step "s1commit" { COMMIT; } session "s2" @@ -24,16 +25,18 @@ step "s2commit" { COMMIT; } permutation "s1alter" "s1commit" "s2nv" -# Prior to PG10, the s2nv would see the uncommitted s1alter change, -# but now it waits. +# Prior to PG10, the s2nv step would see the uncommitted s1alter +# change, but now it waits. permutation "s1alter" "s2nv" "s1commit" +# Prior to PG10, the s2nv step would see the uncommitted s1reset +# change, but now it waits. +permutation "s1restart" "s2nv" "s1commit" + +# In contrast to ALTER setval() is non-transactional, so it doesn't +# have to wait. +permutation "s1restart" "s2nv" "s1commit" + # nextval doesn't release lock until transaction end, so s1alter2 has # to wait for s2commit. permutation "s2begin" "s2nv" "s1alter2" "s2commit" "s1commit" - -# RESTART is nontransactional, so s2nv sees it right away -permutation "s1restart" "s2nv" "s1commit" - -# RESTART does not wait -permutation "s2begin" "s2nv" "s1restart" "s2commit" "s1commit" -- 2.12.0.264.gd6db3f2165.dirty --qdyaxyrhr23whw5z Content-Type: text/x-patch; charset=us-ascii Content-Disposition: attachment; filename="0002-Modify-sequence-catalog-tuple-before-invoking-post-a.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH 4/5] pg_rewind: Refactor the abstraction to fetch from local/libpq source. @ 2020-08-19 12:34 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Heikki Linnakangas @ 2020-08-19 12:34 UTC (permalink / raw) There copy_executeFileMap() and libpq_executeFileMap() contained basically the same logic, just calling different functions to fetch the source files. Refactor so that the common logic is in one place, execute_file_actions(). This makes the abstraction of a "source" server more clear, by introducing a common abstract class, borrowing the object-oriented programming term, that represents all the operations that can be done on the source server. There are two implementations of it, one for fetching via libpq, and another to fetch from a local directory. This adds some code, but makes it easier to understand what's going on. --- src/bin/pg_rewind/copy_fetch.c | 239 +++++---------------- src/bin/pg_rewind/fetch.c | 97 ++++++--- src/bin/pg_rewind/fetch.h | 76 +++++-- src/bin/pg_rewind/file_ops.c | 129 +++++++++++- src/bin/pg_rewind/file_ops.h | 3 + src/bin/pg_rewind/libpq_fetch.c | 361 +++++++++++++++----------------- src/bin/pg_rewind/pg_rewind.c | 70 +++++-- src/bin/pg_rewind/pg_rewind.h | 5 - 8 files changed, 527 insertions(+), 453 deletions(-) diff --git a/src/bin/pg_rewind/copy_fetch.c b/src/bin/pg_rewind/copy_fetch.c index 61aed8018b6..9927a45a07a 100644 --- a/src/bin/pg_rewind/copy_fetch.c +++ b/src/bin/pg_rewind/copy_fetch.c @@ -1,7 +1,7 @@ /*------------------------------------------------------------------------- * * copy_fetch.c - * Functions for using a data directory as the source. + * Functions for using a local data directory as the source. * * Portions Copyright (c) 2013-2020, PostgreSQL Global Development Group * @@ -9,8 +9,6 @@ */ #include "postgres_fe.h" -#include <sys/stat.h> -#include <dirent.h> #include <fcntl.h> #include <unistd.h> @@ -20,146 +18,70 @@ #include "filemap.h" #include "pg_rewind.h" -static void recurse_dir(const char *datadir, const char *path, - process_file_callback_t callback); - -static void execute_pagemap(datapagemap_t *pagemap, const char *path); - -/* - * Traverse through all files in a data directory, calling 'callback' - * for each file. - */ -void -traverse_datadir(const char *datadir, process_file_callback_t callback) +typedef struct { - recurse_dir(datadir, NULL, callback); -} - -/* - * recursive part of traverse_datadir - * - * parentpath is the current subdirectory's path relative to datadir, - * or NULL at the top level. - */ -static void -recurse_dir(const char *datadir, const char *parentpath, - process_file_callback_t callback) + rewind_source common; /* common interface functions */ + + const char *datadir; /* path to the source data directory */ +} local_source; + +static void local_traverse_files(rewind_source *source, + process_file_callback_t callback); +static char *local_fetch_file(rewind_source *source, const char *path, + size_t *filesize); +static void local_fetch_file_range(rewind_source *source, const char *path, + uint64 off, size_t len); +static void local_finish_fetch(rewind_source *source); +static void local_destroy(rewind_source *source); + +rewind_source * +init_local_source(const char *datadir) { - DIR *xldir; - struct dirent *xlde; - char fullparentpath[MAXPGPATH]; + local_source *src; - if (parentpath) - snprintf(fullparentpath, MAXPGPATH, "%s/%s", datadir, parentpath); - else - snprintf(fullparentpath, MAXPGPATH, "%s", datadir); + src = pg_malloc0(sizeof(local_source)); - xldir = opendir(fullparentpath); - if (xldir == NULL) - pg_fatal("could not open directory \"%s\": %m", - fullparentpath); + src->common.traverse_files = local_traverse_files; + src->common.fetch_file = local_fetch_file; + src->common.queue_fetch_range = local_fetch_file_range; + src->common.finish_fetch = local_finish_fetch; + src->common.get_current_wal_insert_lsn = NULL; + src->common.destroy = local_destroy; - while (errno = 0, (xlde = readdir(xldir)) != NULL) - { - struct stat fst; - char fullpath[MAXPGPATH * 2]; - char path[MAXPGPATH * 2]; + src->datadir = datadir; - if (strcmp(xlde->d_name, ".") == 0 || - strcmp(xlde->d_name, "..") == 0) - continue; - - snprintf(fullpath, sizeof(fullpath), "%s/%s", fullparentpath, xlde->d_name); - - if (lstat(fullpath, &fst) < 0) - { - if (errno == ENOENT) - { - /* - * File doesn't exist anymore. This is ok, if the new primary - * is running and the file was just removed. If it was a data - * file, there should be a WAL record of the removal. If it - * was something else, it couldn't have been anyway. - * - * TODO: But complain if we're processing the target dir! - */ - } - else - pg_fatal("could not stat file \"%s\": %m", - fullpath); - } - - if (parentpath) - snprintf(path, sizeof(path), "%s/%s", parentpath, xlde->d_name); - else - snprintf(path, sizeof(path), "%s", xlde->d_name); - - if (S_ISREG(fst.st_mode)) - callback(path, FILE_TYPE_REGULAR, fst.st_size, NULL); - else if (S_ISDIR(fst.st_mode)) - { - callback(path, FILE_TYPE_DIRECTORY, 0, NULL); - /* recurse to handle subdirectories */ - recurse_dir(datadir, path, callback); - } -#ifndef WIN32 - else if (S_ISLNK(fst.st_mode)) -#else - else if (pgwin32_is_junction(fullpath)) -#endif - { -#if defined(HAVE_READLINK) || defined(WIN32) - char link_target[MAXPGPATH]; - int len; - - len = readlink(fullpath, link_target, sizeof(link_target)); - if (len < 0) - pg_fatal("could not read symbolic link \"%s\": %m", - fullpath); - if (len >= sizeof(link_target)) - pg_fatal("symbolic link \"%s\" target is too long", - fullpath); - link_target[len] = '\0'; - - callback(path, FILE_TYPE_SYMLINK, 0, link_target); - - /* - * If it's a symlink within pg_tblspc, we need to recurse into it, - * to process all the tablespaces. We also follow a symlink if - * it's for pg_wal. Symlinks elsewhere are ignored. - */ - if ((parentpath && strcmp(parentpath, "pg_tblspc") == 0) || - strcmp(path, "pg_wal") == 0) - recurse_dir(datadir, path, callback); -#else - pg_fatal("\"%s\" is a symbolic link, but symbolic links are not supported on this platform", - fullpath); -#endif /* HAVE_READLINK */ - } - } + return &src->common; +} - if (errno) - pg_fatal("could not read directory \"%s\": %m", - fullparentpath); +static void +local_traverse_files(rewind_source *source, process_file_callback_t callback) +{ + traverse_datadir(((local_source *) source)->datadir, &process_source_file); +} - if (closedir(xldir)) - pg_fatal("could not close directory \"%s\": %m", - fullparentpath); +static char * +local_fetch_file(rewind_source *source, const char *path, size_t *filesize) +{ + return slurpFile(((local_source *) source)->datadir, path, filesize); } /* - * Copy a file from source to target, between 'begin' and 'end' offsets. + * Copy a file from source to target, starting at 'off', for 'len' bytes. * * If 'trunc' is true, any existing file with the same name is truncated. */ static void -rewind_copy_file_range(const char *path, off_t begin, off_t end, bool trunc) +local_fetch_file_range(rewind_source *source, const char *path, uint64 off, + size_t len) { + const char *datadir = ((local_source *) source)->datadir; PGAlignedBlock buf; char srcpath[MAXPGPATH]; int srcfd; + uint64 begin = off; + uint64 end = off + len; - snprintf(srcpath, sizeof(srcpath), "%s/%s", datadir_source, path); + snprintf(srcpath, sizeof(srcpath), "%s/%s", datadir, path); srcfd = open(srcpath, O_RDONLY | PG_BINARY, 0); if (srcfd < 0) @@ -169,7 +91,7 @@ rewind_copy_file_range(const char *path, off_t begin, off_t end, bool trunc) if (lseek(srcfd, begin, SEEK_SET) == -1) pg_fatal("could not seek in source file: %m"); - open_target_file(path, trunc); + open_target_file(path, false); while (end - begin > 0) { @@ -197,70 +119,17 @@ rewind_copy_file_range(const char *path, off_t begin, off_t end, bool trunc) pg_fatal("could not close file \"%s\": %m", srcpath); } -/* - * Copy all relation data files from datadir_source to datadir_target, which - * are marked in the given data page map. - */ -void -copy_executeFileMap(filemap_t *map) +static void +local_finish_fetch(rewind_source *source) { - file_entry_t *entry; - int i; - - for (i = 0; i < map->nactions; i++) - { - entry = map->actions[i]; - execute_pagemap(&entry->target_modified_pages, entry->path); - - switch (entry->action) - { - case FILE_ACTION_NONE: - /* ok, do nothing.. */ - break; - - case FILE_ACTION_COPY: - rewind_copy_file_range(entry->path, 0, entry->source_size, true); - break; - - case FILE_ACTION_TRUNCATE: - truncate_target_file(entry->path, entry->source_size); - break; - - case FILE_ACTION_COPY_TAIL: - rewind_copy_file_range(entry->path, entry->target_size, - entry->source_size, false); - break; - - case FILE_ACTION_CREATE: - create_target(entry); - break; - - case FILE_ACTION_REMOVE: - remove_target(entry); - break; - - case FILE_ACTION_UNDECIDED: - pg_fatal("no action decided for \"%s\"", entry->path); - break; - } - } - - close_target_file(); + /* + * Nothing to do, local_fetch_file_range() performs the fetching + * immediately. + */ } static void -execute_pagemap(datapagemap_t *pagemap, const char *path) +local_destroy(rewind_source *source) { - datapagemap_iterator_t *iter; - BlockNumber blkno; - off_t offset; - - iter = datapagemap_iterate(pagemap); - while (datapagemap_next(iter, &blkno)) - { - offset = blkno * BLCKSZ; - rewind_copy_file_range(path, offset, offset + BLCKSZ, false); - /* Ok, this block has now been copied from new data dir to old */ - } - pg_free(iter); + pfree(source); } diff --git a/src/bin/pg_rewind/fetch.c b/src/bin/pg_rewind/fetch.c index f41d0f295ea..c8ee38f8e0b 100644 --- a/src/bin/pg_rewind/fetch.c +++ b/src/bin/pg_rewind/fetch.c @@ -24,37 +24,78 @@ #include "filemap.h" #include "pg_rewind.h" -void -fetchSourceFileList(void) -{ - if (datadir_source) - traverse_datadir(datadir_source, &process_source_file); - else - libpqProcessFileList(); -} - /* - * Fetch all relation data files that are marked in the given data page map. + * Execute the actions in the file map, fetching data from the source + * system as needed. */ void -execute_file_actions(filemap_t *filemap) +execute_file_actions(filemap_t *filemap, rewind_source *source) { - if (datadir_source) - copy_executeFileMap(filemap); - else - libpq_executeFileMap(filemap); -} + int i; -/* - * Fetch a single file into a malloc'd buffer. The file size is returned - * in *filesize. The returned buffer is always zero-terminated, which is - * handy for text files. - */ -char * -fetchFile(const char *filename, size_t *filesize) -{ - if (datadir_source) - return slurpFile(datadir_source, filename, filesize); - else - return libpqGetFile(filename, filesize); + for (i = 0; i < filemap->nactions; i++) + { + file_entry_t *entry = filemap->actions[i]; + datapagemap_iterator_t *iter; + BlockNumber blkno; + off_t offset; + + /* + * If this is a relation file, copy the modified blocks. + * + * This is in addition to any other changes. + */ + iter = datapagemap_iterate(&entry->target_modified_pages); + while (datapagemap_next(iter, &blkno)) + { + offset = blkno * BLCKSZ; + + source->queue_fetch_range(source, entry->path, offset, BLCKSZ); + } + pg_free(iter); + + switch (entry->action) + { + case FILE_ACTION_NONE: + /* nothing else to do */ + break; + + case FILE_ACTION_COPY: + /* Truncate the old file out of the way, if any */ + open_target_file(entry->path, true); + source->queue_fetch_range(source, entry->path, + 0, entry->source_size); + break; + + case FILE_ACTION_TRUNCATE: + truncate_target_file(entry->path, entry->source_size); + break; + + case FILE_ACTION_COPY_TAIL: + source->queue_fetch_range(source, entry->path, + entry->target_size, + entry->source_size - entry->target_size); + break; + + case FILE_ACTION_REMOVE: + remove_target(entry); + break; + + case FILE_ACTION_CREATE: + create_target(entry); + break; + + case FILE_ACTION_UNDECIDED: + pg_fatal("no action decided for \"%s\"", entry->path); + break; + } + } + + /* + * We've now copied the list of file ranges that we need to fetch to the + * temporary table. Now, actually fetch all of those ranges. XXX + */ + source->finish_fetch(source); + + close_target_file(); } diff --git a/src/bin/pg_rewind/fetch.h b/src/bin/pg_rewind/fetch.h index b20df8b1537..8be1a9582de 100644 --- a/src/bin/pg_rewind/fetch.h +++ b/src/bin/pg_rewind/fetch.h @@ -1,12 +1,12 @@ /*------------------------------------------------------------------------- * * fetch.h - * Fetching data from a local or remote data directory. + * Abstraction for fetching from source server. * - * This file includes the prototypes for functions used to copy files from - * one data directory to another. The source to copy from can be a local - * directory (copy method), or a remote PostgreSQL server (libpq fetch - * method). + * The source server can be either a libpq connection to a live system, or + * a local data directory. The 'rewind_source' struct abstracts the + * operations to fetch data from the source system, so that the rest of + * the code doesn't need to care what kind of a source its dealing with. * * Copyright (c) 2013-2020, PostgreSQL Global Development Group * @@ -16,29 +16,63 @@ #define FETCH_H #include "access/xlogdefs.h" - +#include "file_ops.h" #include "filemap.h" +#include "libpq-fe.h" + +typedef struct rewind_source +{ + /* + * Traverse all files in the source data directory, and call 'callback' + * on each file. + */ + void (*traverse_files) (struct rewind_source *, + process_file_callback_t callback); + + /* + * Fetch a single file into a malloc'd buffer. The file size is returned + * in *filesize. The returned buffer is always zero-terminated, which is + * handy for text files. + */ + char *(*fetch_file) (struct rewind_source *, const char *path, + size_t *filesize); + + /* + * Request to fetch (part of) a file in the source system, and write it + * the corresponding file in the target system. The source implementation + * may queue up the request and execute it later when convenient. Call + * finish_fetch() to flush the queue and execute all requests. + */ + void (*queue_fetch_range) (struct rewind_source *, const char *path, + uint64 offset, size_t len); + + /* + * Execute all requests queued up with queue_fetch_range(). + */ + void (*finish_fetch) (struct rewind_source *); + + /* + * Get the current WAL insert position in the source system. + */ + XLogRecPtr (*get_current_wal_insert_lsn) (struct rewind_source *); + + /* + * Free this rewind_source object. + */ + void (*destroy) (struct rewind_source *); + +} rewind_source; + /* - * Common interface. Calls the copy or libpq method depending on global - * config options. + * Execute all the actions in 'filemap'. */ -extern void fetchSourceFileList(void); -extern char *fetchFile(const char *filename, size_t *filesize); -extern void execute_file_actions(filemap_t *filemap); +extern void execute_file_actions(filemap_t *filemap, rewind_source *source); /* in libpq_fetch.c */ -extern void libpqProcessFileList(void); -extern char *libpqGetFile(const char *filename, size_t *filesize); -extern void libpq_executeFileMap(filemap_t *map); - -extern void libpqConnect(const char *connstr); -extern XLogRecPtr libpqGetCurrentXlogInsertLocation(void); +extern rewind_source *init_libpq_source(PGconn *conn); /* in copy_fetch.c */ -extern void copy_executeFileMap(filemap_t *map); - -typedef void (*process_file_callback_t) (const char *path, file_type_t type, size_t size, const char *link_target); -extern void traverse_datadir(const char *datadir, process_file_callback_t callback); +extern rewind_source *init_local_source(const char *datadir); #endif /* FETCH_H */ diff --git a/src/bin/pg_rewind/file_ops.c b/src/bin/pg_rewind/file_ops.c index ec37d0b2e0d..4ae343888ee 100644 --- a/src/bin/pg_rewind/file_ops.c +++ b/src/bin/pg_rewind/file_ops.c @@ -15,6 +15,7 @@ #include "postgres_fe.h" #include <sys/stat.h> +#include <dirent.h> #include <fcntl.h> #include <unistd.h> @@ -35,6 +36,9 @@ static void remove_target_dir(const char *path); static void create_target_symlink(const char *path, const char *link); static void remove_target_symlink(const char *path); +static void recurse_dir(const char *datadir, const char *parentpath, + process_file_callback_t callback); + /* * Open a target file for writing. If 'trunc' is true and the file already * exists, it will be truncated. @@ -305,9 +309,6 @@ sync_target_dir(void) * buffer is actually *filesize + 1. That's handy when reading a text file. * This function can be used to read binary files as well, you can just * ignore the zero-terminator in that case. - * - * This function is used to implement the fetchFile function in the "fetch" - * interface (see fetch.c), but is also called directly. */ char * slurpFile(const char *datadir, const char *path, size_t *filesize) @@ -352,3 +353,125 @@ slurpFile(const char *datadir, const char *path, size_t *filesize) *filesize = len; return buffer; } + +/* + * Traverse through all files in a data directory, calling 'callback' + * for each file. + */ +void +traverse_datadir(const char *datadir, process_file_callback_t callback) +{ + recurse_dir(datadir, NULL, callback); +} + +/* + * recursive part of traverse_datadir + * + * parentpath is the current subdirectory's path relative to datadir, + * or NULL at the top level. + */ +static void +recurse_dir(const char *datadir, const char *parentpath, + process_file_callback_t callback) +{ + DIR *xldir; + struct dirent *xlde; + char fullparentpath[MAXPGPATH]; + + if (parentpath) + snprintf(fullparentpath, MAXPGPATH, "%s/%s", datadir, parentpath); + else + snprintf(fullparentpath, MAXPGPATH, "%s", datadir); + + xldir = opendir(fullparentpath); + if (xldir == NULL) + pg_fatal("could not open directory \"%s\": %m", + fullparentpath); + + while (errno = 0, (xlde = readdir(xldir)) != NULL) + { + struct stat fst; + char fullpath[MAXPGPATH * 2]; + char path[MAXPGPATH * 2]; + + if (strcmp(xlde->d_name, ".") == 0 || + strcmp(xlde->d_name, "..") == 0) + continue; + + snprintf(fullpath, sizeof(fullpath), "%s/%s", fullparentpath, xlde->d_name); + + if (lstat(fullpath, &fst) < 0) + { + if (errno == ENOENT) + { + /* + * File doesn't exist anymore. This is ok, if the new primary + * is running and the file was just removed. If it was a data + * file, there should be a WAL record of the removal. If it + * was something else, it couldn't have been anyway. + * + * TODO: But complain if we're processing the target dir! + */ + } + else + pg_fatal("could not stat file \"%s\": %m", + fullpath); + } + + if (parentpath) + snprintf(path, sizeof(path), "%s/%s", parentpath, xlde->d_name); + else + snprintf(path, sizeof(path), "%s", xlde->d_name); + + if (S_ISREG(fst.st_mode)) + callback(path, FILE_TYPE_REGULAR, fst.st_size, NULL); + else if (S_ISDIR(fst.st_mode)) + { + callback(path, FILE_TYPE_DIRECTORY, 0, NULL); + /* recurse to handle subdirectories */ + recurse_dir(datadir, path, callback); + } +#ifndef WIN32 + else if (S_ISLNK(fst.st_mode)) +#else + else if (pgwin32_is_junction(fullpath)) +#endif + { +#if defined(HAVE_READLINK) || defined(WIN32) + char link_target[MAXPGPATH]; + int len; + + len = readlink(fullpath, link_target, sizeof(link_target)); + if (len < 0) + pg_fatal("could not read symbolic link \"%s\": %m", + fullpath); + if (len >= sizeof(link_target)) + pg_fatal("symbolic link \"%s\" target is too long", + fullpath); + link_target[len] = '\0'; + + callback(path, FILE_TYPE_SYMLINK, 0, link_target); + + /* + * If it's a symlink within pg_tblspc, we need to recurse into it, + * to process all the tablespaces. We also follow a symlink if + * it's for pg_wal. Symlinks elsewhere are ignored. + */ + if ((parentpath && strcmp(parentpath, "pg_tblspc") == 0) || + strcmp(path, "pg_wal") == 0) + recurse_dir(datadir, path, callback); +#else + pg_fatal("\"%s\" is a symbolic link, but symbolic links are not supported on this platform", + fullpath); +#endif /* HAVE_READLINK */ + } + } + + if (errno) + pg_fatal("could not read directory \"%s\": %m", + fullparentpath); + + if (closedir(xldir)) + pg_fatal("could not close directory \"%s\": %m", + fullparentpath); +} diff --git a/src/bin/pg_rewind/file_ops.h b/src/bin/pg_rewind/file_ops.h index d8466385cf5..c7630859768 100644 --- a/src/bin/pg_rewind/file_ops.h +++ b/src/bin/pg_rewind/file_ops.h @@ -23,4 +23,7 @@ extern void sync_target_dir(void); extern char *slurpFile(const char *datadir, const char *path, size_t *filesize); +typedef void (*process_file_callback_t) (const char *path, file_type_t type, size_t size, const char *link_target); +extern void traverse_datadir(const char *datadir, process_file_callback_t callback); + #endif /* FILE_OPS_H */ diff --git a/src/bin/pg_rewind/libpq_fetch.c b/src/bin/pg_rewind/libpq_fetch.c index 9c541bb73d5..52c4e147e10 100644 --- a/src/bin/pg_rewind/libpq_fetch.c +++ b/src/bin/pg_rewind/libpq_fetch.c @@ -1,7 +1,7 @@ /*------------------------------------------------------------------------- * * libpq_fetch.c - * Functions for fetching files from a remote server. + * Functions for fetching files from a remote server via libpq. * * Copyright (c) 2013-2020, PostgreSQL Global Development Group * @@ -9,11 +9,6 @@ */ #include "postgres_fe.h" -#include <sys/stat.h> -#include <dirent.h> -#include <fcntl.h> -#include <unistd.h> - #include "catalog/pg_type_d.h" #include "common/connect.h" #include "datapagemap.h" @@ -23,8 +18,6 @@ #include "pg_rewind.h" #include "port/pg_bswap.h" -PGconn *conn = NULL; - /* * Files are fetched max CHUNKSIZE bytes at a time. * @@ -34,30 +27,73 @@ PGconn *conn = NULL; */ #define CHUNKSIZE 1000000 -static void receiveFileChunks(const char *sql); -static void execute_pagemap(datapagemap_t *pagemap, const char *path); -static char *run_simple_query(const char *sql); -static void run_simple_command(const char *sql); +typedef struct +{ + rewind_source common; /* common interface functions */ + + PGconn *conn; +} libpq_source; + +static void init_libpq_conn(PGconn *conn); +static char *run_simple_query(PGconn *conn, const char *sql); +static void run_simple_command(PGconn *conn, const char *sql); + +/* public interface functions */ +static void libpq_traverse_files(rewind_source *source, + process_file_callback_t callback); +static char *libpq_fetch_file(rewind_source *source, const char *path, + size_t *filesize); +static void libpq_queue_fetch_range(rewind_source *source, const char *path, + uint64 off, size_t len); +static void libpq_finish_fetch(rewind_source *source); +static void libpq_destroy(rewind_source *source); +static XLogRecPtr libpq_get_current_wal_insert_lsn(rewind_source *source); -void -libpqConnect(const char *connstr) +/* + * Create a new libpq source. + * + * The caller has already established the connection, but should not try + * to use it while the source is active. + */ +rewind_source * +init_libpq_source(PGconn *conn) { - char *str; - PGresult *res; + libpq_source *src; + + init_libpq_conn(conn); - conn = PQconnectdb(connstr); - if (PQstatus(conn) == CONNECTION_BAD) - pg_fatal("could not connect to server: %s", - PQerrorMessage(conn)); + src = pg_malloc0(sizeof(libpq_source)); - if (showprogress) - pg_log_info("connected to server"); + src->common.traverse_files = libpq_traverse_files; + src->common.fetch_file = libpq_fetch_file; + src->common.queue_fetch_range = libpq_queue_fetch_range; + src->common.finish_fetch = libpq_finish_fetch; + src->common.get_current_wal_insert_lsn = libpq_get_current_wal_insert_lsn; + src->common.destroy = libpq_destroy; + + src->conn = conn; + + return &src->common; +} + +/* + * Initialize a libpq connection for use. + */ +static void +init_libpq_conn(PGconn *conn) +{ + PGresult *res; + char *str; /* disable all types of timeouts */ - run_simple_command("SET statement_timeout = 0"); - run_simple_command("SET lock_timeout = 0"); - run_simple_command("SET idle_in_transaction_session_timeout = 0"); + run_simple_command(conn, "SET statement_timeout = 0"); + run_simple_command(conn, "SET lock_timeout = 0"); + run_simple_command(conn, "SET idle_in_transaction_session_timeout = 0"); + /* we don't intend do any updates. Put the connection in read-only mode to keep us honest */ + run_simple_command(conn, "SET default_transaction_read_only = off"); + + /* secure search_path */ res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL); if (PQresultStatus(res) != PGRES_TUPLES_OK) pg_fatal("could not clear search_path: %s", @@ -70,7 +106,7 @@ libpqConnect(const char *connstr) * currently because we use a temporary table. Better to check for it * explicitly than error out, for a better error message. */ - str = run_simple_query("SELECT pg_is_in_recovery()"); + str = run_simple_query(conn, "SELECT pg_is_in_recovery()"); if (strcmp(str, "f") != 0) pg_fatal("source server must not be in recovery mode"); pg_free(str); @@ -80,27 +116,31 @@ libpqConnect(const char *connstr) * a page is modified while we read it with pg_read_binary_file(), and we * rely on full page images to fix them. */ - str = run_simple_query("SHOW full_page_writes"); + str = run_simple_query(conn, "SHOW full_page_writes"); if (strcmp(str, "on") != 0) pg_fatal("full_page_writes must be enabled in the source server"); pg_free(str); /* - * Although we don't do any "real" updates, we do work with a temporary - * table. We don't care about synchronous commit for that. It doesn't - * otherwise matter much, but if the server is using synchronous - * replication, and replication isn't working for some reason, we don't - * want to get stuck, waiting for it to start working again. + * First create a temporary table, and COPY to load it with the list of + * blocks that we need to fetch. */ - run_simple_command("SET synchronous_commit = off"); + run_simple_command(conn, "CREATE TEMPORARY TABLE fetchchunks(path text, begin int8, len int4)"); + + res = PQexec(conn, "COPY fetchchunks FROM STDIN"); + if (PQresultStatus(res) != PGRES_COPY_IN) + pg_fatal("could not send file list: %s", + PQresultErrorMessage(res)); + PQclear(res); } /* - * Runs a query that returns a single value. + * Run a query that returns a single value. + * * The result should be pg_free'd after use. */ static char * -run_simple_query(const char *sql) +run_simple_query(PGconn *conn, const char *sql) { PGresult *res; char *result; @@ -123,11 +163,12 @@ run_simple_query(const char *sql) } /* - * Runs a command. + * Run a command. + * * In the event of a failure, exit immediately. */ static void -run_simple_command(const char *sql) +run_simple_command(PGconn *conn, const char *sql) { PGresult *res; @@ -141,17 +182,18 @@ run_simple_command(const char *sql) } /* - * Calls pg_current_wal_insert_lsn() function + * Call the pg_current_wal_insert_lsn() function in the remote system. */ -XLogRecPtr -libpqGetCurrentXlogInsertLocation(void) +static XLogRecPtr +libpq_get_current_wal_insert_lsn(rewind_source *source) { + PGconn *conn = ((libpq_source *) source)->conn; XLogRecPtr result; uint32 hi; uint32 lo; char *val; - val = run_simple_query("SELECT pg_current_wal_insert_lsn()"); + val = run_simple_query(conn, "SELECT pg_current_wal_insert_lsn()"); if (sscanf(val, "%X/%X", &hi, &lo) != 2) pg_fatal("unrecognized result \"%s\" for current WAL insert location", val); @@ -166,9 +208,10 @@ libpqGetCurrentXlogInsertLocation(void) /* * Get a list of all files in the data directory. */ -void -libpqProcessFileList(void) +static void +libpq_traverse_files(rewind_source *source, process_file_callback_t callback) { + PGconn *conn = ((libpq_source *) source)->conn; PGresult *res; const char *sql; int i; @@ -246,6 +289,48 @@ libpqProcessFileList(void) PQclear(res); } +/* + * Queue up a request to fetch a piece of a file from remote system. + */ +static void +libpq_queue_fetch_range(rewind_source *source, const char *path, uint64 off, + size_t len) +{ + libpq_source *src = (libpq_source *) source; + uint64 begin = off; + uint64 end = off + len; + + /* + * Write the file range to a temporary table in the server. + * + * The range is sent to the server as a COPY formatted line, to be inserted + * into the 'fetchchunks' temporary table. The libpq_finish_fetch() uses + * the temporary table to actually fetch the data. + */ + + /* Split the range into CHUNKSIZE chunks */ + while (end - begin > 0) + { + char linebuf[MAXPGPATH + 23]; + unsigned int len; + + /* Fine as long as CHUNKSIZE is not bigger than UINT32_MAX */ + if (end - begin > CHUNKSIZE) + len = CHUNKSIZE; + else + len = (unsigned int) (end - begin); + + begin += len; + + snprintf(linebuf, sizeof(linebuf), "%s\t" UINT64_FORMAT "\t%u\n", path, begin, len); + + if (PQputCopyData(src->conn, linebuf, strlen(linebuf)) != 1) + pg_fatal("could not send COPY data: %s", + PQerrorMessage(src->conn)); + } +} + + /*---- * Runs a query, which returns pieces of files from the remote source data * directory, and overwrites the corresponding parts of target files with @@ -256,20 +341,46 @@ libpqProcessFileList(void) * chunk bytea -- file content *---- */ +/* + * Receive all the queued chunks and write them to the target data directory. + */ static void -receiveFileChunks(const char *sql) +libpq_finish_fetch(rewind_source *source) { + libpq_source *src = (libpq_source *) source; PGresult *res; + const char *sql; - if (PQsendQueryParams(conn, sql, 0, NULL, NULL, NULL, NULL, 1) != 1) - pg_fatal("could not send query: %s", PQerrorMessage(conn)); + if (PQputCopyEnd(src->conn, NULL) != 1) + pg_fatal("could not send end-of-COPY: %s", + PQerrorMessage(src->conn)); + + while ((res = PQgetResult(src->conn)) != NULL) + { + if (PQresultStatus(res) != PGRES_COMMAND_OK) + pg_fatal("unexpected result while sending file list: %s", + PQresultErrorMessage(res)); + PQclear(res); + } + + /* + * We've now copied the list of file ranges that we need to fetch to the + * temporary table. Now, actually fetch all of those ranges. + */ + sql = + "SELECT path, begin,\n" + " pg_read_binary_file(path, begin, len, true) AS chunk\n" + "FROM fetchchunks\n"; + + if (PQsendQueryParams(src->conn, sql, 0, NULL, NULL, NULL, NULL, 1) != 1) + pg_fatal("could not send query: %s", PQerrorMessage(src->conn)); pg_log_debug("getting file chunks"); - if (PQsetSingleRowMode(conn) != 1) + if (PQsetSingleRowMode(src->conn) != 1) pg_fatal("could not set libpq connection to single row mode"); - while ((res = PQgetResult(conn)) != NULL) + while ((res = PQgetResult(src->conn)) != NULL) { char *filename; int filenamelen; @@ -363,28 +474,29 @@ receiveFileChunks(const char *sql) } /* - * Receive a single file as a malloc'd buffer. + * Fetch a single file as a malloc'd buffer. */ -char * -libpqGetFile(const char *filename, size_t *filesize) +static char * +libpq_fetch_file(rewind_source *source, const char *path, size_t *filesize) { + PGconn *conn = ((libpq_source *) source)->conn; PGresult *res; char *result; int len; const char *paramValues[1]; - paramValues[0] = filename; + paramValues[0] = path; res = PQexecParams(conn, "SELECT pg_read_binary_file($1)", 1, NULL, paramValues, NULL, NULL, 1); if (PQresultStatus(res) != PGRES_TUPLES_OK) pg_fatal("could not fetch remote file \"%s\": %s", - filename, PQresultErrorMessage(res)); + path, PQresultErrorMessage(res)); /* sanity check the result set */ if (PQntuples(res) != 1 || PQgetisnull(res, 0, 0)) pg_fatal("unexpected result set while fetching remote file \"%s\"", - filename); + path); /* Read result to local variables */ len = PQgetlength(res, 0, 0); @@ -394,7 +506,7 @@ libpqGetFile(const char *filename, size_t *filesize) PQclear(res); - pg_log_debug("fetched file \"%s\", length %d", filename, len); + pg_log_debug("fetched file \"%s\", length %d", path, len); if (filesize) *filesize = len; @@ -402,142 +514,11 @@ libpqGetFile(const char *filename, size_t *filesize) } /* - * Write a file range to a temporary table in the server. - * - * The range is sent to the server as a COPY formatted line, to be inserted - * into the 'fetchchunks' temporary table. It is used in receiveFileChunks() - * function to actually fetch the data. + * Close a libpq source. */ static void -fetch_file_range(const char *path, uint64 begin, uint64 end) +libpq_destroy(rewind_source *source) { - char linebuf[MAXPGPATH + 23]; - - /* Split the range into CHUNKSIZE chunks */ - while (end - begin > 0) - { - unsigned int len; - - /* Fine as long as CHUNKSIZE is not bigger than UINT32_MAX */ - if (end - begin > CHUNKSIZE) - len = CHUNKSIZE; - else - len = (unsigned int) (end - begin); - - snprintf(linebuf, sizeof(linebuf), "%s\t" UINT64_FORMAT "\t%u\n", path, begin, len); - - if (PQputCopyData(conn, linebuf, strlen(linebuf)) != 1) - pg_fatal("could not send COPY data: %s", - PQerrorMessage(conn)); - - begin += len; - } -} - -/* - * Fetch all changed blocks from remote source data directory. - */ -void -libpq_executeFileMap(filemap_t *map) -{ - file_entry_t *entry; - const char *sql; - PGresult *res; - int i; - - /* - * First create a temporary table, and load it with the blocks that we - * need to fetch. - */ - sql = "CREATE TEMPORARY TABLE fetchchunks(path text, begin int8, len int4);"; - run_simple_command(sql); - - sql = "COPY fetchchunks FROM STDIN"; - res = PQexec(conn, sql); - - if (PQresultStatus(res) != PGRES_COPY_IN) - pg_fatal("could not send file list: %s", - PQresultErrorMessage(res)); - PQclear(res); - - for (i = 0; i < map->nactions; i++) - { - entry = map->actions[i]; - - /* If this is a relation file, copy the modified blocks */ - execute_pagemap(&entry->target_modified_pages, entry->path); - - switch (entry->action) - { - case FILE_ACTION_NONE: - /* nothing else to do */ - break; - - case FILE_ACTION_COPY: - /* Truncate the old file out of the way, if any */ - open_target_file(entry->path, true); - fetch_file_range(entry->path, 0, entry->source_size); - break; - - case FILE_ACTION_TRUNCATE: - truncate_target_file(entry->path, entry->source_size); - break; - - case FILE_ACTION_COPY_TAIL: - fetch_file_range(entry->path, entry->target_size, entry->source_size); - break; - - case FILE_ACTION_REMOVE: - remove_target(entry); - break; - - case FILE_ACTION_CREATE: - create_target(entry); - break; - - case FILE_ACTION_UNDECIDED: - pg_fatal("no action decided for \"%s\"", entry->path); - break; - } - } - - if (PQputCopyEnd(conn, NULL) != 1) - pg_fatal("could not send end-of-COPY: %s", - PQerrorMessage(conn)); - - while ((res = PQgetResult(conn)) != NULL) - { - if (PQresultStatus(res) != PGRES_COMMAND_OK) - pg_fatal("unexpected result while sending file list: %s", - PQresultErrorMessage(res)); - PQclear(res); - } - - /* - * We've now copied the list of file ranges that we need to fetch to the - * temporary table. Now, actually fetch all of those ranges. - */ - sql = - "SELECT path, begin,\n" - " pg_read_binary_file(path, begin, len, true) AS chunk\n" - "FROM fetchchunks\n"; - - receiveFileChunks(sql); -} - -static void -execute_pagemap(datapagemap_t *pagemap, const char *path) -{ - datapagemap_iterator_t *iter; - BlockNumber blkno; - off_t offset; - - iter = datapagemap_iterate(pagemap); - while (datapagemap_next(iter, &blkno)) - { - offset = blkno * BLCKSZ; - - fetch_file_range(path, offset, offset + BLCKSZ); - } - pg_free(iter); + pfree(source); + /* NOTE: we don't close the connection here, as it was not opened by us. */ } diff --git a/src/bin/pg_rewind/pg_rewind.c b/src/bin/pg_rewind/pg_rewind.c index 2bdeed26c53..9e04a085226 100644 --- a/src/bin/pg_rewind/pg_rewind.c +++ b/src/bin/pg_rewind/pg_rewind.c @@ -35,8 +35,8 @@ static void usage(const char *progname); static void createBackupLabel(XLogRecPtr startpoint, TimeLineID starttli, XLogRecPtr checkpointloc); -static void digestControlFile(ControlFileData *ControlFile, char *source, - size_t size); +static void digestControlFile(ControlFileData *ControlFile, + const char *content, size_t size); static void getRestoreCommand(const char *argv0); static void sanityChecks(void); static void findCommonAncestorTimeline(XLogRecPtr *recptr, int *tliIndex); @@ -69,6 +69,8 @@ int targetNentries; uint64 fetch_size; uint64 fetch_done; +static PGconn *conn; +static rewind_source *source; static void usage(const char *progname) @@ -269,19 +271,29 @@ main(int argc, char **argv) atexit(disconnect_atexit); - /* Connect to remote server */ - if (connstr_source) - libpqConnect(connstr_source); - /* - * Ok, we have all the options and we're ready to start. Read in all the - * information we need from both clusters. + * Ok, we have all the options and we're ready to start. First, connect + * to remote server. */ - buffer = slurpFile(datadir_target, "global/pg_control", &size); - digestControlFile(&ControlFile_target, buffer, size); - pg_free(buffer); + if (connstr_source) + { + conn = PQconnectdb(connstr_source); + + if (PQstatus(conn) == CONNECTION_BAD) + pg_fatal("could not connect to server: %s", + PQerrorMessage(conn)); + + if (showprogress) + pg_log_info("connected to server"); + + source = init_libpq_source(conn); + } + else + source = init_local_source(datadir_source); /* + * Check the status of the target instance. + * * If the target instance was not cleanly shut down, start and stop the * target cluster once in single-user mode to enforce recovery to finish, * ensuring that the cluster can be used by pg_rewind. Note that if @@ -289,6 +301,10 @@ main(int argc, char **argv) * need to make sure by themselves that the target cluster is in a clean * state. */ + buffer = slurpFile(datadir_target, "global/pg_control", &size); + digestControlFile(&ControlFile_target, buffer, size); + pg_free(buffer); + if (!no_ensure_shutdown && ControlFile_target.state != DB_SHUTDOWNED && ControlFile_target.state != DB_SHUTDOWNED_IN_RECOVERY) @@ -300,17 +316,20 @@ main(int argc, char **argv) pg_free(buffer); } - buffer = fetchFile("global/pg_control", &size); + buffer = source->fetch_file(source, "global/pg_control", &size); digestControlFile(&ControlFile_source, buffer, size); pg_free(buffer); sanityChecks(); /* + * Find the common ancestor timeline between the clusters. + * * If both clusters are already on the same timeline, there's nothing to * do. */ - if (ControlFile_target.checkPointCopy.ThisTimeLineID == ControlFile_source.checkPointCopy.ThisTimeLineID) + if (ControlFile_target.checkPointCopy.ThisTimeLineID == + ControlFile_source.checkPointCopy.ThisTimeLineID) { pg_log_info("source and target cluster are on the same timeline"); rewind_needed = false; @@ -370,12 +389,12 @@ main(int argc, char **argv) chkpttli); /* - * Collect information about all files in the target and source systems. + * Collect information about all files in the both data directories. */ if (showprogress) pg_log_info("reading source file list"); filemap_init(); - fetchSourceFileList(); + source->traverse_files(source, &process_source_file); if (showprogress) pg_log_info("reading target file list"); @@ -423,7 +442,7 @@ main(int argc, char **argv) * modified the target directory and there is no turning back! */ - execute_file_actions(filemap); + execute_file_actions(filemap, source); progress_report(true); @@ -443,7 +462,7 @@ main(int argc, char **argv) if (connstr_source) { - endrec = libpqGetCurrentXlogInsertLocation(); + endrec = source->get_current_wal_insert_lsn(source); endtli = ControlFile_source.checkPointCopy.ThisTimeLineID; } else @@ -465,6 +484,14 @@ main(int argc, char **argv) WriteRecoveryConfig(conn, datadir_target, GenerateRecoveryConfig(conn, NULL)); + /* don't need the source connection anymore */ + source->destroy(source); + if (conn) + { + PQfinish(conn); + conn = NULL; + } + pg_log_info("Done!"); return 0; @@ -627,7 +654,7 @@ getTimelineHistory(ControlFileData *controlFile, int *nentries) /* Get history file from appropriate source */ if (controlFile == &ControlFile_source) - histfile = fetchFile(path, NULL); + histfile = source->fetch_file(source, path, NULL); else if (controlFile == &ControlFile_target) histfile = slurpFile(datadir_target, path, NULL); else @@ -783,16 +810,17 @@ checkControlFile(ControlFileData *ControlFile) } /* - * Verify control file contents in the buffer src, and copy it to *ControlFile. + * Verify control file contents in the buffer 'content', and copy it to *ControlFile. */ static void -digestControlFile(ControlFileData *ControlFile, char *src, size_t size) +digestControlFile(ControlFileData *ControlFile, + const char *content, size_t size) { if (size != PG_CONTROL_FILE_SIZE) pg_fatal("unexpected control file size %d, expected %d", (int) size, PG_CONTROL_FILE_SIZE); - memcpy(ControlFile, src, sizeof(ControlFileData)); + memcpy(ControlFile, content, sizeof(ControlFileData)); /* set and validate WalSegSz */ WalSegSz = ControlFile->xlog_seg_size; diff --git a/src/bin/pg_rewind/pg_rewind.h b/src/bin/pg_rewind/pg_rewind.h index 67f90c2a38c..0dc3dbd5255 100644 --- a/src/bin/pg_rewind/pg_rewind.h +++ b/src/bin/pg_rewind/pg_rewind.h @@ -20,8 +20,6 @@ /* Configuration options */ extern char *datadir_target; -extern char *datadir_source; -extern char *connstr_source; extern bool showprogress; extern bool dry_run; extern bool do_sync; @@ -31,9 +29,6 @@ extern int WalSegSz; extern TimeLineHistoryEntry *targetHistory; extern int targetNentries; -/* general state */ -extern PGconn *conn; - /* Progress counters */ extern uint64 fetch_size; extern uint64 fetch_done; -- 2.20.1 --------------D93EDEBFB124D563B723F4BD Content-Type: text/x-patch; charset=UTF-8; name="0005-Allow-pg_rewind-to-use-a-standby-server-as-the-sourc.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0005-Allow-pg_rewind-to-use-a-standby-server-as-the-sourc.pa"; filename*1="tch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Proposal: allow database-specific role memberships @ 2022-01-21 22:12 Kenaniah Cerny <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Kenaniah Cerny @ 2022-01-21 22:12 UTC (permalink / raw) To: Julien Rouhaud <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Asif Rehman <[email protected]>; PostgreSQL Hackers <[email protected]> The latest rebased version of the patch is attached. On Tue, Jan 11, 2022 at 11:01 PM Julien Rouhaud <[email protected]> wrote: > Hi, > > On Thu, Dec 2, 2021 at 2:26 AM Kenaniah Cerny <[email protected]> wrote: > > > > Attached is a rebased version of the patch that omits catversion.h in > order to avoid conflicts. > > Unfortunately even without that the patch doesn't apply anymore > according to the cfbot: http://cfbot.cputube.org/patch_36_3374.log > > 1 out of 3 hunks FAILED -- saving rejects to file > src/backend/parser/gram.y.rej > [...] > 2 out of 8 hunks FAILED -- saving rejects to file > src/bin/pg_dump/pg_dumpall.c.rej > > Could you send a rebased version? > > In the meantime I'm switching the patch to Waiting on Author. > Attachments: [application/octet-stream] database-role-memberships-v5.patch (69.7K, ../../CA+r_aq85yo=ZePx=QuLXVev4pbn3MhB7gj4dud3uAL5x_Wih3A@mail.gmail.com/3-database-role-memberships-v5.patch) download | inline diff: diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 1e65c426b28e..a0beec6136e6 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1642,11 +1642,10 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </para> <para> - Because user identities are cluster-wide, - <structname>pg_auth_members</structname> - is shared across all databases of a cluster: there is only one - copy of <structname>pg_auth_members</structname> per cluster, not - one per database. + User identities are cluster-wide, but role memberships can be either + cluster-wide or database-specific (as specified by the value of the + <structfield>dbid</structfield> column). The <structname>pg_auth_members</structname> + catalog is shared across all databases of a cluster. </para> <table> @@ -1703,6 +1702,17 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <structfield>roleid</structfield> to others </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>dbid</structfield> <type>oid</type> + (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>) + </para> + <para> + ID of the database that this membership is constrained to; zero if membership is cluster-wide + </para></entry> + </row> + </tbody> </tgroup> </table> diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index a897712de2e5..e5f7f000db73 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -93,6 +93,7 @@ GRANT { USAGE | ALL [ PRIVILEGES ] } [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ] GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replaceable class="parameter">role_specification</replaceable> [, ...] + [ IN DATABASE <replaceable class="parameter">database_name</replaceable> | IN CURRENT DATABASE ] [ WITH ADMIN OPTION ] [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ] @@ -243,7 +244,31 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace This variant of the <command>GRANT</command> command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its - members. + members. Membership is effective cluster-wide unless otherwise constrained + through the use of a database-specific clause. + </para> + + <note> + <para> + Database-specific role membership was introduced in PostgreSQL version 15. In + earlier versions, role membership was always considered to be cluster-wide. Both + database-specific and cluster-wide versions of a role membership grant may exist + at the same time. In the event that multiple grants apply, the membership + privileges conferred are additive. + </para> + </note> + + <para> + If <literal>IN DATABASE <replaceable class="parameter">database_name</replaceable></literal> + is specified, membership in <replaceable class="parameter">role_name</replaceable> + will be effective when the recipient is connected to the database specified by + <replaceable class="parameter">database_name</replaceable>. + </para> + + <para> + If <literal>IN CURRENT DATABASE</literal> is specified, the membership in + <replaceable class="parameter">role_name</replaceable> will be effective when the + recipient is connected to the same database that the grant was issued in. </para> <para> @@ -270,6 +295,10 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace does not allow the noise word <literal>GROUP</literal> in <replaceable class="parameter">role_specification</replaceable>. </para> + + <para> + See <xref linkend="role-membership"/> for more information about role memberships. + </para> </refsect2> </refsect1> @@ -391,10 +420,18 @@ GRANT ALL PRIVILEGES ON kinds TO manuel; </para> <para> - Grant membership in role <literal>admins</literal> to user <literal>joe</literal>: + Grant cluster-wide membership in role <literal>admins</literal> to user <literal>joe</literal>: <programlisting> GRANT admins TO joe; +</programlisting></para> + + <para> + Grant read and write access to user <literal>alice</literal> in the database + named <literal>sales</literal>: + +<programlisting> +GRANT pg_read_all_data, pg_write_all_data TO alice IN DATABASE sales; </programlisting></para> </refsect1> diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 3014c864ea3c..b5013f004451 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -120,6 +120,7 @@ REVOKE [ GRANT OPTION FOR ] REVOKE [ ADMIN OPTION FOR ] <replaceable class="parameter">role_name</replaceable> [, ...] FROM <replaceable class="parameter">role_specification</replaceable> [, ...] + [ IN DATABASE <replaceable class="parameter">database_name</replaceable> | IN CURRENT DATABASE ] [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ] [ CASCADE | RESTRICT ] @@ -298,6 +299,14 @@ REVOKE ALL PRIVILEGES ON kinds FROM manuel; <programlisting> REVOKE admins FROM joe; +</programlisting></para> + + <para> + Revoke write access for user <literal>bob</literal> from the <literal>sales</literal> + database: + +<programlisting> +REVOKE pg_write_all_data FROM bob IN DATABASE sales; </programlisting></para> </refsect1> diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 9067be1d9c78..fa2ed7a11afb 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -308,6 +308,11 @@ CREATE ROLE <replaceable>name</replaceable>; <synopsis> GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ; REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ; +</synopsis> + Role membership can also be granted and revoked within the context of a specific database: +<synopsis> +GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... IN CURRENT DATABASE; +REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... IN DATABASE <replaceable>database_name</replaceable>; </synopsis> You can grant membership to other group roles, too (since there isn't really any distinction between group roles and non-group roles). The @@ -398,8 +403,16 @@ RESET ROLE; <command>SET ROLE admin</command>. </para> - <para> - </para> + <warning> + <para> + There is a privilege escalation risk in granting database-specific + role membership in roles that have any of the <literal>SUPERUSER</literal>, <literal>CREATEDB</literal>, + or <literal>CREATEROLE</literal> attributes. When connected to the specified database, + the <literal>SET ROLE</literal> command would allow for these special privileges to be + assumed by the grantee. It is highly recommended that granting database-specific role + membership in roles with any of these special privileges be avoided. + </para> + </warning> <para> To destroy a group role, use <link @@ -639,7 +652,7 @@ DROP ROLE doomed_role; </para> <para> - Administrators can grant access to these roles to users using the + Administrators can grant cluster-wide access to these roles to users using the <link linkend="sql-grant"><command>GRANT</command></link> command, for example: <programlisting> @@ -647,6 +660,14 @@ GRANT pg_signal_backend TO admin_user; </programlisting> </para> + <para> + Access can also be granted within the context of a specific database, + for example: + +<programlisting> +GRANT pg_read_all_data TO reporting_user IN DATABASE sales; +</programlisting> + </para> </sect1> <sect1 id="perm-functions"> diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c index dfd5fb669eef..c4b2325a6c05 100644 --- a/src/backend/catalog/catalog.c +++ b/src/backend/catalog/catalog.c @@ -258,8 +258,9 @@ IsSharedRelation(Oid relationId) /* These are their indexes */ if (relationId == AuthIdRolnameIndexId || relationId == AuthIdOidIndexId || - relationId == AuthMemRoleMemIndexId || - relationId == AuthMemMemRoleIndexId || + relationId == AuthMemDbMemRoleIndexId || + relationId == AuthMemRoleMemDbIndexId || + relationId == AuthMemMemRoleDbIndexId || relationId == DatabaseNameIndexId || relationId == DatabaseOidIndexId || relationId == SharedDescriptionObjIndexId || diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index da8345561d8f..822d3ab5c96c 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -46,6 +46,7 @@ #include "commands/defrem.h" #include "commands/seclabel.h" #include "commands/tablespace.h" +#include "commands/user.h" #include "mb/pg_wchar.h" #include "miscadmin.h" #include "pgstat.h" @@ -915,6 +916,11 @@ dropdb(const char *dbname, bool missing_ok, bool force) DeleteSharedComments(db_id, DatabaseRelationId); DeleteSharedSecurityLabel(db_id, DatabaseRelationId); + /* + * Delete any roles memberships directly associated with this database. + */ + DropDatabaseSpecificRoles(db_id); + /* * Remove settings associated with this database */ diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index f9d3c1246bb2..e7f3a6b9104c 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -51,10 +51,10 @@ check_password_hook_type check_password_hook = NULL; static void AddRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, - Oid grantorId, bool admin_opt); + Oid grantorId, bool admin_opt, Oid dbid); static void DelRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, - bool admin_opt); + bool admin_opt, Oid dbid); /* Check if current user has createrole privileges */ @@ -453,7 +453,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) AddRoleMems(oldrolename, oldroleid, thisrole_list, thisrole_oidlist, - GetUserId(), false); + GetUserId(), false, InvalidOid); ReleaseSysCache(oldroletup); } @@ -465,10 +465,10 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) */ AddRoleMems(stmt->role, roleid, adminmembers, roleSpecsToIds(adminmembers), - GetUserId(), true); + GetUserId(), true, InvalidOid); AddRoleMems(stmt->role, roleid, rolemembers, roleSpecsToIds(rolemembers), - GetUserId(), false); + GetUserId(), false, InvalidOid); /* Post creation hook for new role */ InvokeObjectPostCreateHook(AuthIdRelationId, roleid, 0); @@ -805,11 +805,11 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) if (stmt->action == +1) /* add members to role */ AddRoleMems(rolename, roleid, rolemembers, roleSpecsToIds(rolemembers), - GetUserId(), false); + GetUserId(), false, InvalidOid); else if (stmt->action == -1) /* drop members from role */ DelRoleMems(rolename, roleid, rolemembers, roleSpecsToIds(rolemembers), - false); + false, InvalidOid); } /* @@ -1025,7 +1025,7 @@ DropRole(DropRoleStmt *stmt) BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(roleid)); - sscan = systable_beginscan(pg_auth_members_rel, AuthMemRoleMemIndexId, + sscan = systable_beginscan(pg_auth_members_rel, AuthMemRoleMemDbIndexId, true, NULL, 1, &scankey); while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan))) @@ -1040,7 +1040,7 @@ DropRole(DropRoleStmt *stmt) BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(roleid)); - sscan = systable_beginscan(pg_auth_members_rel, AuthMemMemRoleIndexId, + sscan = systable_beginscan(pg_auth_members_rel, AuthMemMemRoleDbIndexId, true, NULL, 1, &scankey); while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan))) @@ -1230,6 +1230,17 @@ GrantRole(GrantRoleStmt *stmt) Oid grantor; List *grantee_ids; ListCell *item; + Oid dbid; + + /* Determine if this grant/revoke is database-specific */ + if (stmt->database == NULL) { + dbid = InvalidOid; + } else if (strcmp(stmt->database, "") == 0) { + dbid = MyDatabaseId; + } else { + dbid = get_database_oid(stmt->database, false); + } + if (stmt->grantor) grantor = get_rolespec_oid(stmt->grantor, false); @@ -1264,11 +1275,11 @@ GrantRole(GrantRoleStmt *stmt) if (stmt->is_grant) AddRoleMems(rolename, roleid, stmt->grantee_roles, grantee_ids, - grantor, stmt->admin_opt); + grantor, stmt->admin_opt, dbid); else DelRoleMems(rolename, roleid, stmt->grantee_roles, grantee_ids, - stmt->admin_opt); + stmt->admin_opt, dbid); } /* @@ -1375,7 +1386,7 @@ roleSpecsToIds(List *memberNames) static void AddRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, - Oid grantorId, bool admin_opt) + Oid grantorId, bool admin_opt, Oid dbid) { Relation pg_authmem_rel; TupleDesc pg_authmem_dsc; @@ -1402,7 +1413,7 @@ AddRoleMems(const char *rolename, Oid roleid, else { if (!have_createrole_privilege() && - !is_admin_of_role(grantorId, roleid)) + !is_admin_of_role(grantorId, roleid, dbid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must have admin option on role \"%s\"", @@ -1490,16 +1501,23 @@ AddRoleMems(const char *rolename, Oid roleid, * Check if entry for this role/member already exists; if so, give * warning unless we are adding admin option. */ - authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM, + authmem_tuple = SearchSysCache3(AUTHMEMROLEMEMDB, ObjectIdGetDatum(roleid), - ObjectIdGetDatum(memberid)); + ObjectIdGetDatum(memberid), + ObjectIdGetDatum(dbid)); if (HeapTupleIsValid(authmem_tuple) && (!admin_opt || ((Form_pg_auth_members) GETSTRUCT(authmem_tuple))->admin_option)) { - ereport(NOTICE, - (errmsg("role \"%s\" is already a member of role \"%s\"", - get_rolespec_name(memberRole), rolename))); + if (dbid == InvalidOid) { + ereport(NOTICE, + (errmsg("role \"%s\" is already a member of role \"%s\"", + get_rolespec_name(memberRole), rolename))); + } else { + ereport(NOTICE, + (errmsg("role \"%s\" is already a member of role \"%s\" in database \"%s\"", + get_rolespec_name(memberRole), rolename, get_database_name(dbid)))); + } ReleaseSysCache(authmem_tuple); continue; } @@ -1513,6 +1531,7 @@ AddRoleMems(const char *rolename, Oid roleid, new_record[Anum_pg_auth_members_member - 1] = ObjectIdGetDatum(memberid); new_record[Anum_pg_auth_members_grantor - 1] = ObjectIdGetDatum(grantorId); new_record[Anum_pg_auth_members_admin_option - 1] = BoolGetDatum(admin_opt); + new_record[Anum_pg_auth_members_dbid - 1] = ObjectIdGetDatum(dbid); if (HeapTupleIsValid(authmem_tuple)) { @@ -1553,7 +1572,7 @@ AddRoleMems(const char *rolename, Oid roleid, static void DelRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, - bool admin_opt) + bool admin_opt, Oid dbid) { Relation pg_authmem_rel; TupleDesc pg_authmem_dsc; @@ -1580,7 +1599,7 @@ DelRoleMems(const char *rolename, Oid roleid, else { if (!have_createrole_privilege() && - !is_admin_of_role(GetUserId(), roleid)) + !is_admin_of_role(GetUserId(), roleid, dbid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must have admin option on role \"%s\"", @@ -1599,14 +1618,21 @@ DelRoleMems(const char *rolename, Oid roleid, /* * Find entry for this role/member */ - authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM, + authmem_tuple = SearchSysCache3(AUTHMEMROLEMEMDB, ObjectIdGetDatum(roleid), - ObjectIdGetDatum(memberid)); + ObjectIdGetDatum(memberid), + ObjectIdGetDatum(dbid)); if (!HeapTupleIsValid(authmem_tuple)) { - ereport(WARNING, - (errmsg("role \"%s\" is not a member of role \"%s\"", - get_rolespec_name(memberRole), rolename))); + if (dbid == InvalidOid){ + ereport(WARNING, + (errmsg("role \"%s\" is not a member of role \"%s\"", + get_rolespec_name(memberRole), rolename))); + } else { + ereport(WARNING, + (errmsg("role \"%s\" is not a member of role \"%s\" in database \"%s\"", + get_rolespec_name(memberRole), rolename, get_database_name(dbid)))); + } continue; } @@ -1648,3 +1674,42 @@ DelRoleMems(const char *rolename, Oid roleid, */ table_close(pg_authmem_rel, NoLock); } + +/* + * DropDatabaseSpecificRoles + * + * Delete pg_auth_members entries corresponding to a database that's being + * dropped. + */ +void +DropDatabaseSpecificRoles(Oid databaseId) +{ + Relation pg_authmem_rel; + ScanKeyData key[1]; + SysScanDesc scan; + HeapTuple tup; + + pg_authmem_rel = table_open(AuthMemRelationId, RowExclusiveLock); + + /* + * First, delete all the entries that have the database Oid in the dbid + * field. + */ + ScanKeyInit(&key[0], + Anum_pg_auth_members_dbid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(databaseId)); + /* We leave the other index fields unspecified */ + + scan = systable_beginscan(pg_authmem_rel, AuthMemDbMemRoleIndexId, true, + NULL, 1, key); + + while (HeapTupleIsValid(tup = systable_getnext(scan))) + { + CatalogTupleDelete(pg_authmem_rel, &tup->t_self); + } + + systable_endscan(scan); + + table_close(pg_authmem_rel, RowExclusiveLock); +} diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 90b5da51c950..db2e950f95f8 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3437,6 +3437,7 @@ _copyGrantRoleStmt(const GrantRoleStmt *from) COPY_NODE_FIELD(granted_roles); COPY_NODE_FIELD(grantee_roles); + COPY_SCALAR_FIELD(database); COPY_SCALAR_FIELD(is_grant); COPY_SCALAR_FIELD(admin_opt); COPY_NODE_FIELD(grantor); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 06345da3ba84..66e15ca79a2b 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1215,6 +1215,7 @@ _equalGrantRoleStmt(const GrantRoleStmt *a, const GrantRoleStmt *b) { COMPARE_NODE_FIELD(granted_roles); COMPARE_NODE_FIELD(grantee_roles); + COMPARE_SCALAR_FIELD(database); COMPARE_SCALAR_FIELD(is_grant); COMPARE_SCALAR_FIELD(admin_opt); COMPARE_NODE_FIELD(grantor); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index b5966712ce14..f6f31102c009 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -361,7 +361,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <str> opt_type %type <str> foreign_server_version opt_foreign_server_version -%type <str> opt_in_database +%type <str> opt_in_database opt_grant_in_database %type <str> OptSchemaName %type <list> OptSchemaEltList @@ -7252,6 +7252,11 @@ grantee: ; +opt_grant_in_database: + IN_P CURRENT_P DATABASE { $$ = ""; } + | opt_in_database { $$ = $1; } + ; + opt_grant_grant_option: WITH GRANT OPTION { $$ = true; } | /*EMPTY*/ { $$ = false; } @@ -7264,37 +7269,40 @@ opt_grant_grant_option: *****************************************************************************/ GrantRoleStmt: - GRANT privilege_list TO role_list opt_grant_admin_option opt_granted_by + GRANT privilege_list TO role_list opt_grant_in_database opt_grant_admin_option opt_granted_by { GrantRoleStmt *n = makeNode(GrantRoleStmt); n->is_grant = true; n->granted_roles = $2; n->grantee_roles = $4; - n->admin_opt = $5; - n->grantor = $6; + n->database = $5; + n->admin_opt = $6; + n->grantor = $7; $$ = (Node*)n; } ; RevokeRoleStmt: - REVOKE privilege_list FROM role_list opt_granted_by opt_drop_behavior + REVOKE privilege_list FROM role_list opt_grant_in_database opt_granted_by opt_drop_behavior { GrantRoleStmt *n = makeNode(GrantRoleStmt); n->is_grant = false; n->admin_opt = false; n->granted_roles = $2; n->grantee_roles = $4; - n->behavior = $6; + n->database = $5; + n->behavior = $7; $$ = (Node*)n; } - | REVOKE ADMIN OPTION FOR privilege_list FROM role_list opt_granted_by opt_drop_behavior + | REVOKE ADMIN OPTION FOR privilege_list FROM role_list opt_grant_in_database opt_granted_by opt_drop_behavior { GrantRoleStmt *n = makeNode(GrantRoleStmt); n->is_grant = false; n->admin_opt = true; n->granted_roles = $5; n->grantee_roles = $7; - n->behavior = $9; + n->database = $8; + n->behavior = $10; $$ = (Node*)n; } ; diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index 0a16f8156cb4..1528d2f1a805 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -4624,7 +4624,7 @@ pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode) * session and call stack. That suits two-argument pg_has_role(), but * it gives the three-argument version a lamentable whimsy. */ - if (is_admin_of_role(roleid, role_oid)) + if (is_admin_of_role(roleid, role_oid, MyDatabaseId)) return ACLCHECK_OK; } if (mode & ACL_CREATE) @@ -4658,7 +4658,7 @@ initialize_acl(void) * of pg_auth_members (for roles_is_member_of()), pg_authid (for * has_rolinherit()), or pg_database (for roles_is_member_of()) */ - CacheRegisterSyscacheCallback(AUTHMEMROLEMEM, + CacheRegisterSyscacheCallback(AUTHMEMROLEMEMDB, RoleMembershipCacheCallback, (Datum) 0); CacheRegisterSyscacheCallback(AUTHOID, @@ -4724,7 +4724,7 @@ has_rolinherit(Oid roleid) */ static List * roles_is_member_of(Oid roleid, enum RoleRecurseType type, - Oid admin_of, bool *is_admin) + Oid admin_of, bool *is_admin, Oid databaseId) { Oid dba; List *roles_list; @@ -4779,8 +4779,9 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type, if (type == ROLERECURSE_PRIVS && !has_rolinherit(memberid)) continue; /* ignore non-inheriting roles */ - /* Find roles that memberid is directly a member of */ - memlist = SearchSysCacheList1(AUTHMEMMEMROLE, + /* Find roles that memberid is directly a member of globally */ + memlist = SearchSysCacheList2(AUTHMEMDBMEMROLE, + ObjectIdGetDatum(InvalidOid), ObjectIdGetDatum(memberid)); for (i = 0; i < memlist->n_members; i++) { @@ -4789,7 +4790,8 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type, /* * While otherid==InvalidOid shouldn't appear in the catalog, the - * OidIsValid() avoids crashing if that arises. + * OidIsValid() avoids crashing if that arises. This reports if + * the admin option has been granted globally. */ if (otherid == admin_of && ((Form_pg_auth_members) GETSTRUCT(tup))->admin_option && @@ -4805,6 +4807,35 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type, } ReleaseSysCacheList(memlist); + /* Find roles that memberid is directly a member of for the current database */ + memlist = SearchSysCacheList2(AUTHMEMDBMEMROLE, + ObjectIdGetDatum(MyDatabaseId), + ObjectIdGetDatum(memberid)); + for (i = 0; i < memlist->n_members; i++) + { + HeapTuple tup = &memlist->members[i]->tuple; + Oid otherid = ((Form_pg_auth_members) GETSTRUCT(tup))->roleid; + + /* + * While otherid==InvalidOid shouldn't appear in the catalog, the + * OidIsValid() avoids crashing if that arises. This reports if + * the admin option has been granted for a specific database. + */ + if (otherid == admin_of && + ((Form_pg_auth_members) GETSTRUCT(tup))->admin_option && + ((Form_pg_auth_members) GETSTRUCT(tup))->dbid == databaseId && + OidIsValid(admin_of)) + *is_admin = true; + + /* + * Even though there shouldn't be any loops in the membership + * graph, we must test for having already seen this role. It is + * legal for instance to have both A->B and A->C->B. + */ + roles_list = list_append_unique_oid(roles_list, otherid); + } + ReleaseSysCacheList(memlist); + /* implement pg_database_owner implicit membership */ if (memberid == dba && OidIsValid(dba)) roles_list = list_append_unique_oid(roles_list, @@ -4855,7 +4886,7 @@ has_privs_of_role(Oid member, Oid role) * multi-level recursion, then see if target role is any one of them. */ return list_member_oid(roles_is_member_of(member, ROLERECURSE_PRIVS, - InvalidOid, NULL), + InvalidOid, NULL, InvalidOid), role); } @@ -4881,7 +4912,7 @@ is_member_of_role(Oid member, Oid role) * recursion, then see if target role is any one of them. */ return list_member_oid(roles_is_member_of(member, ROLERECURSE_MEMBERS, - InvalidOid, NULL), + InvalidOid, NULL, InvalidOid), role); } @@ -4917,7 +4948,7 @@ is_member_of_role_nosuper(Oid member, Oid role) * recursion, then see if target role is any one of them. */ return list_member_oid(roles_is_member_of(member, ROLERECURSE_MEMBERS, - InvalidOid, NULL), + InvalidOid, NULL, InvalidOid), role); } @@ -4928,7 +4959,7 @@ is_member_of_role_nosuper(Oid member, Oid role) * or a superuser? */ bool -is_admin_of_role(Oid member, Oid role) +is_admin_of_role(Oid member, Oid role, Oid databaseId) { bool result = false; @@ -4968,7 +4999,8 @@ is_admin_of_role(Oid member, Oid role) return member == GetSessionUserId() && !InLocalUserIdChange() && !InSecurityRestrictedOperation(); - (void) roles_is_member_of(member, ROLERECURSE_MEMBERS, role, &result); + /* Check for WITH ADMIN OPTION either globally or for the given database */ + (void) roles_is_member_of(member, ROLERECURSE_MEMBERS, role, &result, databaseId); return result; } @@ -5044,7 +5076,7 @@ select_best_grantor(Oid roleId, AclMode privileges, * doesn't query any role memberships. */ roles_list = roles_is_member_of(roleId, ROLERECURSE_PRIVS, - InvalidOid, NULL); + InvalidOid, NULL, InvalidOid); /* initialize candidate result as default */ *grantorId = roleId; diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c index eb8308808937..d98948120cbf 100644 --- a/src/backend/utils/cache/catcache.c +++ b/src/backend/utils/cache/catcache.c @@ -1110,7 +1110,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey) case AUTHNAME: case AUTHOID: - case AUTHMEMMEMROLE: + case AUTHMEMMEMROLEDB: case DATABASEOID: /* diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 2e760e8a3bdc..80463e614569 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4060,7 +4060,7 @@ RelationCacheInitializePhase3(void) AuthIdRelationId); load_critical_index(AuthIdOidIndexId, AuthIdRelationId); - load_critical_index(AuthMemMemRoleIndexId, + load_critical_index(AuthMemMemRoleDbIndexId, AuthMemRelationId); load_critical_index(SharedSecLabelObjectIndexId, SharedSecLabelRelationId); diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index f4e7819f1e2d..491dd3ab86fb 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -210,24 +210,35 @@ static const struct cachedesc cacheinfo[] = { }, 128 }, - {AuthMemRelationId, /* AUTHMEMMEMROLE */ - AuthMemMemRoleIndexId, - 2, + {AuthMemRelationId, /* AUTHMEMDBMEMROLE */ + AuthMemDbMemRoleIndexId, + 3, { + Anum_pg_auth_members_dbid, Anum_pg_auth_members_member, Anum_pg_auth_members_roleid, - 0, 0 }, 8 }, - {AuthMemRelationId, /* AUTHMEMROLEMEM */ - AuthMemRoleMemIndexId, - 2, + {AuthMemRelationId, /* AUTHMEMMEMROLEDB */ + AuthMemMemRoleDbIndexId, + 3, + { + Anum_pg_auth_members_member, + Anum_pg_auth_members_roleid, + Anum_pg_auth_members_dbid, + 0 + }, + 8 + }, + {AuthMemRelationId, /* AUTHMEMROLEMEMDB */ + AuthMemRoleMemDbIndexId, + 3, { Anum_pg_auth_members_roleid, Anum_pg_auth_members_member, - 0, + Anum_pg_auth_members_dbid, 0 }, 8 diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 10383c713fee..32b4ce6637ec 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -35,7 +35,7 @@ static void help(void); static void dropRoles(PGconn *conn); static void dumpRoles(PGconn *conn); -static void dumpRoleMembership(PGconn *conn); +static void dumpRoleMembership(PGconn *conn, const char *databaseId); static void dropTablespaces(PGconn *conn); static void dumpTablespaces(PGconn *conn); static void dropDBs(PGconn *conn); @@ -584,7 +584,7 @@ main(int argc, char *argv[]) dumpRoles(conn); /* Dump role memberships */ - dumpRoleMembership(conn); + dumpRoleMembership(conn, "0"); } /* Dump tablespaces */ @@ -937,7 +937,7 @@ dumpRoles(PGconn *conn) * no membership yet. */ static void -dumpRoleMembership(PGconn *conn) +dumpRoleMembership(PGconn *conn, const char *databaseId) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; @@ -951,8 +951,9 @@ dumpRoleMembership(PGconn *conn) "LEFT JOIN %s ur on ur.oid = a.roleid " "LEFT JOIN %s um on um.oid = a.member " "LEFT JOIN %s ug on ug.oid = a.grantor " - "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')" - "ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog); + "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_') " + "AND a.dbid = %s" + "ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog, databaseId); res = executeQuery(conn, buf->data); if (PQntuples(res) > 0) @@ -966,6 +967,8 @@ dumpRoleMembership(PGconn *conn) fprintf(OPF, "GRANT %s", fmtId(roleid)); fprintf(OPF, " TO %s", fmtId(member)); + if (strcmp(databaseId, "0") != 0) + fprintf(OPF, " IN CURRENT DATABASE"); if (*option == 't') fprintf(OPF, " WITH ADMIN OPTION"); @@ -1262,7 +1265,7 @@ dumpDatabases(PGconn *conn) * doesn't have some failure mode with --clean. */ res = executeQuery(conn, - "SELECT datname " + "SELECT datname, oid " "FROM pg_database d " "WHERE datallowconn " "ORDER BY (datname <> 'template1'), datname"); @@ -1273,6 +1276,7 @@ dumpDatabases(PGconn *conn) for (i = 0; i < PQntuples(res); i++) { char *dbname = PQgetvalue(res, i, 0); + char *dbid = PQgetvalue(res, i, 1); const char *create_opts; int ret; @@ -1323,6 +1327,10 @@ dumpDatabases(PGconn *conn) exit_nicely(1); } + /* Dump database-specific roles if server is running 15.0 or later */ + if (server_version >= 150000) + dumpRoleMembership(conn, dbid); + if (filename) { OPF = fopen(filename, PG_BINARY_A); diff --git a/src/include/catalog/pg_auth_members.h b/src/include/catalog/pg_auth_members.h index 1bc027f133d5..26d0d5381e8b 100644 --- a/src/include/catalog/pg_auth_members.h +++ b/src/include/catalog/pg_auth_members.h @@ -33,6 +33,7 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_ Oid member BKI_LOOKUP(pg_authid); /* ID of a member of that role */ Oid grantor BKI_LOOKUP(pg_authid); /* who granted the membership */ bool admin_option; /* granted with admin option? */ + Oid dbid BKI_LOOKUP_OPT(pg_database); /* ID of a database this mapping is effective in */ } FormData_pg_auth_members; /* ---------------- @@ -42,7 +43,8 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_ */ typedef FormData_pg_auth_members *Form_pg_auth_members; -DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_index, 2694, AuthMemRoleMemIndexId, on pg_auth_members using btree(roleid oid_ops, member oid_ops)); -DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_index, 2695, AuthMemMemRoleIndexId, on pg_auth_members using btree(member oid_ops, roleid oid_ops)); +DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_dbid_index, 2694, AuthMemRoleMemDbIndexId, on pg_auth_members using btree(roleid oid_ops, member oid_ops, dbid oid_ops)); +DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_dbid_index, 2695, AuthMemMemRoleDbIndexId, on pg_auth_members using btree(member oid_ops, roleid oid_ops, dbid oid_ops)); +DECLARE_UNIQUE_INDEX(pg_auth_members_dbid_member_role_index, 4715, AuthMemDbMemRoleIndexId, on pg_auth_members using btree(dbid oid_ops, member oid_ops, roleid oid_ops)); #endif /* PG_AUTH_MEMBERS_H */ diff --git a/src/include/commands/user.h b/src/include/commands/user.h index 0b7a3cd65fd2..f5f8e4c00055 100644 --- a/src/include/commands/user.h +++ b/src/include/commands/user.h @@ -33,5 +33,6 @@ extern ObjectAddress RenameRole(const char *oldname, const char *newname); extern void DropOwnedObjects(DropOwnedStmt *stmt); extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt); extern List *roleSpecsToIds(List *memberNames); +extern void DropDatabaseSpecificRoles(Oid databaseId); #endif /* USER_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 3e9bdc781f9b..ad0a64c1f2c0 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2092,6 +2092,8 @@ typedef struct GrantRoleStmt NodeTag type; List *granted_roles; /* list of roles to be granted/revoked */ List *grantee_roles; /* list of member roles to add/delete */ + char *database; /* name of DB this grant applies to + NULL = global, "" = current database, otherwise a named database */ bool is_grant; /* true = GRANT, false = REVOKE */ bool admin_opt; /* with admin option */ RoleSpec *grantor; /* set grantor to other than current role */ diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index 1ce4c5556e70..57dfe78f1673 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -208,7 +208,7 @@ extern int aclmembers(const Acl *acl, Oid **roleids); extern bool has_privs_of_role(Oid member, Oid role); extern bool is_member_of_role(Oid member, Oid role); extern bool is_member_of_role_nosuper(Oid member, Oid role); -extern bool is_admin_of_role(Oid member, Oid role); +extern bool is_admin_of_role(Oid member, Oid role, Oid databaseId); extern void check_is_member_of_role(Oid member, Oid role); extern Oid get_role_oid(const char *rolename, bool missing_ok); extern Oid get_role_oid_or_public(const char *rolename); diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h index 9c1a76e8bb66..6ad506357aa2 100644 --- a/src/include/utils/syscache.h +++ b/src/include/utils/syscache.h @@ -39,8 +39,9 @@ enum SysCacheIdentifier AMPROCNUM, ATTNAME, ATTNUM, - AUTHMEMMEMROLE, - AUTHMEMROLEMEM, + AUTHMEMDBMEMROLE, + AUTHMEMMEMROLEDB, + AUTHMEMROLEMEMDB, AUTHNAME, AUTHOID, CASTSOURCETARGET, diff --git a/src/test/modules/unsafe_tests/Makefile b/src/test/modules/unsafe_tests/Makefile index 3ecf5fcfc5bb..6cf403afcd04 100644 --- a/src/test/modules/unsafe_tests/Makefile +++ b/src/test/modules/unsafe_tests/Makefile @@ -1,6 +1,6 @@ # src/test/modules/unsafe_tests/Makefile -REGRESS = rolenames alter_system_table +REGRESS = rolenames alter_system_table role_membership ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/src/test/modules/unsafe_tests/expected/role_membership.out b/src/test/modules/unsafe_tests/expected/role_membership.out new file mode 100644 index 000000000000..2ea9ba093d13 --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/role_membership.out @@ -0,0 +1,537 @@ +CREATE ROLE role_admin LOGIN SUPERUSER; +\connect postgres role_admin +CREATE FUNCTION check_memberships() + RETURNS TABLE (role name, member name, grantor name, admin_option boolean, datname name) + AS $$ +SELECT + r.rolname as role, + m.rolname as member, + g.rolname as grantor, + admin_option, + d.datname +FROM pg_auth_members a +LEFT JOIN pg_roles r ON r.oid = a.roleid +LEFT JOIN pg_roles m ON m.oid = a.member +LEFT JOIN pg_roles g ON g.oid = a.grantor +LEFT JOIN pg_database d ON d.oid = a.dbid +WHERE + m.rolname LIKE 'role_%' +ORDER BY + 1, 2, 5 +$$ LANGUAGE SQL; +-- Populate test databases +\connect template1 +CREATE TABLE data AS SELECT generate_series(1, 3); +CREATE DATABASE db_1; +CREATE DATABASE db_2; +CREATE DATABASE db_3; +CREATE DATABASE db_4; +-- Read all cluster-wide with admin option +CREATE ROLE role_read_all_with_admin; +GRANT pg_read_all_data TO role_read_all_with_admin WITH ADMIN OPTION; +-- Read all in databases 1 and 2 +CREATE ROLE role_read_12; +GRANT pg_read_all_data TO role_read_12 IN DATABASE db_1; +GRANT pg_read_all_data TO role_read_12 IN DATABASE db_2; +-- Read all in databases 3 and 4 with admin option +CREATE ROLE role_read_34; +GRANT pg_read_all_data TO role_read_34 IN DATABASE db_3 WITH ADMIN OPTION; +GRANT pg_read_all_data TO role_read_34 IN DATABASE db_4 WITH ADMIN OPTION; +-- Inherits read all in databases 3 and 4 +CREATE ROLE role_inherited_34; +GRANT role_read_34 TO role_inherited_34; +-- Inherits read all in database 3 +CREATE ROLE role_inherited_3; +GRANT role_read_34 TO role_inherited_3 IN DATABASE db_3; +-- No inherit +CREATE ROLE role_read_all_noinherit NOINHERIT; +GRANT role_read_all_with_admin TO role_read_all_noinherit; +-- No inherit in databases 1 and 2 +CREATE ROLE role_read_12_noinherit NOINHERIT; +GRANT role_read_12 TO role_read_12_noinherit; +-- Alternate syntax +CREATE ROLE role_read_template1; +GRANT pg_read_all_data TO role_read_template1, role_read_all_noinherit IN CURRENT DATABASE; +-- Failure due to missing database +GRANT pg_read_all_data TO role_read_template1 IN DATABASE non_existent; -- error +ERROR: database "non_existent" does not exist +-- Should warn on duplicate grants +GRANT pg_read_all_data TO role_read_all_with_admin; -- notice +NOTICE: role "role_read_all_with_admin" is already a member of role "pg_read_all_data" +GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1; -- notice +NOTICE: role "role_read_template1" is already a member of role "pg_read_all_data" in database "template1" +-- Should not warn if adjusting admin option +GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- silent +GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- notice +NOTICE: role "role_read_template1" is already a member of role "pg_read_all_data" in database "template1" +-- Check membership table +\connect postgres role_admin +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+------------+--------------+----------- + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_3 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + pg_read_all_data | role_read_template1 | role_admin | t | template1 + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_3 | role_admin | f | db_3 + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(11 rows) + +-- Test membership privileges (db_1) +\connect db_1 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_12; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_34; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_inherited_34; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_inherited_3; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- success +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET ROLE role_read_34; -- success +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET ROLE role_read_34; -- error +ERROR: permission denied to set role "role_read_34" +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12; -- success +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +-- Test membership privileges (db_2) +\connect db_2 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_12; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_34; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_inherited_34; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_inherited_3; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- success +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET ROLE role_read_34; -- success +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET ROLE role_read_34; -- error +ERROR: permission denied to set role "role_read_34" +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12; -- success +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +-- Test membership privileges (db_3) +\connect db_3 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_12; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_34; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_inherited_34; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_inherited_3; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- success +SET ROLE role_read_34; -- success +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- success +SET ROLE role_read_34; -- success +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12; -- error +SELECT * FROM data; -- error +ERROR: permission denied for table data +-- Test membership privileges (db_4) +\connect db_4 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_read_12; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_34; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_inherited_34; +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET ROLE role_inherited_3; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- success +SET ROLE role_read_34; -- success +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- error +ERROR: permission denied to set role "pg_read_all_data" +SET ROLE role_read_34; -- error +ERROR: permission denied to set role "role_read_34" +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + generate_series +----------------- + 1 + 2 + 3 +(3 rows) + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +ERROR: permission denied for table data +SET ROLE role_read_12; -- error +SELECT * FROM data; -- error +ERROR: permission denied for table data +\connect postgres role_admin +-- Should not warn if revoking admin option +REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent +REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+------------+--------------+----------- + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_3 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + pg_read_all_data | role_read_template1 | role_admin | f | template1 + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_3 | role_admin | f | db_3 + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(11 rows) + +-- Should warn if revoking a non-existent membership +REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- success +REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- warning +WARNING: role "role_read_template1" is not a member of role "pg_read_all_data" in database "template1" +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+------------+--------------+----------- + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_3 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_3 | role_admin | f | db_3 + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(10 rows) + +-- Revoke should only apply to the specified level +REVOKE pg_read_all_data FROM role_read_12; -- warning +WARNING: role "role_read_12" is not a member of role "pg_read_all_data" +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+------------+--------------+----------- + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_3 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_3 | role_admin | f | db_3 + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(10 rows) + +-- Ensure cluster-wide admin option can grant cluster-wide and in specific databases +CREATE ROLE role_granted; +SET SESSION AUTHORIZATION role_read_all_with_admin; +GRANT pg_read_all_data TO role_granted; -- success +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success +GRANT pg_read_all_data TO role_granted IN DATABASE db_1; -- success +GRANT role_read_34 TO role_granted; -- error +ERROR: must have admin option on role "role_read_34" +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+--------------------------+--------------+----------- + pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1 + pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres + pg_read_all_data | role_granted | role_read_all_with_admin | f | + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_3 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_3 | role_admin | f | db_3 + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(13 rows) + +-- Ensure database-specific admin option can only grant within that database +SET SESSION AUTHORIZATION role_read_34; +GRANT pg_read_all_data TO role_granted; -- error +ERROR: must have admin option on role "pg_read_all_data" +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- error +ERROR: must have admin option on role "pg_read_all_data" +GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error +ERROR: must have admin option on role "pg_read_all_data" +GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error +ERROR: must have admin option on role "pg_read_all_data" +\connect db_3 +SET SESSION AUTHORIZATION role_read_34; +GRANT pg_read_all_data TO role_granted; -- error +ERROR: must have admin option on role "pg_read_all_data" +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success +GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- notice +NOTICE: role "role_granted" is already a member of role "pg_read_all_data" in database "db_3" +GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error +ERROR: must have admin option on role "pg_read_all_data" +\connect db_4 +SET SESSION AUTHORIZATION role_read_34; +GRANT pg_read_all_data TO role_granted; -- error +ERROR: must have admin option on role "pg_read_all_data" +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success +GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error +ERROR: must have admin option on role "pg_read_all_data" +GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- notice +NOTICE: role "role_granted" is already a member of role "pg_read_all_data" in database "db_4" +\connect postgres role_admin +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+--------------------------+--------------+----------- + pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1 + pg_read_all_data | role_granted | role_read_34 | f | db_3 + pg_read_all_data | role_granted | role_read_34 | f | db_4 + pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres + pg_read_all_data | role_granted | role_read_all_with_admin | f | + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_3 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_3 | role_admin | f | db_3 + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(15 rows) + +-- Should clean up the membership table when dropping a database +\connect postgres role_admin +DROP DATABASE db_3; +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+--------------------------+--------------+----------- + pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1 + pg_read_all_data | role_granted | role_read_34 | f | db_4 + pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres + pg_read_all_data | role_granted | role_read_all_with_admin | f | + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_34 | role_admin | t | db_4 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_34 | role_inherited_34 | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(12 rows) + +-- Should clean up the membership table when dropping a role +DROP ROLE role_read_34; +SELECT * FROM check_memberships(); + role | member | grantor | admin_option | datname +--------------------------+--------------------------+--------------------------+--------------+----------- + pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1 + pg_read_all_data | role_granted | | f | db_4 + pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres + pg_read_all_data | role_granted | role_read_all_with_admin | f | + pg_read_all_data | role_read_12 | role_admin | f | db_1 + pg_read_all_data | role_read_12 | role_admin | f | db_2 + pg_read_all_data | role_read_all_noinherit | role_admin | f | template1 + pg_read_all_data | role_read_all_with_admin | role_admin | t | + role_read_12 | role_read_12_noinherit | role_admin | f | + role_read_all_with_admin | role_read_all_noinherit | role_admin | f | +(10 rows) + diff --git a/src/test/modules/unsafe_tests/sql/role_membership.sql b/src/test/modules/unsafe_tests/sql/role_membership.sql new file mode 100644 index 000000000000..66b6ec6bf7a9 --- /dev/null +++ b/src/test/modules/unsafe_tests/sql/role_membership.sql @@ -0,0 +1,291 @@ +CREATE ROLE role_admin LOGIN SUPERUSER; + +\connect postgres role_admin + +CREATE FUNCTION check_memberships() + RETURNS TABLE (role name, member name, grantor name, admin_option boolean, datname name) + AS $$ +SELECT + r.rolname as role, + m.rolname as member, + g.rolname as grantor, + admin_option, + d.datname +FROM pg_auth_members a +LEFT JOIN pg_roles r ON r.oid = a.roleid +LEFT JOIN pg_roles m ON m.oid = a.member +LEFT JOIN pg_roles g ON g.oid = a.grantor +LEFT JOIN pg_database d ON d.oid = a.dbid +WHERE + m.rolname LIKE 'role_%' +ORDER BY + 1, 2, 5 +$$ LANGUAGE SQL; + +-- Populate test databases +\connect template1 +CREATE TABLE data AS SELECT generate_series(1, 3); + +CREATE DATABASE db_1; +CREATE DATABASE db_2; +CREATE DATABASE db_3; +CREATE DATABASE db_4; + +-- Read all cluster-wide with admin option +CREATE ROLE role_read_all_with_admin; +GRANT pg_read_all_data TO role_read_all_with_admin WITH ADMIN OPTION; + +-- Read all in databases 1 and 2 +CREATE ROLE role_read_12; +GRANT pg_read_all_data TO role_read_12 IN DATABASE db_1; +GRANT pg_read_all_data TO role_read_12 IN DATABASE db_2; + +-- Read all in databases 3 and 4 with admin option +CREATE ROLE role_read_34; +GRANT pg_read_all_data TO role_read_34 IN DATABASE db_3 WITH ADMIN OPTION; +GRANT pg_read_all_data TO role_read_34 IN DATABASE db_4 WITH ADMIN OPTION; + +-- Inherits read all in databases 3 and 4 +CREATE ROLE role_inherited_34; +GRANT role_read_34 TO role_inherited_34; + +-- Inherits read all in database 3 +CREATE ROLE role_inherited_3; +GRANT role_read_34 TO role_inherited_3 IN DATABASE db_3; + +-- No inherit +CREATE ROLE role_read_all_noinherit NOINHERIT; +GRANT role_read_all_with_admin TO role_read_all_noinherit; + +-- No inherit in databases 1 and 2 +CREATE ROLE role_read_12_noinherit NOINHERIT; +GRANT role_read_12 TO role_read_12_noinherit; + +-- Alternate syntax +CREATE ROLE role_read_template1; +GRANT pg_read_all_data TO role_read_template1, role_read_all_noinherit IN CURRENT DATABASE; + +-- Failure due to missing database +GRANT pg_read_all_data TO role_read_template1 IN DATABASE non_existent; -- error + +-- Should warn on duplicate grants +GRANT pg_read_all_data TO role_read_all_with_admin; -- notice +GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1; -- notice + +-- Should not warn if adjusting admin option +GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- silent +GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- notice + +-- Check membership table +\connect postgres role_admin +SELECT * FROM check_memberships(); + +-- Test membership privileges (db_1) +\connect db_1 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success +SET ROLE role_read_12; +SELECT * FROM data; -- success +SET ROLE role_read_34; +SELECT * FROM data; -- error +SET ROLE role_inherited_34; +SELECT * FROM data; -- error +SET ROLE role_inherited_3; +SELECT * FROM data; -- error +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error + +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- success + +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- error +SET ROLE role_read_34; -- success + +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- error +SET ROLE role_read_34; -- error + +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12; -- success +SELECT * FROM data; -- success + +-- Test membership privileges (db_2) +\connect db_2 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success +SET ROLE role_read_12; +SELECT * FROM data; -- success +SET ROLE role_read_34; +SELECT * FROM data; -- error +SET ROLE role_inherited_34; +SELECT * FROM data; -- error +SET ROLE role_inherited_3; +SELECT * FROM data; -- error +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error + +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- success + +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- error +SET ROLE role_read_34; -- success + +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- error +SET ROLE role_read_34; -- error + +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12; -- success +SELECT * FROM data; -- success + +-- Test membership privileges (db_3) +\connect db_3 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success +SET ROLE role_read_12; +SELECT * FROM data; -- error +SET ROLE role_read_34; +SELECT * FROM data; -- success +SET ROLE role_inherited_34; +SELECT * FROM data; -- success +SET ROLE role_inherited_3; +SELECT * FROM data; -- success +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error + +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- error + +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- success +SET ROLE role_read_34; -- success + +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- success +SET ROLE role_read_34; -- success + +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12; -- error +SELECT * FROM data; -- error + +-- Test membership privileges (db_4) +\connect db_4 +SET ROLE role_read_all_with_admin; +SELECT * FROM data; -- success +SET ROLE role_read_12; +SELECT * FROM data; -- error +SET ROLE role_read_34; +SELECT * FROM data; -- success +SET ROLE role_inherited_34; +SELECT * FROM data; -- success +SET ROLE role_inherited_3; +SELECT * FROM data; -- error +SET ROLE role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12_noinherit; +SELECT * FROM data; -- error + +SET SESSION AUTHORIZATION role_read_12; +SET ROLE pg_read_all_data; -- error + +SET SESSION AUTHORIZATION role_inherited_34; +SET ROLE pg_read_all_data; -- success +SET ROLE role_read_34; -- success + +SET SESSION AUTHORIZATION role_inherited_3; +SET ROLE pg_read_all_data; -- error +SET ROLE role_read_34; -- error + +SET SESSION AUTHORIZATION role_read_all_noinherit; +SELECT * FROM data; -- error +SET ROLE pg_read_all_data; -- success +SELECT * FROM data; -- success + +SET SESSION AUTHORIZATION role_read_12_noinherit; +SELECT * FROM data; -- error +SET ROLE role_read_12; -- error +SELECT * FROM data; -- error + +\connect postgres role_admin + +-- Should not warn if revoking admin option +REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent +REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent +SELECT * FROM check_memberships(); + +-- Should warn if revoking a non-existent membership +REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- success +REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- warning +SELECT * FROM check_memberships(); + +-- Revoke should only apply to the specified level +REVOKE pg_read_all_data FROM role_read_12; -- warning +SELECT * FROM check_memberships(); + +-- Ensure cluster-wide admin option can grant cluster-wide and in specific databases +CREATE ROLE role_granted; +SET SESSION AUTHORIZATION role_read_all_with_admin; +GRANT pg_read_all_data TO role_granted; -- success +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success +GRANT pg_read_all_data TO role_granted IN DATABASE db_1; -- success +GRANT role_read_34 TO role_granted; -- error +SELECT * FROM check_memberships(); + +-- Ensure database-specific admin option can only grant within that database +SET SESSION AUTHORIZATION role_read_34; +GRANT pg_read_all_data TO role_granted; -- error +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- error +GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error +GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error + +\connect db_3 +SET SESSION AUTHORIZATION role_read_34; +GRANT pg_read_all_data TO role_granted; -- error +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success +GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- notice +GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error + +\connect db_4 +SET SESSION AUTHORIZATION role_read_34; +GRANT pg_read_all_data TO role_granted; -- error +GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success +GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error +GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- notice + +\connect postgres role_admin +SELECT * FROM check_memberships(); + +-- Should clean up the membership table when dropping a database +\connect postgres role_admin +DROP DATABASE db_3; +SELECT * FROM check_memberships(); + +-- Should clean up the membership table when dropping a role +DROP ROLE role_read_34; +SELECT * FROM check_memberships(); diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 215eb899be3e..79fb69059b68 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -197,6 +197,7 @@ NOTICE: checking pg_tablespace {spcowner} => pg_authid {oid} NOTICE: checking pg_auth_members {roleid} => pg_authid {oid} NOTICE: checking pg_auth_members {member} => pg_authid {oid} NOTICE: checking pg_auth_members {grantor} => pg_authid {oid} +NOTICE: checking pg_auth_members {dbid} => pg_database {oid} NOTICE: checking pg_shdepend {dbid} => pg_database {oid} NOTICE: checking pg_shdepend {classid} => pg_class {oid} NOTICE: checking pg_shdepend {refclassid} => pg_class {oid} ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-01-21 22:12 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-05-31 23:39 [PATCH 1/2] Make ALTER SEQUENCE, including RESTART, fully transactional. Andres Freund <[email protected]> 2020-08-19 12:34 [PATCH 4/5] pg_rewind: Refactor the abstraction to fetch from local/libpq source. Heikki Linnakangas <[email protected]> 2022-01-21 22:12 Re: Proposal: allow database-specific role memberships Kenaniah Cerny <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox