James ,
Seems you make a number of strange moves.
Move 1. You create pg-user "openxpki" at pg-server without a password.
Even if later you configure openxpki server with some password for this
pg-user, ANY password (including empty password) will be accepted for
any operation on behalf of this pg-user, which seems likea security hole.
Move 2. You load a schema (thus creating tables) as a pg-superuser
(postgres); later you try to access these tables as a regular pg-user
openxpki, which should not work.
Move 3. When you run "sudo --user=openxpki openxpkiadm ...", note then
here you refer to a system-user, not to pg-user of the same name.
Your particular error should be gone if you import schema as pg-user
"openxpki":
psql --username openxpki openxpki <
/usr/local/share/examples/openxpki/config/contrib/sql/schema-psql.sql
But further revision of your moves is recommended.
Regards, Sergei
On 6 Feb 24 Tue 21:21, James B. Byrne via OpenXPKI-users wrote:
PostgreSQL-16
FreeBSd-13.2p9
I am trying to setup openxpki using PostgreSQL as the data store. After
installing both postgresql16 and openxpki I completed the following steps
successfully using psql:
psql -U postgres -d postgres
CREATE USER openxpki;
CREATE DATABASE openxpki;
GRANT ALL PRIVILEGES ON DATABASE openxpki TO openxpki;
I altered config.d/system/database.yaml
type: PostgreSQL
name: openxpki
user: openxpki
passwd: openxpki
I checked for local connections in /var/db/postgres/data16/pg_hba.conf:
local all all trust
I successfully loaded the openxpki schema for postgresql:
psql --username postgres openxpki <
/usr/local/share/examples/openxpki/config/contrib/sql/schema-psql.sql
I created the vault key and certificate and moved them to
/usr/local/etc/openxpki/local/keys.
I then tried to load these into openxpki using openxpkiadm. This fails with a
databse permissions error:
sudo --user=openxpki openxpkiadm certificate import --file
/usr/local/etc/openxpki/local/keys/vault.crt
try/catch is experimental at
/usr/local/lib/perl5/site_perl/OpenXPKI/Server/Init.pm line 103.
try/catch is experimental at
/usr/local/lib/perl5/site_perl/OpenXPKI/Server/Init.pm line 107.
Starting import
2024/02/06 13:20:33 Database error: execution of SQL query failed;
__dbi_error__ => ERROR: permission denied for table certificate, __dsn__ =>
dbi:Pg:database=openxpki;sslmode=allow, __query__ => SELECT identifier,
pki_realm, status, req_key FROM certificate WHERE ( identifier = ? ) LIMIT ?
OFFSET ?, __source__ => DBD::Pg::st::execute, __user__ => openxpki
Database error: execution of SQL query failed
__dsn__: dbi:Pg:database=openxpki;sslmode=allow
__dbi_error__: ERROR: permission denied for table certificate
__user__: openxpki
__source__: DBD::Pg::st::execute
__query__: SELECT identifier, pki_realm, status, req_key FROM certificate
WHERE ( identifier = ? ) LIMIT ? OFFSET ?
It also fails for both root and postgres users.
What step have I missed?
_______________________________________________
OpenXPKI-users mailing list
OpenXPKI-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openxpki-users
