Nvim as external editor in psql as Postgres root user - .vimrc (environment?) issue

On 03/16/2018 10:47 AM, Victoria wrote:

Hello; I am using PostgreSQL v.10.2 as a root user (sudo -u postgres -i) on my local Arch Linux installation.

I want to use Neovim (nvim v.0.2.2) as my external editor (\e) in psql; the current default is the Arch Linux default system editor, vi.

If I add this to my ~/.psqlrc (/home/victoria/.psqlrc)

\setenv EDITOR "/usr/bin/nano"

then I can use nano, no problem.

However, if I replace that with

\setenv EDITOR "/usr/bin/nvim"

and chown this postgres directory (to get around a .local/ permissions error that arises)

sudo chown -R postgres:victoria /var/lib/postgres

when I type \e in psql I can edit in nvim.

The issue I have is that as I am in a postgres environment, my user (victoria) ~/.vimrc file (I link my nvim.init file to it) is not being loaded, so I don't have access to my Vim/NeoVim settings.

I guess the question is why sudo -u postgres -i? You can access the
server via psql from your home directory. If you want to work as
postgres user the simplest solution would be to add the .vimrc file to
the postgres user directory.

I tried the nvim -u "<path-to-vimrc>" type statements in my ~/.psqlrc, but that throws an error about not a valid path (again, likely due to the Pg root environment?).

Suggestions? Thank you.

==============================================================================

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian: "... simplest solution would be to add the .vimrc file to the postgres user directory ..."

Good suggestion; I tried that previously, but will try it again. Thanks! :-)

Ok, here is a clumsy solution.

I have this entry in my /home/victoria/.psqlrc file,

\setenv EDITOR "/usr/bin/nvim"

As you see below, I symlink to that file, from postgres.

[victoria@victoria ~]$ sudo -u postgres -i

[postgres@victoria ~]$ pwd

/var/lib/postgres

[postgres@victoria ~]$ ls -la

total 108
drwxrwxr-x 6 postgres victoria 4096 Mar 16 12:48 .
drwxr-xr-x 33 root root 4096 Mar 16 00:00 ..
-rw------- 1 postgres postgres 385 Mar 16 12:49 .bash_history
-rwxr-xr-x 1 root root 806 Mar 16 12:41 .bashrc
drwx------ 2 postgres postgres 4096 Mar 16 12:23 .cache
drwxrwxr-x 2 postgres victoria 4096 Feb 23 13:26 data
drwx------ 3 postgres postgres 4096 Mar 16 12:12 .local
lrwxrwxrwx 1 postgres victoria 62 Feb 23 15:10 .psql_history-postgres -> /mnt/Vancouver/Programming/RDB/postgres/postgres/.psql_history
lrwxrwxrwx 1 postgres victoria 22 Feb 23 14:59 .psqlrc -> /home/victoria/.psqlrc
drwxr-xr-x 2 postgres postgres 4096 Mar 16 12:38 .vim
-rw------- 1 postgres postgres 895 Mar 16 12:48 .viminfo
-rw-r--r-- 1 postgres postgres 68234 Mar 16 12:47 .vimrc

## NOTE: .bashrc and .vimrc are edited COPIES (not symlinks) of /home/victoria/{.bashrc | .vimrc}

[postgres@victoria ~]$ cat /var/lib/postgres/.bashrc

export PSQL_EDITOR="/usr/bin/nvim -u /var/lib/postgres/.vimrc"

## "/var/lib/postgres/.vimrc" is the same as "/home/victoria/.vimrc" EXCEPT
## that I commented out line 77, "execute pathogen#infect(), as that was
## throwing an error when starting nvim (Neovim) as the psql \e external editor.

## Important (slight annoyance: need to load that "postgres" .bashrc file:

[postgres@victoria ~]$ exec bash

[postgres@victoria ~]$ psql

psql (10.2)
Type "help" for help.

[postgres]# \e ## can edit in Neovim, with ~/.vimrc settings, preferences, customizations ...

[postgres]# \q

[postgres@victoria ~]$ exit

exit

[victoria@victoria ~]$

I wasn't able to automatically run the "exec bash" command after starting postgres, hence the need to manually run it in the postgres shell, prior to launching psql.

==============================================================================

On 03/16/2018 01:06 PM, Victoria wrote:

Ok, here is a clumsy solution.

Still not sure why you want to run as the system postgres user. The
system user postgres is not the same as the Postgres database user
postgres. It is just convention that the system user that Postgres runs
as is called postgres. If you want to work in Postgres as the database
user postgres you just need to supply -U postgres to the client(psql in
this case). You can do that from your home account(victoria) without all
the contortions below:)

I have this entry in my /home/victoria/.psqlrc file,

\setenv EDITOR "/usr/bin/nvim"

As you see below, I symlink to that file, from postgres.

[victoria@victoria ~]$ sudo -u postgres -i

[postgres@victoria ~]$ pwd

/var/lib/postgres

[postgres@victoria ~]$ ls -la

total 108
drwxrwxr-x 6 postgres victoria 4096 Mar 16 12:48 .
drwxr-xr-x 33 root root 4096 Mar 16 00:00 ..
-rw------- 1 postgres postgres 385 Mar 16 12:49 .bash_history
-rwxr-xr-x 1 root root 806 Mar 16 12:41 .bashrc
drwx------ 2 postgres postgres 4096 Mar 16 12:23 .cache
drwxrwxr-x 2 postgres victoria 4096 Feb 23 13:26 data
drwx------ 3 postgres postgres 4096 Mar 16 12:12 .local
lrwxrwxrwx 1 postgres victoria 62 Feb 23 15:10 .psql_history-postgres -> /mnt/Vancouver/Programming/RDB/postgres/postgres/.psql_history
lrwxrwxrwx 1 postgres victoria 22 Feb 23 14:59 .psqlrc -> /home/victoria/.psqlrc
drwxr-xr-x 2 postgres postgres 4096 Mar 16 12:38 .vim
-rw------- 1 postgres postgres 895 Mar 16 12:48 .viminfo
-rw-r--r-- 1 postgres postgres 68234 Mar 16 12:47 .vimrc

## NOTE: .bashrc and .vimrc are edited COPIES (not symlinks) of /home/victoria/{.bashrc | .vimrc}

[postgres@victoria ~]$ cat /var/lib/postgres/.bashrc

export PSQL_EDITOR="/usr/bin/nvim -u /var/lib/postgres/.vimrc"

## "/var/lib/postgres/.vimrc" is the same as "/home/victoria/.vimrc" EXCEPT
## that I commented out line 77, "execute pathogen#infect(), as that was
## throwing an error when starting nvim (Neovim) as the psql \e external editor.

## Important (slight annoyance: need to load that "postgres" .bashrc file:

[postgres@victoria ~]$ exec bash

[postgres@victoria ~]$ psql

psql (10.2)
Type "help" for help.

[postgres]# \e ## can edit in Neovim, with ~/.vimrc settings, preferences, customizations ...

[postgres]# \q

[postgres@victoria ~]$ exit

exit

[victoria@victoria ~]$

I wasn't able to automatically run the "exec bash" command after starting postgres, hence the need to manually run it in the postgres shell, prior to launching psql.

==============================================================================

--
Adrian Klaver
adrian.klaver@aklaver.com

@Adrian: Good point!! ;-)

When I re-installed postgres some months ago, I basically followed

https://wiki.archlinux.org/index.php/PostgreSQL

https://serverfault.com/questions/601140/whats-the-difference-between-sudo-su-postgres-and-sudo-u-postgres

HOWEVER, as you note/allude, I tried this:

[victoria@victoria ~]$ pwd

/home/victoria

[victoria@victoria ~]$ createuser -s -U postgres --interactive

Enter name of role to add: victoria
createuser: creation of new role failed: ERROR: role "victoria" already exists

[victoria@victoria ~]$ createdb metab_test

createdb: database creation failed: ERROR: database "metab_test" already exists

[victoria@victoria ~]$ dropdb metab_test
[victoria@victoria ~]$ createdb metab_test

[victoria@victoria ~]$ psql -d metab_test -U victoria

psql (10.2)
Type "help" for help.

[metab_test]# \l
List of databases
┌─────────────┬───────────┬──────────┬─────────────┬─────────────┬───────────────────────┐
│ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │
├─────────────┼───────────┼──────────┼─────────────┼─────────────┼───────────────────────┤
│ metab │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ metab_test │ victoria │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ metabolicdb │ metabolic │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ postgres │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ │
│ template0 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8 │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
└─────────────┴───────────┴──────────┴─────────────┴─────────────┴───────────────────────┘

[metab_test]# \c metab

You are now connected to database "metab" as user "victoria".

[metab]# \d
List of relations
┌────────┬───────────────────────────────┬──────────┬──────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼───────────────────────────────┼──────────┼──────────┤
│ public │ bioent │ table │ postgres │
│ public │ bioent_id_seq │ sequence │ postgres │
│ public │ enzyme_synonyms │ table │ postgres │
│ public │ enzyme_synonyms_id_seq │ sequence │ postgres │
│ public │ glycolysis_enzymes │ table │ postgres │
│ public │ glycolysis_enzymes_id_seq │ sequence │ postgres │
│ public │ glycolysis_metabolites │ table │ postgres │
│ public │ glycolysis_metabolites_id_seq │ sequence │ postgres │
│ public │ metabolite_synonyms │ table │ postgres │
│ public │ metabolite_synonyms_id_seq │ sequence │ postgres │
│ public │ metabolites │ table │ victoria │
│ public │ metabolites_id_seq │ sequence │ victoria │
│ public │ relations │ table │ postgres │
│ public │ relations_id_seq │ sequence │ postgres │
└────────┴───────────────────────────────┴──────────┴──────────┘

[metab]# \e ## launches external editor (neovim), c. victoria's settings ...

[metab]# \q

[victoria@victoria ~]$

Of course (in Arch Linux), the postgres service enabled and running:

sudo systemctl enable postgresql
sudo systemctl enable postgresql

Thank you very much for your comment; this greatly simplifies things!

I guess I just needed a nudge in that direction.

Much appreciated! :-D

==============================================================================

----- Original Message(s): -----
Date: 2018 Mar 16 (Fri) 13:33
From: Adrian ...
Subject: Re: Nvim as external editor in psql as Postgres root user - .vimrc (environment?) issue

On 03/16/2018 01:06 PM, Victoria wrote:

Ok, here is a clumsy solution.

Still not sure why you want to run as the system postgres user. The
system user postgres is not the same as the Postgres database user
postgres. It is just convention that the system user that Postgres runs
as is called postgres. If you want to work in Postgres as the database
user postgres you just need to supply -U postgres to the client(psql in
this case). You can do that from your home account(victoria) without all
the contortions below:)

[snip: my previous post / "solution"]

--
Adrian Klaver
adrian.klaver@aklaver.com