Re: No select privileges when not connecting from login postgres

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