Обсуждение: Running psql commands from a remote location & pg_hba.conf?

I have a monitor user that does a SSH from a remote location onto a database's host then runs commands  of the form:

/usr/bin/psql -c "SELECT COUNT(*) FROM pg_stat_activity; ;" -U monitor -d db1 -q -A -t

The current pg_hba.conf  is


# IPv4 local connections:
host    all         all         123.0.0.1/32          trust
which is far from ideal!

Am I right to presume that I have to change pg_hba.conf to

host    db1    monitor        123.0.0.1/32    trust
host     db1    user1        123.0.0.1/32    ident
host    db1    user2        123.0.0.1/32    ident

then continue this format with individual entries for all the users in db1?

Or is there a 'better' way of running psql commands from a remote location?

Thanks

Austen

Austen Birchall  Senior Database Administrator, Met Office

On Mon, Aug 5, 2013 at 3:45 PM, Birchall, Austen
<austen.birchall@metoffice.gov.uk> wrote:
> Am I right to presume that I have to change pg_hba.conf to
>
> host    db1     monitor         123.0.0.1/32    trust
> host    db1     user1           123.0.0.1/32    ident
> host    db1     user2           123.0.0.1/32    ident
>
> then continue this format with individual entries for all the users in db1?

Not sure this is what you are looking for, but why not defining a role
that contains all the users and then add a single entry related to
such role?
See http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html -> user

Luca

Hi Luca

And thanks once again for your assistance. Yes a role would work although I would have to remember to add any new users
toit as they are created. 

I guess one of the questions I am asking is:

Is there a way to run psql scripts in a database fired off from a remote location without going down the trust
authentication'route' in pg_hba.conf? 

Austen



On Mon, Aug 5, 2013 at 3:45 PM, Birchall, Austen wrote:
> Am I right to presume that I have to change pg_hba.conf to
>
> host    db1     monitor         123.0.0.1/32    trust
> host    db1     user1           123.0.0.1/32    ident
> host    db1     user2           123.0.0.1/32    ident
>
> then continue this format with individual entries for all the users in db1?

Not sure this is what you are looking for, but why not defining a role that contains all the users and then add a
singleentry related to such role? 
See http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html -> user

Luca

On Mon, Aug 5, 2013 at 5:46 PM, Birchall, Austen
<austen.birchall@metoffice.gov.uk> wrote:
> And thanks once again for your assistance. Yes a role would work although I would have to remember to add any new
usersto it as they are created. 

Similar to adding a new line in pg_hba.conf for each new user, but
logically better since you have to remember only to set up roles
correctly, not to also enable them to connect to the database.



> Is there a way to run psql scripts in a database fired off from a remote location without going down the trust
authentication'route' in pg_hba.conf? 
>

You always have to deal with pg_hba.conf, but if the problem is to
"trust" the monitor user and not trust other users, then use a single
line with md5, ident or what you like and use .pgpass
(http://www.postgresql.org/docs/current/static/libpq-pgpass.html) to
store the connection details (including password) for the monitor
user, so that it will connect smoothly and you will be able to run any
batch operation thru it from the remote machine.
Is this what you are looking for?

Luca

psql (8.4.13) on Red Hat 6

I am trying to setup .pgpass and testing it I get:


[postgres@myhost:~]$ whoami
postgres
[postgres@myhost:~]$ echo $HOME
/var/lib/pgsql
[postgres@myhost:~]$ pwd
/var/lib/pgsql
[postgres@myhost:~]$ ls -la .pgpass
-rw-------. 1 postgres postgres 20 Aug  7 13:52 .pgpass
[postgres@myhost:~]$ view .pgpass
*:*:*:austen:austen

[postgres@myhost:~]$ psql -U austen -d mudb
psql: FATAL:  Ident authentication failed for user "austen"
[postgres@myhost:~]$

Can anybody see what I am doing wrong?

Thanks

Austen


Austen Birchall  Senior Database Administrator
Met Office

classmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.com

Amtsgericht Charlottenburg HRB 111815 B | USt.Id.Nr: DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.

9.2 on Red Hat 6

Our OS Sys Admin have decided to withdraw my (DBA) access to the OS postgres account - so instead of
ssh/logging in to a DB host I I have to login in as 'myself' and then gain access to psql etc. via sudo
such as by doing
 PSQL access as the postgres user
sudo -u postgres /usr/bin/psql <command line options>
which I have been granted permissions to do.

I have the following questions regarding this:

1.    Is this standard/best practise?

2.    In simple terms what is justification for dong this - looking at it from a DBA point of view?

3.    As myself I current have no permissions on the ..../psql/data directory and its sub-directories including the log
files,which IMHO I need in order to be able to function as a DBA - is there a preferred way in which I can be
granted/gainthis access other than by granting rights on files at an individual level? 

Happy to (try to) explain further if none of this makes much sense.

Thanks in advance as always

Austen


Austen Birchall  Senior Database Administrator
Met Office

9.2 on Red Hat 6

Up to now I have been creating the sub-directories that (hold) the OS level files that are associated with individual
tablespacesdirectly under the  

..../psql/data directory i.e.

db=# create tablespace test owner test_admin location '/var/lib/pgsql/data/test';
Is this standard/best practise - I can see that the data directory could get 'cluttered' in a db that has many
tablespaces.

Is there a standard/recommended alternative for tablespace location for db which have many tablespaces?

Thanks in advance as always

Austen


Austen Birchall  Senior Database Administrator
Met Office

classmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.com

Amtsgericht Charlottenburg HRB 111815 B | USt.Id.Nr: DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.

Austen,

> sudo -u postgres /bin/bash
> [sudo] password for test:
> Sorry, user test is not allowed to execute '/bin/bash' as postgres

Are you able to execute:
sudo -u postgres -s

--
G.

Gary

sudo -u postgres -s
[sudo] password for ajbircha:
Sorry, user ajbircha is not allowed to execute '/bin/bash' as postgres on exvlocsdbint01.metoffice.gov.uk.

So again no!

Austen

-----Original Message-----
From: Gary Chambers [mailto:gwchamb@gwcmail.com]
Sent: 15 August 2013 15:16
To: Birchall, Austen
Cc: Athanasios Kostopoulos; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] sudo/access to the postres OS user

Austen,

> sudo -u postgres /bin/bash
> [sudo] password for test:
> Sorry, user test is not allowed to execute '/bin/bash' as postgres

Are you able to execute:
sudo -u postgres -s

--
G.

"Birchall, Austen" <austen.birchall@metoffice.gov.uk> wrote:

> Up to now I have been creating the sub-directories that (hold)
> the OS level files that are associated with individual
> tablespaces directly under the
>
> ..../psql/data directory i.e.
>
> db=# create tablespace test owner test_admin location
> '/var/lib/pgsql/data/test';
> Is this standard/best practise - I can see that the data
> directory could get 'cluttered' in a db that has many
> tablespaces.

I've never spent a lot of time browing the subdirectories.  Why
would "cluttered" matter?

> Is there a standard/recommended alternative for tablespace
> location for db which have many tablespaces?

It is not a good idea to put tablespaces under the $PGDATA
directory.  For one thing, it creates challenges for backup
software, which generally tries to copy everything under the
$PGDATA directory and then add everything from each non-default
tablespace.  Without a lot of care, you wind up copying everything
in every non-default tablespace twice.

Also, it doesn't really provide much benefit to use tablespaces
this way.  Unless the tablespace is on a separate filesystem, you
don't get any performance benefit, unless the type of filesystem
being used performs poorly with a large number of files in one
subdirectory.  If they are just being used for logical separation,
then separate schemas (namespaces) is a better way to do that.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Aug 16, 2013 at 5:01 PM, Birchall, Austen
<austen.birchall@metoffice.gov.uk> wrote:

> However for a 24/7 WAL mode database would I need access to the contents of
> the pg_xlog directory?
>
>
>
> Also it I need to do a PITR wouldn’t I need rw access to just about
> everything?
>

Well, the recovery is performed by PostgreSQL itself, so the short
answer is no. You need access to the recovery.conf file and, to some
extent, to the wal archiving space for cleanup and maintenance.
Now it should be interesting to know what you are allowed to do,
because if you have access only to "psql" executable via sudo then
you'll not be able to do a pitr (you need to control the cluster). If
you have the capability to launch a text editor (e.g., Emacs) you
could be able to run a shell (or a shell command) from within that.
If protection is the aim then the cluster has to be carefully set up
(with directory and permissions splitted) so that you can have access
to the objects you need.

Luca

classmarkets GmbH | Schumannstraße 6 | 10117 Berlin | Deutschland
Tel: +49 (0)30 56 59 001-0 | Fax: +49 (0)30 56 59 001-99 | www.classmarkets.com

Amtsgericht Charlottenburg HRB 111815 B | USt.Id.Nr: DE 260731582
Geschäftsführer: Veit Mürz, Fabian Ströhle

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sie darf ausschließlich durch den vorgesehenen Empfänger und Adressaten gelesen, kopiert oder genutzt werden. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Jede unerlaubte Nutzung oder Weitergabe des Inhalts dieser Nachricht, sei es vollständig oder teilweise, ist unzulässig. Bitte beachten Sie, dass E-Mail-Nachrichten an den Absender nicht für fristgebundene Mitteilungen geeignet sind. Fristgebundene Mitteilungen sind daher ausschließlich per Post oder per Telefax zu übersenden.