Sorry, real newbie question about connecting to a database

stan <stanb@panix.com> writes:

But I cannot conect, or swith to user
employee:

stan@smokey:/etc/postgresql/11/main$ psql -U employee
psql: FATAL: Peer authentication failed for user "employee"

This means that you've set pg_hba.conf to specify "peer" authentication,
which by default only lets you connect as a PG role named the
same as your OS account. (This is about are-you-who-you-say-you-are;
whether who-you-say-you-are has privilege to connect is independent.)

You could use some other auth method, or you could set up a mapping
file that says you (stan) are allowed to connect as "employee".

regards, tom lane

On 8/19/19 7:44 AM, stan wrote:

I am developinng an appliction usig Postgresql 11, installed on a UBUTU
18.14 machine.

I ahve vreated a new database to do some testing on restricting access of
specific users/roles to certain data. I have done the following:

REVOKE ALL ON DATABASE pertest FROM employee;
GRANT CONNECT ON DATABASE pertest TO employee;

and I have verifed tht the user employee does exst, I have also doen a few
more GRABTs to allow specific acces. But I cannot conect, or swith to user
employee:

stan@smokey:/etc/postgresql/11/main$ psql -U employee
psql: FATAL: Peer authentication failed for user "employee"

stan=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------+----------+----------+---------+---------+-----------------------
pertest | stan | UTF8 | C.UTF-8
| C.UTF-8 | =Tc/stan +
| | stan=CTc/stan

employee=CTc/stan

Sorrry cut and paste mangled that.

What am I failing to do here?

Tom has spelled out the specific issue. The generic issue is that
security in Postgres is a multi-layer process that involves many moving
parts. You will save yourself a lot of do overs by looking at the
relevant documentation. Starting roughly from outside in:

Server connection:

https://www.postgresql.org/docs/11/runtime-config-connection.html

Client authentication(the pg_hba.conf Tom referred to):

https://www.postgresql.org/docs/11/client-authentication.html

Database roles(users):

https://www.postgresql.org/docs/11/user-manag.html

Role/user permissions:

https://www.postgresql.org/docs/11/sql-grant.html

Finer grained permissions(row level security):

https://www.postgresql.org/docs/11/ddl-rowsecurity.html

The above is intimidating and not something that will be fully
understood in a single reading(or in my case multiple readings:)). Still
a passing familiarity with the concepts will make your life easier.

--
Adrian Klaver
adrian.klaver@aklaver.com