Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9a83d56

Browse files
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 preferenceto pg_authid since access may be restricted for security reasons insome configrations.Robins Tharakan and Simon Riggs
1 parent55acfcb commit9a83d56

File tree

2 files changed

+97
-48
lines changed

2 files changed

+97
-48
lines changed

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

Lines changed: 13 additions & 0 deletions
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

Lines changed: 84 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -74,8 +74,13 @@ static intno_tablespaces = 0;
7474
staticintuse_setsessauth=0;
7575
staticintno_security_labels=0;
7676
staticintno_unlogged_table_data=0;
77+
staticintno_role_passwords=0;
7778
staticintserver_version;
7879

80+
staticcharrole_catalog[10];
81+
#definePG_AUTHID "pg_authid"
82+
#definePG_ROLES "pg_roles "
83+
7984
staticFILE*OPF;
8085
staticchar*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
staticvoid
591617
dropRoles(PGconn*conn)
592618
{
619+
PQExpBufferbuf=createPQExpBuffer();
593620
PGresult*res;
594621
inti_rolname;
595622
inti;
596623

597624
if (server_version >=90600)
598-
res=executeQuery(conn,
625+
printfPQExpBuffer(buf,
599626
"SELECT rolname "
600-
"FROMpg_authid "
627+
"FROM%s "
601628
"WHERE rolname !~ '^pg_' "
602-
"ORDER BY 1");
629+
"ORDER BY 1",role_catalog);
603630
elseif (server_version >=80100)
604-
res=executeQuery(conn,
631+
printfPQExpBuffer(buf,
605632
"SELECT rolname "
606-
"FROMpg_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-
"FROMpg_authid "
701+
"FROM%s "
672702
"WHERE rolname !~ '^pg_' "
673-
"ORDER BY 2");
703+
"ORDER BY 2",role_catalog,role_catalog);
674704
elseif (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-
"FROMpg_authid "
683-
"ORDER BY 2");
712+
"FROM%s "
713+
"ORDER BY 2",role_catalog,role_catalog);
684714
elseif (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-
"FROMpg_authid "
694-
"ORDER BY 2");
723+
"FROM%s "
724+
"ORDER BY 2",role_catalog,role_catalog);
695725
elseif (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-
"FROMpg_authid "
705-
"ORDER BY 2");
734+
"FROM%s "
735+
"ORDER BY 2",role_catalog,role_catalog);
706736
elseif (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-
"FROMpg_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
staticvoid
898929
dumpRoleMembership(PGconn*conn)
899930
{
931+
PQExpBufferbuf=createPQExpBuffer();
900932
PGresult*res;
901933
inti;
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 JOINpg_authid ur on ur.oid = a.roleid "
909-
"LEFT JOINpg_authid um on um.oid = a.member "
910-
"LEFT JOINpg_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 frompg_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 JOINpg_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
elseif (server_version >=90300)
1320-
res=executeQuery(conn,
1354+
printfPQExpBuffer(buf,
13211355
"SELECT datname, "
1322-
"coalesce(rolname, (select rolname frompg_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 JOINpg_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
elseif (server_version >=80400)
1331-
res=executeQuery(conn,
1365+
printfPQExpBuffer(buf,
13321366
"SELECT datname, "
1333-
"coalesce(rolname, (select rolname frompg_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 JOINpg_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
elseif (server_version >=80100)
1342-
res=executeQuery(conn,
1376+
printfPQExpBuffer(buf,
13431377
"SELECT datname, "
1344-
"coalesce(rolname, (select rolname frompg_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 JOINpg_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 FROMpg_authid WHERE rolname = ",count);
1596+
"(SELECT oid FROM%s WHERE rolname = ",count,role_catalog);
15611597
elseif (server_version >=80100)
1562-
printfPQExpBuffer(buf,"SELECT rolconfig[%d] FROMpg_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
inti;
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)
[8]ページ先頭
©2009-2025 Movatter.jp