Обсуждение: Roles and passwds
Hi
I created the following role
postgres=# create role sde LOGIN PASSWORD 'passwd' SUPERUSER NOINHERIT CREATEDB;
CREATE ROLE
postgres=#
However from the command line as the postgres user I can do
-
-bash-4.1$ psql -d testdb -U sde
psql (8.4.13)
Type "help" for help.
testdb=#
So logging on to the testdb database without entering a password.
Is this expected behaviour?
More importantly my colleague can connect to the database as the sde user via a client side GUI by entering any value in the password dialogue box.
Doing the following didn’t help:
testdb=# alter role sde WITH PASSWORD 'passwd';
ALTER ROLE
testdb=# alter role sde valid until 'infinity';
ALTER ROLE
How can I setup the passwd for the sde role?
Many thanks
Austen Birchall
Senior DBA
Met Office
"Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes: > I created the following role > postgres=# create role sde LOGIN PASSWORD 'passwd' SUPERUSER NOINHERIT CREATEDB; > [ but the password isn't used ] > Is this expected behaviour? It can be depending on how you have the server's authentication configured (pg_hba.conf). A password will only be used if the server is configured to demand one for the particular connection attempt. I suspect you've got yours set to "trust" for local connections. Read http://www.postgresql.org/docs/8.4/static/client-authentication.html regards, tom lane
8.4.13 on Red Hat 6 I am doing a backup/restore test of a 'quite large' database I ran the following backup pg_dump -Fc testdb -U postgres > /var/lib/pgsql/backups/pg_testdb_backups/testdb_dev_backup$date_of_backup then I connected to postgres db and did drop dbtest I then attempted to do a restore: pg_restore -d testdb -j testdb_dev_backup201304141900 starting yesterday afternoon but then I came in this morning it is still running and as far as I can tell no data or Linuxfiles have been written 1. Is pg_restore hanging and/or have I done something wrong? 2. Is there a way to check on the progress (% completed) for the pg_restore procedure? Thanks Austen Birchall Senior DBA Met Office
OK
1. I found the -v option which I presume will show progress
2. I realised that I actually had to do a CREATE DATABASE for testdb before doing the restore.
The restore completed very quickly but I got the following errors - this is a postgis database
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4797; 0 31549 TABLE DATA a
dmcntry_gl3m0ahcmp__st cor
pg_restore: [archiver (db)] COPY failed for table "admcntry_gl3m0ahcmp__st"
: ERROR: ESRI: error getting spatial references for srid = 13:
CONTEXT: COPY admcntry_gl3m0ahcmp__st, line 1, column shape: "AA0400006400
0000080010000D00000092120000010000008DAC92EEF4C7B40D92FBFBC88BCAA511DDFDBEB
0E103DCBDCEE0..."
pg_restore: [archiver (db)] Error from TOC entry 4787; 0 30856 TABLE DATA b
igash_st cor
pg_restore: [archiver (db)] COPY failed for table "bigash_st": ERROR: ESRI
: error getting spatial references for srid = 31:
CONTEXT: COPY bigash_st, line 1, column shape: "910300005C000000080118001F
000000B90D00000100000092C4F6A0AB199CE3C0DA931EB4BEC79101B59E92B001B4F8E512.
.."
pg_restore: [archiver (db)] Error from TOC entry 4789; 0 30997 TABLE DATA bigeuhtpoly_st cor
pg_restore: [archiver (db)] COPY failed for table "bigeuhtpoly_st": ERROR: ESRI: error getting spatial references for
srid= 32:
CONTEXT: COPY bigeuhtpoly_st, line 1, column shape:
"D0B84600D92D09000800100020000000B8E2B5040100000091B8F6F2D61B9DBBCE96BF1EE8D9E702BF9AAA03C7A0E508A893..."
WARNING: errors ignored on restore: 3
-bash-4.1$
Anybody know what is happening here?
Austen
-----Original Message-----
From: Birchall, Austen
Sent: 16 April 2013 10:58
To: pgsql-novice@postgresql.org
Subject: pg_restore - hangs or check on progress
8.4.13 on Red Hat 6
I am doing a backup/restore test of a 'quite large' database
I ran the following backup
pg_dump -Fc testdb -U postgres > /var/lib/pgsql/backups/pg_testdb_backups/testdb_dev_backup$date_of_backup
then I connected to postgres db and did
drop dbtest
I then attempted to do a restore:
pg_restore -d testdb -j testdb_dev_backup201304141900
starting yesterday afternoon but then I came in this morning it is still running and as far as I can tell no data or
Linuxfiles have been written
1. Is pg_restore hanging and/or have I done something wrong?
2. Is there a way to check on the progress (% completed) for the pg_restore procedure?
Thanks
Austen Birchall
Senior DBA
Met Office
"Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes:
> The restore completed very quickly but I got the following errors - this is a postgis database
> pg_restore: [archiver (db)] COPY failed for table "admcntry_gl3m0ahcmp__st"
: ERROR: ESRI: error getting spatial references for srid = 13:
> Anybody know what is happening here?
I think pg_restore is not aware that the postgis support tables have to
be loaded up before geometric data can be restored. (That whole area is
something that's been greatly improved since 8.4.) I'd suggest
consulting the postgis docs or mailing lists as to the recommended
procedure for dump/restore on older versions.
regards, tom lane
Thanks for this Tom, you're right as
The vendor docs (ESRI) state that I have to pg_restore for public first then do a separate one for the rest of the db
Austen
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 April 2013 15:03
To: Birchall, Austen
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_restore - hangs or check on progress
"Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes:
> The restore completed very quickly but I got the following errors - this is a postgis database
> pg_restore: [archiver (db)] COPY failed for table "admcntry_gl3m0ahcmp__st"
: ERROR: ESRI: error getting spatial references for srid = 13:
> Anybody know what is happening here?
I think pg_restore is not aware that the postgis support tables have to be loaded up before geometric data can be
restored. (That whole area is something that's been greatly improved since 8.4.) I'd suggest consulting the postgis
docsor mailing lists as to the recommended procedure for dump/restore on older versions.
regards, tom lane
8.4.13 on Red Hat 6 I am doing a backup/restore test of a 'quite large' database I ran the following backup pg_dump -Fc testdb -U postgres > /var/lib/pgsql/backups/pg_testdb_backups/testdb_dev_backup$date_of_backup Id I drop a table I can restore it by doing: pg_restore -t i100 -d testdb -testdb_dev_backup201304141900 What's the best way to get the index backup? Thanks again Austen Birchall Senior DBA Met Office
When minor releases and/or Security releases are made public are there any mechanisms in place for the automatically notifyingthe community that they are available for download? Thanks Austen Birchall DBA Met Office
On 8 May 2013 11:24, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote: > When minor releases and/or Security releases are made public are there any mechanisms in place for the automatically notifyingthe community that they are available for download? Yes, support providers send out their own notices, but you should subscribe to the Announce list (pgsql-announce) http://www.postgresql.org/community/lists/subscribe/ -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 >> When minor releases and/or Security releases are made public are >> there any mechanisms in place for the automatically notifying >> the community that they are available for download? > Yes, support providers send out their own notices, but you should > subscribe to the Announce list (pgsql-announce) > http://www.postgresql.org/community/lists/subscribe/ If you want something more automated, the check_postgres program can check for new revisions of Postgres: http://bucardo.org/check_postgres/check_postgres.pl.html#new_version_pg - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201305081045 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlGKZT8ACgkQvJuQZxSWSsiEhACgiHksZoXmFmtZZTrj+2FG32jY CN4AoOHChm+Wy/llaiwZfshjjYV7S9un =QDk/ -----END PGP SIGNATURE-----