Skip to content

Commit 9a83d56

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Allow pg_dumpall to dump roles w/o user passwords
Add new option --no-role-passwords which dumps roles without passwords. Since we don’t need passwords, we choose to use pg_roles in preference to pg_authid since access may be restricted for security reasons in some configrations. Robins Tharakan and Simon Riggs
1 parent 55acfcb commit 9a83d56

File tree

2 files changed

+97
-48
lines changed

2 files changed

+97
-48
lines changed

‎doc/src/sgml/ref/pg_dumpall.sgml

+13
Original file line numberDiff line numberDiff line change
@@ -332,6 +332,19 @@ PostgreSQL documentation
332332
</listitem>
333333
</varlistentry>
334334

335+
<varlistentry>
336+
<term><option>--no-role-passwords</option></term>
337+
<listitem>
338+
<para>
339+
Do not dump passwords for roles. When restored, roles will have a NULL
340+
password and authentication will always fail until the password is reset.
341+
Since password values aren't needed when this option is specified we
342+
use the catalog view pg_roles in preference to pg_authid, since access
343+
to pg_authid may be restricted by security policy.
344+
</para>
345+
</listitem>
346+
</varlistentry>
347+
335348
<varlistentry>
336349
<term><option>--no-security-labels</option></term>
337350
<listitem>

‎src/bin/pg_dump/pg_dumpall.c

+84-48
Original file line numberDiff line numberDiff line change
@@ -74,8 +74,13 @@ static int no_tablespaces = 0;
7474
static int use_setsessauth = 0;
7575
static int no_security_labels = 0;
7676
static int no_unlogged_table_data = 0;
77+
static int no_role_passwords = 0;
7778
static int server_version;
7879

80+
static char role_catalog[10];
81+
#define PG_AUTHID "pg_authid"
82+
#define PG_ROLES "pg_roles "
83+
7984
static FILE *OPF;
8085
static char *filename = NULL;
8186

@@ -123,6 +128,7 @@ main(int argc, char *argv[])
123128
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
124129
{"no-security-labels", no_argument, &no_security_labels, 1},
125130
{"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1},
131+
{"no-role-passwords", no_argument, &no_role_passwords, 1},
126132

127133
{NULL, 0, NULL, 0}
128134
};
@@ -342,6 +348,25 @@ main(int argc, char *argv[])
342348
exit_nicely(1);
343349
}
344350

351+
if (no_role_passwords && binary_upgrade)
352+
{
353+
fprintf(stderr, _("%s: options --no-role-passwords and --binary-upgrade cannot be used together\n"),
354+
progname);
355+
fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
356+
progname);
357+
exit_nicely(1);
358+
}
359+
360+
/*
361+
* If password values are not required in the dump, switch to
362+
* using pg_roles which is equally useful, just more likely
363+
* to have unrestricted access than pg_authid.
364+
*/
365+
if (no_role_passwords)
366+
sprintf(role_catalog, "%s", PG_ROLES);
367+
else
368+
sprintf(role_catalog, "%s", PG_AUTHID);
369+
345370
/* Add long options to the pg_dump argument list */
346371
if (binary_upgrade)
347372
appendPQExpBufferStr(pgdumpopts, " --binary-upgrade");
@@ -563,6 +588,7 @@ help(void)
563588
printf(_(" --no-security-labels do not dump security label assignments\n"));
564589
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
565590
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
591+
printf(_(" --no-role-passwords do not dump passwords for roles\n"));
566592
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
567593
printf(_(" --use-set-session-authorization\n"
568594
" use SET SESSION AUTHORIZATION commands instead of\n"
@@ -590,30 +616,33 @@ help(void)
590616
static void
591617
dropRoles(PGconn *conn)
592618
{
619+
PQExpBuffer buf = createPQExpBuffer();
593620
PGresult *res;
594621
int i_rolname;
595622
int i;
596623

597624
if (server_version >= 90600)
598-
res = executeQuery(conn,
625+
printfPQExpBuffer(buf,
599626
"SELECT rolname "
600-
"FROM pg_authid "
627+
"FROM %s "
601628
"WHERE rolname !~ '^pg_' "
602-
"ORDER BY 1");
629+
"ORDER BY 1", role_catalog);
603630
else if (server_version >= 80100)
604-
res = executeQuery(conn,
631+
printfPQExpBuffer(buf,
605632
"SELECT rolname "
606-
"FROM pg_authid "
607-
"ORDER BY 1");
633+
"FROM %s "
634+
"ORDER BY 1", role_catalog);
608635
else
609-
res = executeQuery(conn,
636+
printfPQExpBuffer(buf,
610637
"SELECT usename as rolname "
611638
"FROM pg_shadow "
612639
"UNION "
613640
"SELECT groname as rolname "
614641
"FROM pg_group "
615642
"ORDER BY 1");
616643

644+
res = executeQuery(conn, buf->data);
645+
617646
i_rolname = PQfnumber(res, "rolname");
618647

619648
if (PQntuples(res) > 0)
@@ -631,6 +660,7 @@ dropRoles(PGconn *conn)
631660
}
632661

633662
PQclear(res);
663+
destroyPQExpBuffer(buf);
634664

635665
fprintf(OPF, "\n\n");
636666
}
@@ -666,43 +696,43 @@ dumpRoles(PGconn *conn)
666696
"rolcreaterole, rolcreatedb, "
667697
"rolcanlogin, rolconnlimit, rolpassword, "
668698
"rolvaliduntil, rolreplication, rolbypassrls, "
669-
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
699+
"pg_catalog.shobj_description(oid, '%s') as rolcomment, "
670700
"rolname = current_user AS is_current_user "
671-
"FROM pg_authid "
701+
"FROM %s "
672702
"WHERE rolname !~ '^pg_' "
673-
"ORDER BY 2");
703+
"ORDER BY 2", role_catalog, role_catalog);
674704
else if (server_version >= 90500)
675705
printfPQExpBuffer(buf,
676706
"SELECT oid, rolname, rolsuper, rolinherit, "
677707
"rolcreaterole, rolcreatedb, "
678708
"rolcanlogin, rolconnlimit, rolpassword, "
679709
"rolvaliduntil, rolreplication, rolbypassrls, "
680-
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
710+
"pg_catalog.shobj_description(oid, '%s') as rolcomment, "
681711
"rolname = current_user AS is_current_user "
682-
"FROM pg_authid "
683-
"ORDER BY 2");
712+
"FROM %s "
713+
"ORDER BY 2", role_catalog, role_catalog);
684714
else if (server_version >= 90100)
685715
printfPQExpBuffer(buf,
686716
"SELECT oid, rolname, rolsuper, rolinherit, "
687717
"rolcreaterole, rolcreatedb, "
688718
"rolcanlogin, rolconnlimit, rolpassword, "
689719
"rolvaliduntil, rolreplication, "
690720
"false as rolbypassrls, "
691-
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
721+
"pg_catalog.shobj_description(oid, '%s') as rolcomment, "
692722
"rolname = current_user AS is_current_user "
693-
"FROM pg_authid "
694-
"ORDER BY 2");
723+
"FROM %s "
724+
"ORDER BY 2", role_catalog, role_catalog);
695725
else if (server_version >= 80200)
696726
printfPQExpBuffer(buf,
697727
"SELECT oid, rolname, rolsuper, rolinherit, "
698728
"rolcreaterole, rolcreatedb, "
699729
"rolcanlogin, rolconnlimit, rolpassword, "
700730
"rolvaliduntil, false as rolreplication, "
701731
"false as rolbypassrls, "
702-
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
732+
"pg_catalog.shobj_description(oid, '%s') as rolcomment, "
703733
"rolname = current_user AS is_current_user "
704-
"FROM pg_authid "
705-
"ORDER BY 2");
734+
"FROM %s "
735+
"ORDER BY 2", role_catalog, role_catalog);
706736
else if (server_version >= 80100)
707737
printfPQExpBuffer(buf,
708738
"SELECT oid, rolname, rolsuper, rolinherit, "
@@ -712,8 +742,8 @@ dumpRoles(PGconn *conn)
712742
"false as rolbypassrls, "
713743
"null as rolcomment, "
714744
"rolname = current_user AS is_current_user "
715-
"FROM pg_authid "
716-
"ORDER BY 2");
745+
"FROM %s "
746+
"ORDER BY 2", role_catalog);
717747
else
718748
printfPQExpBuffer(buf,
719749
"SELECT 0 as oid, usename as rolname, "
@@ -846,7 +876,8 @@ dumpRoles(PGconn *conn)
846876
appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
847877
PQgetvalue(res, i, i_rolconnlimit));
848878

849-
if (!PQgetisnull(res, i, i_rolpassword))
879+
880+
if (!PQgetisnull(res, i, i_rolpassword) && !no_role_passwords)
850881
{
851882
appendPQExpBufferStr(buf, " PASSWORD ");
852883
appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn);
@@ -897,19 +928,21 @@ dumpRoles(PGconn *conn)
897928
static void
898929
dumpRoleMembership(PGconn *conn)
899930
{
931+
PQExpBuffer buf = createPQExpBuffer();
900932
PGresult *res;
901933
int i;
902934

903-
res = executeQuery(conn, "SELECT ur.rolname AS roleid, "
935+
printfPQExpBuffer(buf, "SELECT ur.rolname AS roleid, "
904936
"um.rolname AS member, "
905937
"a.admin_option, "
906938
"ug.rolname AS grantor "
907939
"FROM pg_auth_members a "
908-
"LEFT JOIN pg_authid ur on ur.oid = a.roleid "
909-
"LEFT JOIN pg_authid um on um.oid = a.member "
910-
"LEFT JOIN pg_authid ug on ug.oid = a.grantor "
940+
"LEFT JOIN %s ur on ur.oid = a.roleid "
941+
"LEFT JOIN %s um on um.oid = a.member "
942+
"LEFT JOIN %s ug on ug.oid = a.grantor "
911943
"WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')"
912-
"ORDER BY 1,2,3");
944+
"ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog);
945+
res = executeQuery(conn, buf->data);
913946

914947
if (PQntuples(res) > 0)
915948
fprintf(OPF, "--\n-- Role memberships\n--\n\n");
@@ -939,6 +972,7 @@ dumpRoleMembership(PGconn *conn)
939972
}
940973

941974
PQclear(res);
975+
destroyPQExpBuffer(buf);
942976

943977
fprintf(OPF, "\n\n");
944978
}
@@ -1298,9 +1332,9 @@ dumpCreateDB(PGconn *conn)
12981332
* databases.
12991333
*/
13001334
if (server_version >= 90600)
1301-
res = executeQuery(conn,
1335+
printfPQExpBuffer(buf,
13021336
"SELECT datname, "
1303-
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1337+
"coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
13041338
"pg_encoding_to_char(d.encoding), "
13051339
"datcollate, datctype, datfrozenxid, datminmxid, "
13061340
"datistemplate, "
@@ -1314,43 +1348,43 @@ dumpCreateDB(PGconn *conn)
13141348
"AS rdatacl, "
13151349
"datconnlimit, "
13161350
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1317-
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1318-
"WHERE datallowconn ORDER BY 1");
1351+
"FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
1352+
"WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
13191353
else if (server_version >= 90300)
1320-
res = executeQuery(conn,
1354+
printfPQExpBuffer(buf,
13211355
"SELECT datname, "
1322-
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1356+
"coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
13231357
"pg_encoding_to_char(d.encoding), "
13241358
"datcollate, datctype, datfrozenxid, datminmxid, "
13251359
"datistemplate, datacl, '' as rdatacl, "
13261360
"datconnlimit, "
13271361
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1328-
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1329-
"WHERE datallowconn ORDER BY 1");
1362+
"FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
1363+
"WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
13301364
else if (server_version >= 80400)
1331-
res = executeQuery(conn,
1365+
printfPQExpBuffer(buf,
13321366
"SELECT datname, "
1333-
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1367+
"coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
13341368
"pg_encoding_to_char(d.encoding), "
13351369
"datcollate, datctype, datfrozenxid, 0 AS datminmxid, "
13361370
"datistemplate, datacl, '' as rdatacl, "
13371371
"datconnlimit, "
13381372
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1339-
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1340-
"WHERE datallowconn ORDER BY 1");
1373+
"FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
1374+
"WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
13411375
else if (server_version >= 80100)
1342-
res = executeQuery(conn,
1376+
printfPQExpBuffer(buf,
13431377
"SELECT datname, "
1344-
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1378+
"coalesce(rolname, (select rolname from %s where oid=(select datdba from pg_database where datname='template0'))), "
13451379
"pg_encoding_to_char(d.encoding), "
13461380
"null::text AS datcollate, null::text AS datctype, datfrozenxid, 0 AS datminmxid, "
13471381
"datistemplate, datacl, '' as rdatacl, "
13481382
"datconnlimit, "
13491383
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1350-
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1351-
"WHERE datallowconn ORDER BY 1");
1384+
"FROM pg_database d LEFT JOIN %s u ON (datdba = u.oid) "
1385+
"WHERE datallowconn ORDER BY 1", role_catalog, role_catalog);
13521386
else
1353-
res = executeQuery(conn,
1387+
printfPQExpBuffer(buf,
13541388
"SELECT datname, "
13551389
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
13561390
"pg_encoding_to_char(d.encoding), "
@@ -1361,6 +1395,8 @@ dumpCreateDB(PGconn *conn)
13611395
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
13621396
"WHERE datallowconn ORDER BY 1");
13631397

1398+
res = executeQuery(conn, buf->data);
1399+
13641400
for (i = 0; i < PQntuples(res); i++)
13651401
{
13661402
char *dbname = PQgetvalue(res, i, 0);
@@ -1557,9 +1593,9 @@ dumpUserConfig(PGconn *conn, const char *username)
15571593
if (server_version >= 90000)
15581594
printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
15591595
"setdatabase = 0 AND setrole = "
1560-
"(SELECT oid FROM pg_authid WHERE rolname = ", count);
1596+
"(SELECT oid FROM %s WHERE rolname = ", count, role_catalog);
15611597
else if (server_version >= 80100)
1562-
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
1598+
printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM %s WHERE rolname = ", count, role_catalog);
15631599
else
15641600
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
15651601
appendStringLiteralConn(buf, username, conn);
@@ -1597,8 +1633,8 @@ dumpDbRoleConfig(PGconn *conn)
15971633
int i;
15981634

15991635
printfPQExpBuffer(buf, "SELECT rolname, datname, unnest(setconfig) "
1600-
"FROM pg_db_role_setting, pg_authid, pg_database "
1601-
"WHERE setrole = pg_authid.oid AND setdatabase = pg_database.oid");
1636+
"FROM pg_db_role_setting, %s u, pg_database "
1637+
"WHERE setrole = u.oid AND setdatabase = pg_database.oid", role_catalog);
16021638
res = executeQuery(conn, buf->data);
16031639

16041640
if (PQntuples(res) > 0)

0 commit comments

Comments
 (0)