Streaming replication as a separate permissions
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.
Obviously the patch needs docs and some system views updates, which I
will add later. But I wanted to post what I have so far for a quick
review to confirm whether I'm on the right track or not... How it
works should be rather obvious - adds a "WITH
REPLICATION/NOREPLICATION" to the create and alter role commands, and
then check this when a connection attempts to start the walsender.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attachments:
repl_role.patchtext/x-patch; charset=US-ASCII; name=repl_role.patchDownload
*** a/src/backend/commands/user.c
--- b/src/backend/commands/user.c
***************
*** 94,99 **** CreateRole(CreateRoleStmt *stmt)
--- 94,100 ----
bool createrole = false; /* Can this user create roles? */
bool createdb = false; /* Can the user create databases? */
bool canlogin = false; /* Can this user login? */
+ bool isreplication = false; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *addroleto = NIL; /* roles to make this a member of */
List *rolemembers = NIL; /* roles to be members of this role */
***************
*** 107,112 **** CreateRole(CreateRoleStmt *stmt)
--- 108,114 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *daddroleto = NULL;
DefElem *drolemembers = NULL;
***************
*** 190,195 **** CreateRole(CreateRoleStmt *stmt)
--- 192,205 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 247,252 **** CreateRole(CreateRoleStmt *stmt)
--- 257,264 ----
createdb = intVal(dcreatedb->arg) != 0;
if (dcanlogin)
canlogin = intVal(dcanlogin->arg) != 0;
+ if (disreplication)
+ isreplication = intVal(disreplication->arg) != 0;
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 341,346 **** CreateRole(CreateRoleStmt *stmt)
--- 353,359 ----
/* superuser gets catupdate right by default */
new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper);
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
if (password)
***************
*** 439,444 **** AlterRole(AlterRoleStmt *stmt)
--- 452,458 ----
int createrole = -1; /* Can this user create roles? */
int createdb = -1; /* Can the user create databases? */
int canlogin = -1; /* Can this user login? */
+ int isreplication = -1; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *rolemembers = NIL; /* roles to be added/removed */
char *validUntil = NULL; /* time the login is valid until */
***************
*** 450,455 **** AlterRole(AlterRoleStmt *stmt)
--- 464,470 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
***************
*** 514,519 **** AlterRole(AlterRoleStmt *stmt)
--- 529,542 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 556,561 **** AlterRole(AlterRoleStmt *stmt)
--- 579,586 ----
createdb = intVal(dcreatedb->arg);
if (dcanlogin)
canlogin = intVal(dcanlogin->arg);
+ if (disreplication)
+ isreplication = intVal(disreplication->arg);
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 600,605 **** AlterRole(AlterRoleStmt *stmt)
--- 625,631 ----
createrole < 0 &&
createdb < 0 &&
canlogin < 0 &&
+ isreplication < 0 &&
!dconnlimit &&
!rolemembers &&
!validUntil &&
***************
*** 685,690 **** AlterRole(AlterRoleStmt *stmt)
--- 711,722 ----
new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
}
+ if (isreplication >= 0)
+ {
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
+ new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
+ }
+
if (dconnlimit)
{
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 510,517 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
! NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
--- 510,518 ----
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOREPLICATION_P
! NOSUPERUSER NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
! NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
***************
*** 523,530 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
! RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
--- 524,532 ----
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA REPLICATION_P
! RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
! ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
***************
*** 864,869 **** AlterOptRoleElem:
--- 866,879 ----
{
$$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
}
+ | REPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(TRUE));
+ }
+ | NOREPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(FALSE));
+ }
| CONNECTION LIMIT SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($3));
***************
*** 11288,11293 **** unreserved_keyword:
--- 11298,11304 ----
| NOCREATEUSER
| NOINHERIT
| NOLOGIN_P
+ | NOREPLICATION_P
| NOSUPERUSER
| NOTHING
| NOTIFY
***************
*** 11330,11335 **** unreserved_keyword:
--- 11341,11347 ----
| REPEATABLE
| REPLACE
| REPLICA
+ | REPLICATION_P
| RESET
| RESTART
| RESTRICT
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
***************
*** 60,65 ****
--- 60,66 ----
static HeapTuple GetDatabaseTuple(const char *dbname);
static HeapTuple GetDatabaseTupleByOid(Oid dboid);
+ static bool is_replication_role(void);
static void PerformAuthentication(Port *port);
static void CheckMyDatabase(const char *name, bool am_superuser);
static void InitCommunication(void);
***************
*** 166,171 **** GetDatabaseTupleByOid(Oid dboid)
--- 167,189 ----
return tuple;
}
+ /*
+ * is_replication_role -- check if the role is a replication role
+ */
+ static bool
+ is_replication_role(void)
+ {
+ bool result = false;
+ HeapTuple utup;
+
+ utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(GetUserId()));
+ if (HeapTupleIsValid(utup))
+ {
+ result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication;
+ ReleaseSysCache(utup);
+ }
+ return result;
+ }
/*
* PerformAuthentication -- authenticate a remote client
***************
*** 658,668 **** InitPostgres(const char *in_dbname, Oid dboid, const char *username,
{
Assert(!bootstrap);
! /* must have authenticated as a superuser */
! if (!am_superuser)
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
--- 676,686 ----
{
Assert(!bootstrap);
! /* must have authenticated as a replication role */
! if (!is_replication_role())
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be replication role to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
*** a/src/include/catalog/pg_authid.h
--- b/src/include/catalog/pg_authid.h
***************
*** 51,56 **** CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MAC
--- 51,57 ----
bool rolcreatedb; /* allowed to create databases? */
bool rolcatupdate; /* allowed to alter catalogs manually? */
bool rolcanlogin; /* allowed to log in as session user? */
+ bool rolreplication; /* role used for streaming replication */
int4 rolconnlimit; /* max connections allowed (-1=no limit) */
/* remaining fields may be null; use heap_getattr to read them! */
***************
*** 72,78 **** typedef FormData_pg_authid *Form_pg_authid;
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 10
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
--- 73,79 ----
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 11
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
***************
*** 80,88 **** typedef FormData_pg_authid *Form_pg_authid;
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolconnlimit 8
! #define Anum_pg_authid_rolpassword 9
! #define Anum_pg_authid_rolvaliduntil 10
/* ----------------
* initial contents of pg_authid
--- 81,90 ----
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolreplication 8
! #define Anum_pg_authid_rolconnlimit 9
! #define Anum_pg_authid_rolpassword 10
! #define Anum_pg_authid_rolvaliduntil 11
/* ----------------
* initial contents of pg_authid
***************
*** 91,97 **** typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 93,99 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t f -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 250,255 **** PG_KEYWORD("nocreateuser", NOCREATEUSER, UNRESERVED_KEYWORD)
--- 250,256 ----
PG_KEYWORD("noinherit", NOINHERIT, UNRESERVED_KEYWORD)
PG_KEYWORD("nologin", NOLOGIN_P, UNRESERVED_KEYWORD)
PG_KEYWORD("none", NONE, COL_NAME_KEYWORD)
+ PG_KEYWORD("noreplication", NOREPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("nosuperuser", NOSUPERUSER, UNRESERVED_KEYWORD)
PG_KEYWORD("not", NOT, RESERVED_KEYWORD)
PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD)
***************
*** 313,318 **** PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD)
--- 314,320 ----
PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
+ PG_KEYWORD("replication", REPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
Magnus Hagander <magnus@hagander.net> writes:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.
Maybe it needn't require "max" permissions, but one of the motivations
for requiring superusernesss was to prevent Joe User from sucking every
last byte of data out of your database (and into someplace he could
examine it at leisure). This patch opens that barn door wide, because
so far as I can see, it allows anybody at all to grant the replication
privilege ... or revoke it, thereby breaking your replication setup.
I think only superusers should be allowed to change the flag.
regards, tom lane
On Thu, Dec 23, 2010 at 10:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.Maybe it needn't require "max" permissions, but one of the motivations
for requiring superusernesss was to prevent Joe User from sucking every
last byte of data out of your database (and into someplace he could
examine it at leisure). This patch opens that barn door wide, because
so far as I can see, it allows anybody at all to grant the replication
privilege ... or revoke it, thereby breaking your replication setup.
I think only superusers should be allowed to change the flag.
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.
I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.
BTW, is it possible to set things up so that a REPLICATION account
can be NOLOGIN, thereby making it really hard to abuse for other
purposes? Or does the login privilege check come too soon?
regards, tom lane
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.
And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Dec 23, 2010 at 16:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.Maybe it needn't require "max" permissions, but one of the motivations
for requiring superusernesss was to prevent Joe User from sucking every
last byte of data out of your database (and into someplace he could
examine it at leisure). This patch opens that barn door wide, because
so far as I can see, it allows anybody at all to grant the replication
privilege ... or revoke it, thereby breaking your replication setup.
I think only superusers should be allowed to change the flag.
That was certainly not intentional - and doesn't work that way for me
at least, unless I broke it right before I submitted it.
oh hang on.. Yeah, it's allowing anybody *that has CREATE ROLE*
privilege to do it, I think. And I agree that's wrong and should be
fixed. But I can't see it allowing anybody at all to do it - am I
misreading the code?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Thu, Dec 23, 2010 at 16:57, Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.
Most of the people I run across in the real world are rather surprised
how *easy* it is to set up, and not how complex. And tbh, the only
complexity complaints I've heard there are about the requirement to
start/backup/stop to get it up and running. I've always told everybody
to create a separate account to do it, and not heard a single comment
about that.
That said, how about a compromise in that we add the replication flag
by default to the initial superuser when it's created? That way, it's
at least possible to remove it if you want to. Would that address your
complexity concern?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
On Thu, Dec 23, 2010 at 16:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think only superusers should be allowed to change the flag.
That was certainly not intentional - and doesn't work that way for me
at least, unless I broke it right before I submitted it.
oh hang on.. Yeah, it's allowing anybody *that has CREATE ROLE*
privilege to do it, I think. And I agree that's wrong and should be
fixed. But I can't see it allowing anybody at all to do it - am I
misreading the code?
Ah, sorry, yeah there are probably CREATE ROLE privilege checks
somewhere upstream of here. I was expecting to see a privilege check
added by the patch itself, and did not, so I complained.
regards, tom lane
Magnus Hagander <magnus@hagander.net> writes:
On Thu, Dec 23, 2010 at 16:57, Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I don't particularly mind breaking that. �If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.
And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. �You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.
Most of the people I run across in the real world are rather surprised
how *easy* it is to set up, and not how complex. And tbh, the only
complexity complaints I've heard there are about the requirement to
start/backup/stop to get it up and running. I've always told everybody
to create a separate account to do it, and not heard a single comment
about that.
FWIW, it seems unreasonable to me to expect that we will not be breaking
any part of a 9.0 replication configuration over the next release or
two. We *knew* we were shipping a rough version that would require
refinements, and this is one of the planned refinements.
That said, how about a compromise in that we add the replication flag
by default to the initial superuser when it's created? That way, it's
at least possible to remove it if you want to. Would that address your
complexity concern?
It does nothing to address my security concern. I want to discourage
people from using superuser accounts for this, full stop.
regards, tom lane
On 12/23/2010 08:59 PM, Magnus Hagander wrote:
On Thu, Dec 23, 2010 at 16:57, Robert Haas<robertmhaas@gmail.com> wrote:
On Thu, Dec 23, 2010 at 10:54 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
Robert Haas<robertmhaas@gmail.com> writes:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.I don't particularly mind breaking that. If we leave it as-is, we'll
be encouraging people to use superuser accounts for things that don't
need that, which can't be good from a security standpoint.And if we break it, we'll be adding an additional, mandatory step to
make replication work that isn't required today. You might think
that's OK, but I think the majority opinion is that it's already
excessively complex.Most of the people I run across in the real world are rather surprised
how *easy* it is to set up, and not how complex. And tbh, the only
complexity complaints I've heard there are about the requirement to
start/backup/stop to get it up and running. I've always told everybody
to create a separate account to do it, and not heard a single comment
about that.
I agree - people I talked to are fairly surprised on us not using a
dedicated replication role but are surprised at the complexity of
actually initializing the replication (mostly the "we cannot do a base
backup over the replication connection" missfeature)
Stefan
On 12/23/10 7:49 AM, Robert Haas wrote:
I haven't looked at the patch yet, but I think we should continue to
allow superuser-ness to be *sufficient* for replication - i.e.
superusers will automatically have the replication privilege just as
they do any other - and merely allow this as an option for when you
want to avoid doing it that way.
Yes. Currently I already create a separate "replicator" superuser just
so that I can simply track which connections belong to replication. It
would be great if it could make the "replicator" user less than a superuser.
If we still make it possible for "postgres" to replicate, then we don't
add any complexity to the simplest setup.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
If we still make it possible for "postgres" to replicate, then we don't
add any complexity to the simplest setup.
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.
regards, tom lane
On 12/23/10 2:21 PM, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
If we still make it possible for "postgres" to replicate, then we don't
add any complexity to the simplest setup.Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.
I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:21 PM, Tom Lane wrote:
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.
I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.
Well, the reverse of that is just as true: if we ship it without
replication permissions on the postgres user, people can change that if
they'd rather not create a separate role for replication. But I think
we should encourage people to NOT do it that way. Setting it up that
way by default hardly encourages use of a more secure arrangement.
regards, tom lane
* Josh Berkus (josh@agliodbs.com) wrote:
On 12/23/10 2:21 PM, Tom Lane wrote:
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.
The point is that the *replication* role can't grant itself superuser
privs. Having the replication role compromised isn't great, but if that
role is *also* a superuser, then the whole database server could be
compromised. Encouraging users to continue to configure remote systems
with the ability to connect as a superuser when it's not necessary is a
bad idea.
One compromise would be to:
a) let superusers be granted the replication permission
b) have pg_dump assume that superusers have that permission when dumping
from a version which pre-dates the replication grant
c) have pg_upgrade assume the superuser has that permission when
upgrading
d) *not* grant replication to the default superuser
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.
Thanks,
Stephen
On 12/23/10 2:33 PM, Stephen Frost wrote:
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.
Well, if we really want people to change their behavior then we need to
make it easy for them:
1) have a replication permission
2) *by default* create a replication user with the replication
permission when we initdb.
3) have an example line for a replication connection by the replication
user in the default pg_hba.conf (commented out).
4) change all our docs and examples to use that replication user.
If using the replication user is easier than any other path, people
will. If it's harder, they won't.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:33 PM, Stephen Frost wrote:
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.
Well, if we really want people to change their behavior then we need to
make it easy for them:
1) have a replication permission
2) *by default* create a replication user with the replication
permission when we initdb.
Yeah, I could see doing that ... the entry would be wasted if you're not
doing any replication, but one wasted catalog entry isn't much.
However, it'd be a real good idea for that role to be NOLOGIN if it's
there by default.
regards, tom lane
* Josh Berkus (josh@agliodbs.com) wrote:
1) have a replication permission
Right, that's what this patch is about.
2) *by default* create a replication user with the replication
permission when we initdb.
I'm not entirely sure about this one.. I'm not against it but I'm also
not really 'for' it. :)
3) have an example line for a replication connection by the replication
user in the default pg_hba.conf (commented out).
Sure.
4) change all our docs and examples to use that replication user.
I don't have a problem with that.
Thanks,
Stephen
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
However, it'd be a real good idea for that role to be NOLOGIN if it's
there by default.
Definitely. I'd also suggest we WARN if someone creates a situation
where a role has both replication and login, and maybe prevent it
altogether, it's just a bad idea..
Thanks,
Stephen
On Thu, Dec 23, 2010 at 23:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:33 PM, Stephen Frost wrote:
A better alternative, imv, would be to just have a & d, and mention in
the release notes that users *should* create a dedicated replication
role which is *not* a superuser but *does* have the replication grant,
but if they don't want to change their existing configurations, they can
just grant the replication privilege to whatever role they're currently
using.Well, if we really want people to change their behavior then we need to
make it easy for them:1) have a replication permission
2) *by default* create a replication user with the replication
permission when we initdb.Yeah, I could see doing that ... the entry would be wasted if you're not
doing any replication, but one wasted catalog entry isn't much.However, it'd be a real good idea for that role to be NOLOGIN if it's
there by default.
That shouldn't be too hard - I'll look at making the patch do that to
make sure it *isn't* that hard ;)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
I'm not entirely sure about this one.. I'm not against it but I'm also
not really 'for' it. :)
2 reasons: (a) if users need to CREATE USER, that *does* add an extra
step and they're more likely to just choose to grant to postgres
instead, (b) having a standard installed user (just like the user
"postgres" is standard) would make our docs, examples and tutorials much
easier to use.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On Thu, Dec 23, 2010 at 5:59 PM, Josh Berkus <josh@agliodbs.com> wrote:
I'm not entirely sure about this one.. I'm not against it but I'm also
not really 'for' it. :)2 reasons: (a) if users need to CREATE USER, that *does* add an extra
step and they're more likely to just choose to grant to postgres
instead, (b) having a standard installed user (just like the user
"postgres" is standard) would make our docs, examples and tutorials much
easier to use.
If the user exists but is disabled by default, I'm not sure that
really provides any advantage over not having it at all. And it
certainly can't be enabled by default.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
If the user exists but is disabled by default, I'm not sure that
really provides any advantage over not having it at all. And it
certainly can't be enabled by default.
I was presuming that NOLOGIN wouldn't prevent a replication connections
via the replication user. So the user would be "enabled" as far as
replication was concerned.
And currently, there is no replication connection in the pg_hba.conf.
So that's not a change; in fact, having a sample one would be an
improvement.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On Dec23, 2010, at 16:54 , Tom Lane wrote:
BTW, is it possible to set things up so that a REPLICATION account
can be NOLOGIN, thereby making it really hard to abuse for other
purposes? Or does the login privilege check come too soon?
Please don't. This violates the principle of least surprise big time!
And, whats worse, violate it in such a way that people *underestimate*
the permissions a particular role has, which from a security POV is
the worst than can happen. You pointed out yourself that REPLICATION
is a powerful permission to have because it essentially grants you read
access to the whole cluster. By allowing roles to connect a WAL receivers
even if they have NOLOGIN set, you're giving these powerful permission
to a role a DBA might think is disabled!
Now, I *can* see that having roles which may only connect as WAL receivers,
not to issue queries, is worthwhile. However, please either
A) Invert a new flag for that, for example SQL/NOSQL. A pure replication
role would have WITH REPLICATION NOSQL, while most other would have
WITH NOREPLICATION SQL. It's debatable wether postgres should have
WITH REPLICATION SQL or WITH NOREPLICATION SQL by default.
B) Forbid REPLICATION roles from connecting as anything else than WAL
receivers altogether. It'd then probably be a good idea to prevent
such roles from having SUPERUSER, CREATEDB, CREATEROLE and INHERIT
set as these flag wouldn't then make any sense.
The only downside of (B) that I can see is that it'd break setups that
work with 9.0.
best regards,
Florian Pflug
Florian Pflug <fgp@phlo.org> writes:
On Dec23, 2010, at 16:54 , Tom Lane wrote:
BTW, is it possible to set things up so that a REPLICATION account
can be NOLOGIN, thereby making it really hard to abuse for other
purposes? Or does the login privilege check come too soon?
Please don't. This violates the principle of least surprise big time!
How so? (Please note I said *can be*, not *has to be*.)
The point of this is to ensure that if someone does illicitly come by
the replication role's password, they can't use it to log in. They can
still steal all your data, but they can't actually get into the
database. I don't see why it's a bad idea to configure things that way.
regards, tom lane
On Dec24, 2010, at 04:16 , Tom Lane wrote:
Florian Pflug <fgp@phlo.org> writes:
On Dec23, 2010, at 16:54 , Tom Lane wrote:
BTW, is it possible to set things up so that a REPLICATION account
can be NOLOGIN, thereby making it really hard to abuse for other
purposes? Or does the login privilege check come too soon?Please don't. This violates the principle of least surprise big time!
How so? (Please note I said *can be*, not *has to be*.)
Because a DBA might "ALTER ROLE replication WITH NOLOGIN", thinking he has
just disabled that role. Only to find out weeks later than he hasn't
and that someone has been using that role to stream weeks worth of
confidential data to who knows where.
The problem here is that you suggest NOLOGIN should mean "Not allowed
to issue SQL commands", which really isn't what the name "NOLOGIN"
conveys. The concept itself is perfectly fine, but the name is dangerously
confusing.
The point of this is to ensure that if someone does illicitly come by
the replication role's password, they can't use it to log in. They can
still steal all your data, but they can't actually get into the
database. I don't see why it's a bad idea to configure things that way.
It's perfectly fine to configure things that way, and is in fact what I would
do. I'd just prefer the name for that setting to convey it's actual meaning
which is why I suggested adding a SQL/NOSQL flag. (Or SESSION/NOSESSION,
or whatever). Or, much simpler, to prevent WITH REPLICATION roles from issuing
SQL commands altogether. That'd achieve your goal just as well and is way less
confusing.
best regards,
Florian Pflug
Florian Pflug <fgp@phlo.org> writes:
The problem here is that you suggest NOLOGIN should mean "Not allowed
to issue SQL commands", which really isn't what the name "NOLOGIN"
conveys.
No, it means "not allowed to connect". It's possible now to issue
commands as a NOLOGIN user, you just have to use SET ROLE to become the
user. I think you're arguing about a design choice that was already
made some time ago.
regards, tom lane
On Thu, Dec 23, 2010 at 11:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Florian Pflug <fgp@phlo.org> writes:
The problem here is that you suggest NOLOGIN should mean "Not allowed
to issue SQL commands", which really isn't what the name "NOLOGIN"
conveys.No, it means "not allowed to connect". It's possible now to issue
commands as a NOLOGIN user, you just have to use SET ROLE to become the
user. I think you're arguing about a design choice that was already
made some time ago.
I think I agree with Florian about the confusing-ness of the proposed
semantics. Aren't you saying you want NOLOGIN mean "not allowed to
log in for the purposes of issuing SQL commands, but allowed to log in
for replication"? Uggh.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote:
I think I agree with Florian about the confusing-ness of the proposed
semantics. Aren't you saying you want NOLOGIN mean "not allowed to
log in for the purposes of issuing SQL commands, but allowed to log in
for replication"? Uggh.
I like the general idea of a replication-only "role" or "login". Maybe
implementing that as a role w/ all the things that come along with it
being a role isn't right, but we don't want to have to reinvent all the
supported auth mechanisms (and please don't propose limiting the auth
options for the replication login!). Is there a way we can leverage the
auth mechanisms, etc, while forcing the 'replication role' to only be
able to do what a 'replication role' should do?
Thanks,
Stephen
On Dec24, 2010, at 05:00 , Tom Lane wrote:
Florian Pflug <fgp@phlo.org> writes:
The problem here is that you suggest NOLOGIN should mean "Not allowed
to issue SQL commands", which really isn't what the name "NOLOGIN"
conveys.No, it means "not allowed to connect".
Exactly. Which proves my point, unless you're ready to argue that
replication connections somehow don't count as "connections".
It's possible now to issue
commands as a NOLOGIN user, you just have to use SET ROLE to become the
user. I think you're arguing about a design choice that was already
made some time ago.
You've lost me, how is that an argument in your favour? I *wasn't* arguing
that NOLOGIN ought to mean "No allowed to issue SQL commands". It was what
*your* proposal of letting a role connect for replication purposes despite
a NOLOGIN flag would *make* NOLOGIN mean.
best regards,
Florian Pflug
On Thu, 2010-12-23 at 10:53 +0100, Magnus Hagander wrote:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.
Is backup part of this new privilege, or not?
I think if we're going to introduce a new level of privilege, then we
should introduce all delegatable privs in one software release. Much
better than having someone think up a new delegatable priv each release
for next 5 years.
Other possible ones include unsafe PL creation, seeing logged SQL etc..
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Dec 27, 2010 at 09:32, Simon Riggs <simon@2ndquadrant.com> wrote:
On Thu, 2010-12-23 at 10:53 +0100, Magnus Hagander wrote:
Here's a patch that changes walsender to require a special privilege
for replication instead of relying on superuser permissions. We
discussed this back before 9.0 was finalized, but IIRC we ran out of
time. The motivation being that you really want to use superuser as
little as possible - and since being a replication slave is a read
only role, it shouldn't require the maximum permission available in
the system.Is backup part of this new privilege, or not?
The "integrated base backup", once we have that, that's based on the
walsender protocol? yes.
pg_dump style backups? No.
I think if we're going to introduce a new level of privilege, then we
should introduce all delegatable privs in one software release. Much
better than having someone think up a new delegatable priv each release
for next 5 years.Other possible ones include unsafe PL creation, seeing logged SQL etc..
That's certainly an option, but that means someone would have to come
up with a list ;) And one that's reasonable - for example, "unsafe pl
creation" is from a security perspective (which is the only thing
that's really intersting here) the same as superuser.
Seeing logged SQL isn't - but being able to filter the logfiles on
that requires a *lot* more than just defining a security privilege. If
we mean "arbitrary log file reading", the easiest way to fix that
would be to stop checking for superuser permissions in the
read-file-function, and instead use the permissions *on the function*
to control it. In fact, that is something that we could (should?) do
for a bunch of other functions as well, so that we can in that way
provide much more granular permissions level than just blanked
assigning of privileges.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Dec 27, 2010 at 9:36 AM, Magnus Hagander <magnus@hagander.net> wrote:
Seeing logged SQL isn't - but being able to filter the logfiles on
that requires a *lot* more than just defining a security privilege. If
we mean "arbitrary log file reading", the easiest way to fix that
would be to stop checking for superuser permissions in the
read-file-function, and instead use the permissions *on the function*
to control it. In fact, that is something that we could (should?) do
for a bunch of other functions as well, so that we can in that way
provide much more granular permissions level than just blanked
assigning of privileges.
That would require having users change the permissions on system
objects, which seems, icky (would they even be dumped?). Given that
the superuser could already create a security definer wrapper function
with the privileges required, I don't think this is needed.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Fri, Dec 24, 2010 at 05:46, Stephen Frost <sfrost@snowman.net> wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
I think I agree with Florian about the confusing-ness of the proposed
semantics. Aren't you saying you want NOLOGIN mean "not allowed to
log in for the purposes of issuing SQL commands, but allowed to log in
for replication"? Uggh.I like the general idea of a replication-only "role" or "login". Maybe
implementing that as a role w/ all the things that come along with it
being a role isn't right, but we don't want to have to reinvent all the
supported auth mechanisms (and please don't propose limiting the auth
options for the replication login!). Is there a way we can leverage the
auth mechanisms, etc, while forcing the 'replication role' to only be
able to do what a 'replication role' should do?
We could quite easily make a replication role *never* be able to
connect to a non-walsender backend. That would mean that if you set
your role to WITH REPLICATION, it can *only* be used for replication
and nothing else (well, you could still SET ROLE to it, but given that
it's not a superuser (anymore), that doesn't have any security
implications. Though we could perhaps restrict that as well in the SET
ROLE processing code).
This requires there to be a separate user for replication in all cases
- which isn't a bad thing IMHO. And it also doesn't "abuse"/confuse
the NOLOGIN attribute.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, 2010-12-27 at 10:36 +0100, Magnus Hagander wrote:
Is backup part of this new privilege, or not?
The "integrated base backup", once we have that, that's based on the
walsender protocol? yes.
pg_dump style backups? No.
Where does pg_start_backup()/stop fit?
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Dec 27, 2010 at 11:34, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-12-27 at 10:36 +0100, Magnus Hagander wrote:
Is backup part of this new privilege, or not?
The "integrated base backup", once we have that, that's based on the
walsender protocol? yes.
pg_dump style backups? No.Where does pg_start_backup()/stop fit?
Good question :)
Given that the integrated-base-backup would call it for you, that one
would definitely get it automatically.
Given that the latest discissions seem to have most people wanting the
replication role *not* to be allowed to log in and run general SQL, we
should not drive the start/stop backup permissions from that
privilege.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Dec 27, 2010 at 10:53, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Dec 24, 2010 at 05:46, Stephen Frost <sfrost@snowman.net> wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
I think I agree with Florian about the confusing-ness of the proposed
semantics. Aren't you saying you want NOLOGIN mean "not allowed to
log in for the purposes of issuing SQL commands, but allowed to log in
for replication"? Uggh.I like the general idea of a replication-only "role" or "login". Maybe
implementing that as a role w/ all the things that come along with it
being a role isn't right, but we don't want to have to reinvent all the
supported auth mechanisms (and please don't propose limiting the auth
options for the replication login!). Is there a way we can leverage the
auth mechanisms, etc, while forcing the 'replication role' to only be
able to do what a 'replication role' should do?We could quite easily make a replication role *never* be able to
connect to a non-walsender backend. That would mean that if you set
your role to WITH REPLICATION, it can *only* be used for replication
and nothing else (well, you could still SET ROLE to it, but given that
it's not a superuser (anymore), that doesn't have any security
implications. Though we could perhaps restrict that as well in the SET
ROLE processing code).This requires there to be a separate user for replication in all cases
- which isn't a bad thing IMHO. And it also doesn't "abuse"/confuse
the NOLOGIN attribute.
Actually, having implemented that and tested it, I realize that's a
pretty bad idea. For one thing, it broke my own pg_streamrecv program,
since it requires the ability to connect to the master and select a
pg_current_xlog_location().
We could allow NOLOGIN connections to do replication, but I agree with
those saying that's pretty confusing - to the point of making it very
easy for a DBA to think he's secure when he's not. And having the
*ability* to use the same role as replication and superuser isn't a
bad thing - it's either *requiring* it or *having it so by default*
that's the problem, IMHO.
I think it's Ok to ship with replication off and require people to
either create a replication role or assign the permissions to their
superuser - as long as this is well documented in the manual.
The compromise I think would be to ship with a replication role
installed and *enabled*. There's no win at all in shipping with a
disabled replication role - it would still require the "extra step"
that people want to avoid.
What I'd rather do is make the "all" keyword for databases in
pg_hba.conf actually include replication connections - now that we
have a separate permissions - thus removing the need to configure a
specific row in pg_hba.conf for those users who just put a "0.0.0.0/0
md5" row in there to dumb it down. That still let's those who care
about higher security lock it down if they want to, while still
removing a step for those users who don't care.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Dec27, 2010, at 12:15 , Magnus Hagander wrote:
Actually, having implemented that and tested it, I realize that's a
pretty bad idea. For one thing, it broke my own pg_streamrecv program,
since it requires the ability to connect to the master and select a
pg_current_xlog_location().
I'm starting to think what we really want here is a kind of read-only
superuser. WITH REPLICATION already essentially gives you read-only
access to the whole database. Thus, allowing WITH REPLICATION roles
read-only access to everything on the SQL level also doesn't really
extend their abilities, it merely makes getting some information faster
and more convenient. It'd also make WITH REPLICATION the perfect fit
for pg_dump-style backups, if you're uneasy about using a superuser
for that.
best regards,
Florian Pflug
On Mon, 2010-12-27 at 12:00 +0100, Magnus Hagander wrote:
On Mon, Dec 27, 2010 at 11:34, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-12-27 at 10:36 +0100, Magnus Hagander wrote:
Is backup part of this new privilege, or not?
The "integrated base backup", once we have that, that's based on the
walsender protocol? yes.
pg_dump style backups? No.Where does pg_start_backup()/stop fit?
Good question :)
Given that the integrated-base-backup would call it for you, that one
would definitely get it automatically.Given that the latest discissions seem to have most people wanting the
replication role *not* to be allowed to log in and run general SQL, we
should not drive the start/stop backup permissions from that
privilege.
So what your suggesting would actually defeat the purpose of having the
new privilege. Unless we trust in a new, untried method. Hmmm.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Dec 27, 2010 at 14:25, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-12-27 at 12:00 +0100, Magnus Hagander wrote:
On Mon, Dec 27, 2010 at 11:34, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-12-27 at 10:36 +0100, Magnus Hagander wrote:
Is backup part of this new privilege, or not?
The "integrated base backup", once we have that, that's based on the
walsender protocol? yes.
pg_dump style backups? No.Where does pg_start_backup()/stop fit?
Good question :)
Given that the integrated-base-backup would call it for you, that one
would definitely get it automatically.Given that the latest discissions seem to have most people wanting the
replication role *not* to be allowed to log in and run general SQL, we
should not drive the start/stop backup permissions from that
privilege.So what your suggesting would actually defeat the purpose of having the
new privilege. Unless we trust in a new, untried method. Hmmm.
No, it doesn't.
In my experience, most DBAs will connect with their DBA account
(usually the superuser, yes..) to run pg_start_backup() and
pg_stop_backup(). That's no reason to let the slave sever run with
superuser privileges all the time...
That said, I agree that the we shouldn't *prevent* the DBA from
setting up an account that is both superuser and replicator - just
that we shouldn't do it by default.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, 2010-12-27 at 14:41 +0100, Magnus Hagander wrote:
Where does pg_start_backup()/stop fit?
Good question :)
Given that the integrated-base-backup would call it for you, that one
would definitely get it automatically.Given that the latest discissions seem to have most people wanting the
replication role *not* to be allowed to log in and run general SQL, we
should not drive the start/stop backup permissions from that
privilege.So what your suggesting would actually defeat the purpose of having the
new privilege. Unless we trust in a new, untried method. Hmmm.No, it doesn't.
In my experience, most DBAs will connect with their DBA account
(usually the superuser, yes..) to run pg_start_backup() and
pg_stop_backup(). That's no reason to let the slave sever run with
superuser privileges all the time...
Remember the standby's superuser id is exactly the same as the main
server's superuserid. So unless you are going to stop the standby from
knowing its own superusers there's no huge benefit there. Is that what
you mean to do?
That said, I agree that the we shouldn't *prevent* the DBA from
setting up an account that is both superuser and replicator - just
that we shouldn't do it by default.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Dec 27, 2010 at 14:51, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-12-27 at 14:41 +0100, Magnus Hagander wrote:
Where does pg_start_backup()/stop fit?
Good question :)
Given that the integrated-base-backup would call it for you, that one
would definitely get it automatically.Given that the latest discissions seem to have most people wanting the
replication role *not* to be allowed to log in and run general SQL, we
should not drive the start/stop backup permissions from that
privilege.So what your suggesting would actually defeat the purpose of having the
new privilege. Unless we trust in a new, untried method. Hmmm.No, it doesn't.
In my experience, most DBAs will connect with their DBA account
(usually the superuser, yes..) to run pg_start_backup() and
pg_stop_backup(). That's no reason to let the slave sever run with
superuser privileges all the time...Remember the standby's superuser id is exactly the same as the main
server's superuserid. So unless you are going to stop the standby from
knowing its own superusers there's no huge benefit there. Is that what
you mean to do?
I'm sorry, I have no idea what you mean by that.
You will certainly be able to log into the standby with a superuser
account, nobody is preventing that. This is about protecting the
*master*. For example, from modifications made by a user who hacked
the standby.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Dec 27, 2010 at 10:53, Magnus Hagander <magnus@hagander.net> wrote:
We could quite easily make a replication role *never* be able to
connect to a non-walsender backend. That would mean that if you set
your role to WITH REPLICATION, it can *only* be used for replication
and nothing else (well, you could still SET ROLE to it, but given that
it's not a superuser (anymore), that doesn't have any security
implications.
Actually, having implemented that and tested it, I realize that's a
pretty bad idea.
OK, so if we're not going to recommend that REPLICATION roles be
NOLOGIN, we're back to the original question: should the REPLICATION
bit give any other special privileges? I can see the point of allowing
such a user to issue pg_start_backup and pg_stop_backup.
regards, tom lane
On Mon, Dec 27, 2010 at 16:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Dec 27, 2010 at 10:53, Magnus Hagander <magnus@hagander.net> wrote:
We could quite easily make a replication role *never* be able to
connect to a non-walsender backend. That would mean that if you set
your role to WITH REPLICATION, it can *only* be used for replication
and nothing else (well, you could still SET ROLE to it, but given that
it's not a superuser (anymore), that doesn't have any security
implications.Actually, having implemented that and tested it, I realize that's a
pretty bad idea.OK, so if we're not going to recommend that REPLICATION roles be
NOLOGIN, we're back to the original question: should the REPLICATION
bit give any other special privileges? I can see the point of allowing
such a user to issue pg_start_backup and pg_stop_backup.
Yes, those would definitely be useful.
We are, basically, talking about where we'll relax the "only
superuser" one, right? Since they are normal roles, the DBA can always
GRANT permissions on *objects* to them, but there are superuser-only
things taht you can't GRANT away...
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, 2010-12-27 at 14:54 +0100, Magnus Hagander wrote:
You will certainly be able to log into the standby with a superuser
account, nobody is preventing that. This is about protecting the
*master*. For example, from modifications made by a user who hacked
the standby.
The users for master and standby are identical, so if they have access
to the standby, they have access to the master. That's why we allow
replication to be specifically excluded by the pg_hba.conf.
So I don't see how this helps.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
On Mon, Dec 27, 2010 at 16:45, Simon Riggs <simon@2ndquadrant.com> wrote:
On Mon, 2010-12-27 at 14:54 +0100, Magnus Hagander wrote:
You will certainly be able to log into the standby with a superuser
account, nobody is preventing that. This is about protecting the
*master*. For example, from modifications made by a user who hacked
the standby.The users for master and standby are identical, so if they have access
to the standby, they have access to the master. That's why we allow
replication to be specifically excluded by the pg_hba.conf.
You are assuming there *is* a standby.
This is a defence against someone connecting with psql (or whatever)
directly to the master, *pretending to be* the standby (same
username/password, possibly even the same server ip).
Currently, this user gets the key to the kingdom and can modify things
freely on the master. With the patch, this user cannot. He can still
initiate streaming and eventually capture all your data, but he can't
modify it.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Dec 27, 2010 at 16:40, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Dec 27, 2010 at 16:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Dec 27, 2010 at 10:53, Magnus Hagander <magnus@hagander.net> wrote:
We could quite easily make a replication role *never* be able to
connect to a non-walsender backend. That would mean that if you set
your role to WITH REPLICATION, it can *only* be used for replication
and nothing else (well, you could still SET ROLE to it, but given that
it's not a superuser (anymore), that doesn't have any security
implications.Actually, having implemented that and tested it, I realize that's a
pretty bad idea.OK, so if we're not going to recommend that REPLICATION roles be
NOLOGIN, we're back to the original question: should the REPLICATION
bit give any other special privileges? I can see the point of allowing
such a user to issue pg_start_backup and pg_stop_backup.Yes, those would definitely be useful.
Updated patch, still pending docs, but otherwise updated: allow
start/stop backup, make sure only superuser can turn on/off the flag,
include in system views, show properly in psql.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attachments:
repl_role.patchtext/x-patch; charset=US-ASCII; name=repl_role.patchDownload
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 8301,8310 **** pg_start_backup(PG_FUNCTION_ARGS)
struct stat stat_buf;
FILE *fp;
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to run a backup")));
if (RecoveryInProgress())
ereport(ERROR,
--- 8301,8310 ----
struct stat stat_buf;
FILE *fp;
! if (!superuser() && !is_authenticated_user_replication_role())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser or replication role to run a backup")));
if (RecoveryInProgress())
ereport(ERROR,
***************
*** 8493,8502 **** pg_stop_backup(PG_FUNCTION_ARGS)
int waits = 0;
bool reported_waiting = false;
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser to run a backup"))));
if (RecoveryInProgress())
ereport(ERROR,
--- 8493,8502 ----
int waits = 0;
bool reported_waiting = false;
! if (!superuser() && !is_authenticated_user_replication_role())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or replication role to run a backup"))));
if (RecoveryInProgress())
ereport(ERROR,
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 15,20 **** CREATE VIEW pg_roles AS
--- 15,21 ----
rolcreatedb,
rolcatupdate,
rolcanlogin,
+ rolreplication,
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
***************
*** 30,35 **** CREATE VIEW pg_shadow AS
--- 31,37 ----
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
+ rolreplication AS userepl,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
setconfig AS useconfig
***************
*** 54,59 **** CREATE VIEW pg_user AS
--- 56,62 ----
usecreatedb,
usesuper,
usecatupd,
+ userepl,
'********'::text as passwd,
valuntil,
useconfig
*** a/src/backend/commands/user.c
--- b/src/backend/commands/user.c
***************
*** 94,99 **** CreateRole(CreateRoleStmt *stmt)
--- 94,100 ----
bool createrole = false; /* Can this user create roles? */
bool createdb = false; /* Can the user create databases? */
bool canlogin = false; /* Can this user login? */
+ bool isreplication = false; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *addroleto = NIL; /* roles to make this a member of */
List *rolemembers = NIL; /* roles to be members of this role */
***************
*** 107,112 **** CreateRole(CreateRoleStmt *stmt)
--- 108,114 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *daddroleto = NULL;
DefElem *drolemembers = NULL;
***************
*** 190,195 **** CreateRole(CreateRoleStmt *stmt)
--- 192,205 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 247,252 **** CreateRole(CreateRoleStmt *stmt)
--- 257,264 ----
createdb = intVal(dcreatedb->arg) != 0;
if (dcanlogin)
canlogin = intVal(dcanlogin->arg) != 0;
+ if (disreplication)
+ isreplication = intVal(disreplication->arg) != 0;
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 272,277 **** CreateRole(CreateRoleStmt *stmt)
--- 284,296 ----
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to create superusers")));
}
+ else if (isreplication)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create replication users")));
+ }
else
{
if (!have_createrole_privilege())
***************
*** 341,346 **** CreateRole(CreateRoleStmt *stmt)
--- 360,366 ----
/* superuser gets catupdate right by default */
new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper);
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
if (password)
***************
*** 439,444 **** AlterRole(AlterRoleStmt *stmt)
--- 459,465 ----
int createrole = -1; /* Can this user create roles? */
int createdb = -1; /* Can the user create databases? */
int canlogin = -1; /* Can this user login? */
+ int isreplication = -1; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *rolemembers = NIL; /* roles to be added/removed */
char *validUntil = NULL; /* time the login is valid until */
***************
*** 450,455 **** AlterRole(AlterRoleStmt *stmt)
--- 471,477 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
***************
*** 514,519 **** AlterRole(AlterRoleStmt *stmt)
--- 536,549 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 556,561 **** AlterRole(AlterRoleStmt *stmt)
--- 586,593 ----
createdb = intVal(dcreatedb->arg);
if (dcanlogin)
canlogin = intVal(dcanlogin->arg);
+ if (disreplication)
+ isreplication = intVal(disreplication->arg);
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 594,605 **** AlterRole(AlterRoleStmt *stmt)
--- 626,645 ----
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to alter superusers")));
}
+ else if (((Form_pg_authid) GETSTRUCT(tuple))->rolreplication || isreplication >= 0)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter replication users")));
+ }
else if (!have_createrole_privilege())
{
if (!(inherit < 0 &&
createrole < 0 &&
createdb < 0 &&
canlogin < 0 &&
+ isreplication < 0 &&
!dconnlimit &&
!rolemembers &&
!validUntil &&
***************
*** 685,690 **** AlterRole(AlterRoleStmt *stmt)
--- 725,736 ----
new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
}
+ if (isreplication >= 0)
+ {
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
+ new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
+ }
+
if (dconnlimit)
{
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 510,517 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
! NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
--- 510,518 ----
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOREPLICATION_P
! NOSUPERUSER NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
! NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
***************
*** 523,530 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
! RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
--- 524,532 ----
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA REPLICATION_P
! RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
! ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
***************
*** 864,869 **** AlterOptRoleElem:
--- 866,879 ----
{
$$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
}
+ | REPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(TRUE));
+ }
+ | NOREPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(FALSE));
+ }
| CONNECTION LIMIT SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($3));
***************
*** 11288,11293 **** unreserved_keyword:
--- 11298,11304 ----
| NOCREATEUSER
| NOINHERIT
| NOLOGIN_P
+ | NOREPLICATION_P
| NOSUPERUSER
| NOTHING
| NOTIFY
***************
*** 11330,11335 **** unreserved_keyword:
--- 11341,11347 ----
| REPEATABLE
| REPLACE
| REPLICA
+ | REPLICATION_P
| RESET
| RESTART
| RESTRICT
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
***************
*** 231,236 **** static int SecurityRestrictionContext = 0;
--- 231,240 ----
static bool SetRoleIsActive = false;
+ /* Remember if the authenticated user is a replication role */
+ static bool AuthenticatedUserIsReplicationRole = false;
+
+
/*
* GetUserId - get the current effective user ID.
*
***************
*** 389,394 **** SetUserIdAndContext(Oid userid, bool sec_def_context)
--- 393,407 ----
/*
+ * Check if the authenticated user is a replication role
+ */
+ bool
+ is_authenticated_user_replication_role(void)
+ {
+ return AuthenticatedUserIsReplicationRole;
+ }
+
+ /*
* Initialize user identity during normal backend startup
*/
void
***************
*** 418,423 **** InitializeSessionUserId(const char *rolename)
--- 431,437 ----
AuthenticatedUserId = roleid;
AuthenticatedUserIsSuperuser = rform->rolsuper;
+ AuthenticatedUserIsReplicationRole = rform->rolreplication;
/* This sets OuterUserId/CurrentUserId too */
SetSessionUserId(roleid, AuthenticatedUserIsSuperuser);
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
***************
*** 658,668 **** InitPostgres(const char *in_dbname, Oid dboid, const char *username,
{
Assert(!bootstrap);
! /* must have authenticated as a superuser */
! if (!am_superuser)
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
--- 658,668 ----
{
Assert(!bootstrap);
! /* must have authenticated as a replication role */
! if (!is_authenticated_user_replication_role())
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be replication role to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 2195,2200 **** describeRoles(const char *pattern, bool verbose)
--- 2195,2204 ----
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
+ if (pset.sversion >= 90100)
+ {
+ appendPQExpBufferStr(&buf,"\n, r.rolreplication");
+ }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
***************
*** 2254,2259 **** describeRoles(const char *pattern, bool verbose)
--- 2258,2267 ----
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
+ if (pset.sversion >= 90100)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
*** a/src/include/catalog/pg_authid.h
--- b/src/include/catalog/pg_authid.h
***************
*** 51,56 **** CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MAC
--- 51,57 ----
bool rolcreatedb; /* allowed to create databases? */
bool rolcatupdate; /* allowed to alter catalogs manually? */
bool rolcanlogin; /* allowed to log in as session user? */
+ bool rolreplication; /* role used for streaming replication */
int4 rolconnlimit; /* max connections allowed (-1=no limit) */
/* remaining fields may be null; use heap_getattr to read them! */
***************
*** 72,78 **** typedef FormData_pg_authid *Form_pg_authid;
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 10
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
--- 73,79 ----
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 11
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
***************
*** 80,88 **** typedef FormData_pg_authid *Form_pg_authid;
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolconnlimit 8
! #define Anum_pg_authid_rolpassword 9
! #define Anum_pg_authid_rolvaliduntil 10
/* ----------------
* initial contents of pg_authid
--- 81,90 ----
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolreplication 8
! #define Anum_pg_authid_rolconnlimit 9
! #define Anum_pg_authid_rolpassword 10
! #define Anum_pg_authid_rolvaliduntil 11
/* ----------------
* initial contents of pg_authid
***************
*** 91,97 **** typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 93,99 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t f -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 357,362 **** extern void ValidatePgVersion(const char *path);
--- 357,363 ----
extern void process_shared_preload_libraries(void);
extern void process_local_preload_libraries(void);
extern void pg_bindtextdomain(const char *domain);
+ extern bool is_authenticated_user_replication_role(void);
/* in access/transam/xlog.c */
extern bool BackupInProgress(void);
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 250,255 **** PG_KEYWORD("nocreateuser", NOCREATEUSER, UNRESERVED_KEYWORD)
--- 250,256 ----
PG_KEYWORD("noinherit", NOINHERIT, UNRESERVED_KEYWORD)
PG_KEYWORD("nologin", NOLOGIN_P, UNRESERVED_KEYWORD)
PG_KEYWORD("none", NONE, COL_NAME_KEYWORD)
+ PG_KEYWORD("noreplication", NOREPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("nosuperuser", NOSUPERUSER, UNRESERVED_KEYWORD)
PG_KEYWORD("not", NOT, RESERVED_KEYWORD)
PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD)
***************
*** 313,318 **** PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD)
--- 314,320 ----
PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
+ PG_KEYWORD("replication", REPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
Magnus Hagander <magnus@hagander.net> writes:
Updated patch, still pending docs, but otherwise updated: allow
start/stop backup, make sure only superuser can turn on/off the flag,
include in system views, show properly in psql.
I'd suggest avoiding creating the static cache variable
AuthenticatedUserIsReplicationRole. This can't possibly be sufficiently
interesting from a performance point of view to justify the risks
associated with stale cache values. Just look up the pg_authid syscache
entry when needed, ie, treat it more like rolcreaterole than rolsuper.
BTW, you forgot pg_dumpall support.
regards, tom lane
On Mon, Dec 27, 2010 at 22:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Updated patch, still pending docs, but otherwise updated: allow
start/stop backup, make sure only superuser can turn on/off the flag,
include in system views, show properly in psql.I'd suggest avoiding creating the static cache variable
AuthenticatedUserIsReplicationRole. This can't possibly be sufficiently
interesting from a performance point of view to justify the risks
associated with stale cache values. Just look up the pg_authid syscache
entry when needed, ie, treat it more like rolcreaterole than rolsuper.
Sure, I catually had it that way first. But doing it this way was less
code. But I realize I should've revisited that decision when I made
the change to pg_start_backup and pg_stop_backup - before that the
checks would only happen during a very short window of time at the
start of the connection, but now it can happen later..
BTW, you forgot pg_dumpall support.
Gah. I knew that, but somehow dropped it from my TODO. Thanks for the reminder!
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Dec 27, 2010 at 22:53, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Dec 27, 2010 at 22:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Updated patch, still pending docs, but otherwise updated: allow
start/stop backup, make sure only superuser can turn on/off the flag,
include in system views, show properly in psql.I'd suggest avoiding creating the static cache variable
AuthenticatedUserIsReplicationRole. This can't possibly be sufficiently
interesting from a performance point of view to justify the risks
associated with stale cache values. Just look up the pg_authid syscache
entry when needed, ie, treat it more like rolcreaterole than rolsuper.Sure, I catually had it that way first. But doing it this way was less
code. But I realize I should've revisited that decision when I made
the change to pg_start_backup and pg_stop_backup - before that the
checks would only happen during a very short window of time at the
start of the connection, but now it can happen later..BTW, you forgot pg_dumpall support.
Gah. I knew that, but somehow dropped it from my TODO. Thanks for the reminder!
Ok, here's an updated patch that does both these and includes
documentation and regression test changes. With that, I think we're
good to go.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Attachments:
repl_role.patchtext/x-patch; charset=US-ASCII; name=repl_role.patchDownload
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 1236,1241 ****
--- 1236,1252 ----
</row>
<row>
+ <entry><structfield>rolreplication</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry>
+ Role is a replication role. That is, this role can initiate streaming
+ replication (see <xref linkend="streaming-replication">) and set/unset
+ the system backup mode using <function>pg_start_backup</> and
+ <function>pg_stop_backup</>.
+ </entry>
+ </row>
+
+ <row>
<entry><structfield>rolconnlimit</structfield></entry>
<entry><type>int4</type></entry>
<entry>
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13969,13982 **** SELECT set_config('log_statement_stats', 'off', false);
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
</entry>
<entry><type>text</type></entry>
! <entry>Prepare for performing on-line backup (restricted to superusers)</entry>
</row>
<row>
<entry>
<literal><function>pg_stop_backup()</function></literal>
</entry>
<entry><type>text</type></entry>
! <entry>Finish performing on-line backup (restricted to superusers)</entry>
</row>
<row>
<entry>
--- 13969,13982 ----
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
</entry>
<entry><type>text</type></entry>
! <entry>Prepare for performing on-line backup (restricted to superusers or replication roles)</entry>
</row>
<row>
<entry>
<literal><function>pg_stop_backup()</function></literal>
</entry>
<entry><type>text</type></entry>
! <entry>Finish performing on-line backup (restricted to superusers or replication roles)</entry>
</row>
<row>
<entry>
*** a/doc/src/sgml/high-availability.sgml
--- b/doc/src/sgml/high-availability.sgml
***************
*** 636,643 **** protocol to make nodes agree on a serializable transactional order.
<para>
If you want to use streaming replication, set up authentication on the
primary server to allow replication connections from the standby
! server(s); that is, provide a suitable entry or entries in
! <filename>pg_hba.conf</> with the database field set to
<literal>replication</>. Also ensure <varname>max_wal_senders</> is set
to a sufficiently large value in the configuration file of the primary
server.
--- 636,643 ----
<para>
If you want to use streaming replication, set up authentication on the
primary server to allow replication connections from the standby
! server(s); that is, create a role and provide a suitable entry or
! entries in <filename>pg_hba.conf</> with the database field set to
<literal>replication</>. Also ensure <varname>max_wal_senders</> is set
to a sufficiently large value in the configuration file of the primary
server.
***************
*** 796,810 **** archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
It is very important that the access privileges for replication be set up
so that only trusted users can read the WAL stream, because it is
easy to extract privileged information from it. Standby servers must
! authenticate to the primary as a superuser account.
! So a role with the <literal>SUPERUSER</> and <literal>LOGIN</>
! privileges needs to be created on the primary.
</para>
<para>
Client authentication for replication is controlled by a
<filename>pg_hba.conf</> record specifying <literal>replication</> in the
<replaceable>database</> field. For example, if the standby is running on
! host IP <literal>192.168.1.100</> and the superuser's name for replication
is <literal>foo</>, the administrator can add the following line to the
<filename>pg_hba.conf</> file on the primary:
--- 796,823 ----
It is very important that the access privileges for replication be set up
so that only trusted users can read the WAL stream, because it is
easy to extract privileged information from it. Standby servers must
! authenticate to the primary as an account that has the
! <literal>REPLICATION</> privilege. So a role with the
! <literal>REPLICATION</> and <literal>LOGIN</> privileges needs to be
! created on the primary.
</para>
+
+ <note>
+ <para>
+ It is recommended that a dedicated user account is used for replication.
+ While it is possible to add the <literal>REPLICATION</> privilege to
+ a superuser account for the purporses of replication, this is not
+ recommended. While <literal>REPLICATION</> privilege gives very high
+ permissions, it does not allow the user to modify any data on the
+ primary system, which the <literal>SUPERUSER</> privilege does.
+ </para>
+ </note>
+
<para>
Client authentication for replication is controlled by a
<filename>pg_hba.conf</> record specifying <literal>replication</> in the
<replaceable>database</> field. For example, if the standby is running on
! host IP <literal>192.168.1.100</> and the account name for replication
is <literal>foo</>, the administrator can add the following line to the
<filename>pg_hba.conf</> file on the primary:
***************
*** 823,829 **** host replication foo 192.168.1.100/32 md5
standby (specify <literal>replication</> in the <replaceable>database</>
field).
For example, if the primary is running on host IP <literal>192.168.1.50</>,
! port <literal>5432</literal>, the superuser's name for replication is
<literal>foo</>, and the password is <literal>foopass</>, the administrator
can add the following line to the <filename>recovery.conf</> file on the
standby:
--- 836,842 ----
standby (specify <literal>replication</> in the <replaceable>database</>
field).
For example, if the primary is running on host IP <literal>192.168.1.50</>,
! port <literal>5432</literal>, the account name for replication is
<literal>foo</>, and the password is <literal>foopass</>, the administrator
can add the following line to the <filename>recovery.conf</> file on the
standby:
*** a/doc/src/sgml/ref/alter_role.sgml
--- b/doc/src/sgml/ref/alter_role.sgml
***************
*** 31,36 **** ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
--- 31,37 ----
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
+ | REPLICATION | NOREPLICATION
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
***************
*** 63,69 **** ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
Attributes not mentioned in the command retain their previous settings.
Database superusers can change any of these settings for any role.
Roles having <literal>CREATEROLE</> privilege can change any of these
! settings, but only for non-superuser roles.
Ordinary roles can only change their own password.
</para>
--- 64,70 ----
Attributes not mentioned in the command retain their previous settings.
Database superusers can change any of these settings for any role.
Roles having <literal>CREATEROLE</> privilege can change any of these
! settings, but only for non-superuser and non-replication roles.
Ordinary roles can only change their own password.
</para>
***************
*** 127,132 **** ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <repl
--- 128,135 ----
<term><literal>NOINHERIT</literal></term>
<term><literal>LOGIN</literal></term>
<term><literal>NOLOGIN</literal></term>
+ <term><literal>REPLICATION</literal></term>
+ <term><literal>NOREPLICATION</literal></term>
<term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
<term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term>
<term><literal>ENCRYPTED</></term>
*** a/doc/src/sgml/ref/alter_user.sgml
--- b/doc/src/sgml/ref/alter_user.sgml
***************
*** 31,36 **** ALTER USER <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replace
--- 31,37 ----
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
+ | REPLICATION | NOREPLICATION
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
*** a/doc/src/sgml/ref/create_role.sgml
--- b/doc/src/sgml/ref/create_role.sgml
***************
*** 31,36 **** CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
--- 31,37 ----
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
+ | REPLICATION | NOREPLICATION
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
***************
*** 175,180 **** CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
--- 176,196 ----
</varlistentry>
<varlistentry>
+ <term><literal>REPLICATION</literal></term>
+ <term><literal>NOREPLICATION</literal></term>
+ <listitem>
+ <para>
+ These clauses determine whether a role is allowed to initiate
+ streaming replication or put the system in and out of backup mode.
+ A role having the <literal>REPLICATION</> attribute is a very
+ highly privileged role, and should only be used on roles actually
+ used for replication. If not specified,
+ <literal>NOREPLICATION</literal> is the default.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
<listitem>
<para>
*** a/doc/src/sgml/ref/create_user.sgml
--- b/doc/src/sgml/ref/create_user.sgml
***************
*** 31,36 **** CREATE USER <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
--- 31,37 ----
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
+ | REPLICATION | NOREPLICATION
| CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
| VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 8301,8310 **** pg_start_backup(PG_FUNCTION_ARGS)
struct stat stat_buf;
FILE *fp;
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to run a backup")));
if (RecoveryInProgress())
ereport(ERROR,
--- 8301,8310 ----
struct stat stat_buf;
FILE *fp;
! if (!superuser() && !is_authenticated_user_replication_role())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser or replication role to run a backup")));
if (RecoveryInProgress())
ereport(ERROR,
***************
*** 8493,8502 **** pg_stop_backup(PG_FUNCTION_ARGS)
int waits = 0;
bool reported_waiting = false;
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser to run a backup"))));
if (RecoveryInProgress())
ereport(ERROR,
--- 8493,8502 ----
int waits = 0;
bool reported_waiting = false;
! if (!superuser() && !is_authenticated_user_replication_role())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or replication role to run a backup"))));
if (RecoveryInProgress())
ereport(ERROR,
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 15,20 **** CREATE VIEW pg_roles AS
--- 15,21 ----
rolcreatedb,
rolcatupdate,
rolcanlogin,
+ rolreplication,
rolconnlimit,
'********'::text as rolpassword,
rolvaliduntil,
***************
*** 30,35 **** CREATE VIEW pg_shadow AS
--- 31,37 ----
rolcreatedb AS usecreatedb,
rolsuper AS usesuper,
rolcatupdate AS usecatupd,
+ rolreplication AS userepl,
rolpassword AS passwd,
rolvaliduntil::abstime AS valuntil,
setconfig AS useconfig
***************
*** 54,59 **** CREATE VIEW pg_user AS
--- 56,62 ----
usecreatedb,
usesuper,
usecatupd,
+ userepl,
'********'::text as passwd,
valuntil,
useconfig
*** a/src/backend/commands/user.c
--- b/src/backend/commands/user.c
***************
*** 94,99 **** CreateRole(CreateRoleStmt *stmt)
--- 94,100 ----
bool createrole = false; /* Can this user create roles? */
bool createdb = false; /* Can the user create databases? */
bool canlogin = false; /* Can this user login? */
+ bool isreplication = false; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *addroleto = NIL; /* roles to make this a member of */
List *rolemembers = NIL; /* roles to be members of this role */
***************
*** 107,112 **** CreateRole(CreateRoleStmt *stmt)
--- 108,114 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *daddroleto = NULL;
DefElem *drolemembers = NULL;
***************
*** 190,195 **** CreateRole(CreateRoleStmt *stmt)
--- 192,205 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 247,252 **** CreateRole(CreateRoleStmt *stmt)
--- 257,264 ----
createdb = intVal(dcreatedb->arg) != 0;
if (dcanlogin)
canlogin = intVal(dcanlogin->arg) != 0;
+ if (disreplication)
+ isreplication = intVal(disreplication->arg) != 0;
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 272,277 **** CreateRole(CreateRoleStmt *stmt)
--- 284,296 ----
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to create superusers")));
}
+ else if (isreplication)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create replication users")));
+ }
else
{
if (!have_createrole_privilege())
***************
*** 341,346 **** CreateRole(CreateRoleStmt *stmt)
--- 360,366 ----
/* superuser gets catupdate right by default */
new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper);
new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
if (password)
***************
*** 439,444 **** AlterRole(AlterRoleStmt *stmt)
--- 459,465 ----
int createrole = -1; /* Can this user create roles? */
int createdb = -1; /* Can the user create databases? */
int canlogin = -1; /* Can this user login? */
+ int isreplication = -1; /* Is this a replication role? */
int connlimit = -1; /* maximum connections allowed */
List *rolemembers = NIL; /* roles to be added/removed */
char *validUntil = NULL; /* time the login is valid until */
***************
*** 450,455 **** AlterRole(AlterRoleStmt *stmt)
--- 471,477 ----
DefElem *dcreaterole = NULL;
DefElem *dcreatedb = NULL;
DefElem *dcanlogin = NULL;
+ DefElem *disreplication = NULL;
DefElem *dconnlimit = NULL;
DefElem *drolemembers = NULL;
DefElem *dvalidUntil = NULL;
***************
*** 514,519 **** AlterRole(AlterRoleStmt *stmt)
--- 536,549 ----
errmsg("conflicting or redundant options")));
dcanlogin = defel;
}
+ else if (strcmp(defel->defname, "isreplication") == 0)
+ {
+ if (disreplication)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ disreplication = defel;
+ }
else if (strcmp(defel->defname, "connectionlimit") == 0)
{
if (dconnlimit)
***************
*** 556,561 **** AlterRole(AlterRoleStmt *stmt)
--- 586,593 ----
createdb = intVal(dcreatedb->arg);
if (dcanlogin)
canlogin = intVal(dcanlogin->arg);
+ if (disreplication)
+ isreplication = intVal(disreplication->arg);
if (dconnlimit)
{
connlimit = intVal(dconnlimit->arg);
***************
*** 594,605 **** AlterRole(AlterRoleStmt *stmt)
--- 626,645 ----
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to alter superusers")));
}
+ else if (((Form_pg_authid) GETSTRUCT(tuple))->rolreplication || isreplication >= 0)
+ {
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to alter replication users")));
+ }
else if (!have_createrole_privilege())
{
if (!(inherit < 0 &&
createrole < 0 &&
createdb < 0 &&
canlogin < 0 &&
+ isreplication < 0 &&
!dconnlimit &&
!rolemembers &&
!validUntil &&
***************
*** 685,690 **** AlterRole(AlterRoleStmt *stmt)
--- 725,736 ----
new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
}
+ if (isreplication >= 0)
+ {
+ new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
+ new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
+ }
+
if (dconnlimit)
{
new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 510,517 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER
! NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
--- 510,518 ----
MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
! NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOREPLICATION_P
! NOSUPERUSER NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
! NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
***************
*** 523,530 **** static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
! RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
--- 524,532 ----
QUOTE
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REINDEX
! RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA REPLICATION_P
! RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
! ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
***************
*** 864,869 **** AlterOptRoleElem:
--- 866,879 ----
{
$$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
}
+ | REPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(TRUE));
+ }
+ | NOREPLICATION_P
+ {
+ $$ = makeDefElem("isreplication", (Node *)makeInteger(FALSE));
+ }
| CONNECTION LIMIT SignedIconst
{
$$ = makeDefElem("connectionlimit", (Node *)makeInteger($3));
***************
*** 11288,11293 **** unreserved_keyword:
--- 11298,11304 ----
| NOCREATEUSER
| NOINHERIT
| NOLOGIN_P
+ | NOREPLICATION_P
| NOSUPERUSER
| NOTHING
| NOTIFY
***************
*** 11330,11335 **** unreserved_keyword:
--- 11341,11347 ----
| REPEATABLE
| REPLACE
| REPLICA
+ | REPLICATION_P
| RESET
| RESTART
| RESTRICT
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
***************
*** 231,236 **** static int SecurityRestrictionContext = 0;
--- 231,237 ----
static bool SetRoleIsActive = false;
+
/*
* GetUserId - get the current effective user ID.
*
***************
*** 389,394 **** SetUserIdAndContext(Oid userid, bool sec_def_context)
--- 390,413 ----
/*
+ * Check if the authenticated user is a replication role
+ */
+ bool
+ is_authenticated_user_replication_role(void)
+ {
+ bool result = false;
+ HeapTuple utup;
+
+ utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(AuthenticatedUserId));
+ if (HeapTupleIsValid(utup))
+ {
+ result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication;
+ ReleaseSysCache(utup);
+ }
+ return result;
+ }
+
+ /*
* Initialize user identity during normal backend startup
*/
void
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
***************
*** 658,668 **** InitPostgres(const char *in_dbname, Oid dboid, const char *username,
{
Assert(!bootstrap);
! /* must have authenticated as a superuser */
! if (!am_superuser)
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
--- 658,668 ----
{
Assert(!bootstrap);
! /* must have authenticated as a replication role */
! if (!is_authenticated_user_replication_role())
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be replication role to start walsender")));
/* process any options passed in the startup packet */
if (MyProcPort != NULL)
*** a/src/bin/pg_dump/pg_dumpall.c
--- b/src/bin/pg_dump/pg_dumpall.c
***************
*** 653,668 **** dumpRoles(PGconn *conn)
i_rolconnlimit,
i_rolpassword,
i_rolvaliduntil,
i_rolcomment;
int i;
/* note: rolconfig is dumped later */
! if (server_version >= 80200)
printfPQExpBuffer(buf,
"SELECT rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, rolcatupdate, "
"rolcanlogin, rolconnlimit, rolpassword, "
! "rolvaliduntil, "
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
"FROM pg_authid "
"ORDER BY 1");
--- 653,678 ----
i_rolconnlimit,
i_rolpassword,
i_rolvaliduntil,
+ i_rolreplication,
i_rolcomment;
int i;
/* note: rolconfig is dumped later */
! if (server_version >= 90100)
printfPQExpBuffer(buf,
"SELECT rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, rolcatupdate, "
"rolcanlogin, rolconnlimit, rolpassword, "
! "rolvaliduntil, rolreplication, "
! "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
! "FROM pg_authid "
! "ORDER BY 1");
! else if (server_version >= 80200)
! printfPQExpBuffer(buf,
! "SELECT rolname, rolsuper, rolinherit, "
! "rolcreaterole, rolcreatedb, rolcatupdate, "
! "rolcanlogin, rolconnlimit, rolpassword, "
! "rolvaliduntil, false as rolreplication, "
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
"FROM pg_authid "
"ORDER BY 1");
***************
*** 671,677 **** dumpRoles(PGconn *conn)
"SELECT rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, rolcatupdate, "
"rolcanlogin, rolconnlimit, rolpassword, "
! "rolvaliduntil, null as rolcomment "
"FROM pg_authid "
"ORDER BY 1");
else
--- 681,688 ----
"SELECT rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, rolcatupdate, "
"rolcanlogin, rolconnlimit, rolpassword, "
! "rolvaliduntil, false as rolreplication, "
! "null as rolcomment "
"FROM pg_authid "
"ORDER BY 1");
else
***************
*** 686,691 **** dumpRoles(PGconn *conn)
--- 697,703 ----
"-1 as rolconnlimit, "
"passwd as rolpassword, "
"valuntil as rolvaliduntil, "
+ "false as rolreplication, "
"null as rolcomment "
"FROM pg_shadow "
"UNION ALL "
***************
*** 699,704 **** dumpRoles(PGconn *conn)
--- 711,717 ----
"-1 as rolconnlimit, "
"null::text as rolpassword, "
"null::abstime as rolvaliduntil, "
+ "false as rolreplication, "
"null as rolcomment "
"FROM pg_group "
"WHERE NOT EXISTS (SELECT 1 FROM pg_shadow "
***************
*** 717,722 **** dumpRoles(PGconn *conn)
--- 730,736 ----
i_rolconnlimit = PQfnumber(res, "rolconnlimit");
i_rolpassword = PQfnumber(res, "rolpassword");
i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
+ i_rolreplication = PQfnumber(res, "rolreplication");
i_rolcomment = PQfnumber(res, "rolcomment");
if (PQntuples(res) > 0)
***************
*** 765,770 **** dumpRoles(PGconn *conn)
--- 779,789 ----
else
appendPQExpBuffer(buf, " NOLOGIN");
+ if (strcmp(PQgetvalue(res, i, i_rolreplication), "t") == 0)
+ appendPQExpBuffer(buf, " REPLICATION");
+ else
+ appendPQExpBuffer(buf, " NOREPLICATION");
+
if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0)
appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
PQgetvalue(res, i, i_rolconnlimit));
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 2195,2200 **** describeRoles(const char *pattern, bool verbose)
--- 2195,2204 ----
appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
ncols++;
}
+ if (pset.sversion >= 90100)
+ {
+ appendPQExpBufferStr(&buf,"\n, r.rolreplication");
+ }
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
***************
*** 2254,2259 **** describeRoles(const char *pattern, bool verbose)
--- 2258,2267 ----
if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
add_role_attribute(&buf, _("Cannot login"));
+ if (pset.sversion >= 90100)
+ if (strcmp(PQgetvalue(res, i, 8), "t") == 0)
+ add_role_attribute(&buf, _("Replication"));
+
conns = atoi(PQgetvalue(res, i, 6));
if (conns >= 0)
{
*** a/src/include/catalog/pg_authid.h
--- b/src/include/catalog/pg_authid.h
***************
*** 51,56 **** CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MAC
--- 51,57 ----
bool rolcreatedb; /* allowed to create databases? */
bool rolcatupdate; /* allowed to alter catalogs manually? */
bool rolcanlogin; /* allowed to log in as session user? */
+ bool rolreplication; /* role used for streaming replication */
int4 rolconnlimit; /* max connections allowed (-1=no limit) */
/* remaining fields may be null; use heap_getattr to read them! */
***************
*** 72,78 **** typedef FormData_pg_authid *Form_pg_authid;
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 10
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
--- 73,79 ----
* compiler constants for pg_authid
* ----------------
*/
! #define Natts_pg_authid 11
#define Anum_pg_authid_rolname 1
#define Anum_pg_authid_rolsuper 2
#define Anum_pg_authid_rolinherit 3
***************
*** 80,88 **** typedef FormData_pg_authid *Form_pg_authid;
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolconnlimit 8
! #define Anum_pg_authid_rolpassword 9
! #define Anum_pg_authid_rolvaliduntil 10
/* ----------------
* initial contents of pg_authid
--- 81,90 ----
#define Anum_pg_authid_rolcreatedb 5
#define Anum_pg_authid_rolcatupdate 6
#define Anum_pg_authid_rolcanlogin 7
! #define Anum_pg_authid_rolreplication 8
! #define Anum_pg_authid_rolconnlimit 9
! #define Anum_pg_authid_rolpassword 10
! #define Anum_pg_authid_rolvaliduntil 11
/* ----------------
* initial contents of pg_authid
***************
*** 91,97 **** typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
--- 93,99 ----
* user choices.
* ----------------
*/
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t f -1 _null_ _null_ ));
#define BOOTSTRAP_SUPERUSERID 10
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 357,362 **** extern void ValidatePgVersion(const char *path);
--- 357,363 ----
extern void process_shared_preload_libraries(void);
extern void process_local_preload_libraries(void);
extern void pg_bindtextdomain(const char *domain);
+ extern bool is_authenticated_user_replication_role(void);
/* in access/transam/xlog.c */
extern bool BackupInProgress(void);
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 250,255 **** PG_KEYWORD("nocreateuser", NOCREATEUSER, UNRESERVED_KEYWORD)
--- 250,256 ----
PG_KEYWORD("noinherit", NOINHERIT, UNRESERVED_KEYWORD)
PG_KEYWORD("nologin", NOLOGIN_P, UNRESERVED_KEYWORD)
PG_KEYWORD("none", NONE, COL_NAME_KEYWORD)
+ PG_KEYWORD("noreplication", NOREPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("nosuperuser", NOSUPERUSER, UNRESERVED_KEYWORD)
PG_KEYWORD("not", NOT, RESERVED_KEYWORD)
PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD)
***************
*** 313,318 **** PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD)
--- 314,320 ----
PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
+ PG_KEYWORD("replication", REPLICATION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1285,1295 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels | (((((SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::text WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text ELSE NULL::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid = 0) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'column'::text AS objtype, rel.relnamespace AS objnamespace, ((CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END || '.'::text) || (att.attname)::text) AS objname, l.provider, l.label FROM (((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid <> 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (pro.proisagg = true) THEN 'aggregate'::text WHEN (pro.proisagg = false) THEN 'function'::text ELSE NULL::text END AS objtype, pro.pronamespace AS objnamespace, (((CASE WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text ELSE 'type'::text END AS objtype, typ.typnamespace AS objnamespace, CASE WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (l.objoid)::text AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) WHERE (l.objsubid = 0);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
--- 1285,1295 ----
pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted);
pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
! pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolreplication, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels | (((((SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::text WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text ELSE NULL::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid = 0) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'column'::text AS objtype, rel.relnamespace AS objnamespace, ((CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END || '.'::text) || (att.attname)::text) AS objname, l.provider, l.label FROM (((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid <> 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (pro.proisagg = true) THEN 'aggregate'::text WHEN (pro.proisagg = false) THEN 'function'::text ELSE NULL::text END AS objtype, pro.pronamespace AS objnamespace, (((CASE WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text ELSE 'type'::text END AS objtype, typ.typnamespace AS objnamespace, CASE WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (l.objoid)::text AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) WHERE (l.objsubid = 0);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
! pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
***************
*** 1317,1323 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
! pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;
pg_user_mappings | SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, CASE WHEN (u.umuser = (0)::oid) THEN 'public'::name ELSE a.rolname END AS usename, CASE WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions ELSE NULL::text[] END AS umoptions FROM ((pg_user_mapping u LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");
rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;
--- 1317,1323 ----
pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
! pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, pg_shadow.userepl, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;
pg_user_mappings | SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, CASE WHEN (u.umuser = (0)::oid) THEN 'public'::name ELSE a.rolname END AS usename, CASE WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions ELSE NULL::text[] END AS umoptions FROM ((pg_user_mapping u LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");
rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;
On Tue, Dec 28, 2010 at 13:05, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Dec 27, 2010 at 22:53, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Dec 27, 2010 at 22:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
Updated patch, still pending docs, but otherwise updated: allow
start/stop backup, make sure only superuser can turn on/off the flag,
include in system views, show properly in psql.I'd suggest avoiding creating the static cache variable
AuthenticatedUserIsReplicationRole. This can't possibly be sufficiently
interesting from a performance point of view to justify the risks
associated with stale cache values. Just look up the pg_authid syscache
entry when needed, ie, treat it more like rolcreaterole than rolsuper.Sure, I catually had it that way first. But doing it this way was less
code. But I realize I should've revisited that decision when I made
the change to pg_start_backup and pg_stop_backup - before that the
checks would only happen during a very short window of time at the
start of the connection, but now it can happen later..BTW, you forgot pg_dumpall support.
Gah. I knew that, but somehow dropped it from my TODO. Thanks for the reminder!
Ok, here's an updated patch that does both these and includes
documentation and regression test changes. With that, I think we're
good to go.
I've applied this version (with some minor typo-fixes).
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Wed, Dec 29, 2010 at 5:09 AM, Magnus Hagander <magnus@hagander.net>wrote:
Ok, here's an updated patch that does both these and includes
documentation and regression test changes. With that, I think we're
good to go.I've applied this version (with some minor typo-fixes).
Do you think we could have worded these a bit better
<entry>Prepare for performing on-line backup (restricted to superusers or
replication roles)</entry>
to say 'restricted to superusers _and_ replication roles'.
Saying 'restricted to superusers _or_ replication roles' may mean that at
any time we allow one or the other, but not both (reader might assume that
decision is based on some other GUC).
Using 'and' would mean that we allow it for both of those roles.
Any specific reason NOREPLICATION_P and REPLICATION_P use the _P suffix?
AIUI, that suffix is used in gram.y to tag a token to mean it belongs to
Parser, and to avoid conflict with the same token elsewhere; NULL_P is a
good example.
In pg_authid.h, 8 spaces used between 'bool' and 'rolreplication', instead
tabs should have been used as the surrounding code.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
On Wed, Dec 29, 2010 at 15:05, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
On Wed, Dec 29, 2010 at 5:09 AM, Magnus Hagander <magnus@hagander.net>
wrote:Ok, here's an updated patch that does both these and includes
documentation and regression test changes. With that, I think we're
good to go.I've applied this version (with some minor typo-fixes).
Do you think we could have worded these a bit better
<entry>Prepare for performing on-line backup (restricted to superusers or
replication roles)</entry>to say 'restricted to superusers _and_ replication roles'.
Saying 'restricted to superusers _or_ replication roles' may mean that at
any time we allow one or the other, but not both (reader might assume that
decision is based on some other GUC).
Uh, not sure, actually. I would read the "and" as meaning you needed
*both*, which isn't true. We do allow, at any time, one or the other -
*or* both.
Any specific reason NOREPLICATION_P and REPLICATION_P use the _P suffix?
Um, I just copied it off a similar entry elsewhere. I saw no comment
about what _P actually means, and I can't say I know. I know very
little about the bison files :-)
AIUI, that suffix is used in gram.y to tag a token to mean it belongs to
Parser, and to avoid conflict with the same token elsewhere; NULL_P is a
good example.In pg_authid.h, 8 spaces used between 'bool' and 'rolreplication', instead
tabs should have been used as the surrounding code.
Bleh. Well, pgindent will fix that.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Excerpts from Magnus Hagander's message of mié dic 29 11:40:34 -0300 2010:
On Wed, Dec 29, 2010 at 15:05, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
Any specific reason NOREPLICATION_P and REPLICATION_P use the _P suffix?
Um, I just copied it off a similar entry elsewhere. I saw no comment
about what _P actually means, and I can't say I know. I know very
little about the bison files :-)
Some lexer keywords have a _P prefix because otherwise they'd collide
with some symbol in Windows header files or something like that. It's
old stuff, but I think you, Magnus, were around at that time.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, Dec 29, 2010 at 20:12, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Excerpts from Magnus Hagander's message of mié dic 29 11:40:34 -0300 2010:
On Wed, Dec 29, 2010 at 15:05, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
Any specific reason NOREPLICATION_P and REPLICATION_P use the _P suffix?
Um, I just copied it off a similar entry elsewhere. I saw no comment
about what _P actually means, and I can't say I know. I know very
little about the bison files :-)Some lexer keywords have a _P prefix because otherwise they'd collide
with some symbol in Windows header files or something like that. It's
old stuff, but I think you, Magnus, were around at that time.
Heh. That doesn't mean I *remember* it :-)
But yes, I see in commit 12c942383296bd626131241c012c2ab81b081738 the
comment "convert some keywords.c symbols to KEYWORD_P to prevent
conflict".
Based on that, I should probably change it back, right? I just tried a
patch for it and it compiles and checks just fine with the _P parts
removed.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Excerpts from Magnus Hagander's message of jue dic 30 08:57:09 -0300 2010:
On Wed, Dec 29, 2010 at 20:12, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
Some lexer keywords have a _P prefix because otherwise they'd collide
with some symbol in Windows header files or something like that. It's
old stuff, but I think you, Magnus, were around at that time.Heh. That doesn't mean I *remember* it :-)
:-)
But yes, I see in commit 12c942383296bd626131241c012c2ab81b081738 the
comment "convert some keywords.c symbols to KEYWORD_P to prevent
conflict".
Wow, what a mess of a patch ... nowadays this would be like 10 commits
(or so I hope) ... hey, did Bruce sabotage the qnx4 port surreptitiously?
Based on that, I should probably change it back, right? I just tried a
patch for it and it compiles and checks just fine with the _P parts
removed.
Hmm, I wouldn't bother really. It's not that important anyway, IMHO.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On tor, 2010-12-23 at 17:29 -0500, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:21 PM, Tom Lane wrote:
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.Well, the reverse of that is just as true: if we ship it without
replication permissions on the postgres user, people can change that if
they'd rather not create a separate role for replication. But I think
we should encourage people to NOT do it that way. Setting it up that
way by default hardly encourages use of a more secure arrangement.
I think this argument is a bit inconsistent in the extreme. You might
as well argue that a superuser shouldn't have any permissions by
default, to discourage users from using it. They can always grant
permissions back to it. I don't see why this particular one is so
different.
If we go down this road, we'll end up with a mess of permissions that a
superuser has and doesn't have.
On ons, 2010-12-29 at 11:09 +0100, Magnus Hagander wrote:
I've applied this version (with some minor typo-fixes).
This page is now somewhat invalidated:
http://developer.postgresql.org/pgdocs/postgres/role-attributes.html
First, it doesn't mention the replication privilege, and second it
continues to claim that superuser status bypasses all permission checks.
On Thu, Dec 30, 2010 at 9:54 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
On tor, 2010-12-23 at 17:29 -0500, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
On 12/23/10 2:21 PM, Tom Lane wrote:
Well, that's one laudable goal here, but "secure by default" is another
one that ought to be taken into consideration.I don't see how *not* granting the superuser replication permissions
makes things more secure. The superuser can grant replication
permissions to itself, so why is suspending them by default beneficial?
I'm not following your logic here.Well, the reverse of that is just as true: if we ship it without
replication permissions on the postgres user, people can change that if
they'd rather not create a separate role for replication. But I think
we should encourage people to NOT do it that way. Setting it up that
way by default hardly encourages use of a more secure arrangement.I think this argument is a bit inconsistent in the extreme. You might
as well argue that a superuser shouldn't have any permissions by
default, to discourage users from using it. They can always grant
permissions back to it. I don't see why this particular one is so
different.If we go down this road, we'll end up with a mess of permissions that a
superuser has and doesn't have.
+1.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Magnus Hagander <magnus@hagander.net> writes:
But yes, I see in commit 12c942383296bd626131241c012c2ab81b081738 the
comment "convert some keywords.c symbols to KEYWORD_P to prevent
conflict".
Based on that, I should probably change it back, right? I just tried a
patch for it and it compiles and checks just fine with the _P parts
removed.
I'd leave it be, it's fine as-is.
regards, tom lane
On Thu, Dec 30, 2010 at 15:54, Peter Eisentraut <peter_e@gmx.net> wrote:
On ons, 2010-12-29 at 11:09 +0100, Magnus Hagander wrote:
I've applied this version (with some minor typo-fixes).
This page is now somewhat invalidated:
http://developer.postgresql.org/pgdocs/postgres/role-attributes.html
Hmm. Somehow I missed that page completely when looking through the
docs. I'll go update that.
First, it doesn't mention the replication privilege, and second it
continues to claim that superuser status bypasses all permission checks.
Well, that was *already* wrong.
superuser doesn't bypass NOLOGIN.
That doesn't mean it shouldn't be fixed, but that's independent of the
replication role.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Fri, Dec 31, 2010 at 15:38, Magnus Hagander <magnus@hagander.net> wrote:
On Thu, Dec 30, 2010 at 15:54, Peter Eisentraut <peter_e@gmx.net> wrote:
On ons, 2010-12-29 at 11:09 +0100, Magnus Hagander wrote:
I've applied this version (with some minor typo-fixes).
This page is now somewhat invalidated:
http://developer.postgresql.org/pgdocs/postgres/role-attributes.html
Hmm. Somehow I missed that page completely when looking through the
docs. I'll go update that.
BTW, shouldn't CONNECTION LIMIT be listed on that page? and INHERIT?
And VALID UNTIL? They're all role attributes, no? At least the last
two certainly interact with the authentication system...
First, it doesn't mention the replication privilege, and second it
continues to claim that superuser status bypasses all permission checks.Well, that was *already* wrong.
superuser doesn't bypass NOLOGIN.
That doesn't mean it shouldn't be fixed, but that's independent of the
replication role.
I've committed a fix for this.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Jan 3, 2011 at 6:00 AM, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Dec 31, 2010 at 15:38, Magnus Hagander <magnus@hagander.net> wrote:
On Thu, Dec 30, 2010 at 15:54, Peter Eisentraut <peter_e@gmx.net> wrote:
On ons, 2010-12-29 at 11:09 +0100, Magnus Hagander wrote:
I've applied this version (with some minor typo-fixes).
This page is now somewhat invalidated:
http://developer.postgresql.org/pgdocs/postgres/role-attributes.html
Hmm. Somehow I missed that page completely when looking through the
docs. I'll go update that.BTW, shouldn't CONNECTION LIMIT be listed on that page? and INHERIT?
And VALID UNTIL? They're all role attributes, no?
+1.
First, it doesn't mention the replication privilege, and second it
continues to claim that superuser status bypasses all permission checks.Well, that was *already* wrong.
superuser doesn't bypass NOLOGIN.
That doesn't mean it shouldn't be fixed, but that's independent of the
replication role.I've committed a fix for this.
I still think this is the wrong approach. Saying superuser doesn't
bypass nologin is like saying that it doesn't bypass the need to enter
the correct password to authenticate to it. You have to BE the
superuser before you start bypassing permissions checks, and NOLOGIN
and a possible password prompts control WHO CAN BECOME superuser. On
the other hand, the REPLICATION privilege is denying you the right to
perform an operation *even though you already are authenticated as a
superuser*. I don't think there's anywhere else in the system where
we allow a privilege to non-super-users but deny that same privilege
to super-users, and I don't think we should be starting now.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On the other hand, the REPLICATION privilege is denying you the right to
perform an operation *even though you already are authenticated as a
superuser*. I don't think there's anywhere else in the system where
we allow a privilege to non-super-users but deny that same privilege
to super-users, and I don't think we should be starting now.
You might want to reflect on rolcatupdate a bit before asserting that
there are no cases where privileges are ever denied to superusers.
However, that precedent would suggest that the default should be to
grant the replication bit to superusers.
regards, tom lane
On Mon, Jan 3, 2011 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On the other hand, the REPLICATION privilege is denying you the right to
perform an operation *even though you already are authenticated as a
superuser*. I don't think there's anywhere else in the system where
we allow a privilege to non-super-users but deny that same privilege
to super-users, and I don't think we should be starting now.You might want to reflect on rolcatupdate a bit before asserting that
there are no cases where privileges are ever denied to superusers.
Oh, huh. I wasn't aware of that.
However, that precedent would suggest that the default should be to
grant the replication bit to superusers.
Yes it would.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Jan 3, 2011 at 17:23, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jan 3, 2011 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On the other hand, the REPLICATION privilege is denying you the right to
perform an operation *even though you already are authenticated as a
superuser*. I don't think there's anywhere else in the system where
we allow a privilege to non-super-users but deny that same privilege
to super-users, and I don't think we should be starting now.You might want to reflect on rolcatupdate a bit before asserting that
there are no cases where privileges are ever denied to superusers.Oh, huh. I wasn't aware of that.
However, that precedent would suggest that the default should be to
grant the replication bit to superusers.Yes it would.
Just to be clear: are we saying that "CREATE ROLE foo SUPERUSER"
should grant both superuser and replication, as well as the default
"postgres" user also having replication as well?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Jan 3, 2011 at 5:50 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Jan 3, 2011 at 17:23, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jan 3, 2011 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On the other hand, the REPLICATION privilege is denying you the right to
perform an operation *even though you already are authenticated as a
superuser*. I don't think there's anywhere else in the system where
we allow a privilege to non-super-users but deny that same privilege
to super-users, and I don't think we should be starting now.You might want to reflect on rolcatupdate a bit before asserting that
there are no cases where privileges are ever denied to superusers.Oh, huh. I wasn't aware of that.
However, that precedent would suggest that the default should be to
grant the replication bit to superusers.Yes it would.
Just to be clear: are we saying that "CREATE ROLE foo SUPERUSER"
should grant both superuser and replication, as well as the default
"postgres" user also having replication as well?
I think that's what we're saying.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Jan 5, 2011 at 04:24, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jan 3, 2011 at 5:50 PM, Magnus Hagander <magnus@hagander.net> wrote:
On Mon, Jan 3, 2011 at 17:23, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Jan 3, 2011 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On the other hand, the REPLICATION privilege is denying you the right to
perform an operation *even though you already are authenticated as a
superuser*. I don't think there's anywhere else in the system where
we allow a privilege to non-super-users but deny that same privilege
to super-users, and I don't think we should be starting now.You might want to reflect on rolcatupdate a bit before asserting that
there are no cases where privileges are ever denied to superusers.Oh, huh. I wasn't aware of that.
However, that precedent would suggest that the default should be to
grant the replication bit to superusers.Yes it would.
Just to be clear: are we saying that "CREATE ROLE foo SUPERUSER"
should grant both superuser and replication, as well as the default
"postgres" user also having replication as well?I think that's what we're saying.
Ok, done and applied.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Wed, Jan 5, 2011 at 8:24 AM, Magnus Hagander <magnus@hagander.net> wrote:
Ok, done and applied.
Thanks.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On mån, 2011-01-03 at 11:20 -0500, Tom Lane wrote:
You might want to reflect on rolcatupdate a bit before asserting that
there are no cases where privileges are ever denied to superusers.
Arguably, the reason that that is hardly documented and slightly
deprecated is that the underlying design decision is questionable.
On tis, 2011-01-04 at 22:24 -0500, Robert Haas wrote:
Just to be clear: are we saying that "CREATE ROLE foo SUPERUSER"
should grant both superuser and replication, as well as the default
"postgres" user also having replication as well?I think that's what we're saying.
So now superusers have it by default but you can explicitly revoke it?
I guess that's still inconsistent with other superuser behavior. You
can't revoke a superuser's CREATEDB bit, for example. (You can, but it
won't have an effect, of course.)