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@xxxxxxxxxxx
