Thread: No select privileges when not connecting from login postgres
I'm trying to get a new build of 9.6.1 working on a machine
running Debian stable (jessie) and I'm seeing some odd
behavior where things work correctly if I run psql when
logged in as postgres, but if I run it as user 'doom' (my
usual login), I don't seem to have any select privileges.
Even this fails silenlty:
select 'world' as hello;
But if run logged in as postgres, all is well:
sudo su - postgres
/usr/local/pgsql/bin/psql --dbname=doom --username=doom
doom=# select 'world' as hello;
select 'world' as hello;
hello
-------
world
(1 row)
Note that I'm talking about the unix logins, in both cases
the postgresql username/role is 'doom' (which has Superuser
privileges and is the owner of the 'doom' database).
Looking at how the program files are installed, they're all
owned by 'root' with group 'staff':
ls -la /usr/local/pgsql/bin/psql
-rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql
So I added doom to the staff group and reloaded pg, but that
didn't help either. The files in the data tree are all
owned by postgres, but I don't think that's unusual:
drwx------ 1 postgres postgres 42 Nov 26 16:14 base
I'm running out of ideas for things to check. Any suggestions?
On Wed, Nov 30, 2016 at 08:48:41PM -0800, Joseph Brenner wrote: > I'm trying to get a new build of 9.6.1 working on a machine > running Debian stable (jessie) and I'm seeing some odd > behavior where things work correctly if I run psql when > logged in as postgres, but if I run it as user 'doom' (my > usual login), I don't seem to have any select privileges. > Even this fails silenlty: > > select 'world' as hello; > > But if run logged in as postgres, all is well: > > sudo su - postgres > /usr/local/pgsql/bin/psql --dbname=doom --username=doom > doom=# select 'world' as hello; > select 'world' as hello; > hello > ------- > world > (1 row) > [...] > I'm running out of ideas for things to check. Any suggestions? > Any unusual errors in the logs? Or maybe a "\o /somefile" in your ~doom/.psqlrc? -- Julien Rouhaud http://dalibo.com - http://dalibo.org
On 11/30/2016 08:48 PM, Joseph Brenner wrote: > I'm trying to get a new build of 9.6.1 working on a machine > running Debian stable (jessie) and I'm seeing some odd > behavior where things work correctly if I run psql when > logged in as postgres, but if I run it as user 'doom' (my > usual login), I don't seem to have any select privileges. > Even this fails silenlty: > > select 'world' as hello; > > But if run logged in as postgres, all is well: > > sudo su - postgres > /usr/local/pgsql/bin/psql --dbname=doom --username=doom > doom=# select 'world' as hello; > select 'world' as hello; > hello > ------- > world > (1 row) > > Note that I'm talking about the unix logins, in both cases > the postgresql username/role is 'doom' (which has Superuser > privileges and is the owner of the 'doom' database). > > Looking at how the program files are installed, they're all > owned by 'root' with group 'staff': > > ls -la /usr/local/pgsql/bin/psql > -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql > > So I added doom to the staff group and reloaded pg, but that > didn't help either. The files in the data tree are all > owned by postgres, but I don't think that's unusual: > > drwx------ 1 postgres postgres 42 Nov 26 16:14 base > > I'm running out of ideas for things to check. Any suggestions? When you are logged in as OS user 'doom', the case that is not working, what does: psql -V show? Is the psql connection the exact same as the one you show for the postgres OS user case? > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote: > I'm trying to get a new build of 9.6.1 working on a machine > running Debian stable (jessie) and I'm seeing some odd > behavior where things work correctly if I run psql when > logged in as postgres, but if I run it as user 'doom' (my > usual login), I don't seem to have any select privileges. > Even this fails silenlty: > > select 'world' as hello; > > But if run logged in as postgres, all is well: > > sudo su - postgres > /usr/local/pgsql/bin/psql --dbname=doom --username=doom > doom=# select 'world' as hello; > select 'world' as hello; > hello > ------- > world > (1 row) > > I run Debian testing (stretch/sid). For years now, the Postgres binaries are stored in:- /usr/lib/postgresql/<major version>/bin and are root:root owned. E.g.:- /usr/lib/postgresql/9.6/bin/psql appears as:- -rwxr-xr-x 1 root root 580864 Nov 2 21:04 psql So, it is executable by anybody, which is not an issue with me. The reason behind this path convention is to enable you to run different major versions of Postgres on the same computer. I don't know how you installed Postgres and all I can suggest for starters is to physically remove it and re-install from the Debian packages. HTH, Rob
Logged in as "doom", -V shows version 9.6.1 as I'd expect: /usr/local/pgsql/bin/psql -V psql (PostgreSQL) 9.6.1 To *literally* answer your question though, I have a different version installed in my $PATH: psql -V psql (PostgreSQL) 9.4.9 But yes, I've been invoking psql the same way as login 'doom' or login 'postgres': /usr/local/pgsql/bin/psql --dbname=doom --username=doom On Sat, Dec 3, 2016 at 7:10 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 11/30/2016 08:48 PM, Joseph Brenner wrote: >> >> I'm trying to get a new build of 9.6.1 working on a machine >> running Debian stable (jessie) and I'm seeing some odd >> behavior where things work correctly if I run psql when >> logged in as postgres, but if I run it as user 'doom' (my >> usual login), I don't seem to have any select privileges. >> Even this fails silenlty: >> >> select 'world' as hello; >> >> But if run logged in as postgres, all is well: >> >> sudo su - postgres >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom >> doom=# select 'world' as hello; >> select 'world' as hello; >> hello >> ------- >> world >> (1 row) >> >> Note that I'm talking about the unix logins, in both cases >> the postgresql username/role is 'doom' (which has Superuser >> privileges and is the owner of the 'doom' database). >> >> Looking at how the program files are installed, they're all >> owned by 'root' with group 'staff': >> >> ls -la /usr/local/pgsql/bin/psql >> -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql >> >> So I added doom to the staff group and reloaded pg, but that >> didn't help either. The files in the data tree are all >> owned by postgres, but I don't think that's unusual: >> >> drwx------ 1 postgres postgres 42 Nov 26 16:14 base >> >> I'm running out of ideas for things to check. Any suggestions? > > > When you are logged in as OS user 'doom', the case that is not working, what > does: > > psql -V > > show? > > Is the psql connection the exact same as the one you show for the postgres > OS user case? > > > > >> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
The version in the Debian stable repos right now is 9.4, and I saw an issue with it I wanted to check against the latest version, so I did a build of it from a tarball. (Admittedly, there's no particular reason I need to be running stable, and I was just wondering the other day why I wasn't using testing or unstable). The build steps I used are relatively conventional (following the directions in the INSTALL file): this is how I ended up with an installation in /usr/local. The only thing unusual about the steps that I followed was I built with port 5433 (rather than 5432) as the default, to make it easier to run it in parallel with the system version, and I'm trying to use the "with-perl" build option to link to a fresh build of perl: sudo su export LD_LIBRARY_PATH='/home/doom/End/Sys/Perl5/perl-5.24.0' ldconfig ./configure --with-pgport=5433 --with-perl --with-openssl make make install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data mkdir /var/lib/postgresql-9.6.1/ chown postgres /var/lib/postgresql-9.6.1/ su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > /var/lib/postgresql-9.6.1/logfile 2>&1 & On Sat, Dec 3, 2016 at 7:23 AM, rob stone <floriparob@gmail.com> wrote: > > On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote: >> I'm trying to get a new build of 9.6.1 working on a machine >> running Debian stable (jessie) and I'm seeing some odd >> behavior where things work correctly if I run psql when >> logged in as postgres, but if I run it as user 'doom' (my >> usual login), I don't seem to have any select privileges. >> Even this fails silenlty: >> >> select 'world' as hello; >> >> But if run logged in as postgres, all is well: >> >> sudo su - postgres >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom >> doom=# select 'world' as hello; >> select 'world' as hello; >> hello >> ------- >> world >> (1 row) >> >> > > I run Debian testing (stretch/sid). > > For years now, the Postgres binaries are stored in:- > > /usr/lib/postgresql/<major version>/bin > > and are root:root owned. > > E.g.:- > > /usr/lib/postgresql/9.6/bin/psql appears as:- > > -rwxr-xr-x 1 root root 580864 Nov 2 21:04 psql > > So, it is executable by anybody, which is not an issue with me. > > The reason behind this path convention is to enable you to run > different major versions of Postgres on the same computer. > > I don't know how you installed Postgres and all I can suggest for > starters is to physically remove it and re-install from the Debian > packages. > > HTH, > Rob
> Any unusual errors in the logs? Or maybe a "\o /somefile" in your ~doom/.psqlrc? No, nothing much in the logs after "autovacuum launcher started", and I don't have a .psqlrc file. On Sat, Dec 3, 2016 at 6:56 AM, Julien Rouhaud <julien.rouhaud@dalibo.com> wrote: > On Wed, Nov 30, 2016 at 08:48:41PM -0800, Joseph Brenner wrote: >> I'm trying to get a new build of 9.6.1 working on a machine >> running Debian stable (jessie) and I'm seeing some odd >> behavior where things work correctly if I run psql when >> logged in as postgres, but if I run it as user 'doom' (my >> usual login), I don't seem to have any select privileges. >> Even this fails silenlty: >> >> select 'world' as hello; >> >> But if run logged in as postgres, all is well: >> >> sudo su - postgres >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom >> doom=# select 'world' as hello; >> select 'world' as hello; >> hello >> ------- >> world >> (1 row) >> [...] >> I'm running out of ideas for things to check. Any suggestions? >> > > Any unusual errors in the logs? Or maybe a "\o /somefile" in your > ~doom/.psqlrc? > > -- > Julien Rouhaud > http://dalibo.com - http://dalibo.org
On 12/03/2016 02:55 PM, Joseph Brenner wrote: > The version in the Debian stable repos right now is 9.4, and I saw an > issue with it I wanted to check against the latest version, so I did a > build of it from a tarball. Save yourself some trouble and use the PGDG repos: https://www.postgresql.org/download/linux/debian/ PostgreSQL Apt Repository > > (Admittedly, there's no particular reason I need to be running stable, > and I was just wondering the other day why I wasn't using testing or > unstable). > > The build steps I used are relatively conventional (following the > directions in the INSTALL file): this is how I ended up with an > installation in /usr/local. > > The only thing unusual about the steps that I followed was I built > with port 5433 (rather than 5432) as the default, to make it easier to > run it in parallel with the system version, and I'm trying to use the > "with-perl" build option to link to a fresh build of perl: In your psql commands you do not show connecting to port 5433 > > sudo su > export LD_LIBRARY_PATH='/home/doom/End/Sys/Perl5/perl-5.24.0' > ldconfig > ./configure --with-pgport=5433 --with-perl --with-openssl > > make > make install > adduser postgres > mkdir /usr/local/pgsql/data > chown postgres /usr/local/pgsql/data > mkdir /var/lib/postgresql-9.6.1/ > chown postgres /var/lib/postgresql-9.6.1/ > su - postgres > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data > > /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > > /var/lib/postgresql-9.6.1/logfile 2>&1 & > > > On Sat, Dec 3, 2016 at 7:23 AM, rob stone <floriparob@gmail.com> wrote: >> >> On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote: >>> I'm trying to get a new build of 9.6.1 working on a machine >>> running Debian stable (jessie) and I'm seeing some odd >>> behavior where things work correctly if I run psql when >>> logged in as postgres, but if I run it as user 'doom' (my >>> usual login), I don't seem to have any select privileges. >>> Even this fails silenlty: >>> >>> select 'world' as hello; >>> >>> But if run logged in as postgres, all is well: >>> >>> sudo su - postgres >>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom >>> doom=# select 'world' as hello; >>> select 'world' as hello; >>> hello >>> ------- >>> world >>> (1 row) >>> >>> >> >> I run Debian testing (stretch/sid). >> >> For years now, the Postgres binaries are stored in:- >> >> /usr/lib/postgresql/<major version>/bin >> >> and are root:root owned. >> >> E.g.:- >> >> /usr/lib/postgresql/9.6/bin/psql appears as:- >> >> -rwxr-xr-x 1 root root 580864 Nov 2 21:04 psql >> >> So, it is executable by anybody, which is not an issue with me. >> >> The reason behind this path convention is to enable you to run >> different major versions of Postgres on the same computer. >> >> I don't know how you installed Postgres and all I can suggest for >> starters is to physically remove it and re-install from the Debian >> packages. >> >> HTH, >> Rob > > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/12/16 22:55, Joseph Brenner wrote: > The version in the Debian stable repos right now is 9.4, and I saw an > issue with it I wanted to check against the latest version, so I did a > build of it from a tarball. You can get the latest version from the PostgreSQL apt repo: http://wiki.postgresql.org/wiki/Apt Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
> Save yourself some trouble and use the PGDG repos... Yes, I was just trying one of those a little while ago. It exhibits the exact same behavior as my build from scratch. > In your psql commands you do not show connecting to port 5433 That's correct: my reading of the installation instructions is that I'm not supposed to have to do that: if you feed an alternate port number to configure, it's supposed to change the default for both server and client. There was at least one experiment though where I started both server and client with the usual port, 5432-- my feeling is that can't be the problem, but I'll probably look into some more (Tom Lane seems to feel there might be an issue there). On Sat, Dec 3, 2016 at 4:46 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 12/03/2016 02:55 PM, Joseph Brenner wrote: >> >> The version in the Debian stable repos right now is 9.4, and I saw an >> issue with it I wanted to check against the latest version, so I did a >> build of it from a tarball. > > > Save yourself some trouble and use the PGDG repos: > > https://www.postgresql.org/download/linux/debian/ > > PostgreSQL Apt Repository > >> >> (Admittedly, there's no particular reason I need to be running stable, >> and I was just wondering the other day why I wasn't using testing or >> unstable). >> >> The build steps I used are relatively conventional (following the >> directions in the INSTALL file): this is how I ended up with an >> installation in /usr/local. >> >> The only thing unusual about the steps that I followed was I built >> with port 5433 (rather than 5432) as the default, to make it easier to >> run it in parallel with the system version, and I'm trying to use the >> "with-perl" build option to link to a fresh build of perl: > > > In your psql commands you do not show connecting to port 5433 > > >> >> sudo su >> export LD_LIBRARY_PATH='/home/doom/End/Sys/Perl5/perl-5.24.0' >> ldconfig >> ./configure --with-pgport=5433 --with-perl --with-openssl >> >> make >> make install >> adduser postgres >> mkdir /usr/local/pgsql/data >> chown postgres /usr/local/pgsql/data >> mkdir /var/lib/postgresql-9.6.1/ >> chown postgres /var/lib/postgresql-9.6.1/ >> su - postgres >> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data >> >> /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > >> /var/lib/postgresql-9.6.1/logfile 2>&1 & >> >> >> On Sat, Dec 3, 2016 at 7:23 AM, rob stone <floriparob@gmail.com> wrote: >>> >>> >>> On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote: >>>> >>>> I'm trying to get a new build of 9.6.1 working on a machine >>>> running Debian stable (jessie) and I'm seeing some odd >>>> behavior where things work correctly if I run psql when >>>> logged in as postgres, but if I run it as user 'doom' (my >>>> usual login), I don't seem to have any select privileges. >>>> Even this fails silenlty: >>>> >>>> select 'world' as hello; >>>> >>>> But if run logged in as postgres, all is well: >>>> >>>> sudo su - postgres >>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom >>>> doom=# select 'world' as hello; >>>> select 'world' as hello; >>>> hello >>>> ------- >>>> world >>>> (1 row) >>>> >>>> >>> >>> I run Debian testing (stretch/sid). >>> >>> For years now, the Postgres binaries are stored in:- >>> >>> /usr/lib/postgresql/<major version>/bin >>> >>> and are root:root owned. >>> >>> E.g.:- >>> >>> /usr/lib/postgresql/9.6/bin/psql appears as:- >>> >>> -rwxr-xr-x 1 root root 580864 Nov 2 21:04 psql >>> >>> So, it is executable by anybody, which is not an issue with me. >>> >>> The reason behind this path convention is to enable you to run >>> different major versions of Postgres on the same computer. >>> >>> I don't know how you installed Postgres and all I can suggest for >>> starters is to physically remove it and re-install from the Debian >>> packages. >>> >>> HTH, >>> Rob >> >> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com